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?
rpk_Kannan 0 Newbie Poster
Netcode 33 Veteran Poster
Insert Query:
INSERT INTO table_name (column_names) VALUES (data_values)Update Query:
UPDATE table_name
SET column_name = value
WHERE conditionDelete Query:
DELETE FROM MemberDetails WHERE condition;
Netcode 33 Veteran Poster
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.