Re: I connect to Oracle through java code on the sam e machine, what to change to connect from another

Try to commit the delete first before you do the insert.

-----Original Message-----
From: loushy G [mailto:[email protected]]
Sent: Wednesday, January 15, 2003 2:30 AM
To: Oracle
Subject: [oracle] Re: I connect to Oracle through java code on the same
machine, what to change to connect from another


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



---
Professional Design Patterns in VB.NET: 
Building Adaptable Applications

Want to know how design patterns bring reusable 
design and adaptabilty to your applications? How 
to recognize the need for a design pattern 
solution? How to select, design, and implement 
the right patterns? How parts of the .NET Framework 
(like the .NET Data Providers and .NET Remoting) 
take advantage of design patterns? This book 
presents a practical approach to using design 
patterns in VB.NET, by focusing on the relevance 
of design patterns in the different tiers of a 
distributed n-tier architecture. 

http://www.wrox.com/books/1861006985.htm

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].