Hey everyone, still very new to this and can't seem to find exactly what I'm looking for. Quick run-through on what I'm trying to accomplish. I have a datagridview (3 columns - Id, Name, Address) that is connected to a local .mdf database file, that I'm able to search through using a search textbox. My goal NOW is to submit records into the database directly using 2 text fields and the Id field to automatically increment. (Id++, txtName.Text, txtAddress.Text) and to use a send button(btnSend) to activate this event.(PLEASE KEEP IN MIND, MY GOAL IS TO HAVE EVERYONE INCLUDING THE NEW RECORD SHOW UP IN THE DATAGRIDVIEW AND FOR THE NEW ROW TO BE INSERTED DIRECTLY TO THE DATABASE AND SAVE ANY CHANGES) I've been hammering at this for a couple days now and would appreciate any help. Below is my code, but please keep in mind I'm still new and trying to figure this language out so if there's any unnecessary code, please do let me know... Also if you want to help with one additional thing, maybe some code on how to export that table to a different file from an export button. Thanks! ***I'm currently also getting an error saying "Cannot find table 0." when I click the btnSend button.***
Public Sub btnSend_Click(ByVal sender As Object, e As EventArgs) Handles btnSend.Click
Try
Dim connectionString As String
Dim connection As SqlConnection
Dim ds As New DataSet("Table")
Dim dataset As New DataSet()
Dim sqlInsert As String
Dim sqlSelect As String
Dim Id As Integer = 5
Dim newRow As DataRow = dataset.Tables(0).NewRow()
connectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=""" & My.Application.Info.DirectoryPath & "\Database1.mdf"";Integrated Security=True;"
sqlInsert = "INSERT INTO Table (@Id, @Name, @Address) VALUES (" & Id & ", '" & txtName.Text & "','" & txtAddress.Text & "')"
sqlSelect = "SELECT * FROM Table"
connection = New SqlConnection(connectionString)
Dim da As New SqlDataAdapter()
connection.Open()
da.Fill(ds)
Using da
da.SelectCommand = New SqlCommand(sqlSelect)
da.InsertCommand = New SqlCommand(sqlInsert)
da.InsertCommand.Parameters.Add(New SqlParameter("Id", SqlDbType.Int, 4, Id))
da.InsertCommand.Parameters.Add(New SqlParameter("Name", SqlDbType.NText, 50, txtName.Text))
da.InsertCommand.Parameters.Add(New SqlParameter("Address", SqlDbType.NText, 50, txtAddress.Text))
Using dataset
da.Fill(dataset)
newRow("Id") = Id
newRow("Name") = txtName.Text
newRow("Address") = txtAddress.Text
dataset.Tables(0).Rows.Add(newRow)
da.Update(dataset)
End Using
Using newDataSet As New DataSet()
da.Fill(newDataSet)
End Using
End Using
connection.Close()
Catch ex As Exception
MsgBox(ex.Message)
Throw New Exception("Problem loading persons")
End Try
Dim updatedRowCount As String = gvDataViewer.RowCount - 1
lblRowCount.Text = "[Total Row Count: " & updatedRowCount & "]"
End Sub