Hai sir! i am kannan please help me

insert, update, delete and search coding in VB.Net with access and sql? and way of connect and create access database using in vb.net?

Recommended Answers

All 2 Replies

Insert Query:

INSERT INTO table_name (column_names) VALUES (data_values)

Update Query:

UPDATE table_name
SET column_name = value
WHERE condition

Delete Query:
DELETE FROM MemberDetails WHERE condition;

Here is a simple code snippet for one of my apps. It searches, updates and deletes data. Maybe you can change the data and controls to suit your own app. Just take note of the 'updateMidwife' procedure which does the update function and the 'deleteMidwife' procedure which does the delete function. This is for sql-server

Imports System.Data
Imports System.Data.SqlClient

Public Class searchMidwife

    Dim objdataset As DataSet
    Dim objdataview As DataView
    Dim objcurrencymanager As CurrencyManager

    Private Sub searchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchButton.Click

        If parameterComboBox.Text = "---Please Select---" Then
            MessageBox.Show("PLease select a search parameter", "Midwife Search", _
                              MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        If parameterComboBox.Text <> "---Please Select---" And searchTextBox.Text = "" Then
            MessageBox.Show("Please enter Midwife detail based on selected search parameter", _
                             "Midwife Search", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If parameterComboBox.Text <> "Staff Code" And searchTextBox.Text <> "" Then
            GetStaffCode()
        ElseIf parameterComboBox.Text = "Phone" And searchTextBox.Text <> "" Then
            GetPhone()
        End If


    End Sub

    Sub GetStaffCode()

        Dim con As SqlConnection = New SqlConnection _
    ("server=localhost; database= MSS; user id= sa; password= clement;")
        Dim objDataAdapter As New SqlDataAdapter( _
             "SELECT * FROM [Midwives] WHERE Staff_Code = '" + searchTextBox.Text + "' ", con)


        Dim objDataSet As DataSet
        Dim objDataView As DataView
        Dim objCurrencyManager As CurrencyManager


        objDataSet = New DataSet()

        ' Fill the DataSet object with data...
        objDataAdapter.Fill(objDataSet, "midwives")

        ' Set the DataView object to the DataSet object...
        objDataView = New DataView(objDataSet.Tables("midwives"))

        ' Set our CurrencyManager object to the DataView object...
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)

        Dim intPosition As Integer

        ' If the search field is not empty then...
        If searchTextBox.Text <> "" Then
            objDataView.Sort = "Staff_code"

            ' Find the user_id...
            intPosition = objDataView.Find(searchTextBox.Text)

            Dim objdatatable As DataTable = objDataSet.Tables("Midwives")

            For Each myDataRow As DataRow In objdatatable.Rows

                staffCodeTextBox.Text = Convert.ToString(myDataRow("Staff_Code"))
                firstNameTextBox.Text = Convert.ToString(myDataRow("First_Name"))
                lastNameTextBox.Text = Convert.ToString(myDataRow("Last_Name"))
                phoneTextBox.Text = Convert.ToString(myDataRow("Phone"))
                stateTextBox.Text = Convert.ToString(myDataRow("State"))
                LGATextBox.Text = Convert.ToString(myDataRow("Local_Government"))
                facilityTextBox.Text = Convert.ToString(myDataRow("Facility_Name"))
            Next

            With resultDataGridView
                .AutoGenerateColumns = True
                .DataSource = objDataSet
                .DataMember = "Midwives"

                ' Declare and set the alternating rows style...
                Dim objAlternatingCellStyle As New DataGridViewCellStyle()
                objAlternatingCellStyle.BackColor = Color.WhiteSmoke
                resultDataGridView.AlternatingRowsDefaultCellStyle = objAlternatingCellStyle
            End With

        Else

            MessageBox.Show("please enter staff code", "midwives Search" _
                              , MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        If intPosition = -1 Then
            ' Display a message that the record was not found...

            tslAction.Text = "Record Not Found"
        Else
            ' Otherwise display a message that the record was
            ' found and reposition the CurrencyManager to that
            ' record...

            tslAction.Text = "Record Found"

            objCurrencyManager.Position = intPosition

        End If


    End Sub

    Sub GetPhone()

        Dim con As SqlConnection = New SqlConnection _
            ("server=localhost; database= MSS; user id= sa; password= clement;")
        Dim objDataAdapter As New SqlDataAdapter( _
             "SELECT * FROM [Midwives] WHERE Phone = '" + searchTextBox.Text + "'", con)


        Dim objDataSet As DataSet
        Dim objDataView As DataView
        Dim objCurrencyManager As CurrencyManager


        objDataSet = New DataSet()

        ' Fill the DataSet object with data...
        objDataAdapter.Fill(objDataSet, "midwives")

        ' Set the DataView object to the DataSet object...
        objDataView = New DataView(objDataSet.Tables("midwives"))

        ' Set our CurrencyManager object to the DataView object...
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)

        Dim intPosition As Integer

        ' If the search field is not empty then...
        If searchTextBox.Text <> "" Then
            objDataView.Sort = "Phone"

            ' Find the user_id...
            intPosition = objDataView.Find(searchTextBox.Text)

            Dim objdatatable As DataTable = objDataSet.Tables("Midwives")

            For Each myDataRow As DataRow In objdatatable.Rows

                staffCodeTextBox.Text = Convert.ToString(myDataRow("Staff_Code"))
                firstNameTextBox.Text = Convert.ToString(myDataRow("First_Name"))
                lastNameTextBox.Text = Convert.ToString(myDataRow("Last_Name"))
                phoneTextBox.Text = Convert.ToString(myDataRow("Phone"))
                stateTextBox.Text = Convert.ToString(myDataRow("State"))
                LGATextBox.Text = Convert.ToString(myDataRow("Local_Government"))
                facilityTextBox.Text = Convert.ToString(myDataRow("Facility_Name"))
            Next

            With resultDataGridView
                .AutoGenerateColumns = True
                .DataSource = objDataSet
                .DataMember = "Midwives"

                ' Declare and set the alternating rows style...
                Dim objAlternatingCellStyle As New DataGridViewCellStyle()
                objAlternatingCellStyle.BackColor = Color.WhiteSmoke
                resultDataGridView.AlternatingRowsDefaultCellStyle = objAlternatingCellStyle
            End With

        Else

            MessageBox.Show("please enter phone number", "midwives Search" _
                              , MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        If intPosition = -1 Then
            ' Display a message that the record was not found...

            tslAction.Text = "Record Not Found"
        Else
            ' Otherwise display a message that the record was
            ' found and reposition the CurrencyManager to that
            ' record...

            tslAction.Text = "Record Found"

            objCurrencyManager.Position = intPosition

        End If


    End Sub

    Private Sub deleteToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles deleteToolStripButton.Click

        If searchTextBox.Text <> "" Then

            Dim alert As DialogResult = _
            MessageBox.Show("Are you you want to delete midwife data", "Delete Midwife", _
                             MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            Select Case alert
                Case Windows.Forms.DialogResult.Yes
                    DeleteMidwife()
                Case Windows.Forms.DialogResult.No
                    'do nothing 
            End Select

        End If

    End Sub

    Private Sub UpdateMidwife()

        '*******************************************************************
        'TODO: connect to the books table and subtract the quantity
        'borrowed from the quantitiy available of the corresponding 
        'book
        Try
            Dim con As SqlConnection = New  _
            SqlConnection("server=localhost;database = MSS;" & _
                "user id = sa; password = clement;")
            Dim objCommand As SqlCommand = New SqlCommand()

            ' Set the SqlCommand object properties...
            With objCommand
                .Connection = con
                .CommandText = "UPDATE Midwives " & _
                "SET First_name = @Fname, last_name = @lname, phone = @phone," + _
                        "state = @state, local_government = @LGA, facility_name=@facname " + _
                        "WHERE staff_code = '" + staffCodeTextBox.Text + "'"
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@Fname", firstNameTextBox.Text)
                .Parameters.AddWithValue("@lname", lastNameTextBox.Text)
                .Parameters.AddWithValue("@phone", phoneTextBox.Text)
                .Parameters.AddWithValue("@state", stateTextBox.Text)
                .Parameters.AddWithValue("@LGA", LGATextBox.Text)
                .Parameters.AddWithValue("@facname", facilityTextBox.Text)

            End With

            ' Open the connection...
            con.Open()
            ' Execute the SqlCommand object to update the data...
            objCommand.ExecuteNonQuery()
            ' Close the connection...
            con.Close()

            MessageBox.Show("Record Updated", "Midwife" _
                              , MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception

        End Try

    End Sub
    Private Sub DeleteMidwife()

        '*******************************************************************
        'TODO: connect to the books table delete from the database 
        'the selected boks 

        Dim con As SqlConnection = New  _
        SqlConnection("server=localhost;database = MSS;" & _
            "user id = sa; password = clement;")
        Dim objCommand As SqlCommand = New SqlCommand()

        ' Set the SqlCommand object properties...
        With objCommand
            .Connection = con
            .CommandText = "DELETE FROM Midwives " & _
            "WHERE staff_code = '" + staffCodeTextBox.Text + "'"
            .CommandType = CommandType.Text
        End With

        ' Open the connection...
        con.Open()
        ' Execute the SqlCommand object to update the data...
        objCommand.ExecuteNonQuery()
        ' Close the connection...
        con.Close()

        MessageBox.Show("Record Deleted", "Midwives" _
                          , MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub editToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editToolStripButton.Click

        Panel1.Visible = True
        With Me
            .Width = 589
            .Height = 471
        End With
    End Sub

    Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
        UpdateMidwife()
    End Sub

    Private Sub searchMidwife_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Panel1.Visible = False

        With Me
            Width = 589
            .Height = 364
        End With

    End Sub

    Private Sub cancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles undoButton.Click
        'return to original size
        searchMidwife_Load(sender, e)

    End Sub
End Class
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.