Create Read Update Delete from gridview and save in the database

Hi Everyone,

I've been dealing with this issue for four hours now and almost giving up. I just want to ask how create, delete, update and delete can be done with the use of datagrid and update SQL server.

This is what I have:

Quote:

---

Private Sub LoadData()



Dim connectionString As String = "Data Source=MyComputer\SQLEXPRESS;Initial Catalog=APP;" & "Integrated Security=SSPI;"

conn = New SqlConnection(connectionString)
conn.Open()

Dim adap As New SqlDataAdapter("Select SerialNum , BuildNum, PartBuildID , OrderNum , XPart , YPart ,Rot, Height , PartMass , SupportMass , Special FROM tblBuildPlan where BuildNum =" & cmbBuildNum.SelectedValue(), conn)
Dim ds As New DataSet
adap.Fill(ds, "BAM")
GVBuilPlan.DataSource = ds.Tables(0)

Catch ex As Exception
MessageBox.Show(ex.Message)

End Try

---

For update button this is what I have:

Code:

---

Try
            Dim cmbl As SqlCommandBuilder = New SqlCommandBuilder(da)
            cmbl.GetUpdateCommand()
            da.Update(ds, "BAM")
            MessageBox.Show("Success")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

---

I can successfully display the values in the db in the gridview but the problem happens when I try to edit or add data and hit update button.

This is the error:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.


According to what I read, I have to have a primiary key which I do, and it is the SerialNum from the tblBuildMaster table.

Any help, would be greatly appreciated.

Thank you everyone!

Hi -- what's in the

Code:

---

cmbl.GetUpdateCommand()

---

procedure?

First of all, please use the proper names for things. In your post you say "gridview" in some places and "datagrid" in others, so you're creating confusion there. In fact, I'm guessing that it's actually a DataGridView, in which case you have never actually given us accurate information. Please take the fractions of a second it requires to be accurate with the information you provide. We can only go by what you give us so if you don't accurate information then it can be hard for us to give you an accurate solution.

In this case at least, that part is irrelevant though. You have given us the important part, i.e. the error message. When you use a command builder, it has to be able to uniquely identify a record in order to generate an UPDATE statement and a DELETE statement and for that it uses the primary key. If your table has no primary key or your query doesn't retrieve and identify it then the command builder can't do its job and you get the error message you're seeing.

So, the solution is to ensure that your table has a primary key in the database first of all. You must then make sure that that PK column is included in your query. Once that's done, the PK column has to be identified in your application. To do that last one, set the MissingSchemaAction property of your data adapter to AddWithKey.

By the way, get rid of this:

Code:

---

cmbl.GetUpdateCommand()

---

It's completely useless.