sql 服务器 - vb.net 在调用"Fill"之前尚未初始化 SelectCommand 属性

Question

当我运行代码时，我看到这些错误在调用'Fill'之前没有初始化SelectCommand属性。在"adb.Fill (ds1)"

Imports System.Data.Sql
Module ComModule
Public sqlconn As New SqlClient.SqlConnection
Public Sub openconn()
    If sqlconn.State = 1 Then sqlconn.Close()
    Try
        sqlconn.ConnectionString = "Data Source=MRSOFTWARE-PC;Initial Catalog=ComShop;Integrated Security=True"
        sqlconn.Open()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Not Connection", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign)
        sqlconn.Close()
        End
    End Try
End Sub
Public Function LastNum(tablename, orderbyfield) As Integer
    LastNum = 0
    Dim str = "select * from " & tablename & "order by" & orderbyfield
    Dim adb As New SqlClient.SqlDataAdapter()
    Dim ds1 = New DataSet
    adb.Fill(ds1)
    Dim DT As DataTable
    DT = ds1.Tables(0)
    If DT.Rows.Count <> 0 Then
        Dim i = DT.Rows.Count - 1
        LastNum = Val(DT.Rows(i).Item(0))
    End If
End Function

终端模块

---

TextBox1.Text = Format(LastNum("Customer", "CustomerId") + 1, "c0")

Answer 1

试试这个…

首先，你必须使用参数化查询来避免SQL注入。

你所需要的是，一个SQLCommand对象，它有一个有效的sql查询。然后，您应该将SQLCommand对象作为参数传递给SQLAdapter构造函数。

Imports System.Data.Sql
    Module ComModule
        Public sqlconn As New SqlClient.SqlConnection
        Public Sub openconn()
            If sqlconn.State = 1 Then sqlconn.Close()
            Try
                sqlconn.ConnectionString = "Data Source=MRSOFTWARE-PC;Initial Catalog=ComShop;Integrated Security=True"
                sqlconn.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Not Connection", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign)
                sqlconn.Close()
                End
            End Try
        End Sub
        Public Function LastNum(tablename, orderbyfield) As Integer
            LastNum = 0
            Dim str = "select * from @tablename order by @orderbyfield"
            Dim sqlCmd As New SqlClient.SqlCommand(str , sqlCon)
            sqlCmd.Parameters.Add("@tablename", SqlDbType.VarChar, 50).Value=tablename
            sqlCmd.Parameters.Add("@orderbyfield", SqlDbType.VarChar, 50).Value=orderbyfield
            Dim adb As New SqlClient.SqlDataAdapter(sqlCmd)
            Dim ds1 = New DataSet
            adb.Fill(ds1)
            Dim DT As DataTable
            DT = ds1.Tables(0)
            If DT.Rows.Count <> 0 Then
                Dim i = DT.Rows.Count - 1
                LastNum = Val(DT.Rows(i).Item(0))
            End If
        End Function

    End Module