Results 1 to 10 of 10

Thread: Update query

  1. #1
    suresh09 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Apr 2009
    Posts
    10
    Reputation
    64

    Update query

    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.

  2. #2
    kulrom's Avatar
    kulrom is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 3.5
    Join Date
    May 2005
    Location
    Republic of Macedonia
    Posts
    2,855
    Reputation
    259
    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:
    Code:
    updateSql = "UPDATE student  SET name = '" & Me.TextBox2.Text & "' WHERE User_id =" & Me.TextBox1.Text & ""
    cmd = New SqlCommand(updateSql, cn)
    cmd.ExecuteNonQuery()

  3. #3
    suresh09 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Apr 2009
    Posts
    10
    Reputation
    64
    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).

  4. #4
    suresh09 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Apr 2009
    Posts
    10
    Reputation
    64
    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).

  5. #5
    kulrom's Avatar
    kulrom is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 3.5
    Join Date
    May 2005
    Location
    Republic of Macedonia
    Posts
    2,855
    Reputation
    259
    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?

  6. #6
    suresh09 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Apr 2009
    Posts
    10
    Reputation
    64
    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.

  7. #7
    Tom
    Tom is offline VB.NET Forum Idol
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Aug 2005
    Posts
    746
    Reputation
    385
    Quote Originally Posted by suresh09 View Post
    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)

  8. #8
    suresh09 is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Apr 2009
    Posts
    10
    Reputation
    64
    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.
    Attached Files Attached Files

  9. #9
    kulrom's Avatar
    kulrom is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 3.5
    Join Date
    May 2005
    Location
    Republic of Macedonia
    Posts
    2,855
    Reputation
    259
    s = "select * from student WHERE User_id ='" & Me.TextBox1.Text & "'"

  10. #10
    Tom
    Tom is offline VB.NET Forum Idol
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Aug 2005
    Posts
    746
    Reputation
    385
    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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Harvest time tracking