Update query

[suresh09]

I have an sqlserver 2000 database and i am able to add records and read from it but i cant modify existing records. My test table just hav user_id,Name, , Phone and State for columns.


updateSql = "UPDATE student " & _
"SET name = '" & Me.TextBox2.Text & "' " & _
"WHERE User_id = '" + Me.TextBox1.Text + "'"
cmd = New SqlCommand(updateSql, cn)
cmd.ExecuteNonQuery()

my problem is the statement was executing,but the database not changed.
I cant get my update command to actually do anything.
Please help
thanks in advance.

 

[kulrom]

I guess you don't need single quotes around numeric values.
Also you should use & character (operator) to concatenate two strings in VB.NET

Therefore your code might look like following:

updateSql = "UPDATE student  SET name = '" & Me.TextBox2.Text & "' WHERE User_id =" [B]&[/B] Me.TextBox1.Text [B]&[/B] ""
cmd = New SqlCommand(updateSql, cn)
cmd.ExecuteNonQuery()

 

[suresh09]

still my database was not changed.

z = "UPDATE student SET Lastname = '" & Me.TextBox3.Text & "' WHERE User_id ='" & Me.TextBox1.Text & "'"

cmd = New SqlCommand(z, cn)
cmd.ExecuteNonQuery()

user_id(varchar),name (char).

 

[suresh09]

still my database was not changed.

z = "UPDATE student SET Lastname = '" & Me.TextBox3.Text & "' WHERE User_id ='" & Me.TextBox1.Text & "'"

cmd = New SqlCommand(z, cn)
cmd.ExecuteNonQuery()

user_id(varchar),name (char).

 

[kulrom]

Do you get an error message or just nothing happens while your database is still not updated.

I don't see that you did anything wrong but, also i don't see that you call Open and Close methods. cn.Open() and cn.Close()

Also is it possible that TextBox1.Text doesn't match any user_id record?

 

[suresh09]

this is my code
cn.Open()

Dim z, s, ID As String
z = "UPDATE student SET Lastname = '" & Me.TextBox3.Text & "' WHERE User_id ='" & Me.TextBox1.Text & "'"

cmd = New SqlCommand(z, cn)
cmd.ExecuteNonQuery()
MsgBox("Profile updated successfully")
cn.Close()

in page load fn i hav code for view the profile, there is no error for matchng correct userid,oly d problem is update.

 

[Tom]

z = "UPDATE student SET Lastname = '" & Me.TextBox3.Text & "' WHERE User_id ='" & Me.TextBox1.Text & "'"

cmd = New SqlCommand(z, cn)
cmd.ExecuteNonQuery()
user_id(varchar),name (char).

You mention a column named "Name" but nothing in your query uses that...

Also the sqlCommand.ExecuteNonQuery provides a return value of the number of records affected by your statement, try using it to see if its affecting any records or not.

Dim intNumberOfRecordsUpdated as Integer
intNumberOfRecordsUpdated = cmd.ExecuteNonQuery
MessageBox.Show("Records Updated = " & intNumberOfRecordsUpdated.ToString)

 

[suresh09]

my database

User_id Firstname Lastname
------------------------------------------------
s1 suresh kumar
s2 Gokul Ganesh

update code:

cn.Open()

Dim z, s, ID As String
'z = "update student set [Lastname] =' " + Me.TextBox2.Text + "' where [User_Id]='" + Me.TextBox3.Text + "'"

z = "UPDATE student SET Lastname = '" & Me.TextBox3.Text & "' WHERE User_id ='" & Me.TextBox1.Text & "'"

cmd = New SqlCommand(z, cn)
Dim intNumberOfRecordsUpdated As Integer
intNumberOfRecordsUpdated = cmd.ExecuteNonQuery
MsgBox("Records Updated = " & intNumberOfRecordsUpdated.ToString)
MsgBox("Profile updated successfully")

it display 1 row affected & profile updated successfully

checking for update:

s = "select * from student"
cmd = New SqlCommand(s, cn)
sdr = cmd.ExecuteReader()
sdr.Read()
ID = sdr.GetString(2)
MsgBox(ID)
MsgBox("Profile updated successfully")
cn.Close()

but it show the data previously(before update) stored.

 

[kulrom]

s = "select * from student WHERE User_id ='" & Me.TextBox1.Text & "'"

 

[Tom]

First I dont know what values are being passed from your text box at the time. Second do ya see the importance of naming your textboxes and variable more meaningfull yet... Third are you going into Sql Server and looking into the table itself to confirm the records are being updated and with what new values.

Your msgbox meand nothing except that you didnt get an error, so disregard that for the moment. The rows effected at least tell you that something did update.... with what values I dont know.

I would suggest the following,
01) place break points right after each place you assign your update statement, copy & paste what "s" is so we can see what values are in your updates

02) confirm whats going on in the actual database

03) make sure your select statement is using the same id number as your update and that there is not more then one record with the same id