从dataagridview错误添加值 [英] Add value from dataagridview error

问题描述

我必须将datagridview中的所有行值插入到sql server database.But无法插入第一行。



这是我的编码...

i have to insert the all rows values from datagridview to sql server database.But the first row could not be inserted.

this is my coding...

Dim conn As New SqlConnection
        Dim cmd As SqlCommand
        Dim sqlstring As String
        Dim paramdic As New Dictionary(Of String, Object)
        Dim row As DataGridViewRow = DataGridView1.Rows(0)

        conn.ConnectionString = "Data Source=ATHILINGAM\SQLEXPRESS;Initial Catalog=Attendance;Integrated Security=True;"

        conn.Open()

        paramdic.Add("@StaffId", "StaffId")
        paramdic.Add("@StaffName", "StaffName")
        sqlstring = "INSERT INTO dbo.Attend1(StaffId, StaffName,Status,Date) VALUES ('" & row.Cells(0).Value.ToString & "','" & row.Cells(1).Value.ToString & "','" & row.Cells(2).Value.ToString & "','" & DateTimePicker1.Text & "')"
        cmd = New SqlCommand(sqlstring, conn)
        For Each keyval As KeyValuePair(Of String, Object) In paramdic
            cmd.Parameters.AddWithValue(keyval.Key, keyval.Value)
        Next
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        conn.Close()

我的编码有什么错误......请告诉我......



我尝试了什么：



i尝试将datagridview的所有行值插入sql server数据库

what mistakes in my coding...pls tell me...

What I have tried:

i try to insert all rows value of datagridview to sql server database

推荐答案

我怀疑你的 StaffId 是一个自动生成的 IDENT您不必传递查询的ITY 字段，请尝试不使用此字段。如果您仍有问题，请从您的表格和约束中创建一个脚本并将其包含在您的问题中。

顺便说一句。为了测试它通常更容易使用SQL Server Management Studio或其他数据库工具。



如果你想知道生成的ID的值，你可以使用 OUTPUT ，请参见此处的示例：

c＃ - 执行Insert命令并在Sql中返回插入的Id - Stack Overflow [ ^ ]

I suspect your StaffId is an autogenerated IDENTITY field that you do not have to pass in your query, try without this field. If you still have problems please create a script from your table and constraints and include it in your question.
Btw. for testing it is often easier to use SQL Server Management Studio or another db tool.

If you want to know the value of the generated ID, you can use OUTPUT, see example here:
c# - Execute Insert command and return inserted Id in Sql - Stack Overflow[^]

你需要使用一个循环遍历所有行，如下所示：

You need to use a loop to traverse all rows, like this:

For Each row As DataGridViewRow In DataGridView1.Rows
... your code here
Next

您还没有将参数用于参数化正确查询，请参见此处的示例：

vb.net - 如何创建参数化SQL查询？我为什么要？ - 堆栈溢出 [ ^ ]

Also you are not using your parameters for a parameterized query correctly, see example here:
vb.net - How do I create a parameterized SQL query? Why Should I? - Stack Overflow[^]

这篇关于从dataagridview错误添加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！