SQL server 2008 Save,Edit,Delete and Search from Datagridview Vb.net 2008

Submitted by princenathan on Saturday, December 10, 2011 - 01:21.
  1. Imports System.Data
  2. Imports System.Data.SqlClient
  3. Public Class frmRID
  4.     Dim sCommand As SqlCommand
  5.     Dim sAdapter As SqlDataAdapter
  6.     Dim sBuilder As SqlCommandBuilder
  7.     Dim sDs As DataSet
  8.     Dim sTable As DataTable
  9.  
  10.     Private Sub cnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cnew.Click
  11.         Call main()
  12.         Con.Open()
  13.         If cnew.Text = "New" Then
  14.  
  15.             cnew.Text = "Save"
  16.             cedit.Text = "Cancel"
  17.             clear()
  18.  
  19.             unlock()
  20.             cdel.Enabled = False
  21.             csearch.Enabled = False
  22.             'txt1.Enabled = False
  23.          
  24.             Dim sql As String = "SELECT * FROM DOrecieve_inventory"
  25.             'Dim connection As New SqlConnection '(connectionString)
  26.             'connection.Open()
  27.             sCommand = New SqlCommand(sql, Con)
  28.             sAdapter = New SqlDataAdapter(sCommand)
  29.             sBuilder = New SqlCommandBuilder(sAdapter)
  30.             sDs = New DataSet()
  31.             sAdapter.Fill(sDs, "DOrecieve_inventory")
  32.             sTable = sDs.Tables("DOrecieve_inventory")
  33.             Con.Close()
  34.             grid1.DataSource = sDs.Tables("DOrecieve_inventory")
  35.             'grid1.ReadOnly = True
  36.             grid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
  37.  
  38.         Else
  39.             cnew.Text = "Save"
  40.             cnew.Text = "New"
  41.             cedit.Text = "Update"
  42.             save()
  43.             clear()
  44.             lock()
  45.             cdel.Enabled = True
  46.             csearch.Enabled = True
  47.         End If
  48.     End Sub
  49.     Private Sub clear()
  50.         'grid1.Rows.Clear()
  51.     End Sub
  52.     Private Sub unlock()
  53.         'grid1.Enabled = True
  54.     End Sub
  55.     Private Sub lock()
  56.         'grid1.Enabled = False
  57.     End Sub
  58.     Private Sub save()
  59.         Call main()
  60.         Con.Open()
  61.  
  62.         Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
  63.  
  64.         Try
  65.            
  66.             nonqueryCommand.CommandText = _
  67.                "INSERT  INTO DOrecieve_inventory (type,lot,reference,location,meas,units,pounds) VALUES (@type,@lot,@reference,@location,@meas,@units,@pounds)"
  68.  
  69.             ' Add Parameters to Command Parameters collection
  70.             nonqueryCommand.Parameters.Add("@type", SqlDbType.VarChar, 50)
  71.             nonqueryCommand.Parameters.Add("@lot", SqlDbType.VarChar, 50)
  72.             nonqueryCommand.Parameters.Add("@reference", SqlDbType.VarChar, 50)
  73.             nonqueryCommand.Parameters.Add("@location", SqlDbType.VarChar, 50)
  74.             nonqueryCommand.Parameters.Add("@meas", SqlDbType.VarChar, 50)
  75.             nonqueryCommand.Parameters.Add("@units", SqlDbType.VarChar, 50)
  76.             nonqueryCommand.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
  77.  
  78.  
  79.             ' Prepare command for repeated execution
  80.             nonqueryCommand.Prepare()
  81.  
  82.             ' Data to be inserted
  83.             For Each row As DataGridViewRow In grid1.Rows
  84.                 If Not row.IsNewRow Then
  85.                     nonqueryCommand.Parameters("@type").Value = row.Cells(0).Value.ToString
  86.                     nonqueryCommand.Parameters("@lot").Value = row.Cells(1).Value.ToString
  87.                     nonqueryCommand.Parameters("@reference").Value = row.Cells(2).Value.ToString
  88.                     nonqueryCommand.Parameters("@location").Value = row.Cells(3).Value.ToString
  89.                     nonqueryCommand.Parameters("@meas").Value = row.Cells(4).Value.ToString
  90.                     nonqueryCommand.Parameters("@units").Value = row.Cells(5).Value.ToString
  91.                     nonqueryCommand.Parameters("@pounds").Value = row.Cells(6).Value.ToString
  92.                     'MsgBox("record save")
  93.  
  94.                 End If
  95.             Next
  96.  
  97.             nonqueryCommand.ExecuteNonQuery()
  98.  
  99.         Catch ex As SqlException
  100.             ' Display error
  101.             Console.WriteLine("Error: " & ex.ToString())
  102.         Finally
  103.             ' Close Connection
  104.             Con.Close()
  105.             'Console.WriteLine("Connection Closed")
  106.  
  107.         End Try
  108.  
  109.  
  110.     End Sub
  111.  
  112.    
  113.     Private Sub frmRID_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  114.         lock()
  115.     End Sub
  116.  
  117.     Private Sub cedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cedit.Click
  118.         If cedit.Text = "Update" Then
  119.            
  120.             updatesave()
  121.             csearch.Text = "Search"
  122.             clear()
  123.             lock()
  124.         ElseIf cedit.Text = "Cancel" Then
  125.             cnew.Text = "New"
  126.             cedit.Text = "Update"
  127.             lock()
  128.             clear()
  129.             cdel.Enabled = True
  130.             csearch.Enabled = True
  131.         End If
  132.     End Sub
  133.     Private Sub updatesave()
  134.         Call main()
  135.         Con.Open()
  136.         Dim cmd As SqlCommand = Con.CreateCommand()
  137.         'Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
  138.  
  139.         cmd.CommandType = CommandType.Text
  140.         cmd.CommandText = "update DOrecieve_inventory set lot=@lot,reference=@reference,location=@location,meas=@mas,units=@units,pounds=@pounds where type=@type"
  141.  
  142.         cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
  143.         cmd.Parameters.Add("@lot", SqlDbType.VarChar, 50)
  144.         cmd.Parameters.Add("@reference", SqlDbType.VarChar, 50)
  145.         cmd.Parameters.Add("@location", SqlDbType.VarChar, 50)
  146.         cmd.Parameters.Add("@mas", SqlDbType.VarChar, 50)
  147.         cmd.Parameters.Add("@units", SqlDbType.VarChar, 50)
  148.         cmd.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
  149.         ' Prepare command for repeated execution
  150.         cmd.Prepare()
  151.  
  152.         ' Data to be inserted
  153.         For Each row As DataGridViewRow In grid1.Rows
  154.             If Not row.IsNewRow Then
  155.                 cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
  156.                 cmd.Parameters("@lot").Value = row.Cells(1).Value.ToString
  157.                 cmd.Parameters("@reference").Value = row.Cells(2).Value.ToString
  158.                 cmd.Parameters("@location").Value = row.Cells(3).Value.ToString
  159.                 cmd.Parameters("@mas").Value = row.Cells(4).Value.ToString
  160.                 cmd.Parameters("@units").Value = row.Cells(5).Value.ToString
  161.                 cmd.Parameters("@pounds").Value = row.Cells(6).Value.ToString
  162.                 'MsgBox("update save")
  163.             End If
  164.         Next
  165.  
  166.         If Con.State = ConnectionState.Closed Then
  167.             Con.Open()
  168.         End If
  169.         cmd.Connection = Con
  170.         Try
  171.             cmd.ExecuteNonQuery() 'Then
  172.             MsgBox("Record Has Updated...!!", MsgBoxStyle.Information, "UPDATE EXPENDITURE")
  173.         Catch ex As Exception
  174.             MsgBox(ex.Message)
  175.         End Try
  176.  
  177.         Con.Close()
  178.     End Sub
  179.  
  180.     Private Sub cdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdel.Click
  181.         On Error GoTo err
  182.  
  183.         If cdel.Text = "Delete" Then
  184.             Select Case MsgBox("Are you sure You want to Delete this Record", MsgBoxStyle.YesNo)
  185.                 Case MsgBoxResult.Yes
  186.                     deleterecord()
  187.                     csearch.Text = "Search"
  188.                     clear()
  189.                     txt1.Text = ""
  190.                     lock()
  191.                     grid1.Refresh()
  192.                 Case MsgBoxResult.No
  193.                     GoTo err
  194.             End Select
  195.         End If
  196. err:
  197.         Exit Sub
  198.     End Sub
  199.     Private Sub deleterecord()
  200.         Call main()
  201.         Con.Open()
  202.         Dim adapter As New SqlDataAdapter
  203.  
  204.         Dim cmd As SqlCommand = Con.CreateCommand()
  205.         cmd.CommandType = CommandType.Text
  206.         cmd.CommandText = "delete DOrecieve_inventory where type=@type"
  207.  
  208.         cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
  209.  
  210.         'xxx Prepare command for repeated execution
  211.         cmd.Prepare()
  212.  
  213.         ' Data to be inserted
  214.         For Each row As DataGridViewRow In grid1.Rows
  215.             If Not row.IsNewRow Then
  216.                 cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
  217.             End If
  218.         Next
  219.  
  220.         If Con.State = ConnectionState.Closed Then
  221.             Con.Open()
  222.         End If
  223.         cmd.Connection = Con
  224.         Try
  225.             cmd.ExecuteNonQuery() 'Then
  226.             MsgBox("Record Has Delete..!!", MsgBoxStyle.Information, "DElete EXPENDITURE")
  227.         Catch ex As Exception
  228.             MsgBox(ex.Message)
  229.         End Try
  230.  
  231.         Con.Close()
  232.  
  233.  
  234.     End Sub
  235.  
  236.     Private Sub csearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles csearch.Click
  237.    
  238.        
  239.         If csearch.Text = "Search" Then
  240.             csearch.Text = "Cancel"
  241.             cnew.Enabled = False
  242.             unlock()
  243.             txt1.Focus()
  244.  
  245.         ElseIf csearch.Text = "Cancel" Then
  246.             csearch.Text = "Search"
  247.             cnew.Enabled = True
  248.             'clear()
  249.             txt1.Text = ""
  250.             lock()
  251.  
  252.         End If
  253.  
  254.  
  255.     End Sub
  256.  
  257.     Private Sub txt1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt1.TextChanged
  258.  
  259.         showauto()
  260.         showsearch()
  261.  
  262.     End Sub
  263.     Private Sub showauto()
  264.         Call main()
  265.         Con.Open()
  266.  
  267.         Dim cmd As New SqlCommand("SELECT type FROM DOrecieve_inventory", Con)
  268.         Dim ds As New DataSet
  269.         Dim da As New SqlDataAdapter(cmd)
  270.         da.Fill(ds, "My List") 'list can be any name u want
  271.  
  272.         Dim col As New AutoCompleteStringCollection
  273.         Dim i As Integer
  274.         For i = 0 To ds.Tables(0).Rows.Count - 1
  275.             col.Add(ds.Tables(0).Rows(i)("type").ToString())
  276.  
  277.         Next
  278.         txt1.AutoCompleteSource = AutoCompleteSource.CustomSource
  279.         txt1.AutoCompleteCustomSource = col
  280.         txt1.AutoCompleteMode = AutoCompleteMode.Suggest
  281.  
  282.         Con.Close()
  283.     End Sub
  284.     Private Sub showsearch()
  285.         Call main()
  286.         Con.Open()
  287.  
  288.         Dim cmd As New SqlCommand("select * from DOrecieve_inventory where type like '%" + txt1.Text + "%'", Con)
  289.         Dim Adpt As New SqlDataAdapter(cmd)
  290.         Dim ds As New DataSet()
  291.         If (Adpt.Fill(ds, "DOrecieve_inventory")) Then
  292.  
  293.             grid1.DataSource = ds.Tables(0)
  294.             MessageBox.Show("match found")
  295.  
  296.         Else
  297.         MessageBox.Show("match not found")
  298.         End If
  299.         Con.Close()
  300.     End Sub
  301.  
  302.    
  303. End Class

Comments

Submitted by vuthy (not verified) on Fri, 12/16/2011 - 21:39

want to learn programming. want to know about vb.net.

hello pwede mo ba akong tulungan gawin ang database employee using access 2007 as my backend. i need only SAVE, EDIT, DELETE and UPDATE. and help me how to connect the msaccess data to vb.net. this is my email adress. [email protected]

for example my gridview column Emp_id Name Dept Sal Status 101 ravi MBA 1000 good this status report i have entered in runtime how to i enter in runtime i want vb.net code

Submitted by Anonymous (not verified) on Mon, 12/24/2012 - 11:45

Excuse me. Can you help me. I want buiding a small program for add, delete data from vb.net 2010 with sql 2008. but I don't understand it. Can You help me by small example. Thank you very much

Submitted by Anonymous (not verified) on Tue, 07/30/2013 - 08:51

can help me to store and retrieve the images captured in webcam? tnx

Submitted by gsgokulraj (not verified) on Mon, 02/24/2014 - 20:28

Thank you brother.. really I search for this on many days.. very thanks to you..

Submitted by zazazaza (not verified) on Mon, 06/19/2017 - 17:34

good Program and thank you very much.