VB.net SQL参数列表框 [英] VB.net SQLparameter Listbox

问题描述

你好，
希望我可以在此代码中获得一些帮助.我试图用在两个列表框中找到的数据填充表格.但是我遇到了一个错误:SQLException未处理.变量名"@TrackTitle"已经声明.变量名称在查询批处理或存储过程中必须唯一.反正有做这项工作吗?我尝试了各种不同的配置，但没有运气.

Hello,
Was hoping I could get some help with this code. I''m trying to fill the table with the data found in two Listboxs. But I''m an getting an error: SQLException was unhandled. The variable name ''@TrackTitle'' has already been declared. Variable names must be unique within a query batch or stored procedure. Is there anyway to make this work? I''ve tried all sorts of different configurations but no luck.

objConnection.Open()

           .CommandText = "INSERT INTO Tracks " & _
                    "(TrackTitle, TrackSeqNum) " & _
                    "VALUES(@TrackTitle, @TrackSeqNum )"

           For i = 0 To ListBox3.Items.Count - 1
               .Parameters.AddWithValue("@TrackTitle", ListBox3.Items(i))
               .Parameters.AddWithValue("@TrackSeqNum", ListBox1.Items(i))

               .ExecuteNonQuery()
           Next
           
           objConnection.Close()

推荐答案

1)仅第一次(i = 0)可以添加参数！对于下一个循环，必须对其进行更改！
或...
2)以这种方式在运行时构建查询:

1) Only for the first time (i=0) you can add parameter! For the next loop you must change it!
or...
2) Build query in run-time in this way:

sSQL = "INSERT INTO Tracks (TrackTitle, TrackSeqNum) " & vbCrLf & _
"VALUES( '" & ListBox3.Items(i) & "', '" & ListBox1.Items(i)"')"

然后，您需要使用CommandText = sSQL创建命令对象并调用ExecuteNonQuery.

Then you need to create command object with CommandText = sSQL and call ExecuteNonQuery.

您可以使用像这样的参数化查询.请注意，参数在for循环之前添加，并在循环中更新了值.

You can use parameterized query like this. Note that parameter are added before for loop and have the value updated in the loop.

objConnection.Open()
           .CommandText = "INSERT INTO Tracks " & _
                    "(TrackTitle, TrackSeqNum) " & _
                    "VALUES(@TrackTitle, @TrackSeqNum )"

           .Parameters.AddWithValue("@TrackTitle", "")
           .Parameters.AddWithValue("@TrackSeqNum", "")
           For i = 0 To ListBox3.Items.Count - 1
               .Parameters("@TrackTitle").Value = ListBox3.Items(i)
               .Parameters("@TrackSeqNum").Value = ListBox1.Items(i)
               .ExecuteNonQuery()
           Next
           objConnection.Close()

这篇关于VB.net SQL参数列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！