Save Data From Datagridview To Database in vb.net 2008

Question

0.00/5 (No votes)

See more:

Dear Sir / Madam

I Made Project Which Contain Datagridview in form and also i use database as sql 2005

now i have problem to save data from datagridview rows to database in button click event

My code save only last row's data in database how to save all rows data from datagridview to database Here is my code

VB

RS.Open("select * from purchasebill where entryno like '" & TextEntryNo.Text & "'", CN, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockPessimistic)

        If (RS.EOF And RS.BOF) Then

            RS.AddNew()
            MsgBox(TextEntryNo.Text & "   Is Saved")

        Else

            If RS("EntryNo").Value = TextEntryNo.Text Then

                MsgBox(TextEntryNo.Text & "    User Ia Allready Exist")
                   
                CmdNew.Focus()

               Exit Sub

            End If

        End If
       
        RS("EntryNo").Value = TextEntryNo.Text
        RS("Entrydate").Value = MaskEntryDate.Text
        RS("BillNo").Value = TextBillNo.Text
        RS("billDate").Value = MaskBillDate.Text
        RS("SupplierName").Value = TextSupplierName.Text
        RS("Billtype").Value = ComboBillType.Text
         
     For i As Integer = 0 To DGVRecord.RowCount - 1

            RS("productname").Value = Me.DGVRecord.Rows(i).Cells("Product").Value
            RS("batchno").Value = Me.DGVRecord.Rows(i).Cells("batchno").Value
            RS("expirydate").Value = Me.DGVRecord.Rows(i).Cells("Expiry").Value

        Next

        RS.Update()

        RS.Close()


Please Help me to solve this 
Thanking you
Jayeshkumar m Patel

Posted 24-Jun-12 6:39am

Jayeshkumar M Patel

Updated 21-Oct-17 5:41am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ryanjayson · Answer 1 · 2013-11-21T22:32:00

Try to include

RS.Update()

on your loop:

 For i As Integer = 0 To DGVRecord.RowCount - 1

           RS("productname").Value = Me.DGVRecord.Rows(i).Cells("Product").Value
           RS("batchno").Value = Me.DGVRecord.Rows(i).Cells("batchno").Value
           RS("expirydate").Value = Me.DGVRecord.Rows(i).Cells("Expiry").Value

       RS.Update()
Next

if doesn't work that code try the code below:

VB

''Declare this to your class
 Public Const CONNECTION_STRING As String = "Data Source=YOUR_HOSTNAME;Initial Catalog=DATABASE_NAME; Integrated Security=True"
    Dim dbConnection As SqlConnection = New SqlConnection(CONNECTION_STRING)
Dim ds As DataSet = New DataSet
Dim da As SqlDataAdapter
''//Declare this to your class

 Private Sub updateDGV() 
        Dim SQLText As String = "SELECT Field1,Field2,Field3,Field4,Field5 FROM TEST"

        Try
            If dbConnection.State = ConnectionState.Open Then dbConnection.Close()
            dbConnection.Open()
            da= New SqlDataAdapter(SQLText, dbConnection)
            da.Fill(ds, "TEST")
            dbConnection.Close()
 
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Update(ds, "TEST")
     
       Catch ex As Exception
            MessageBox.Show(ex.ToString)
       End Try
  
  End Sub

Call the updateDGV method to your Save button.
Hope this will help you.

JonCox · Answer 2 · 2012-06-24T07:25:00

The issue you have is that you are over writting the values through each iteration. You could try to move the update call to inside the for loop. However, this will not be very effecient as you would make n number of update calls to the Db.

[Just consolidated both answers from the user into 1]

VB

For i As Integer = 0 To DGVRecord.RowCount - 1

            RS(&quot;productname&quot;).Value = Me.DGVRecord.Rows(i).Cells(&quot;Product&quot;).Value
            RS(&quot;batchno&quot;).Value = Me.DGVRecord.Rows(i).Cells(&quot;batchno&quot;).Value
            RS(&quot;expirydate&quot;).Value = Me.DGVRecord.Rows(i).Cells(&quot;Expiry&quot;).Value
'The update is moved into the loop creating multiple calls to the update
RS.Update()
        Next
        RS.Close()

A better way is to create a dynamic update string. I am not great with vb but not knowing what your RS object is coded like does not help.

Here is an implementation that uses a dataset and a data adapter to fill a datagridview (in c#)

C#

SqlDataAdapter da=new SqlDataAdapter("select * from product",strcon);
SqlCommandBuilder cb=new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource=ds.Tables[0];

//now u can save changes to back end with
da.Update(ds);

this is not my code but from:
http://forums.devshed.com/net-development-87/update-table-in-database-through-datagridview-in-winform-350445.html[^]

Save Data From Datagridview To Database in vb.net 2008

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0