p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

aspdotnet_website_programming thread: updating records with asp.net using vb.net


Message #1 by elainedeclaro@y... on Wed, 15 Jan 2003 07:44:25
I am trying to write an Edit My Registration Info page for my project. 
What I did was to delete the record of the currently logged in user from 
the users table of the database and then insert it again using the current 
values in the form that the person submitted.  For some reason, the 
information is not updated.  I checked the delete code and it works. I 
checked the insert code and it works. But if I put them together, the 
information is not updated.  Here is the code:

First, I read the database to select the details for that particular user 
and put them into the corresponding text boxes.

Sub ReadData()
	objConnection = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0; " & _
			"data source=" & Server.MapPath
("\dsws\db\DSWS2K.mdb"))

	Dim strSQL as string = "SELECT	* FROM Students " & _
							"WHERE IDNo = '" & 
Request.Cookies("IDNo").Value & "'"
	
	Dim myCommand As New OleDbCommand(strSQL, objConnection)
    Dim myReader As OleDbDataReader
    
    objConnection.Open()
    myReader = myCommand.ExecuteReader()
    ' Always call Read before accessing data.
    While myReader.Read()
        lblIDNo.Text =  myReader.GetString(0)
        lblName.Text =  myReader.GetString(1)
        txtEmail.Text = myReader.GetString(2)
        ddlYear.SelectedIndex = myReader.GetInt32(3) - 1
        txtCourse.Text = myReader.GetString(4)
        txtAddress.Text = myReader.GetString(5)
        txtPhone.Text = myReader.GetString(6)
        txtMobile.Text = myReader.GetString(7)
        
    End While
    ' always call Close when done reading.
    myReader.Close()
    ' Close the connection when done with it.
    objConnection.Close()

End Sub

When the user clicks on the submit button, I call the delete and insert 
statements.

		Dim strSQL as string = "INSERT INTO Students " & _
								"(IDNo, 
Name, Email, YearLevel, Course, Address, PhoneNo, MobileNo, Pwd) VALUES 
(?, ?, ?, ?, ?, ?, ?, ?, ?)"
		Dim strSQL2 as string = "DELETE FROM Students WHERE (IDNo 
= ?)"
								
								
		Dim dbComm as new OleDbCommand(strSQL, objConnection)
		dbComm.Parameters.Add("IDNo", OleDbType.VarChar, 50, 
"IDNo")
		dbComm.Parameters.Add("Name", OleDbType.VarChar, 50, 
"Name")
		dbComm.Parameters.Add("Email", OleDbType.VarChar, 50, 
"Email")
		dbComm.Parameters.Add("prmYear", OleDbType.Integer, 50, 
"YearLevel")
		dbComm.Parameters.Add("Course", OleDbType.VarChar, 50, 
"Course")
        dbComm.Parameters.Add("Address", OleDbType.VarChar, 100, "Address")
        dbComm.Parameters.Add("prmPhoneNo", OleDbType.VarChar, 50, 
"PhoneNo")
        dbComm.Parameters.Add("prmMobileNo", OleDbType.VarChar, 50, 
"MobileNo")
        dbComm.Parameters.Add("prmPwd", OleDbType.VarChar, 50, "Pwd")

        dbComm.Parameters("IDNo").Value = Request.Cookies("IDNo").Value
		dbComm.Parameters("Name").Value = Request.Cookies
("Name").Value
		dbComm.Parameters("Email").Value = txtEmail.Text
        dbComm.Parameters("prmYear").Value = CInt(CStr
(ddlYear.SelectedItem.Value))
        dbComm.Parameters("Course").Value = txtCourse.Text
		dbComm.Parameters("Address").Value = txtAddress.Text
        dbComm.Parameters("prmPhoneNo").Value = txtPhone.Text
        dbComm.Parameters("prmMobileNo").Value = txtMobile.Text
        dbComm.Parameters("prmPwd").Value = CStr(txtPwd.Text)
		
		Dim dbComm2 as new OleDbCommand(strSQL2, objConnection)
		dbComm2.Parameters.Add("IDNo", OleDbType.VarChar, 50, 
"IDNo")
		dbComm2.Parameters("IDNo").Value = Request.Cookies
("IDNo").Value
		
		Try
			objConnection.Open()
            dbComm2.ExecuteNonQuery() 'Delete first
            Response.Write("finished delete")
			dbComm.ExecuteNonQuery()  'Insert new record in 
lieu of update
			Response.Write("finished insert")
			ReadData()
			Response.Redirect("Services.aspx")
		Catch ex as Exception
			Response.Write(ex.Message)
			Response.End()
		Finally
			If objConnection.State = ConnectionState.Open Then
				objConnection.Close()
            End If
		End Try
	End If
End Sub



Message #2 by "Mike Gale" <info@d...> on Thu, 16 Jan 2003 08:18:20 +1300
elainedeclaro@y... wrote:
> I am trying to write an Edit My Registration Info page for my project.
> What I did was to delete the record of the currently logged in user
> from the users table of the database and then insert it again using
> the current values in the form that the person submitted.  For some
> reason, the information is not updated.  I checked the delete code
> and it works. I checked the insert code and it works. But if I put
> them together, the information is not updated.  Here is the code:

In this situation I would advise UPDATING the database rather than a
DELETE+INSERT approach.

Mike Gale, Decision Engineering (NZ) Ltd.

CAUTION - This message may contain privileged and confidential
information intended only for the use of the addressee/s. 

If you are not the intended recipient of this message you are hereby
notified that any use, dissemination, distribution or reproduction of
this message is prohibited without prior written consent.  Any views
expressed in this message may not reflect the views of Decision
Engineering.    





  Return to Index