how to add, insert and delete in VB.Net? My sql is sql server.

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 793 Views - Last Post: 25 September 2018 - 06:37 AM Rate Topic: ****- 1 Votes

#1 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 01:18 PM

This is my code. Please help me. Badly needed :( I have a database, I only need is add edit delete. Thank you!!!

Attached File(s)


Is This A Good Question/Topic? 0
  • +

Replies To: how to add, insert and delete in VB.Net? My sql is sql server.

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14685
  • View blog
  • Posts: 58,681
  • Joined: 12-June 08

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 01:23 PM

Please post your code here, and use he code tag button.
Was This Post Helpful? 0
  • +
  • -

#3 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 01:26 PM

This is form 1


Public Class Form1
    Dim sqa As New SqlConnect

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        sqa.ModifyRecord("Insert into Books values('" & txtISBN.Text & "','" & txtTitle.Text & "','" & txtPrice.Text & "','" & txtPubdate.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.ModifyRecord("Insert into Publisher values('" & txtPubID.Text & "','" & txtpubName.Text & "','" & txtpubZip.Text & "','" & txtPubAdd.Text & "','" & txtPubCity.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.ModifyRecord("Insert into Authors values('" & txtAuthorID.Text & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.SelectMultipleRecord("Select * from Books")
        DataGridView1.DataSource = sqa.ds.Tables("tbl")
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        sqa.SelectRecord("Select * from Books")
        txtISBN.Text = sqa.getField1()
        txtTitle.Text = sqa.getField2()
        txtPrice.Text = sqa.getField3()
        txtPubdate.Text = sqa.getField4()

        'Authors Table

        sqa.SelectRecord("Select Authors.AuthorID, Authors.FirstName, Authors.LastName from Authors Inner Join Books On Authors.AuthorID = Books.AuthorID")
        txtAuthorID.Text = sqa.getField1()
        txtFirstName.Text = sqa.getField2()
        txtLastName.Text = sqa.getField3()

        'Publisher Table

        sqa.SelectRecord("Select Publisher.PublisherID, Publisher.PubName, Publisher.PubZip, Publisher.PubAddress, Publisher.PubCity from Publisher Inner Join Books on Publisher.PublisherID = Books.PublisherID")

        txtPubID.Text = sqa.getField1()
        txtpubName.Text = sqa.getField2()
        txtpubZip.Text = sqa.getField3()
        txtPubAdd.Text = sqa.getField4()
        txtPubCity.Text = sqa.getField5()


        sqa.SelectMultipleRecord("SELECT * from Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID INNER JOIN Publisher ON Books.PublisherID = Publisher.PublisherID")

        DataGridView1.DataSource = sqa.ds.Tables("tbl")

    End Sub

   
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        sqa.SelectRecord("Select * from Books where Isbn='" & txtsearch.Text & "'")
        txtISBN.Text = sqa.getField1()
        txtTitle.Text = sqa.getField2()
        txtPrice.Text = sqa.getField3()
        txtPubdate.Text = sqa.getField4()

        'Authors Table

        sqa.SelectRecord("Select Authors.AuthorID, Authors.FirstName, Authors.LastName from Authors Inner Join Books On Authors.AuthorID = Books.AuthorID where Isbn='" & txtsearch.Text & "'")
        txtAuthorID.Text = sqa.getField1()
        txtFirstName.Text = sqa.getField2()
        txtLastName.Text = sqa.getField3()

        'Publisher Table

        sqa.SelectRecord("Select Publisher.PublisherID, Publisher.PubName, Publisher.PubZip, Publisher.PubAddress, Publisher.PubCity from Publisher Inner Join Books on Publisher.PublisherID = Books.PublisherID where Isbn='" & txtsearch.Text & "'")

        txtPubID.Text = sqa.getField1()
        txtpubName.Text = sqa.getField2()
        txtpubZip.Text = sqa.getField3()
        txtPubAdd.Text = sqa.getField4()
        txtPubCity.Text = sqa.getField5()

    End Sub


    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        sqa.ModifyRecord("Update Books set Isbn='" & txtISBN.Text & "', Title = '" & txtTitle.Text & "', Price='" & txtPrice.Text & "', Date='" & txtPubdate.Text & "' where Isbn='" & txtISBN.Text & "'")
        MessageBox.Show("The record has been update", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information)


    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        sqa.ModifyRecord("Delete from Books where Isbn='" & txtISBN.Text & "'")
        MessageBox.Show("The record has been deleted", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick


        Dim index As Integer
        index = e.RowIndex
        Dim selectedRow As DataGridViewRow
        selectedRow = DataGridView1.Rows(index)
        txtTitle.Text = selectedRow.Cells(1).Value.ToString
        txtPrice.Text = selectedRow.Cells(2).Value.ToString
        txtPubdate.Text = selectedRow.Cells(3).Value.ToString
        txtAuthorID.Text = selectedRow.Cells(4).Value.ToString
        txtFirstName.Text = selectedRow.Cells(5).Value.ToString
        txtLastName.Text = selectedRow.Cells(6).Value.ToString
        txtPubID.Text = selectedRow.Cells(7).Value.ToString
        txtpubName.Text = selectedRow.Cells(8).Value.ToString
        txtpubZip.Text = selectedRow.Cells(9).Value.ToString
        txtPubAdd.Text = selectedRow.Cells(10).Value.ToString
        txtPubCity.Text = selectedRow.Cells(11).Value.ToString

    End Sub
End Class




class 1

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql

Public Class SqlConnect

    Dim conn As SqlConnection
    Dim com As SqlCommand
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Public ds As DataSet
    
    Private Sub connect()

        conn = New SqlConnection("Server=(localdb)\Marimar;Initial Catalog=BookDatabase;Integrated Security=SSPI")
        conn.Open()

    End Sub

    Public Sub SelectRecord(sql As String)
        connect()
        com = New SqlCommand(sql, conn)
        dr = com.ExecuteReader
        dr.Read()
    End Sub

    Public Sub SelectMultipleRecord(sql As String)
        connect()
        da = New SqlDataAdapter(sql, conn)
        ds = New DataSet()
        da.Fill(ds, "tbl")
    End Sub

    Public Sub ModifyRecord(sql As String)
        connect()
        com = New SqlCommand(sql, conn)
        com.ExecuteNonQuery()
    End Sub


    Public Function getField1()
        If dr.HasRows() Then
            Return dr.Item(0)
        Else
            Return ""
        End If
    End Function

    Public Function getField2()
        If dr.HasRows() Then
            Return dr.Item(1)
        Else
            Return ""
        End If
    End Function

    Public Function getField3()
        If dr.HasRows() Then
            Return dr.Item(2)
        Else
            Return ""
        End If
    End Function

    Public Function getField4()
        If dr.HasRows() Then
            Return dr.Item(3)
        Else
            Return ""
        End If
    End Function


    Public Function getField5()
        If dr.HasRows() Then
            Return dr.Item(4)
        Else
            Return ""
        End If
    End Function

End Class


I only need the code for the button add, edit and delete. I don't know the condition or the code :(/> I'm hopeless.

This post has been edited by IronRazer: 24 September 2018 - 03:22 PM
Reason for edit:: Added Code Tags

Was This Post Helpful? 0
  • +
  • -

#4 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 01:33 PM

Public Class Form1
    Dim sqa As New SqlConnect

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        sqa.ModifyRecord("Insert into Books values('" & txtISBN.Text & "','" & txtTitle.Text & "','" & txtPrice.Text & "','" & txtPubdate.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.ModifyRecord("Insert into Publisher values('" & txtPubID.Text & "','" & txtpubName.Text & "','" & txtpubZip.Text & "','" & txtPubAdd.Text & "','" & txtPubCity.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.ModifyRecord("Insert into Authors values('" & txtAuthorID.Text & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')")
        MessageBox.Show("The record has been added", "New Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

        sqa.SelectMultipleRecord("Select * from Books")
        DataGridView1.DataSource = sqa.ds.Tables("tbl")
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        sqa.SelectRecord("Select * from Books")
        txtISBN.Text = sqa.getField1()
        txtTitle.Text = sqa.getField2()
        txtPrice.Text = sqa.getField3()
        txtPubdate.Text = sqa.getField4()

        'Authors Table

        sqa.SelectRecord("Select Authors.AuthorID, Authors.FirstName, Authors.LastName from Authors Inner Join Books On Authors.AuthorID = Books.AuthorID")
        txtAuthorID.Text = sqa.getField1()
        txtFirstName.Text = sqa.getField2()
        txtLastName.Text = sqa.getField3()

        'Publisher Table

        sqa.SelectRecord("Select Publisher.PublisherID, Publisher.PubName, Publisher.PubZip, Publisher.PubAddress, Publisher.PubCity from Publisher Inner Join Books on Publisher.PublisherID = Books.PublisherID")

        txtPubID.Text = sqa.getField1()
        txtpubName.Text = sqa.getField2()
        txtpubZip.Text = sqa.getField3()
        txtPubAdd.Text = sqa.getField4()
        txtPubCity.Text = sqa.getField5()


        sqa.SelectMultipleRecord("SELECT * from Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID INNER JOIN Publisher ON Books.PublisherID = Publisher.PublisherID")

        DataGridView1.DataSource = sqa.ds.Tables("tbl")

    End Sub

   
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        sqa.SelectRecord("Select * from Books where Isbn='" & txtsearch.Text & "'")
        txtISBN.Text = sqa.getField1()
        txtTitle.Text = sqa.getField2()
        txtPrice.Text = sqa.getField3()
        txtPubdate.Text = sqa.getField4()

        'Authors Table

        sqa.SelectRecord("Select Authors.AuthorID, Authors.FirstName, Authors.LastName from Authors Inner Join Books On Authors.AuthorID = Books.AuthorID where Isbn='" & txtsearch.Text & "'")
        txtAuthorID.Text = sqa.getField1()
        txtFirstName.Text = sqa.getField2()
        txtLastName.Text = sqa.getField3()

        'Publisher Table

        sqa.SelectRecord("Select Publisher.PublisherID, Publisher.PubName, Publisher.PubZip, Publisher.PubAddress, Publisher.PubCity from Publisher Inner Join Books on Publisher.PublisherID = Books.PublisherID where Isbn='" & txtsearch.Text & "'")

        txtPubID.Text = sqa.getField1()
        txtpubName.Text = sqa.getField2()
        txtpubZip.Text = sqa.getField3()
        txtPubAdd.Text = sqa.getField4()
        txtPubCity.Text = sqa.getField5()

    End Sub


    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        sqa.ModifyRecord("Update Books set Isbn='" & txtISBN.Text & "', Title = '" & txtTitle.Text & "', Price='" & txtPrice.Text & "', Date='" & txtPubdate.Text & "' where Isbn='" & txtISBN.Text & "'")
        MessageBox.Show("The record has been update", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information)


    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        sqa.ModifyRecord("Delete from Books where Isbn='" & txtISBN.Text & "'")
        MessageBox.Show("The record has been deleted", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick


        Dim index As Integer
        index = e.RowIndex
        Dim selectedRow As DataGridViewRow
        selectedRow = DataGridView1.Rows(index)
        txtTitle.Text = selectedRow.Cells(1).Value.ToString
        txtPrice.Text = selectedRow.Cells(2).Value.ToString
        txtPubdate.Text = selectedRow.Cells(3).Value.ToString
        txtAuthorID.Text = selectedRow.Cells(4).Value.ToString
        txtFirstName.Text = selectedRow.Cells(5).Value.ToString
        txtLastName.Text = selectedRow.Cells(6).Value.ToString
        txtPubID.Text = selectedRow.Cells(7).Value.ToString
        txtpubName.Text = selectedRow.Cells(8).Value.ToString
        txtpubZip.Text = selectedRow.Cells(9).Value.ToString
        txtPubAdd.Text = selectedRow.Cells(10).Value.ToString
        txtPubCity.Text = selectedRow.Cells(11).Value.ToString

    End Sub
End Class




Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql

Public Class SqlConnect

    Dim conn As SqlConnection
    Dim com As SqlCommand
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Public ds As DataSet
    
    Private Sub connect()

        conn = New SqlConnection("Server=(localdb)\Marimar;Initial Catalog=BookDatabase;Integrated Security=SSPI")
        conn.Open()

    End Sub

    Public Sub SelectRecord(sql As String)
        connect()
        com = New SqlCommand(sql, conn)
        dr = com.ExecuteReader
        dr.Read()
    End Sub

    Public Sub SelectMultipleRecord(sql As String)
        connect()
        da = New SqlDataAdapter(sql, conn)
        ds = New DataSet()
        da.Fill(ds, "tbl")
    End Sub

    Public Sub ModifyRecord(sql As String)
        connect()
        com = New SqlCommand(sql, conn)
        com.ExecuteNonQuery()
    End Sub


    Public Function getField1()
        If dr.HasRows() Then
            Return dr.Item(0)
        Else
            Return ""
        End If
    End Function

    Public Function getField2()
        If dr.HasRows() Then
            Return dr.Item(1)
        Else
            Return ""
        End If
    End Function

    Public Function getField3()
        If dr.HasRows() Then
            Return dr.Item(2)
        Else
            Return ""
        End If
    End Function

    Public Function getField4()
        If dr.HasRows() Then
            Return dr.Item(3)
        Else
            Return ""
        End If
    End Function


    Public Function getField5()
        If dr.HasRows() Then
            Return dr.Item(4)
        Else
            Return ""
        End If
    End Function

End Class


Was This Post Helpful? 0
  • +
  • -

#5 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 09:00 PM

Hi, there is quite a lot wrong in your code. Not using parameters is not a good start. Your statements are in need of amendment, and so, I am going to give you a project I made for you. However, I made and tested this in VB.NET and it works perfectly.

But I done it with MySQL, as I don't have any other DB technology installed, however, with a few tweaks you can copy your own code and adapt it to my code to fit your needs. This template I've provided you with, you can use as a guide to properly construct your own code, or you can use my application to build your app from.

Sadly, no body is going to code this for you on here, so hopefully you will be grateful for the fact I took time out to put this together for you. I've commented all the code inline, so its easy to read and follow, and if I have time, I will answer any questions you have, should you wish to change any of it. I'm sure the other members will help out if you get stuck.

Find my attached project.
Attached File  TestVBApp.zip (73.35K)
Number of downloads: 23

My database structure looked like this:
Attached Image

This is the record I inserted:
Attached Image

This is the application reporting it inserted successfully:
Attached Image

You may need to do a tab tidying up, but its fully working.

Code as follows :: The MySQL Class/Namespace. Paste this into a class file ::
Imports MySql.Data.MySqlClient
Imports System
Imports System.Data
Imports System.Windows.Forms

Namespace MySQLNameSpace
    Public Class MySQLVars
        Public Shared cString As String = "server=localhost;user id=root;password=root;persistsecurityinfo=True;database=visuals_db;port=3306"
        'The above cString is my connection string. Yours will be different. You need to use your cString
        Public Shared execSelectUnameQuery As String = "SELECT username FROM users WHERE [email protected]"
        'Select a username FROM users table, WHERE the condition is that the email equals a value
        Public Shared execSelectIDQuery As String = "SELECT id FROM users WHERE [email protected]"
        'Select id FROM the users table WHERE email equals a value
        Public Shared execUpdateNameQuery As String = "UPDATE users SET [email protected] WHERE [email protected] AND [email protected];"
        'Update users table and SET the name field with value of Fname WHERE the email is the condition and so is the password
        Public Shared execInsertUserQuery As String = "INSERT INTO users (id,name,email,password,username) VALUES ('',@Fname,@Email,@Pass,@Username)"
        'Insert INTO the users table the id, name, email, password and username with the VALUES blank, Fname,Email,Pass,Username
        Public Shared execDeleteUserQuery As String = "DELETE FROM users WHERE [email protected]"
        'Delete a user from the users table where the condition is the id field exists
        Public Shared Con As MySqlConnection = New MySqlConnection(cString)
        'Build a connection and pass the Connection string to it
        Public Shared NewUserName As String
        'Username variable for retrived results
        Public Shared UID As Int16
        'ID for retrieved results
    End Class

    Public Class MySQL
        Public Shared Function DoConnection() As Boolean
            Select Case MySQLVars.Con.State = ConnectionState.Closed
                'Select the condition of the conection based on its state
                Case True 'if closed, we open it
                    MySQLVars.Con.Open()
                    Return True
                Case False 'if open we close it
                    MySQLVars.Con.Close()
                    Return False
            End Select

            Return False
        End Function
        'The insert statement
        Public Shared Function Insert(ByVal Statement As String) As Boolean
            'I prefer to pass my statements to the methods, and generally structure my parameters in a seperate class. But I didn't have time to do that, so this will work just as well for you.
            Try
                If MySQLVars.Con IsNot Nothing Then

                    While DoConnection() = False 'This will open and close the connection
                    End While

                    Using cmd As MySqlCommand = New MySqlCommand(Statement, MySQLVars.Con)
                        'Use using blocks because they are self disposing
                        'The above combines our command and statement with out connection string
                        cmd.Parameters.AddWithValue("@Fname", frmMain1.tbName.Text)
                        cmd.Parameters.AddWithValue("@Email", frmMain1.tbEmail.Text)
                        cmd.Parameters.AddWithValue("@Pass", frmMain1.tbPassword.Text)
                        cmd.Parameters.AddWithValue("@Username", frmMain1.tbName.Text)
                        'The above are the parameters our statements will be looking for above in the MySQLVars class
                        'You must use parameters, to stop MySQL injection and AddWith Valuse since Add is depreceiated
                        cmd.ExecuteNonQuery() 'Execute the statement
                    End Using

                    While DoConnection() = True 'This will open and close the connection
                    End While
                    Return True
                End If

                Return False
            Catch ex As Exception
                MessageBox.Show("Tell the developer there is an error at " & ex.StackTrace.ToString())
                Return False
            End Try
        End Function

        Public Shared Function Update(ByVal Statement As String) As Boolean
            Try

                If MySQLVars.Con IsNot Nothing Then

                    While DoConnection() = False
                    End While

                    Using cmd As MySqlCommand = New MySqlCommand(Statement, MySQLVars.Con)
                        cmd.Parameters.AddWithValue("@Fname", frmMain1.tbName.Text)
                        cmd.Parameters.AddWithValue("@Email", frmMain1.tbEmail.Text)
                        cmd.Parameters.AddWithValue("@Pass", frmMain1.tbPassword.Text)
                        cmd.ExecuteNonQuery()
                    End Using

                    While DoConnection() = True 'This will open and close the connection
                    End While
                    Return True
                End If

                Return False
            Catch ex As Exception
                MessageBox.Show("Tell the developer there is an error at " & ex.StackTrace.ToString())
                Return False
            End Try
        End Function

        Public Shared Function [Select](ByVal Statement As String) As String
            'Keywords not valid as identifiyers, so we wrapped this one in [Select] brackets
            Try
                If MySQLVars.Con IsNot Nothing Then

                    While DoConnection() = False
                    End While

                    Using cmd As MySqlCommand = New MySqlCommand(Statement, MySQLVars.Con)
                        cmd.Parameters.AddWithValue("@Email", frmMain1.tbEmail.Text)

                        Using QueryReader As MySqlDataReader = cmd.ExecuteReader()
                            'Use a reader to read the data, and use HasRows to check if there are any
                            If QueryReader.HasRows Then
                                While QueryReader.Read()
                                    MySQLVars.NewUserName = QueryReader.GetString(0)
                                End While
                            End If
                        End Using
                    End Using

                    While DoConnection() = True 'This will open and close the connection
                    End While
                    Return MySQLVars.NewUserName
                End If

                Return MySQLVars.NewUserName
            Catch ex As Exception
                MessageBox.Show("Tell the developer there is an error at " & ex.StackTrace.ToString())
                Return MySQLVars.NewUserName
            End Try
        End Function
        'The delete statement is a bit different and requires 2 statements, although we only passed one statement, we will change the statement by creating a new command with a new statement.
        Public Shared Function Delete(ByVal Statement As String) As Boolean
            Try
                If MySQLVars.Con IsNot Nothing Then

                    While DoConnection() = False
                    End While

                    Using cmdA As MySqlCommand = New MySqlCommand(MySQLVars.execSelectIDQuery, MySQLVars.Con)
                        'With MySQLVars.execSelectIDQuery we select a new command statement by passing that variable statement to it from the MySQLVars class, other than the statement we passed to the method. The statement passed to the method is used on the second command statement.

                        cmdA.Parameters.AddWithValue("@Email", frmMain1.tbEmail.Text)
                        Using QueryReader As MySqlDataReader = cmdA.ExecuteReader()
                            If QueryReader.HasRows Then
                                While QueryReader.Read()
                                    MySQLVars.UID = QueryReader.GetString(0)
                                End While
                            Else
                                Return False
                            End If
                        End Using
                    End Using
                    'Second command statement follows with the statement we passed to the method
                    Using cmdB As MySqlCommand = New MySqlCommand(Statement, MySQLVars.Con)
                        cmdB.Parameters.AddWithValue("@id", MySQLVars.UID)
                        cmdB.ExecuteNonQuery()
                    End Using

                    While DoConnection() = True 'This will open and close the connection
                    End While
                    Return True
                End If

                Return True
            Catch ex As Exception
                MessageBox.Show("Tell the developer there is an error at " & ex.StackTrace.ToString())
                Return False
            End Try
        End Function
    End Class
End Namespace



I used four buttons to initiate the command calls for the statements. Buttons are named:
btnInsert
btnUpdate
btnDelete
btnSelect

4 Textboxs are named ::
tbName
tbEmail
tbPassword
TextBox1

Create a new form and call it frmMain1, and paste this into it replacing the default text in it.
Option Explicit On
Option Strict On
Option Infer Off
Imports MySql.Data.MySqlClient
Imports MySql
Imports TestVBApp.MySQLNameSpace

Public Class frmMain1
    Private Sub frmMain1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click

        If MySQLNameSpace.MySQL.Insert(MySQLVars.execInsertUserQuery) = True Then
            MessageBox.Show("User has been Created") 'if insert success
        Else
            MessageBox.Show("User has not been Created") 'if failed
        End If
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        If MySQLNameSpace.MySQL.Update(MySQLVars.execUpdateNameQuery) = True Then
            MessageBox.Show("User has been updated") 'if sucess
        Else
            MessageBox.Show("User has not been updated, check your details again") 'if failed
        End If
    End Sub

    Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
        If tbName.Text <> String.Empty Then
            MySQLNameSpace.MySQL.Select(MySQLVars.execSelectUnameQuery)
            TextBox1.Text = MySQLVars.NewUserName

            If MySQLVars.NewUserName <> String.Empty Then 'if result retrived, the MySQLVars.NewUserName will have a value
                MessageBox.Show("Your username is now: " & MySQLVars.NewUserName) 'Success
                MySQLVars.NewUserName = String.Empty 'Reset the variable for next update otherwise this statement will always be true.
            Else
                MessageBox.Show("We could not find a user with those details") 'failed
            End If
        End If
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        If tbEmail.Text <> String.Empty Then
            MySQLNameSpace.MySQL.Delete(MySQLVars.execDeleteUserQuery)

            If MySQLNameSpace.MySQL.Delete(MySQLVars.execDeleteUserQuery) = True Then
                MessageBox.Show("User has been deleted") 'success
            Else
                MessageBox.Show("User may have been deleted already") 'failed
            End If
        End If
    End Sub
End Class



Hope it helps you to understand how to build your connections, statements and how to execute them.

Post back once you have adapted it to your own code. As I said, nobody will do this for you, but this will give your project a kick start. Let me know if you found it helpful.

This post has been edited by Sheepings: 22 September 2018 - 09:06 PM

Was This Post Helpful? 1
  • +
  • -

#6 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 09:05 PM

Thank you, I will follow this step and code of yours. We need slq server for the database so, i will study your code. Thank you for your concern sir. I'll appreciate it. This is my project for my rdbms subject. I need to study later. This helps me alot. Thank you.
Was This Post Helpful? 0
  • +
  • -

#7 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 22 September 2018 - 09:11 PM

You're welcome. Remember to hit the green plus on my post if you found it useful. Btw, if you are using it with SQL and not MySQL, you can change it to
Using cmdB As SqlCommand = New SqlCommand(Statement, MySQLVars.Con)
instead of
Using cmdB As MySqlCommand = New MySqlCommand(Statement, MySQLVars.Con)
etc...
Anything that's MySQL, change to SQL, and remember to set your imports for SQL

Take care, and good luck
Was This Post Helpful? 1
  • +
  • -

#8 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 24 September 2018 - 09:58 PM

Thanks sir! I'll do this now. :)
Was This Post Helpful? 0
  • +
  • -

#9 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 24 September 2018 - 11:19 PM

There's no error when i'm run it.

but, if i add some data.
The message was Data already exist, even if its not existing.

What is the error? Can't find it.

Thank you!!!

Attached image(s)

  • Attached Image
  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#10 Programmer2004   User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 96
  • Joined: 25-October 17

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 01:04 AM

The problem is that you compare two variables with themselfes in these lines:

If txtISBN.Text = txtISBN.Text Then



and

If txtAuthorID.Text = txtAuthorID.Text Then



You compare the variable with itself, and it will always return that it's equal. Below that equations you've inserted "Data already Exist" message box, that's why you get it every time you try to add something to table.
Also, it seems that you load txtISBN.Text and txtAuthorID.Text with values from the table, so even if it succeeds, the values will remain the same.

So don't overwrite txtISBN.Text or txtAuthorID.Text with table contents. Instead, compare them to table contents.

This post has been edited by Programmer2004: 25 September 2018 - 01:05 AM

Was This Post Helpful? 1
  • +
  • -

#11 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 01:11 AM

Why didn't you implement what I gave you in mysql format? It is effortlessly easy to change and adapt to your own application.

It is counterproductive coming here for help when you're ignoring the advice you already received.

Yet here you are again, without showing any atempt to rectify, what I showed you on your other topic. You're still using concatenation, and no parameters dispite my warnings about sql injection attempts.

And what is this logic:
If txtISbn.text = txtISbn.text?

Put at the top of your page:
Option strict on
Option explicit on

I'm not in the habit of helping people who ignore working examples and sound advice.

Might I also suggest you take out a good book on vb.net programming.
Was This Post Helpful? 1
  • +
  • -

#12 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 01:20 AM

I gave you working code, and an application to learn from. I even spent time converting it from c# to vb.net by rewriting it for you.

Can you please show where you tried to implement it?
Was This Post Helpful? 1
  • +
  • -

#13 Skymari101   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-September 18

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 01:23 AM

I'm not ignored your code and your help, but.. it's to risky because my professor would asked me about that. I'm sorry. and I don't get it huhu :(
Was This Post Helpful? 0
  • +
  • -

#14 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 01:32 AM

But that is why you are here. To learn, and to ask questions, and to ask how to implement my code to yours. You need to post topics asking these things. Saying that your professor will know you had help , is not worse than failing.

If you show some effort, I will try to help you, as much as I can. At-least try to implement the parameterized queries.

This post has been edited by Sheepings: 25 September 2018 - 05:37 AM

Was This Post Helpful? 1
  • +
  • -

#15 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6666
  • View blog
  • Posts: 27,294
  • Joined: 12-December 12

Re: how to add, insert and delete in VB.Net? My sql is sql server.

Posted 25 September 2018 - 04:50 AM

When posting code please directly copy and paste the code, between code tags, not just as screenshots.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2