Connect an SQL database to a DataGridView with separate command buttons?

Question

Hi, I'm a bit puzzled here. I did a form containing a simple datagridview containing data from an MDB file. The connection was alltogether done by Visual Studios wizard so alot of stuff was created automatically. Then I databinded the textboxes to each column in the database and managed to update the database via my own command buttons such as "Update" with this code:

        Me.MainTableBindingSource.EndEdit()
        Me.MainTableTableAdapter.Update(Me.DBDataSet.MainTable)
        Me.DBDataSet.MainTable.AcceptChanges()

This doesn't seem to work with sql. At this point, I've done everything from scratch in this order, added a datagridview and added a database connection with the wizard when connecting the datagridview to a database. Only this time around I created an SQL connection instead.

And Visual Studio created "MainTableTableAdapter", "DBDataSet", "DBDataSet.MainTable".

The SQL server is something which was installed automatically when installing Visual Studio and it does seem to work after creating the table adapter and dataset if there's data in it. In some time I plan to use an SQL Server on the internet which hosts the dataset. So I want to be able to easily edit the source.

The only thing missing now is how to add a row, delete selected row and editing selected row via my own textboxes. More like a fill-in form. Any ideas to put me in the right direction? I've tried googling it and I've found some stuff, but most of it contains stuff on how to create the datasets and etc. And I'm not sure what Visual Studio has done automatically. And I want to update everything just as easily as I did with these three lines of code when I used a local MDB file.

Answer 1

If it's SQL you can do something like this. Here's an example delete function:

Public Shared Function DeleteStuff(ByVal id As Integer) As Integer

 Dim query As String = _
  "DELETE FROM tbl_Stuff " & _
  "WHERE ID_Stuff = " & id & ";"
 Dim connection As SqlConnection = YourDB.GetConnection
 Dim deleteCommand As New SqlCommand(query, connection)
 Dim rowCount As Integer = 0
 Try
  connection.Open()
  rowCount = deleteCommand.ExecuteNonQuery()
 Catch ex As SqlException
  Throw ex
 Finally
  connection.Close()

 End Try
 Return rowCount
End Function

There may be better ways, but you can always pass in SQL queries.

EDIT: Sorry this is more than three lines of code. ;)