Adding Parameters To Stored Procedure

[dhmfh]

I have the following function. I need some help in passing some parameters to the Stored Procedure

Public Shared Function PropertyBasicSearch()

Dim DBConnection As IDbConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim DBSQLStatement As String = "EXEC DSP_WPropertyBasicSearch"
Dim DBCommand As IDbCommand = New SqlCommand()
Dim DataAdapter As IDbDataAdapter = New SqlDataAdapter()
Dim DataSet As DataSet = New DataSet

DBCommand.CommandText = DBSQLStatement
DBCommand.Connection = DBConnection
DataAdapter.SelectCommand = DBCommand

DataAdapter.Fill(DataSet)

Return DataSet

DBConnection.Close()

End Function

 

[ca8msm]

Have a look at the Parameters.Add method of the command


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[dhmfh]

I have added 2 parameters as suggested

I have now tried to run this and an error is returned as follows
Unable to cast object of type 'System.String' to type 'System.Data.SqlClient.SqlParameter'.

So I try to cast the parameters I want to pass and intellisense tells me value of type 'integer' cannot be converted to 'System.Data.SqlClient.SqlParameter'. I am a bit stuck now and where to go from here

Public Shared Function PropertyBasicSearch()

Dim DBConnection As IDbConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim DBSQLStatement As String = "EXEC DSP_WPropertyBasicSearch"
Dim DBCommand As IDbCommand = New SqlCommand()

Dim ParameterWeek As New SqlParameter("@Weeks", SqlDbType.Int, 4)
ParameterWeek = HttpContext.Current.Session("Weeks")

Dim ParameterBeds As New SqlParameter("@Bed", SqlDbType.Int, 4)
ParameterBeds = HttpContext.Current.Session("Beds")

DBCommand.Parameters.Add(ParameterWeek)
DBCommand.Parameters.Add(ParameterBeds)

Dim DataAdapter As IDbDataAdapter = New SqlDataAdapter()
Dim DataSet As DataSet = New DataSet

DBCommand.CommandText = DBSQLStatement
DBCommand.Connection = DBConnection
DataAdapter.SelectCommand = DBCommand

DataAdapter.Fill(DataSet)

Return DataSet

DBConnection.Close()

End Function

 

[ca8msm]

That's because you are setting the parameter object to an integer rather than setting it's value.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[dhmfh]

I have now added the value. I run it and it tells me the that it expects a parameter to be passed, when these are the only two to be passed to the stored procedure.

Public Shared Function PropertyBasicSearch()

Dim DBConnection As IDbConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim DBSQLStatement As String = "EXEC DSP_WPropertyBasicSearch"
Dim DBCommand As IDbCommand = New SqlCommand()

Dim ParameterWeek As New SqlParameter("@Weeks", SqlDbType.Int, 8)
ParameterWeek.Value = CType(HttpContext.Current.Session("Weeks"), Integer)

Dim ParameterBeds As New SqlParameter("@Beds", SqlDbType.Int, 8)
ParameterBeds.Value = CType(HttpContext.Current.Session("Beds"), Integer)

DBCommand.Parameters.Add(ParameterWeek)
DBCommand.Parameters.Add(ParameterBeds)

Dim DataAdapter As IDbDataAdapter = New SqlDataAdapter()
Dim DataSet As DataSet = New DataSet

DBCommand.CommandText = DBSQLStatement
DBCommand.Connection = DBConnection
DataAdapter.SelectCommand = DBCommand

DataAdapter.Fill(DataSet)

Return DataSet

DBConnection.Close()

End Function

 

[jshurst]

You could try the shorthand way...
Here's an example

Dim displaySpecificCodeDataTable As New DataTable
Dim sqlCommand As New SqlCommand("cspSelectSomeCode", MyConnection1)

With sqlCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Code", SqlDbType.VarChar).Value = "whatever"
.Parameters.Add("@Description", SqlDbType.VarChar).Value = "whatever2"
End With

Dim dataAdapter As New SqlDataAdapter(sqlCommand)
dataAdapter.Fill(displaySpecificCodeDataTable)