Passing a variable to a stored procedure

[mrrrl]

Looking for info on how to pass a variable (ie name) to a stored procedure.

Here is the stored procedure (@name the variable):

CREATE PROCEDURE dbo.NewSelectCommand_Test_1
@name text
AS
SET NOCOUNT ON;
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM authors WHERE (au_lname like @name)

And here is the VB7 code calling the stored procedure which is done by SqlDataAdapter1, TextBox1.Text holds the variable to pass to the stored procedure:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
TextBox1.Text = "Ringer"
DataSet31.Clear()
SqlDataAdapter1.Fill(DataSet31)
DataGrid1.DataBind()
End Sub

Can't seem to figure out how to pass the TextBox1.Text to the stored procedure.

TIA

 

[chrissie1]

straight from MSDN helpfile

Public Sub AddSqlParameters()
    ' ...
    ' create myDataSet and myDataAdapter
    ' ...
    myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", SqlDbType.VarChar, 80).Value = "toasters"
    myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", SqlDbType.Int).Value = 239
    
    myDataAdapter.Fill(myDataSet)
End Sub 'AddSqlParameters

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[mrrrl]

I think I am on the right course, I tried this using your answer:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
SqlDataAdapter1.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 40).Value = "Ringer"
DataSet31.Clear()
SqlDataAdapter1.Fill(DataSet31)
DataGrid1.DataBind()
End Sub

But I get this error:

Server Error in '/WebApplication2' Application.
--------------------------------------------------------------------------------

Too many arguments were supplied for procedure NewSelectCommand_Test_1.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Too many arguments were supplied for procedure NewSelectCommand_Test_1.

Source Error:


Line 78: SqlDataAdapter1.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 40).Value = "Ringer"
Line 79: DataSet31.Clear()
Line 80: SqlDataAdapter1.Fill(DataSet31)
Line 81: DataGrid1.DataBind()
Line 82: End Sub


Source File: c:\inetpub\

http://wwwroot\WebApplication2\WebForm1.aspx.vb

Line: 80

Stack Trace:


[SqlException: Too many arguments were supplied for procedure NewSelectCommand_Test_1.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
WebApplication2.WebForm1.Button3_Click(Object sender, EventArgs e) in c:\inetpub\

http://wwwroot\WebApplication2\WebForm1.aspx.vb:80

System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

 

[mrrrl]

Anyone have ay ideas why I am getting this error?

Too many arguments were supplied for procedure NewSelectCommand_Test_1.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
SqlDataAdapter1.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 40).Value = "Ringer"
DataSet31.Clear()
SqlDataAdapter1.Fill(DataSet31)
DataGrid1.DataBind()
End Sub

Its erroring on the second SqlDataAdapter1.

TIA

 

[Kris11]

Do you have @name argument on NewSelectCommand_Test_1 stored procedure and are you connecting to the same database where you are making changes on?

-Kris

 

[mrrrl]

Yes to both.

Here is the stored procedure:

CREATE PROCEDURE dbo.NewSelectCommand_Test_1
@name text
AS
SET NOCOUNT ON;
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM authors WHERE (au_lname like @name)

And I am connecting to the same database.

 

[Kris11]

Can you put a break point at the line

 SqlDataAdapter1.Fill(DataSet31)

and see what is SqlDataAdapter1.SelectCommand.CommandText. I am guessing that it is a different SP than NewSelectCommand_Test_1

-Kris

 

[bobisto]

You shouln't add parameter to a command on mouse click. Every time you click that button, the parameter is added. You should just set that previously added parameter to some value.

Use a Sql Profiler to see the sql statement. See does that statement will execute in Query analizer.