vb.net 如何将参数传递给SqlDataAdapter

How to pass parameters to SqlDataAdapter

提问by Shmewnix

I have a Vb.net program that queries a database to get a bunch of records. I can't quite figure out how to pass parameters. below is my code:

我有一个 Vb.net 程序，它查询数据库以获取一堆记录。我不太明白如何传递参数。下面是我的代码：

Dim connectionString As String
    Dim sqlCnn As SqlConnection
    Dim sqlCmd As SqlCommand
    Dim sql As String

    Private Function GetCustomerData() As DataTable
        locationdb = "10.0.1.1"

        connectionString = ("Data Source=" & locationdb & ";Initial     Catalog=TestDB;Persist Security Info=True;User ID=user;Password=password")
        sql = ("SELECT lCustomerID,CustomerName,address FROM customers where @active = True...ETC")
        sqlCnn = New SqlConnection(connectionString)
        Dim CategoryAdapter As New SqlDataAdapter(sql, sqlCnn)
        Dim CustomerInfo As New DataSet()
        sqlCmd.Parameters.AddWithValue("@StartDate", frmMain.Startdate)
        sqlCmd.Parameters.AddWithValue("@EndDate", frmMain.Enddate)
        sqlCmd.Parameters.AddWithValue("@Department", "ALL")
        sqlCmd.Parameters.AddWithValue("@Active", "1")
        sqlCmd.Parameters.AddWithValue("@Visits", "ALL")
        CategoryAdapter.Fill(CustomerInfo, "Customers")
        Return CustomerInfo.Tables(0)
    End Function

I need to pass:

我需要通过：

@stardate
@enddate
@Deparment
@Active
@Visits

I am receiving the error:

我收到错误：

   NullReferenceException was unhandled.  Object reference not set to an instance of an    object.

at line:

在线：

  sqlCmd.Parameters.AddWithValue("@StartDate", frmMain.Startdate)

frmMain.Startdateand frmMain.Enddateare defined by a Datetime picker datetimepicker1and datetimepicker2on frmMain

frmMain.Startdate和frmMain.Enddate由日期时间选择器定义datetimepicker1and datetimepicker2上frmMain

回答by MethodMan

here is an example of what you can use and how to pass Parameters you have to make the changes where necessary

这是您可以使用的内容以及如何传递参数的示例，您必须在必要时进行更改

Public Shared Function GetCustomerInfo(stardate As DateTime, enddate As DateTime, Department As String, Active as String, Visits as Int33) As List(Of String)
    Dim cszList = New List(Of String)()
    Dim DSCityStateZipLookup As New DataSet()
    'load the List one time to be used thru out the intire application
    Dim ConnString = System.Configuration.ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString
    Using connStr As New SqlConnection(ConnString)
        Using cmd As New SqlCommand("your Stored Proc name goes here", connStr)
            cmd.Parameters.AddWithValue("@stardate", stardate)//make sure you assign a value to startdate
            cmd.Parameters.AddWithValue("@enddate", enddate)//make sure you assign a value to enddate
            cmd.Parameters.AddWithValue("@Deparment", Deparment)//make sure you assign a value to //Department
            cmd.Parameters.AddWithValue("@Active", Active)//make sure you assign a value to Active
            cmd.Parameters.AddWithValue("@Visits", Visits)//make sure you assign a value to Visits
            cmd.Connection.Open()
            New SqlDataAdapter(cmd).Fill(DSCityStateZipLookup)
            'If we get a record back from the above stored procedure call, that in itself means the information the user provided from
            'the UI is in the database. On the other hand, if we do not get a record back from the stored procedure call, we should
            'simply advise the user that the information they provided does not exist in the database, and to double check their spelling.
            If DSCityStateZipLookup.Tables.Count = 0 OrElse (DSCityStateZipLookup.Tables.Count > 0 AndAlso DSCityStateZipLookup.Tables(0).Rows.Count = 0) Then
                cszList.Add("Your Error Message goes here if any.")
            End If
        End Using
    End Using
    Return cszList
End Function