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

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
LVL 1
KevinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Thank you and apologies for not responding sooner.

Kind Regards,
N
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.