VBA如何在SQL批量语法中插入动态参数

Ken 发表于 Dev

肯

我正在尝试使用vb opendialog打开一个随机的csv文件，并将csv中的数据插入SQL Server2014。当涉及到硬编码数据时，这似乎还不错，但是对于动态随机上载它却有问题。非常卡在这里，所以请帮助我。

这是我的动态测试代码，并且我已经创建了与文件格式和csv文件的列名匹配的数据库，表和列：

 Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Dim con As String = "Server=KEN-PC;Database=testData;Trusted_Connection=True;"
    Dim myStream As Stream = Nothing
    Dim openFileDialog2 As New OpenFileDialog()

    openFileDialog2.InitialDirectory = "c:\Desktop"
    openFileDialog2.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
    openFileDialog2.FilterIndex = 2
    openFileDialog2.RestoreDirectory = True

    If openFileDialog2.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
        Try
            myStream = openFileDialog2.OpenFile()
            If (myStream IsNot Nothing) Then
                ' Insert code to read the stream here.
                TextBox2.Text = openFileDialog2.FileName
                Dim conString As String = OpenFileDialog1.FileName

                Try
                    Using connection As New SqlConnection(connetionString)
                        connection.ConnectionString = con
                        sql = "BULK INSERT cvtest
                        FROM" & "'constring'" & "With ( FIELDTERMINATOR = ',', 
                          ROWTERMINATOR = '\n')"
                        connection.Open()
                        adapter = New SqlDataAdapter(sql, connection)
                        adapter.Fill(ds)

                        connection.Close()
                    End Using

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

            End If
        Catch Ex As Exception
            MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
        Finally
            ' Check this again, since we need to make sure we didn't throw an exception on open.
            If (myStream IsNot Nothing) Then
                myStream.Close()
            End If
        End Try
    End If
End Sub

我以PHP方式执行它，但是会引发列不匹配的错误，但是我认为不是这样，因为我已经创建了相同的东西，并且使用了硬代码数据对其进行了测试，它使我获得了成功，感谢您的阅读和希望能给个建议。

肯

啊哈...我自己解决了，关键是sql命令无法捕获我分配的动态参数，因此我执行以下操作，它可以正常工作

                        sql = "
                        BULK INSERT cvtest
                        FROM 'close' With ( FIELDTERMINATOR = ',', 
                          ROWTERMINATOR = '\n')"
                        sql = sql.Replace("close", conString)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。