current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to have a SqlDataSource coded programmatically with stored procedure and with parameters. Later I want to assign this SqlDataSource to a listbox as a datasource.But I am getting an error that the stored procedure needs a parameter that wasn't supplied. I do not understand why its giving me the error despite supplying it.

The Code I am using is as below:

sqlDS = new SqlDataSource();
sqlDS.ConnectionString = DC.ConnectionString;
sqlDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
sqlDS.SelectParameters.Add("@aPara_Name", TypeCode.String, aPara_Value);
sqlDS.SelectParameters[0].Direction = ParameterDirection.Input;
sqlDS.SelectCommand = "usp_StoredProcedure_1";
sqlDS.DataBind();
this.Controls.Add(sqlDS);

Listbox1.DataSource = sqlDS;
Listbox1.DataTextField = "Title";
Listbox1.DataValueField = "Value";
Listbox1.DataBind();   //this is where I get the error saying that stored procedure requires a parameter that wasn't passed!

can someone guide me where I am going wrong?

share|improve this question
    
Can you provide your sp code, please? –  Serge Jun 19 '13 at 9:27
    
@Serge - here u go CREATE PROCEDURE usp_StoredProcedure_1 @aPara_Name varchar(20) AS SELECT * FROM aTable WHERE (SUBUNI = @aPara_Name) AND (SUBWON LIKE '____') –  user1889838 Jun 19 '13 at 9:29
    
TypeCode.String... could you try with a SqlDbType instead (SqlDbType.Varchar that is) out of curiosity? –  Serge Jun 19 '13 at 9:30
    
@Serge, what would be syntax please? for .Add method? –  user1889838 Jun 19 '13 at 9:35
    
Actually I think the problem is you have to bind your parameter to a control (or a coocki, a querystring, ...) because it'll try to retrieve that binded element's value. If no such element exist, SqlDataSource might not be what you should use (you could try using a SqlDataAdapter which would fill a DataTable). –  Serge Jun 19 '13 at 9:50

3 Answers 3

up vote 0 down vote

Could you try this approach instead?

                var com = new SqlConnection(DC.ConnectionString).CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = @"usp_StoredProcedure_1";
                com.Parameters.Add("@aPara_Name", SqlDbType.VarChar, 20).Value = aPara_Value;

                var table = new DataTable();

                new SqlDataAdapter(com).Fill(table);

                Listbox1.DataSource = table;
share|improve this answer
    
yes I have tried to do it similar way and it works, I guess it was something with the way I was approaching, should have done it this way at the first place. Many thanks –  user1889838 Jun 19 '13 at 10:02
    
Dont forget to mark correct answer. It may help others. –  Microsoft DN Jun 19 '13 at 11:36
up vote 0 down vote

I was having the exact same problem and finally got the answer. It's simply a matter of not inserting the "@" sign when you declare the parameter in the "SelectParameters.Add()" method. So all you would have to do is to change the following line from:

sqlDS.SelectParameters.Add("@aPara_Name", TypeCode.String, aPara_Value);

to:

sqlDS.SelectParameters.Add("aPara_Name", TypeCode.String, aPara_Value);

Hope this helps.

share|improve this answer
up vote 0 down vote

I agree with @kumbaya. Faced the same issue. Removed the @, and it worked fine.

Your code at line 4 should be edited as 

sqlDS.SelectParameters.Add("aPara_Name", TypeCode.String, aPara_Value);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.