sql server语言 - 添加参数函数与OLEDB数据使用字符串数组

Question

我是VB的新手。NET编程所以原谅我的无知。我试图通过使用OLEDB来获取excel信息，然后将OLEDB信息传递给SQLCommand更新的参数，将数据从excel电子表格更新到SQL数据库。For语句区域似乎是问题，因为我不断得到下面的错误。我添加了一些消息框，看看我是否在paraArray(I)和excelArray(I)中获得数据，它工作并完美循环。只是不知道为什么它说我没有声明变量，当我执行sqlCmd。ExecuteScalar

错误:"类型为'System.Data.SqlClient. sql '的未处理异常。在System.Data.dll中发生SqlException'。附加信息:必须声明标量变量"@INGREDIENTS"。

如果你需要澄清，我将提供。这是我在stackoverflow上的第一篇文章。我很高兴听到专家们的意见。

sqlUpdate = "UPDATE xxxxx.dbo.testtable SET INGREDIENTS= @INGREDIENTS, SERVINGSIZE= @SERVINGSIZE where SPC= @SPC"
Dim sqlCon As New SqlConnection("Server=Myserver;Database=MyDatabase;User Id=xx;Password=xxxxx")
            sqlCon.Open()
Dim excelCmd2 As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" + sheetName + "]", con)
Dim sqlQuery As New System.Data.SqlClient.SqlCommand("SELECT * FROM xxxxx.dbo.testtable", sqlCon)
Using dataRead As System.Data.OleDb.OleDbDataReader = excelCmd2.ExecuteReader()
                    If dataRead.HasRows() Then
                        While dataRead.Read()
                            Dim sqlCmd As New SqlCommand
                            sqlCmd.Connection = sqlCon
                            sqlCmd.CommandType = CommandType.Text
                            sqlCmd.CommandText = sqlUpdate
                            Dim paraArray() As String = {"@SPC","@INGREDIENTS","@SERVINGSIZE"} 
                            Dim excelArray() As String = {"SPC", "INGREDIENTS", "SERVINGSIZE"}
                            Dim i As Integer = 0
                            For i = 0 To 2                                    
                                sqlCmd.Parameters.AddWithValue(paraArray(i), SqlDbType.Variant).Value = Convert.ToString(dataRead.Item(excelArray(i)))
                                sqlCmd.ExecuteScalar()
                            Next
                        End While        
                    End If
                End Using

Bjorn正确答案后的新代码和Steve的详细解释

sqlUpdate = "UPDATE xxxxx.dbo.testtable SET INGREDIENTS= @INGREDIENTS, SERVINGSIZE= @SERVINGSIZE where SPC= @SPC"
    Dim sqlCon As New SqlConnection("Server=Myserver;Database=MyDatabase;User Id=xx;Password=xxxxx")
                sqlCon.Open()
    Dim excelCmd2 As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" + sheetName + "]", con)
    Dim sqlQuery As New System.Data.SqlClient.SqlCommand("SELECT * FROM xxxxx.dbo.testtable", sqlCon)
Dim paraArray() As String = {"@SPC", "@INGREDIENTS", "@SERVINGSIZE", "@SERV_IN_GRAMS", "@SERV_PER_CON", "@CALORIES", "@CALORIES_FAT", "@TOTAL_FAT", "@SAT_FAT", "@TRANS_FAT", "@POLY_FAT", "@MONO_FAT", "@CHOLESTEROL", "@POTASSIUM", "@SODIUM", "@TOTAL_CARB", "@DIET_FIBER", "@SUGARS", "@SUGAR_ALC", "@PROTEIN", "@VIT_A", "@VIT_C", "@CAL", "@IRON", "@VIT_D", "@VIT_E", "@VIT_K", "@THIAMIN", "@ROBO", "@NIACIN", "@VIT_B12", "@BIOTIN", "@PANTO_ACID", "@PHOSPHORUS", "@IODINE", "@MAGNESIUM", "@ZINC", "@SELENIUM", "@COPPER", "@MANGANESE", "@CHROMIUM", "@MOLYBDENUM", "@FOLATE", "@VIT_B6", "@CHLORIDE", "@PH", "@WATER_CONTENT", "@MOISTURE", "@FAT_PERCENT", "@ASH", "@SULFUR_DIOXIDE", "@PEROXIDE", "@SORBIC_ACID", "@TOTAL_PLATE", "@YEAST_MOLD", "@ENTER_BACT", "@COLIFORM", "@ECOLI", "@SALMONELLA", "@STAPH_AUR", "@LISTEROA", "@KOSHER", "@HALAL", "@ORGANIC", "@ALL_NAT", "@VEGETAR", "@VEGAN", "@RAW", "@SPROUTED", "@GLUTEN", "@WHEAT", "@CASEIN", "@CORN", "@DAIRY", "@EGG", "@GMO", "@LACTOSE", "@PEANUT", "@TREE_NUT", "@SOY", "@YEAST", "@SHELL_FISH", "@FISH", "@RECYCLE", "@BEEF_GRADE", "@CHILD_NUT", "@MARINE_CERT"}
            Using dataRead As System.Data.OleDb.OleDbDataReader = excelCmd2.ExecuteReader()
                If dataRead.HasRows() Then
                    Dim sqlCmd As New SqlCommand
                    sqlCmd.Connection = sqlCon
                    sqlCmd.CommandType = CommandType.Text
                    sqlCmd.CommandText = sqlUpdate
                    For i = 0 To paraArray.Length - 1
                        sqlCmd.Parameters.AddWithValue(paraArray(i), String.Empty)
                    Next
                    Dim excelArray() As String = {"SPC", "INGREDIENTS", "SERVINGSIZE", "SERV_IN_GRAMS", "SERV_PER_CON", "CALORIES", "CALORIES_FAT", "TOTAL_FAT", "SAT_FAT", "TRANS_FAT", "POLY_FAT", "MONO_FAT", "CHOLESTEROL", "POTASSIUM", "SODIUM", "TOTAL_CARB", "DIET_FIBER", "SUGARS", "SUGAR_ALC", "PROTEIN", "VIT_A", "VIT_C", "CAL", "IRON", "VIT_D", "VIT_E", "VIT_K", "THIAMIN", "ROBO", "NIACIN", "VIT_B12", "BIOTIN", "PANTO_ACID", "PHOSPHORUS", "IODINE", "MAGNESIUM", "ZINC", "SELENIUM", "COPPER", "MANGANESE", "CHROMIUM", "MOLYBDENUM", "FOLATE", "VIT_B6", "CHLORIDE", "PH", "WATER_CONTENT", "MOISTURE", "FAT_PERCENT", "ASH", "SULFUR_DIOXIDE", "PEROXIDE", "SORBIC_ACID", "TOTAL_PLATE", "YEAST_MOLD", "ENTER_BACT", "COLIFORM", "ECOLI", "SALMONELLA", "STAPH_AUR", "LISTEROA", "KOSHER", "HALAL", "ORGANIC", "ALL_NAT", "VEGETAR", "VEGAN", "RAW", "SPROUTED", "GLUTEN", "WHEAT", "CASEIN", "CORN", "DAIRY", "EGG", "GMO", "LACTOSE", "PEANUT", "TREE_NUT", "SOY", "YEAST", "SHELL_FISH", "FISH", "RECYCLE", "BEEF_GRADE", "CHILD_NUT", "MARINE_CERT"}
                    While dataRead.Read()
                        For i = 0 To excelArray.Length - 1
                            sqlCmd.Parameters(i).Value = Convert.ToString(dataRead.Item(excelArray(i)))
                        Next
                        sqlCmd.ExecuteNonQuery()
                    End While
                End If
            End Using

Answer 1

将ExecuteScalar移出循环

For i = 0 To 2                                    
    sqlCmd.Parameters.AddWithValue(paraArray(i), SqlDbType.Variant).Value = _
           Convert.ToString(dataRead.Item(excelArray(i)))
Next
sqlCmd.ExecuteNonQuery()

在您的代码中，命令执行得太早。您只添加了第一个参数，因此您得到了错误。(顺便说一下，这不是AddWithValue的正确语法，它需要参数名称，作为第二个参数，要使用的值)

与您的问题无关，但是ExecuteScalar用于从SELECT查询中检索值(第一行的第一列)。它也适用于其他类型的查询，但INSERT/UPDATE/DELETE的推荐方法是ExecuteNonQuery，它返回命令

所涉及的行数

我还建议对你的代码做一个小的优化。将SqlCommand的创建移出DataReader的循环，并在循环中只更新值，而不重新创建命令和参数集合

 Dim paraArray() As String = {"@SPC","@INGREDIENTS","@SERVINGSIZE"} 
 Using dataRead As System.Data.OleDb.OleDbDataReader = excelCmd2.ExecuteReader()
    If dataRead.HasRows() Then
        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = sqlCon
        sqlCmd.CommandType = CommandType.Text
        sqlCmd.CommandText = sqlUpdate
        For i = 0 To paraArray.Length - 1                                    
            sqlCmd.Parameters-AddWithValue(paraArray(i), string.Empty)
        Next
        Dim excelArray() As String = {"SPC", "INGREDIENTS", "SERVINGSIZE"}
        While dataRead.Read()
            Dim i As Integer = 0
            For i = 0 To excelArray.Lenth - 1
                sqlCmd.Parameters(i).Value = Convert.ToString(dataRead.Item(excelArray(i)))
            Next
            sqlCmd.ExecuteNonQuery()
        End While        
    End If
End Using

Answer 2

在设置所有参数之前执行命令:

For i = 0 To 2                                    
    sqlCmd.Parameters.AddWithValue(paraArray(i), SqlDbType.Variant).Value = Convert.ToString(dataRead.Item(excelArray(i)))
    'Executes in first cycle and fails:
    sqlCmd.ExecuteScalar()
Next

将sqlCmd.ExecuteScalar()移出循环:

For i = 0 To 2                                    
    sqlCmd.Parameters.AddWithValue(paraArray(i), SqlDbType.Variant).Value = Convert.ToString(dataRead.Item(excelArray(i)))
Next
sqlCmd.ExecuteScalar()