Have something to say? Contribute New Article Reply to this Article
data from gridview to database access
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..
<pre><code>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</code></pre>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 TryActually 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 :
<pre><code>cmd = New OleDbCommand("insert into bom(matname1,matnum1,matqty1) values ("</code></pre>
says Ending ")" expected..
and error on line
<pre><code>Next i ")", cn)</code></pre>
says : End of line expected..
Can you please help me further ??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 TryJust 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 & ")"
This question has already been solved
You
© 2012 DaniWeb® LLC
