VB.NET - How to add, update and delete values directly to a SQL Database table

Kevin
Kevin used Ask the Experts™
on
Good Afternoon,

Like the title says, I am having problems figuring out how to add, update and delete values directly to my SQL database table.

Below is my application as well as the table in the SQL Database.
applicationtablepropertiestablequery
ADDING NEW VALUES TO TABLE

This was initially working with the below code.

Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click

        sql = "INSERT INTO Contacts " & _
                " (ClientNumber, OfficerName, OfficerEmail, AssistantName, AssistantEmail) " & _
                " VALUES ('" & txtClientNum.Text & "', '" & txtOfficerName.Text & "', '" & txtOfficerEmail.Text & "', '" & txtAssitName.Text & "', '" & txtAssitEmail.Text & "')"
     
        conn = New SqlConnection(SQLConnString)
        conn.Open()

        Dim dscmd As New SqlCommand(sql, conn)
        dscmd.CommandTimeout = 60

        conn.Close()

        'Repopulate datagridview with updated changes.
        subFillDataGridView()

        txtClientNum.Text = ""
        txtOfficerName.Text = ""
        txtOfficerEmail.Text = ""
        txtAssitName.Text = ""
        txtAssitEmail.Text = ""

    End Sub

Open in new window


However I read somewhere that it was best practice to use parameters, so I added an "ID" column in my table made it the primary key and set it to auto-increment and had a go with the below code, but its not working.

No errors, just doesn't add the new information to the SQL table.

Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click

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

        Dim addcommand As New SqlCommand("INSERT INTO [dbo].[Contacts] ([ClientNumber] ,[OfficerName] ,[OfficerEmail] ,[AssistantName] ,[AssistantEmail]) VALUES ", conn)

        addcommand.Parameters.Add("@cnum", SqlDbType.NVarChar).Value = txtClientNum.Text
        addcommand.Parameters.Add("@offname", SqlDbType.NVarChar).Value = txtOfficerName.Text
        addcommand.Parameters.Add("@offemail", SqlDbType.NVarChar).Value = txtOfficerEmail
        addcommand.Parameters.Add("@assname", SqlDbType.NVarChar).Value = txtAssitName.Text
        addcommand.Parameters.Add("@assemail", SqlDbType.NVarChar).Value = txtAssitEmail

conn.Close()

        'Repopulate datagridview with updated changes.
        subFillDataGridView()

        'Clear textboxes
        txtRecordID.Text = ""
        txtClientNum.Text = ""
        txtOfficerName.Text = ""
        txtOfficerEmail.Text = ""
        txtAssitName.Text = ""
        txtAssitEmail.Text = ""

    End Sub

Open in new window


UPDATING EXISTING VALUES TO TABLE

With the updating I again tried the using parameters a little differently but have been unsuccessful.

No errors, just does not update the SQL table.

 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click

        Dim cmd As New SqlCommand
        Dim sSQL As String = String.Empty

        conn = New SqlConnection(SQLConnString)
        conn.Open()
        cmd.CommandType = CommandType.Text

        sSQL = "UPDATE Contacts SET ClientNumber = @cnum ,OfficerName = @offname ,OfficerEmail = @offemail ,AssistantName = @assname ,AssistantEmail = @assemail WHERE id = @id"

        cmd.CommandText = sSQL

        cmd.Parameters.Add("@cnum", SqlDbType.NVarChar).Value = txtClientNum.Text
        cmd.Parameters.Add("@offname", SqlDbType.NVarChar).Value = txtOfficerName.Text
        cmd.Parameters.Add("@offemail", SqlDbType.NVarChar).Value = txtOfficerEmail
        cmd.Parameters.Add("@assname", SqlDbType.NVarChar).Value = txtAssitName.Text
        cmd.Parameters.Add("@assemail", SqlDbType.NVarChar).Value = txtAssitEmail
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = txtRecordID.Text

        conn.Close()

        'Repopulate datagridview with updated changes.
        subFillDataGridView()

        'Clear textboxes
        txtRecordID.Text = ""
        txtClientNum.Text = ""
        txtOfficerName.Text = ""
        txtOfficerEmail.Text = ""
        txtAssitName.Text = ""
        txtAssitEmail.Text = ""

    End Sub

Open in new window


DELETING VALES FROM TABLE

This I attempted a new approach but again is not working.

No errors, just doesn't delete the selected record from the SQL table.

Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click

        Dim msgResult = MsgBox("Are you sure you want to delete the selected record?", vbYesNo, "Confirm delete")

        If msgResult = vbNo Then
            Exit Sub
        End If

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

        sql = "DELETE FROM Contacts"

        sql = sql & " WHERE ClientNumber = '" & txtClientNum.Text & "'" & _
                        " AND OfficerName = '" & txtOfficerName.Text & "'" & _
                        " AND OfficerEmail = '" & txtOfficerEmail.Text & "'" & _
                        " AND AssistantName = '" & txtAssitName.Text & "'" & _
                        " AND AssistantEmail = '" & txtAssitName.Text & "'"

        Dim dscmd As New SqlCommand(sql, conn)
        dscmd.CommandTimeout = 60

        Try

            Dim recordsAffected As Int32 = dscmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox("ERROR: " & ex.Message, MsgBoxStyle.Critical, "Error Deleting Record")

        End Try

        conn.Close()

        'Repopulate datagridview with updated changes.
        subFillDataGridView()

        'Clear textboxes
        txtRecordID.Text = ""
        txtClientNum.Text = ""
        txtOfficerName.Text = ""
        txtOfficerEmail.Text = ""
        txtAssitName.Text = ""
        txtAssitEmail.Text = ""

    End Sub

Open in new window


Can someone help me with this please?

Regards
N
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't personally believe that any of this ever worked, if you have pasted the code "as is" and have not modified it after pasting. I can say this with confidence because your code is missing the most vital statement of all and that is:

cmd.ExecuteNonQuery()

Open in new window


There is code for everything except, executing the command. Why not google and find a complete working example.
KevinInformation Technology

Author

Commented:
Thank you and apologies for not responding sooner.

Kind Regards,
N

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial