Avatar of Kevin
Kevin
Flag for United States of America asked on

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
Visual Basic.NET

Avatar of undefined
Last Comment
Kevin

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Nitin Sontakke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kevin

ASKER
Thank you and apologies for not responding sooner.

Kind Regards,
N
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck