Hi again !!

I am making a widows application wherein i have a form containing two datagridviews..

The first one( named dgv1) consists of all the data from the database and a checkbox column..

While the user selects the rows using checkbox, the data are displayed in adjoining datagridview(named dgv3)..

Now, the issue is that when i click submit button, i want the data in dgv3 to be inserted in database (Ms access-2003)..

I have referred and tried various snippets, as per my understanding but in vain !! none working for me.. Can u plz help ??

I am using : vb.net, vs2008, ms access 2003..

Thank You !!

This is what i have done so far..

Imports System.Data
Imports System.Data.OleDb
Imports System.EventArgs
Imports System.Data.OleDb.OleDbCommand
Imports System.Data.OleDb.OleDbConnection

Public Class BOM
    Inherits System.Windows.Forms.Form

    Dim wrkdir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim bs As New BindingSource
    Dim edit As Boolean

Imports System.Data
Imports System.Data.OleDb
Imports System.EventArgs
Imports System.Data.OleDb.OleDbCommand
Imports System.Data.OleDb.OleDbConnection

Public Class BOM
    Inherits System.Windows.Forms.Form

    Dim wrkdir As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location())
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim bs As New BindingSource
    Dim edit As Boolean
    'Dim cnn As OleDbConnection

    Dim cnn As New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")

Private Sub BOM_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'HemDatabase1DataSet3.partno' table. You can move, or remove it, as needed.

        'Me.PartnoTableAdapter.Fill(Me.HemDatabase1DataSet3.partno)
        dgv1.DataSource = Me.HemDatabase1DataSet3.partno
        bs.DataSource = ds.Tables(0)

Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click

        ds.Tables.Clear()

        If TypeToolStripTextBox.Text <> "" Then

            Dim sql As String = "SELECT * from (partno) WHERE type='" & TypeToolStripTextBox.Text & "';"
            Dim cmd As New OleDbCommand(sql, cnn)
            da = New OleDbDataAdapter(cmd)
            da.Fill(ds, "partno")
            bs.DataSource = ds.Tables(0)
            dgv1.DataSource = bs

        End If

    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ds.Tables.Clear()

        Dim sql As String = "SELECT * From partno;"
        Dim cmd As New OleDbCommand(sql, cnn)

        da.SelectCommand = cmd

        Dim cmdbuilder As New OleDbCommandBuilder(da)
        da.Fill(ds, "partno")
        bs.DataSource = ds.Tables(0)
        dgv1.DataSource = bs

    End Sub

Private Sub Btn_Transfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Transfer.Click
        
        dgv3.Rows.Clear()
        For Each _rw As DataGridViewRow In dgv1.Rows
            If _rw.Cells(0).Value = True Then
                dgv3.Rows.Add(_rw.Cells(0).Value, _rw.Cells(1).Value, _rw.Cells(2).Value, _rw.Cells(3).Value, _rw.Cells(4).Value, _rw.Cells(5).Value, _rw.Cells(6).Value, _rw.Cells(7).Value)
            End If
        Next

    End Sub

Button1 : Load all data in datagridview
Btn_Transfer : Display selected rows from dgv1 in dgv3

For insertion, i used this code, lately .. But i m not being able to get any response on the click event.. I mean, nothing happens !!

Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Dim oda As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from partno", "Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
        Dim ds As System.Data.DataSet = New System.Data.DataSet()
        oda.Fill(ds)

        'insert
        Dim dr As System.Data.DataRow = ds.Tables(0).NewRow()
        dr(0) = 10
        oda.Update(ds)

    End Sub

Your help would be greatly appreciated..

Yes.. button 3 is submit !! Can you please tell me what should i mention in the statement to retrieve values from some column, some row in datagridview ??
In other forms i have used statement as :

cmd = New OleDbCommand("insert into contactinfo(usertype,compname,compadd,compcity,comppin,compphcode,compph,compfaxcode,compfax,compmail,compsite,compregoff,compfact,products,interest,region,place,contper1,comprole1,compemail1,contnum1,ext1,mobnum1,contper2,comprole2,compemail2,contnum2,ext2,mobnum2,contper3,comprole3,compemail3,contnum3,ext3,mobnum3,weeklyoff,vat,cst,pan,ecc,commissionerate,range,division,servicetax,bankname,bankbranch,accnum,rtgs,instrname,modelnum,installdate,amc,callibration,period,expdate) values ('Agent','" & textBox1.Text & "','" & textBox2.Text & "','" & textBox39.Text & "','" & textBox41.Text & "','" & textBox45.Text & "','" & textBox3.Text & "','" & textBox4.Text & "','" & textBox46.Text & "','" & textBox5.Text & "','" & textBox6.Text & "','" & textBox7.Text & "','" & textBox8.Text & "','" & textBox42.Text & "','" & textBox43.Text & "','" & ComboBox1.SelectedItem.ToString() & "','" & textBox44.Text & "','" & textBox9.Text & "','" & textBox10.Text & "','" & textBox47.Text & "','" & textBox11.Text & "','" & textBox12.Text & "','" & textBox13.Text & "','" & textBox18.Text & "','" & textBox17.Text & "','" & textBox48.Text & "','" & textBox16.Text & "','" & textBox15.Text & "','" & textBox14.Text & "','" & textBox23.Text & "','" & textBox22.Text & "','" & textBox49.Text & "','" & textBox21.Text & "','" & textBox20.Text & "','" & textBox19.Text & "','" & textBox50.Text & "','" & textBox25.Text & "','" & textBox26.Text & "','" & textBox27.Text & "','" & textBox28.Text & "','" & textBox29.Text & "','" & textBox30.Text & "','" & textBox31.Text & "','" & textBox32.Text & "','" & textBox33.Text & "','" & textBox34.Text & "','" & textBox35.Text & "','" & textBox36.Text & "','" & textBox37.Text & "','" & textBox38.Text & "','" & dateTimePicker1.Text & "','" & ComboBox2.SelectedItem.ToString() & "','" & ComboBox3.SelectedItem.ToString() & "','" & textBox40.Text & "','" & dateTimePicker2.Text & "')", cn)

What should i write in context of dgv3, column and row specifics ??

Im getting confused here... on submit you want to insert grid values from dgv3 or ur want to select something from database?

I want to insert values from dgv3 in databse.. Here is what i tried !!

Well i just tried this.. Can you please guide me what should i mention to include the rows of dgv3 in insertion statement ?? I tried implementing what i could gather by self.. Thank you for your patience !! Here is what i did..

Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()

            Dim oda As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from partno", "Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            Dim ds As System.Data.DataSet = New System.Data.DataSet()
            oda.Fill(ds)

            cmd = New OleDbCommand("insert into bom(bomfor,bomtype) values ('" & textBox3.Text & "','" & ComboBox1.SelectedItem.ToString() & "')", cn)

            Dim dr As System.Data.DataRow = ds.Tables(0).NewRow()
            dr(0) = 10
            oda.Update(ds)

            MsgBox("Your Record Inserted Successfully ")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try
    End Sub

Edited 3 Years Ago by diafol: fixed formatting

I am providing the skin for insert into db, you need to change as u needed.

Dim conn As SqlConnection
        Dim strCnn As String = "your connection string;"
        conn = New SqlConnection(strCnn)
         conn.Open()

        Try


            If DataGridView1.Rows.Count > 0 Then

                Dim cmdInsert As New SqlCommand
                cmdInsert.Connection = conn
                Dim strCommandText As String
                Dim values As String = ""

                strCommandText = "INSERT INTO your insert statement here)VALUES("
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                

                    values = strCommandText & "Prepare your Values to pass from Grid i mean get the Cell values you want to insert into db "
                    cmdInsert.CommandText = values
                    cmdInsert.ExecuteNonQuery()

                    values = ""
                Next
                cmdInsert = Nothing
            End If



        Catch ex As Exception
         MsgBox(ex.ToString)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try

Edited 5 Years Ago by Pgmer: n/a

Thank you for your guidance.. will revert soon with the outcome..

Actually i am a first timer so can understand whatever you are trying to say but cannot implement it right way.. That is why i posted the code depicting what i did.. Hoping if you could help me with the code that i posted would be greatly helpful !! I am going through the code and trying it though !!

Thank You !!

The code what you have given will not insert records into DB so i gave you the code how to loop through the datagridview and get the values and insert into db.

Ok.. I have used oledb strings in other forms.. This will b okay right ?? I am implementing it.. Will revert to you with outcome..

Thank You !!

Hello.. Trying to implement what you suggested, i have this form of updated code.. However, it is giving me errors..

 Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()

            'Dim oda As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from partno", "Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            'Dim ds As System.Data.DataSet = New System.Data.DataSet()
            'oda.Fill(ds)


            cmd = New OleDbCommand("insert into bom(matname1,matnum1,matqty1) values ("
            For i As Integer = 0 To dgv3.Rows.Count - 1

                cmd.Parameters.AddWithValue("@Mname", dgv3.Rows(i).Cells(5).Value)
                cmd.Parameters.AddWithValue("@Mnum", dgv3.Rows(i).Cells(4).Value)
                cmd.Parameters.AddWithValue("@Mqty", dgv3.Rows(i).Cells(7).Value)

                cmd.CommandText = "insert into bom(matname1,matnum1,matqty1) values (@Mname,@Mnum,@Mqty)"
                cmd.CommandType = Data.CommandType.Text

                cmd.ExecuteNonQuery()
            Next i ")", cn)

            'Dim dr As System.Data.DataRow = ds.Tables(0).NewRow()
            'dr(0) = 10
            'oda.Update(ds)

            MsgBox("Your Record Inserted Successfully ")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try
    End Sub

An error on line :

cmd = New OleDbCommand("insert into bom(matname1,matnum1,matqty1) values ("

says Ending ")" expected..
and error on line

Next i ")", cn)

says : End of line expected..

Can you please help me further ??

Edited 3 Years Ago by mike_2000_17: Fixed formatting

Hello.. I tried using your code.. But i got stuck with some errors.. could you please help ??
My updated code is :

Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()

            'Dim oda As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from partno", "Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            'Dim ds As System.Data.DataSet = New System.Data.DataSet()
            'oda.Fill(ds)


            cmd = New OleDbCommand("insert into bom(matname1,matnum1,matqty1) values("

            For i As Integer = 0 To dgv3.Rows.Count - 1

                cmd.Parameters.AddWithValue("@Mname", dgv3.Rows(i).Cells(5).Value)
                cmd.Parameters.AddWithValue("@Mnum", dgv3.Rows(i).Cells(4).Value)
                cmd.Parameters.AddWithValue("@Mqty", dgv3.Rows(i).Cells(7).Value)

                cmd.CommandText = "insert into bom(matname1,matnum1,matqty1) values (@Mname,@Mnum,@Mqty)"
                cmd.CommandType = Data.CommandType.Text

                cmd.ExecuteNonQuery()
            Next i ")", cn)

            'Dim dr As System.Data.DataRow = ds.Tables(0).NewRow()
            'dr(0) = 10
            'oda.Update(ds)

            MsgBox("Your Record Inserted Successfully ")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try
    End Sub

The error is in line :

cmd = New OleDbCommand("insert into bom(matname1,matnum1,matqty1) values("

saying that missing ")" expected..
Also a error i line :

Next i ")", cn)

says "End of statement expected"..

Thank you so much for your help !!

Try this code..

Try

            Dim cn As OleDbConnection
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()


            Dim cmd As New OleDbCommand
            cmd.CommandType = Data.CommandType.Text
            Dim Strcommandtext As String = "insert into bom(matname1,matnum1,matqty1) VALUES("
            Dim values As String = ""
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                values = Strcommandtext & DataGridView1.Rows(i).Cells(5).Value & "," & DataGridView1.Rows(i).Cells(4).Value & "," & DataGridView1.Rows(i).Cells(7).Value
                ' Here you check wheater the Insert statement is correct or not. If not then correct the above line accordingly.
                cmd.CommandText = values
                cmd.ExecuteNonQuery()
            Next i
            cmd = Nothing
           
            cn.Close()
            MsgBox("Your Record Inserted Successfully ")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")


        End Try

Just dont copy and paste and run. Try to understand the code. you only can debug ur application and see whats going on...

Hello.. I tried executing this code, with relevant changes and updations.. Works fine.. The error is at line :

cmd.ExecuteNonQuery

the error says : ExecuteNonQuery: Connection property has not been initialized.

Can you please guide me as to how this can b sorted out ?? M trying self since long.. But could not find it..

Thank you !! :)

Could you please point out which line m i missing out ?? I am not being able to figure out your meaning.. first time so, hope you can understand !!

Thank You !! :)

Done.. shows syntax error in insert statement at runtime !!

I tried with some 3-4 modifications but not working.. This is insert snippet..

Dim cmd As New OleDbCommand
            cmd.Connection = cn
            cmd.CommandType = Data.CommandType.Text
            Dim Strcommandtext As String = "insert into bom(matname1,matnum1,matqty1) VALUES("
            Dim values As String = ""
            For i As Integer = 0 To dgv3.Rows.Count - 1
                values = Strcommandtext & dgv3.Rows(i).Cells(5).Value & "," & dgv3.Rows(i).Cells(4).Value & "," & dgv3.Rows(i).Cells(7).Value
                cmd.CommandText = values
                cmd.ExecuteNonQuery()
            Next i
            cmd = Nothing

The modifications show error at compile time.. This code, above, gives error on runtime..

You need to take the statement before running cmd.ExecuteNonQuery() line in debug mode and paste the Insert statement in SQL so you will come to know where is the syntax error. and will be easy to fix.May be missing some Quotes or something.
I guess its missing ")" at the end.

& dgv3.Rows(i).Cells(7).Value & ")"

Edited 5 Years Ago by Pgmer: n/a

Ok.. I am trying it that way !! Thank You.. Will revert soon with the outcome.. :)

Hello.. I am trying the way u suggest and being able to solve 3-4 errors.. however, i am just stuck with this one which says : no value given for one or more required parameters.
I tried all possible modifications as i could think of, or refer to form net.. But all in vain.. Could you please help ??
Here is the code

Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click

        Try

            Dim cn As OleDbConnection
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()


            Dim cmd As New OleDbCommand
            cmd.Connection = cn
            cmd.CommandType = Data.CommandType.Text
            Dim Strcommandtext As String = "insert into bom(' matname1 ',' matnum1 ',' matqty1 ') VALUES("
            Dim values As String = ""
            For i As Integer = 0 To dgv3.Rows.Count - 1
                values = Strcommandtext & dgv3.Rows(i).Cells(5).Value & "," & dgv3.Rows(i).Cells(4).Value & "," & dgv3.Rows(i).Cells(7).Value & ")"
                cmd.CommandText = values
                cmd.ExecuteNonQuery()
            Next i
            cmd = Nothing

            cn.Close()
            MsgBox("Your Record Inserted Successfully ")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

        End Try
End Sub

While debugging, i could understnd that it does store the value in variable "values" in first iteration. But, doesn't execute the statement which increments counter i..

Thank you so much !!

Hey.. I could solve it !! thank you so much for your help.. :)

Appreciated.. Thank you again !!

This question has already been answered. Start a new discussion instead.