The variable name ‘@’ has already been declared and must declare scalar variable @.

huangapple go评论88阅读模式
英文:

The variable name '@' has already been declared & must declare scalar variable @

问题

我想使用sqlparameters和for循环从DataGridView插入数据到数据库:

  1. Dim sqlAddCmd As New SqlCommand
  2. sqlAddCmd.CommandText =
  3. "INSERT INTO TableName (Val,Idx,Ref,Sta,Col,Point,Mat,Refer,Cover,IDNo,Mas)" _
  4. & "VALUES (@Val,@No,@Ref,@Sta,@Col,@Point,@Mat,@Refer,@Cover,@IDNo,@Mast)"
  5. sqlAddCmd.Transaction = transaction
  6. With sqlAddCmd.Parameters
  7. .Add("@Val", SqlDbType.Int)
  8. .Add("@No", SqlDbType.TinyInt)
  9. .Add("@Ref", SqlDbType.Char)
  10. .Add("@Sta", SqlDbType.Char)
  11. .Add("@Col", SqlDbType.VarChar)
  12. .Add("@Point", SqlDbType.SmallInt)
  13. .Add("@Mat", SqlDbType.VarChar)
  14. .Add("@Refer", SqlDbType.Int)
  15. .Add("@Cover", SqlDbType.TinyInt)
  16. .Add("@IDNo", SqlDbType.VarChar)
  17. .Add("@Mast", SqlDbType.Char)
  18. End With
  19. For intI = 0 To daGridView.Rows.Count - 1
  20. With sqlAddCmd
  21. .Parameters("@Val").Value = lngNewID
  22. .Parameters("@No").Value = daGridView.Rows(intI).Cells("dgvNo").Value
  23. .Parameters("@Ref").Value = daGridView.Rows(intI).Cells("dgvRef").Value
  24. .Parameters("@Sta").Value = daGridView.Rows(intI).Cells("dgvSta").Value
  25. .Parameters("@Col").Value = daGridView.Rows(intI).Cells("dgvCol").Value
  26. .Parameters("@Point").Value = daGridView.Rows(intI).Cells("dgvPoint").Value
  27. .Parameters("@Mat").Value = daGridView.Rows(intI).Cells("dgiMat").Value
  28. .Parameters("@Refer").Value = daGridView.Rows(intI).Cells("dgiRefer").Value
  29. .Parameters("@Cover").Value = daGridView.Rows(intI).Cells("dgiCover").Value
  30. .Parameters("@IDNo").Value = daGridView.Rows(intI).Cells("dgiIDNo").Value
  31. .Parameters("@Mast").Value = daGridView.Rows(intI).Cells("dgiMast").Value
  32. End With
  33. sqlAddCmd.ExecuteNonQuery()
  34. Next

但是当我尝试插入时,会显示错误代码:

变量名@Val已经声明。变量必须在查询批处理或存储过程内是唯一的。
必须声明标量变量@No。

我知道"Val"必须是唯一的,所以我根据其他人的建议进行了更改,但仍然遇到这样的问题。是否有遗漏的东西?

英文:

I want to insert data from DataGridView to the database by using sqlparameters and for loop:

  1. Dim sqlAddCmd As New SqlCommand
  2. sqlAddCmd.CommandText =
  3. "INSERT INTO TableName (Val,Idx,Ref,Sta,Col,Point,Mat,Refer,Cover,IDNo,Mas)" _
  4. & "VALUES (@Val,@No,@Ref,@Sta,@Col,@Point,@Mat,@Refer,@Cover,@IDNo,@Mast)"
  5. sqlAddCmd.Transaction = transaction
  6. With sqlAddCmd.Parameters
  7. .Add("@Val", SqlDbType.Int)
  8. .Add("@No", SqlDbType.TinyInt)
  9. .Add("@Ref", SqlDbType.Char)
  10. .Add("@Sta", SqlDbType.Char)
  11. .Add("@Col", SqlDbType.VarChar)
  12. .Add("@Point", SqlDbType.SmallInt)
  13. .Add("@Mat", SqlDbType.VarChar)
  14. .Add("@Refer", SqlDbType.Int)
  15. .Add("@Cover", SqlDbType.TinyInt)
  16. .Add("@IDNo", SqlDbType.VarChar)
  17. .Add("@Mast", SqlDbType.Char)
  18. End With
  19. For intI = 0 To daGridView.Rows.Count
  20. With sqlAddCmd
  21. .Parameters("@Val").Value = lngNewID
  22. .Parameters("@No").Value = daGridView.Rows(intI).Cells("dgvNo").Value
  23. .Parameters("@Ref").Value = daGridView.Rows(intI).Cells("dgvRef").Value
  24. .Parameters("@Sta").Value = daGridView.Rows(intI).Cells("dgvSta").Value
  25. .Parameters("@Col").Value = daGridView.Rows(intI).Cells("dgvCol").Value
  26. .Parameters("@Point").Value = daGridView.Rows(intI).Cells("dgvPoint").Value
  27. .Parameters("@Mat").Value = daGridView.Rows(intI).Cells("dgiMat").Value
  28. .Parameters("@Refer").Value = daGridView.Rows(intI).Cells("dgiRefer").Value
  29. .Parameters("@Cover").Value = daGridView.Rows(intI).Cells("dgiCover").Value
  30. .Parameters("@IDNo").Value = daGridView.Rows(intI).Cells("dgiIDNo").Value
  31. .Parameters("@Mast").Value = daGridView.Rows(intI).Cells("dgiMast").Value
  32. End With
  33. sqlAddCmd.ExecuteNonQuery()
  34. Next

But when I want to insert this it will show the error code

> The Variable Name @Val has already been declared. Variable must be unique within a query batch or stored procedure.
> Must declare the scalar variable @No.

I know the val must be unique so I do that based on what the others suggest but still get the problem like this. Is there something missing?

答案1

得分: 0

我明白了。问题是我已经使用了@Val参数。所以我创建了一个新的参数叫做@ValDG。然后,我不再使用For ... Loop,而是使用Do ... While。所以代码如下:

  1. intI = 0
  2. Do While intI <= datagrid.Rows.Count - 1
  3. sqlcommand.commandText = "INSERT INTO TableName (Val,Idx,Ref,Sta,Col,Point,Mat,Refer,Cover,IDNo,Mas)" _
  4. & "VALUES (@ValDG,@No,@Ref,@Sta,@Col,@Point,@Mat,@Refer,@Cover,@IDNo,@Mast)"
  5. sqlcommand.Transaction = transaction
  6. With sqlcommand.parameters
  7. .Add("@ValDG",SqlDbType.int).Value = lngNewID
  8. ' 其他参数设置...
  9. End With
  10. sqlcommand.ExecuteNonQuery()
  11. intI +=1
  12. Loop

希望这对你有帮助。抱歉回复晚了。

英文:

I got the answer. The problem is I already use the @Val parameter. So I create new Parameter called @ValDG. And then instead of using For ... Loop,
I use Do ... While. So the code is like this:

  1. intI = 0
  2. Do While intI &lt;= datagrid.Rows.Count - 1
  3. sqlcommand.commandText = &quot;INSERT INTO TableName (Val,Idx,Ref,Sta,Col,Point,Mat,Refer,Cover,IDNo,Mas)&quot; _
  4. &amp; &quot;VALUES (@ValDG,@No,@Ref,@Sta,@Col,@Point,@Mat,@Refer,@Cover,@IDNo,@Mast)&quot;
  5. sqlcommand.Transaction = transaction
  6. With sqlcommand.parameters
  7. .Add(&quot;@ValDG&quot;,SqlDbType.int).Value = lngNewID
  8. .....
  9. End With
  10. sqlcommand.ExecuteNonQuery()
  11. intI +=1
  12. Loop

I hope it will help. Sorry for the late answer post.

huangapple
  • 本文由 发表于 2023年7月24日 16:30:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76752668.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定