how to pass in parameters in sqldatasource

[vcllvc]

I need to create a sqldatasource which connect to SQL Server 7 using OLEDbDataSource. The select involves two stored procedures and each procedure takes a parameter.

My question is how do I pass in the two parameters to the stored procedures correctly?

I tried to add the following :

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {

e.Command.Parameters.Add(new OleDbParameter("@dealer", dealerCode));
}

But I got the following error message:

"Procedure 'spINVOICE' expects parameter '@dealer', which was not supplied. "



Can somebody help, please?

 

[jbenson001]

You have to assian a value to the parameter you just created

 

[vcllvc]

oh, sorry, the dealerCode is assigned.

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {

string dealerCode ="12345";
e.Command.Parameters.Add(new OleDbParameter("@dealer", dealerCode));
}

But the problem doesn't resolve.

any thoughts??

 

[jbenson001]

You have a sqldatasource and are adding and OLEparameter.. try adding a sqlparameter...

 

[vcllvc]

actually, i can't.

The connection is OleDB, and if I use SQLParameter, it will give me the following:
"The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not SqlParameter objects. "

 

[vcllvc]

the situtation is that if I only call 1 stored procedured within the sqldatasource is fine, but if I try to call two SP within the datasource, it gives me a hard time.

 

[vcllvc]

this is the dataSource code:

<asp:SqlDataSource ID="sqldsMainProducerStatement" runat="server" ConnectionString="<%$ ConnectionStrings:OleDBConnectionString %>"
OnSelected="sqldsMainProducerStatement_Selected" OnSelecting="SqlDataSource2_Selecting"
ProviderName="<%$ ConnectionStrings:OleDBConnectionString.ProviderName %>" SelectCommand="spINVOICE;1" SelectCommandType="StoredProcedure"></asp:SqlDataSource>



This is the datasource selecting event:

protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {
string dealerCode = "";

if (Session["DEALERCODE"] != null) {
dealerCode = Session["DEALERCODE"].ToString();
}
e.Command.Parameters.Add(new OleDbParameter("@dealer", dealerCode));
}