Updating form

I have a form with some data I read from a SQL Database. If I modify the form, when I close the form, SQL isn't updated. My Code is:

Private sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim strsql As String = "SELECT * from names where Id='" & Me.TextH1.Text & "'"
            Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, cn)
            Dim dt = New DataTable
                Me.LblIdH.Text = Trim$("" & dt.rows(0).item("Id"))
                Me.TextH2.Text = Trim$("" & dt.rows(0).item("Nom"))
                Me.TextH3.Text = Trim$("" & dt.rows(0).item("PrimerCognom"))
                Me.TextH4.Text = Trim$("" & dt.rows(0).item("SegonCognom"))
                Me.TextH5.Text = Trim$("" & dt.rows(0).item("CarrerNum"))
                Me.TextH6.Text = Trim$("" & dt.rows(0).item("Localitat"))
                Me.TextH7.Text = Trim$("" & dt.rows(0).item("Codipostal"))
                Me.TextH8.Text = Trim$("" & dt.rows(0).item("Telefon1"))
                Me.TextH9.Text = Trim$("" & dt.rows(0).item("Telefon2"))
                Me.TextH10.Text = Trim$("" & dt.rows(0).item("email"))
                Me.DTPicker1.Value = dt.rows(0).item("datanaix")
                Me.CmbBoxH1.Text = Trim$("" & dt.rows(0).item("provincia"))
                Me.CmbEscola.Text = Trim$("" & dt.rows(0).item("cluboescola"))
end sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strsql As String = "SELECT * from names where Id=" & Me.LblIdH.Text
        Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, cn)
        Dim dt = New DataTable
            dt.rows(0).item("Nom") = Trim$("" & Me.TextH2.Text)
            dt.rows(0).item("PrimerCognom") = Trim$("" & Me.TextH3.Text)
            dt.rows(0).item("SegonCognom") = Trim$("" & Me.TextH4.Text)
            dt.rows(0).item("nomcomplet") = Trim$("" & Me.TextH2.Text) & " " & Trim$("" & Me.TextH3.Text) & " " & Trim$("" & Me.TextH4.Text)
            dt.rows(0).item("CarrerNum") = Trim$("" & Me.TextH5.Text)
            dt.rows(0).item("Localitat") = Trim$("" & Me.TextH6.Text)
            dt.rows(0).item("Codipostal") = Trim$("" & Me.TextH7.Text)
            dt.rows(0).item("Telefon1") = Trim$("" & Me.TextH8.Text)
            dt.rows(0).item("Telefon2") = Trim$("" & Me.TextH9.Text)
            dt.rows(0).item("email") = Trim$("" & Me.TextH10.Text)
            dt.rows(0).item("datanaix") = Me.DTPicker1.Value
            dt.rows(0).item("provincia") = Me.CmbBoxH1.Text
            dt.rows(0).item("cluboescola") = Me.CmbEscola.Text
end sub

The debugger stops at : da.Update(dt) line, with the following error:

Non controlled exception type 'System.InvalidOperationException' in microsoft.visualbasic.dll

Additional Information: Update requires that UpdateCommand is valid when DataRow has modified rows.

How I can update information in my Database ?


Who is Participating?
tgannettsConnect With a Mentor Commented:
In the Names table on the SQL Server, have you set a key field?
You need to use a CommandBuilder or a parameterized command to provide the SQL Command Text for the Update.

Add the following lines to the Button_Click sub:

Dim cb as SQLCommandBuilder


cb = new SQLCommandBuilder(da)

UralulaAuthor Commented:
Thanks for your help, but if I do this I get this message:

Additional Information: Dynamic SQL generation for UpdateCommand is not compatible with SelectCommand, that does not return any key column information.

To expand on Tom's comments, I think you need one more line of code:

Put this line above the call to da.Update

da.UpdateCommand = cb.GetUpdateCommand

UralulaAuthor Commented:
Thanks Tom.

I don't remember to create the key field in my SQL Table.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.