Question

vb.net将DataGridView中的数据保存到SQL数据库

Private Sub ButOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButOK.Click

Try
Dim conn As New OleDbConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=Loops;Data Source=SEC-PC\sqlexpress")

conn.Open()
If ConnectionState.Open Then
Dim sqlstr As String
sqlstr = "Select * from CSP"
Dim MyAdapter As New OleDb.OleDbDataAdapter(sqlstr, conn)
Dim ds As New DataSet
Dim dt As New DataTable()
MyAdapter.Fill(ds, "CSP")

With ds.Tables("CSP")
For i = 0 To Form1.DataGridView1.Rows.Count - 1
Dim dr As DataRow = .NewRow
dr("NAME1") = Form1.DataGridView1.Rows(i).Cells(1).Value
dr("NAME2") = Form1.DataGridView1.Rows(i).Cells(2).Value
.Rows.Add(dr)
Next
End With

MyAdapter.Update(ds.Tables("CSP"))
conn.Close()

End If

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub
我想执行的代码是将DataGridView1中的几行数据（有些列有空值）一次性保存到SQL数据库中，但是总是出现如下报警提示，我在网上收了下，如下语句也不行，求指教！

Dim cmdBuilder As New OdbcCommandBuilder(MyAdapter)MyAdapter.InsertCommand = cmdBuilder.GetInsertCommand

Answer 1

    Dim ap As SqlClient.SqlDataAdapter'这个变量很重要
    Private Function Save_Data() As Boolean '保存数据库的操作
        Try
            Me.DataGridView1.EndEdit()
            ' Me.BindingSource1 = Me.DataGridView1.DataSource
            Me.BindingSource1.EndEdit()
            Dim bd As New SqlClient.SqlCommandBuilder(ap)
            ap.UpdateCommand = bd.GetUpdateCommand
            ap.Update(Me.BindingSource1.DataSource)
            Find(str_cmd)
            Return True
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try
    End Function

Answer 2

首先将表读取到dataset
用datagridview绑定数据源
在
datagridview中直接修改字段的值后，用
sqldataadapter的update方法更新
dataset即可！