如何将参数传递给SqlDataAdapter [英] How to pass parameters to SqlDataAdapter
本文介绍了如何将参数传递给SqlDataAdapter的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个 Vb.net 程序,它查询数据库以获取一堆记录.我不太清楚如何传递参数.下面是我的代码:
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:
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
我需要通过:
@stardate
@enddate
@Deparment
@Active
@Visits
我收到错误:
NullReferenceException was unhandled. Object reference not set to an instance of an object.
在线:
sqlCmd.Parameters.AddWithValue("@StartDate", frmMain.Startdate)
frmMain.Startdate
和 frmMain.Enddate
由 datetimepicker1
and datetimepicker2
上的日期时间选择器定义>frmMain
frmMain.Startdate
and frmMain.Enddate
are defined by a Datetime picker datetimepicker1
and datetimepicker2
on frmMain
推荐答案
这里有一个示例,说明您可以使用什么以及如何传递参数您必须在必要时进行更改
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
这篇关于如何将参数传递给SqlDataAdapter的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文