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

Submitted by princenathan on Saturday, December 10, 2011 - 01:21.

Imports System.Data
Imports System.Data.SqlClient
Public Class frmRID
    Dim sCommand As SqlCommand
    Dim sAdapter As SqlDataAdapter
    Dim sBuilder As SqlCommandBuilder
    Dim sDs As DataSet
    Dim sTable As DataTable
 
    Private Sub cnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cnew.Click
        Call main()
        Con.Open()
        If cnew.Text = "New" Then
 
            cnew.Text = "Save"
            cedit.Text = "Cancel"
            clear()
 
            unlock()
            cdel.Enabled = False
            csearch.Enabled = False
            'txt1.Enabled = False
          
            Dim sql As String = "SELECT * FROM DOrecieve_inventory"
            'Dim connection As New SqlConnection '(connectionString)
            'connection.Open()
            sCommand = New SqlCommand(sql, Con)
            sAdapter = New SqlDataAdapter(sCommand)
            sBuilder = New SqlCommandBuilder(sAdapter)
            sDs = New DataSet()
            sAdapter.Fill(sDs, "DOrecieve_inventory")
            sTable = sDs.Tables("DOrecieve_inventory")
            Con.Close()
            grid1.DataSource = sDs.Tables("DOrecieve_inventory")
            'grid1.ReadOnly = True
            grid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
 
        Else
            cnew.Text = "Save"
            cnew.Text = "New"
            cedit.Text = "Update"
            save()
            clear()
            lock()
            cdel.Enabled = True
            csearch.Enabled = True
        End If
    End Sub
    Private Sub clear()
        'grid1.Rows.Clear()
    End Sub
    Private Sub unlock()
        'grid1.Enabled = True
    End Sub
    Private Sub lock()
        'grid1.Enabled = False
    End Sub
    Private Sub save()
        Call main()
        Con.Open()
 
        Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
 
        Try
           
            nonqueryCommand.CommandText = _
               "INSERT  INTO DOrecieve_inventory (type,lot,reference,location,meas,units,pounds) VALUES (@type,@lot,@reference,@location,@meas,@units,@pounds)"
 
            ' Add Parameters to Command Parameters collection
            nonqueryCommand.Parameters.Add("@type", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@lot", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@reference", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@location", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@meas", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@units", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
 
 
            ' Prepare command for repeated execution
            nonqueryCommand.Prepare()
 
            ' Data to be inserted
            For Each row As DataGridViewRow In grid1.Rows
                If Not row.IsNewRow Then
                    nonqueryCommand.Parameters("@type").Value = row.Cells(0).Value.ToString
                    nonqueryCommand.Parameters("@lot").Value = row.Cells(1).Value.ToString
                    nonqueryCommand.Parameters("@reference").Value = row.Cells(2).Value.ToString
                    nonqueryCommand.Parameters("@location").Value = row.Cells(3).Value.ToString
                    nonqueryCommand.Parameters("@meas").Value = row.Cells(4).Value.ToString
                    nonqueryCommand.Parameters("@units").Value = row.Cells(5).Value.ToString
                    nonqueryCommand.Parameters("@pounds").Value = row.Cells(6).Value.ToString
                    'MsgBox("record save")
 
                End If
            Next
 
            nonqueryCommand.ExecuteNonQuery()
 
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            Con.Close()
            'Console.WriteLine("Connection Closed")
 
        End Try
 
 
    End Sub
 
    
    Private Sub frmRID_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lock()
    End Sub
 
    Private Sub cedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cedit.Click
        If cedit.Text = "Update" Then
           
            updatesave()
            csearch.Text = "Search"
            clear()
            lock()
        ElseIf cedit.Text = "Cancel" Then
            cnew.Text = "New"
            cedit.Text = "Update"
            lock()
            clear()
            cdel.Enabled = True
            csearch.Enabled = True
        End If
    End Sub
    Private Sub updatesave()
        Call main()
        Con.Open()
        Dim cmd As SqlCommand = Con.CreateCommand()
        'Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
 
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "update DOrecieve_inventory set lot=@lot,reference=@reference,location=@location,meas=@mas,units=@units,pounds=@pounds where type=@type"
 
        cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@lot", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@reference", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@location", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@mas", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@units", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
        ' Prepare command for repeated execution
        cmd.Prepare()
 
        ' Data to be inserted
        For Each row As DataGridViewRow In grid1.Rows
            If Not row.IsNewRow Then
                cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
                cmd.Parameters("@lot").Value = row.Cells(1).Value.ToString
                cmd.Parameters("@reference").Value = row.Cells(2).Value.ToString
                cmd.Parameters("@location").Value = row.Cells(3).Value.ToString
                cmd.Parameters("@mas").Value = row.Cells(4).Value.ToString
                cmd.Parameters("@units").Value = row.Cells(5).Value.ToString
                cmd.Parameters("@pounds").Value = row.Cells(6).Value.ToString
                'MsgBox("update save")
            End If
        Next
 
        If Con.State = ConnectionState.Closed Then
            Con.Open()
        End If
        cmd.Connection = Con
        Try
            cmd.ExecuteNonQuery() 'Then
            MsgBox("Record Has Updated...!!", MsgBoxStyle.Information, "UPDATE EXPENDITURE")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
        Con.Close()
    End Sub
 
    Private Sub cdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdel.Click
        On Error GoTo err
 
        If cdel.Text = "Delete" Then
            Select Case MsgBox("Are you sure You want to Delete this Record", MsgBoxStyle.YesNo)
                Case MsgBoxResult.Yes
                    deleterecord()
                    csearch.Text = "Search"
                    clear()
                    txt1.Text = ""
                    lock()
                    grid1.Refresh()
                Case MsgBoxResult.No
                    GoTo err
            End Select
        End If
err:
        Exit Sub
    End Sub
    Private Sub deleterecord()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
 
        Dim cmd As SqlCommand = Con.CreateCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "delete DOrecieve_inventory where type=@type"
 
        cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
 
        'xxx Prepare command for repeated execution
        cmd.Prepare()
 
        ' Data to be inserted
        For Each row As DataGridViewRow In grid1.Rows
            If Not row.IsNewRow Then
                cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
            End If
        Next
 
        If Con.State = ConnectionState.Closed Then
            Con.Open()
        End If
        cmd.Connection = Con
        Try
            cmd.ExecuteNonQuery() 'Then
            MsgBox("Record Has Delete..!!", MsgBoxStyle.Information, "DElete EXPENDITURE")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
        Con.Close()
 
 
    End Sub
 
    Private Sub csearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles csearch.Click
   
        
        If csearch.Text = "Search" Then
            csearch.Text = "Cancel"
            cnew.Enabled = False
            unlock()
            txt1.Focus()
 
        ElseIf csearch.Text = "Cancel" Then
            csearch.Text = "Search"
            cnew.Enabled = True
            'clear()
            txt1.Text = ""
            lock()
 
        End If
 
 
    End Sub
 
    Private Sub txt1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt1.TextChanged
 
        showauto()
        showsearch()
 
    End Sub
    Private Sub showauto()
        Call main()
        Con.Open()
 
        Dim cmd As New SqlCommand("SELECT type FROM DOrecieve_inventory", Con)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds, "My List") 'list can be any name u want
 
        Dim col As New AutoCompleteStringCollection
        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            col.Add(ds.Tables(0).Rows(i)("type").ToString())
 
        Next
        txt1.AutoCompleteSource = AutoCompleteSource.CustomSource
        txt1.AutoCompleteCustomSource = col
        txt1.AutoCompleteMode = AutoCompleteMode.Suggest
 
        Con.Close()
    End Sub
    Private Sub showsearch()
        Call main()
        Con.Open()
 
        Dim cmd As New SqlCommand("select * from DOrecieve_inventory where type like '%" + txt1.Text + "%'", Con)
        Dim Adpt As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        If (Adpt.Fill(ds, "DOrecieve_inventory")) Then
 
            grid1.DataSource = ds.Tables(0)
            MessageBox.Show("match found")
 
        Else
        MessageBox.Show("match not found")
        End If
        Con.Close()
    End Sub
 
   
End Class

Comments

student of it

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

yes i want to learn vb.net

yes i want to learn vb.net with mssql server

About VB.net and SQL server

it easy to learn about Vb.net and Sql server you need to start basic then apply the code.. with more IQ, Imagination and love...

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]

want to learn vb 2010

hi! would you like to teach me vb 2010 database programming? From 0 to advance level.

Sure!

just pm me as a ymail [email protected] thanks regard!

plase sent sql database for me

may you send your sql Database for me for project add delete search and update because when i download none database. Please Help me. my email: [email protected]

Step by step Explanation

I need step by step explanation on how to connect to a database and make changes like add/update/delete.Please help me.. My Email:[email protected]

how to edit Gridview

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

searching for help

Thanks

Help me for .NET and SQL server 2008

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

can help me to store and

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

Thank u

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

good Program

good Program and thank you very much.

Add new comment

Add new comment
178 views