Passing SP parameters

[loiter]

Hello,

how do I pass parameters to a stored procedure using OLEDB? (sql server 2000)

I want the user to be able to enter query parameters in a text box and have those values passed in the "exec stored procedure" string.

Thanks,

J

[ITGURU]

Hello

There are two ways using which we can execute SP with parameters:

1. Add value of parameters with spname in the SQL String like as follows:

EXECUTE spName 'Param1', param2, 'Param3'

Prefix and suffix single quote with parameter value if its type is string or date type.

2. Or by following method:

Dim connectionSql As new SqlConnection(connectionString)
Dim commandSql As New SqlCommand()
Dim parameterSql As SqlParameter
Dim count As Integer

With commandSql
.Connection = connectionSql
.CommandText = spName
.CommandType = CommandType.StoredProcedure
.Parameters.Clear()
For count = 0 to Num ' Num means Number of parameters
parameterSql = New SqlParameter()
With parameterSql
.ParameterName = "@ParamName"
.Direction = System.Data.ParameterDirection.Input
.SqlDbType = SqlDbType.VarChar
.Value = ParamValue
End With
commandSql.Parameters.Add(parameterSql)
Next
.Execute
End With

Hope this will solve your problem.

[loiter]

Hello,

Thanks for the reply. I understand how to send them, is it possible to load the results into a datatable?

With my current code I have this:

Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()

It appears your code doesn't make use of a datatable.

Thanks again.

Jeff

[ITGURU]

Hello,

I think you haven't gone through different types of calling method of the Update method of the SQLDataAdapter.

you can pass DataSet or DataTable or DataView as first parameter in the SQLDataAdapter object.

Hope this will clear you about usage of SQLDataAdapter.