Avatar of Kevin
KevinFlag 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.
User generated imageUser generated imageUser generated image
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
ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Kevin
Kevin
Flag of United States of America image

ASKER

Thank you and apologies for not responding sooner.

Kind Regards,
N
Visual Basic.NET
Visual Basic.NET

Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,

96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo