a problem in sql update via .net

[rockers123]

hi. now im currently developing an application as my semester project and i strucked becuse of an small error. i have tried to update a record set in the sql server 2005 databse table by giving the following code. but its not showing any errors its working and displaying a message succeeded. but in database table i cant find any changes. pls help me........

my advance thanks for those who viewing my thread.

Public Shared ReadOnly SQLConnectionString As String = "Server=RUSHAN\SQLEXPRESS;Database=nolimit;Trusted_Connection=True;"
    
Dim cnn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim bc As SqlClient.SqlDataAdapter
Dim dr As SqlClient.SqlDataReader
 
Private Sub cus_mod_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cus_mod_btn.Click
        Dim sql2 As String = "UPDATE customer SET name='" & cus_mod_nme.Text & "', address='" & cus_mod_add.Text & "' ,contact='" & cus_mod_con.Text & "' WHERE (id='" & cus_mod_id.SelectedIndex & "') "
        cnn = New SqlClient.SqlConnection(SQLConnectionString)
 
        'cnn = New SqlClient.SqlConnection(SQLConnectionString)
        cnn.Open()
 
        cmd = New SqlClient.SqlCommand(sql2, cnn)
 
        cmd.ExecuteNonQuery()
        MsgBox("Records affected")
        cnn.Close()
    End Sub

[stlaural]

Wellcome to the forums!

You should display the value of your string sql2 and make sure your statement is OK and that the values are right.

I also noticed that you are using cus_mod_id.SelectedIndex for the ID value, are you sure that cus_mod_id.SelectedIndex really return the proper value for the ID field? I mean, if cus_mod_id is a Listbox or something like that it doesn't mean that the SelectedIndex really correspond to the ID of the record in your database.

[cicatrix]

Also, your code is insecure for SQL injections. It's probably all right with a learning project but bad habits must be broken right from the beginning. Use parametrized queries like this, not only it is more secure, but also more readable as well:

Private Sub cus_mod_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cus_mod_btn.Click
 
    Dim sql2 As String = "UPDATE customer SET name=@p_name, address=@p_address, contact=@p_contact  WHERE id=@p_id;"
 
    Try
        cnn = New SqlClient.SqlConnection(SQLConnectionString)
        cnn.Open()
 
        cmd = New SqlClient.SqlCommand(sql2, cnn)
 
        With cmd.Parameters
            .AddWithValue("@p_name", cus_mod_nme.Text)
            .AddWithValue("@p_address", cus_mod_add.Text)
            .AddWithValue("@p_contact", cus_mod_con.Text)
            .AddWithValue("p_id", cus_mod_id.SelectedIndex) ' CHECK THIS!!!! .SelectedIndex is probably not the ID from the table.
        End With
 
        Dim ret As Integer = cmd.ExecuteNonQuery()
 
        MsgBox(String.Format("Records affected: {0}", ret))
 
    Catch ex As Exception
        MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
    Finally
        cnn.Close()
    End Try
End Sub

As you see, the ret variable will actually show the number of affected records (if it's 0 then something is wrong).
And most probably it's due to a combobox/listbox selected index which doesn't correspond to the actual id from the table.

You should bound your cus_mod_id to a data source (some Datatable perhaps) containing IDs and Display names of your curtomers. Then you should assign its .DisplayMember and .ValueMember properties and use .SelectedValue instead of .SelectedIndex

For example (provided you have ID and NAME in your customer table):

Use a data adapter to fill a data table with results of 'SELECT ID, NAME FROM Customer'
Dim dt As New DataTable("customer_ds")

cus_mod_id.DataSource = dt
cus_mod_id.DisplayMember = "NAME"
cus_mod_id.ValueMember = "ID"

Then you should use .SelectedValue property of the listbox instead of .SelectedIndex

[rockers123]

Wellcome to the forums!

You should display the value of your string sql2 and make sure your statement is OK and that the values are right.

I also noticed that you are using cus_mod_id.SelectedIndex for the ID value, are you sure that cus_mod_id.SelectedIndex really return the proper value for the ID field? I mean, if cus_mod_id is a Listbox or something like that it doesn't mean that the SelectedIndex really correspond to the ID of the record in your database.

its showing correct records what are in the table in combo box. when i try that with a text box by manually entered the id, its ok, i can modify. why its not updating when i did the same via the combo box. do u have any solution to use the combo box and to update?

[stlaural]

its showing correct records what are in the table in combo box. when i try that with a text box by manually entered the id, its ok, i can modify

So when you enter the ID manually it works? If so, Like I said, maybe the SelectedIndex value is not equal to the record ID.

Have a look at the code that Cicatrix posted, it also displays the number of records updated, whit that you will see if the record you want to update was found or not.

[rockers123]

Also, your code is insecure for SQL injections. It's probably all right with a learning project but bad habits must be broken right from the beginning. Use parametrized queries like this, not only it is more secure, but also more readable as well:

Private Sub cus_mod_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cus_mod_btn.Click
 
    Dim sql2 As String = "UPDATE customer SET name=@p_name, address=@p_address, contact=@p_contact  WHERE id=@p_id;"
 
    Try
        cnn = New SqlClient.SqlConnection(SQLConnectionString)
        cnn.Open()
 
        cmd = New SqlClient.SqlCommand(sql2, cnn)
 
        With cmd.Parameters
            .AddWithValue("@p_name", cus_mod_nme.Text)
            .AddWithValue("@p_address", cus_mod_add.Text)
            .AddWithValue("@p_contact", cus_mod_con.Text)
            .AddWithValue("p_id", cus_mod_id.SelectedIndex) ' CHECK THIS!!!! .SelectedIndex is probably not the ID from the table.
        End With
 
        Dim ret As Integer = cmd.ExecuteNonQuery()
 
        MsgBox(String.Format("Records affected: {0}", ret))
 
    Catch ex As Exception
        MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
    Finally
        cnn.Close()
    End Try
End Sub

As you see, the ret variable will actually show the number of affected records (if it's 0 then something is wrong).
And most probably it's due to a combobox/listbox selected index which doesn't correspond to the actual id from the table.

You should bound your cus_mod_id to a data source (some Datatable perhaps) containing IDs and Display names of your curtomers. Then you should assign its .DisplayMember and .ValueMember properties and use .SelectedValue instead of .SelectedIndex

For example (provided you have ID and NAME in your customer table):

Use a data adapter to fill a data table with results of 'SELECT ID, NAME FROM Customer'
Dim dt As New DataTable("customer_ds")

cus_mod_id.DataSource = dt
cus_mod_id.DisplayMember = "NAME"
cus_mod_id.ValueMember = "ID"

Then you should use .SelectedValue property of the listbox instead of .SelectedIndex

i tried with a text box by manually entering the id. now its ok. i did using the way posted and i feel more secure than what i done earlier. herafter i'll do my queris parametrized. thank you