在vb.net 2008中将数据从Datagridview保存到数据库 [英] Save Data From Datagridview To Database in vb.net 2008

问题描述

尊敬的先生/女士



我制作的项目包含Datagridview的形式，我也使用数据库作为sql 2005



现在我有问题在按钮点击事件中将数据从datagridview行保存到数据库



我的代码只保存数据库中的最后一行数据如何保存全部从datagridview到数据库的行数据这是我的代码

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

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

推荐答案

您遇到的问题是您在每次迭代时都在编写值。您可以尝试将更新调用移到for循环内部。但是，这将不会非常有效，因为你会对Db进行n次更新调用。



[刚刚将用户的两个答案合并为1] />

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]

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
'The update is moved into the loop creating multiple calls to the update
RS.Update()
        Next
        RS.Close()

更好的方法是创建动态更新字符串。我对vb不是很了解但不知道你的RS对象是什么编码就好无助。



这是一个使用数据集和数据适配器填充的实现一个datagridview（在c＃中）

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#)

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);

这不是我的代码，而是来自：
http://forums.devshed.com/net -development-87 / update-table-in-database-through-datagridview-in-winform-350445.html [ ^ ]

尝试在你的循环中包含

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:

''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

调用updateDGV方法保存按钮。

希望这会对你有帮助。

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

这篇关于在vb.net 2008中将数据从Datagridview保存到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！