Problem with syntax, code works fine in a different area of my project. VB.NET

Question

So I am trying to update a database and a datagridview with a "save" button, I used the part of this code earlier in my program for another function, but here it is giving me a syntax error. Can anyone tell me where? I don't understand where it is.

This part of the code works when I add an employee.

Private Sub AddEmployee_Click(sender As Object, e As EventArgs) Handles AddEmployee.Click
    Dim Msg, Style, Title, Response, mystring
    Msg = "Do you want to add employee ?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "MsgBox Demonstration"
    ' Display message.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        TableAdapterManager.UpdateAll(Database13DataSet)
        con.Open()
        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = "Insert INTO dbo.employees (EmpID, LastName, FirstName, AddressHalf, SSN, VehNumb, Certification)  values ('" + EmpID.Text + "' , '" + LastName1.Text + "', '" + FirstName1.Text + "', '" + AddyHalf1.Text + "', '" + SocialNum.Text + "', '" + VehNumb.Text + "', '" + Certification1.Text + "')"
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MessageBox.Show("Employee Added")
    Else
        mystring = True
        MessageBox.Show("Cancelled")
    End If
    con.Close()

This part of the code is the part that doesn't work. I think it has something to do with my coding trying to update a table but I cannot figure it out.

    Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
        Dim Msg, Style, Title, Response, mystring
        Msg = "Do you want to update employee ?"
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Title = "MsgBox Demonstration"
        ' Display message.
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            TableAdapterManager.UpdateAll(Database13DataSet)
            con.Open()
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "Update employees SET (EmpID, LastName, FirstName, AddressHalf, SSN, VehNumb, Certification)  Where ( ModEmpID.Text , ModLastName.Text , ModFirstName.Text,  ModAddy.Text ,  ModSSN.Text , ModVehNum.Text , ModCerts.Text )"
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            MessageBox.Show("Employee Added")
            con.Close()
        Else
            mystring = True
            MessageBox.Show("Cancelled")
        End If
        con.Close()
    End Sub

    Public Sub Updating()
        Me.EmployeesTableAdapter.Fill(Me.Database13DataSet.Employees)
    End Sub
End Class

Answer 1

If Response = vbYes Then
    TableAdapterManager.UpdateAll(Database13DataSet)
    con.Open()
    cmd.CommandType = System.Data.CommandType.Text
    cmd.CommandText = "Insert INTO dbo.employees (EmpID, LastName, FirstName, AddressHalf, SSN, VehNumb, Certification)  values ('" + EmpID.Text + "' , '" + LastName1.Text + "', '" + FirstName1.Text + "','" + AddyHalf1.Text + "', '" + SocialNum.Text + "', '" + VehNumb.Text + "', '" + Certification1.Text + "')"
    cmd.Connection = con
    cmd.ExecuteNonQuery()
    MessageBox.Show("Employee Added")
Else

Nooo.

It doesn't work like that; it was specifically intended not to work like that

---

You have tableadapters; nowhere at all, ever, in any of your code should there be "INSERT INTO.. or "UPDATE .., select, delete or any other kind of SQL

Let's have a real quick back-to-basics

At some point you've followed some tutorial that probably had you do something that caused a XyzDataSet.xsd file to appear in your project. Inside it there are datatables and tableadapters and the whole thing looks kinda like a database.

It's a local representation of a database; the table adapters download data from the database into the dataset's datatables; you manipulate the data/show the user/change it/add to it/delete from it.. ..and when you're done you call upon the tableadapter to push it back to the database.

TableAdapters know how to do all that stuff you've put in your code; you can open the XyzDataSet.Designer.vb file and see it; it has thousands of lines of code intended for pulling and pushing a database

If you reach a point where you think "I don't actually have a facility for... downloading all the employees called smith" then you go to your dataset, you find the employees table adapter, you right click it and you Add Query.. SELECT * FROM employees WHERE name like @name, you call it FillByName, you finish the wizard, and suddenly your employeeTableAdapter has a new method called FillByName that takes a datatable and a string name. You call it like eta.FillByName(myXyzDataset.Employees, "Smith") - it does all the databasey bit for you, the command, the parameters, the connection..

You want to add a new employee; again it's dead easy and the tableadapter will save it, you just have to put the new emp into the local datatable:

Dim emp = myXyzDataSet.Employees.NewEmployeeRow()

emp.Name = "John Smith"
emp.Age = 23
...

myXyzDataSet.Employees.AddEmployeeRow(emp)

There's a shortcut if you know all the values:

myXyzDataSet.Employees.AddEmployeeRow("John Smith", 23, ...)

Either way your local data cache, the datatable, now contains a new record that needs saving. That's done with:

employeeTableAdapter.Update(myXyzDataSet.Employees)

The TA will look at the row and see it has been recently added. It will run the INSERT command it has built in - you don't need to do it

If you had edited a row:

Dim r = myXyzDataSet.Employees(0) 'first one.. or maybe you'll loop and find John Smith, or use the Find method..

r.Name = "Joe Smith"

Then the row knows it has been altered. The tableadapter will know it too, and when you call Update (think of it like Save, it's not just for SQL UPDATE) it will fire the built in UPDATE command and save the name change back to the DB.

Happens similarly for DELETE..

TableAdapters are the devices that pull and push data. If you want to add custom SQLs to your app, add them to the TAs and call the methods. Don't fill your code with direct use of db commands

Answer 2

I finally figured it out after another hour...

Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
    Dim Msg, Style, Title, Response, mystring
    Msg = "Do you want to update employee ?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "MsgBox Demonstration"
    ' Display message.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        DataGridView1.CurrentRow.Cells(0).Value = Me.ModEmpID.Text
        DataGridView1.CurrentRow.Cells(1).Value = Me.ModLastName.Text
        DataGridView1.CurrentRow.Cells(2).Value = Me.ModFirstName.Text
        DataGridView1.CurrentRow.Cells(3).Value = Me.ModAddy.Text
        DataGridView1.CurrentRow.Cells(4).Value = Me.ModSSN.Text
        DataGridView1.CurrentRow.Cells(5).Value = Me.ModVehNum.Text
        DataGridView1.CurrentRow.Cells(6).Value = Me.ModCerts.Text

        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            Dim cmd4 As New SqlCommand("", con)
            cmd4.CommandText = "update Employees set LastName ='" & DataGridView1.Rows(i).Cells(1).Value & "' , FirstName= '" & DataGridView1.Rows(i).Cells(2).Value & "' , AddressHalf = '" & DataGridView1.Rows(i).Cells(3).Value & "' , SSN = '" & DataGridView1.Rows(i).Cells(4).Value & "' , VehNumb = '" & DataGridView1.Rows(i).Cells(5).Value & "' , Certification = '" & DataGridView1.Rows(i).Cells(6).Value & "'Where EmpID = '" & DataGridView1.Rows(i).Cells(0).Value & "'  "
            con.Open()
            cmd4.ExecuteNonQuery()
            con.Close()
        Next
        MessageBox.Show("Employee Updated")

    Else
        mystring = True
    MessageBox.Show("Cancelled")
    End If
    con.Close()
End Sub