data from gridview to database access

Posted by Ruchi22 under VB.NET on 10/20/2011 | Points: 10 | Views : 6670 | Status : [Member] | Replies : 5
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..

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





Responses

Posted by: Ruchi22 on: 10/20/2011 [Member] Starter | Points: 25

Up
0
Down
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..
[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]

Ruchi22, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ruchi22 on: 10/20/2011 [Member] Starter | Points: 25

Up
0
Down
Is the solution so tough as to no inputs or replies ??

Anyways, me too working on it..

Hope to get advice soon..

Thank you for the efforts, though..

Ruchi22, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 10/20/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi ruchi on btn_transfer click when you adding row to dgv2 after inserting row in dgv2 just write SendKeys.Send("{TAB}") and see is it working or not...

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Ruchi22, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ruchi22 on: 10/20/2011 [Member] Starter | Points: 25

Up
0
Down
Hello..

I tried it.. but it is disabling my transfer button as well :(

Ruchi22, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ruchi22 on: 10/21/2011 [Member] Starter | Points: 25

Up
0
Down
Hey.. I tried it in some way i could think of.. I think the track is proper.. just some of your guidance could help me get what i want.. Please help.. This is my current, 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 ??



Ruchi22, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response