This topic is locked

Stored Procedure with out put parameter

8/16/2020 9:10:10 AM
ASPRunner.NET General questions
W
Willie531 authorDevClub member

Can't seem to get this to work, I keep getting Null Reference errors .

I know the storeprocedure works and has been tested, can anyone see what I'm doing wrong here?

btw using mssql Server 2016
Thank you in advance.
DB.Query("EXEC dbo.sp_GetNewCustomerRow @CustomerNo");

dynamic rs = DB.Query("Select @CustomerNo;"); //rs.fetchAssoc();

dynamic data = rs.Value(0); //rs.Value(@CustomerNo);

if(data)

{

pageObject.setProxyValue("pv_CustomerNo", data["CustomerNo"]);

}

else

{

MVCFunctions.Echo("Something Wrong In Before display calcs");

}

FrankR_ENTA 8/16/2020



Can't seem to get this to work, I keep getting Null Reference errors .

I know the storeprocedure works and has been tested, can anyone see what I'm doing wrong here?

btw using mssql Server 2016
Thank you in advance.
DB.Query("EXEC dbo.sp_GetNewCustomerRow @CustomerNo");

dynamic rs = DB.Query("Select @CustomerNo;"); //rs.fetchAssoc();

dynamic data = rs.Value(0); //rs.Value(@CustomerNo);

if(data)

{

pageObject.setProxyValue("pv_CustomerNo", data["CustomerNo"]);

}

else

{

MVCFunctions.Echo("Something Wrong In Before display calcs");

}


I'll do my best to help you today. I have done a great deal with stored procedures successfully.
First, give us some background on what you are trying to achieve. You posted some manually created C#.

What version do you have? If you have V10.4, do you want help walking through the set up of a SQL View?

W
Willie531 authorDevClub member 8/16/2020

Hi Frank,

Currently using the 10.4.
Basically the storeprocedure returns back the next customer number as OUTPUT.
I need to be able to get this number.



I'll do my best to help you today. I have done a great deal with stored procedures successfully.
First, give us some background on what you are trying to achieve. You posted some manually created C#.

What version do you have? If you have V10.4, do you want help walking through the set up of a SQL View?

FrankR_ENTA 8/16/2020



Hi Frank,

Currently using the 10.4.
Basically the storeprocedure returns back the next customer number as OUTPUT.
I need to be able to get this number.


You have no relationship between the first and second DB.Query's. A typical use of DB.Query is:



dynamic data;

dynamic rs = DB.Query("select * from carsmake");

while(data = rs.fetchAssoc())
W
Willie531 authorDevClub member 8/18/2020

Thank you Frank
But this doesn't help with working stored procedures having output parameters



You have no relationship between the first and second DB.Query's. A typical use of DB.Query is:



dynamic data;

dynamic rs = DB.Query("select * from carsmake");

while(data = rs.fetchAssoc())


W
WebReports 8/18/2020

How about something like:
string newid = tDAL.DBLookup("exec GetNewID");

W
Willie531 authorDevClub member 8/19/2020

Thank you, but no.
you're looking at a result value, not the output parameters.

EXEC uspFindProductByModel @model_year = 2018, @product_count = @count OUTPUT;



How about something like:
string newid = tDAL.DBLookup("exec GetNewID");

D
drochel 9/7/2020

I am using a stored procedure to return a value for an email distribution list. Here is how I am executing the SP and obtaining the results.
dynamic rs = DB.Query("EXEC [dbo].[usp_Email_Recipient_List] @UserRole = N'Recruiter'");

XVar data = rs.fetchAssoc();
string EmailDistributionList = data["RecipientList"];

I originally was only receiving NULL results and was confused because the SP was working fine if ran directly. I finally determined that I needed to update the select statement my SP that returned the results. The "SELECT @RecipientList" in my SP had to be changed to "SELECT @RecipientListas RecipientList" to allow me to capture the results returned.
Hope this helps.

H
heilmanmd 9/10/2020

Hello
I used stored procs a lot in my projects...
I'm using .NET ver 9.8 ( I know bit old, but heck... it works for what I need )
I'm also using DB2 ( don't worry concept is the same )
here is a working example of calling a stored proc that returns stuff and then using that returned stuff
in a word, it works, I have stored procs that return arrays of stuff as well
Hope this helps...
Best

Mark
// THIS SETS UP FOR THE HDG, FLOOR, BLOCK SO NEED TO ADJUST THE SLAB / CHNL NUM OPTIONS WHEN PICK THE BLOCK...

sqlcmd = "call NYEGEONET.get_availchnls ( " + hdgid + ",'" + floor + "');";

rs = tDAL.CustomQuery(sqlcmd);

datars = CommonFunctions.db_fetch_array(rs);

if (datars) {

fns = String.Format("{0}", datars["FNS"]);

lchnl = String.Format("{0}", datars["LCHNL"]);

clgeo = String.Format("{0}", datars["LGEO"]);

cldate = String.Format("{0}", datars["LDATE"]);

clmtype = String.Format("{0}", datars["LMTYPE"]);

cldist = String.Format("{0}", datars["LDIST"]);

if (clgeo.Length > 0) {

cfm = "<b><font size='1' color='blue'>Avail Chnls calcd on Visit </font><b> " +

"
" +

"<b><font size='1' color='blue'>Dist: </font><font size='1' color='black'>&nbsp;" + cldist + "&nbsp;</font></b>" +

"<b><font size='1' color='blue'>Geo: </font> <font size='1' color='black'>&nbsp;" + clgeo + "&nbsp;</font></b>" +

"<b><font size='1' color='blue'>Chnl: </font> <font size='1' color='black'>&nbsp;" + lchnl + "&nbsp;</font></b>" +

"
" +

"<b><font size='1' color='blue'>Mine Meth: </font><b><font size='1' color='black'>&nbsp;" + clmtype + "&nbsp;</font></b>" +

"<b><font size='1' color='blue'>Date: </font> <font size='1' color='black'>&nbsp;" + cldate + "&nbsp;</font></b>";

} else {

cfm = "<b><font size='1' color='blue'>No prior Chnl #'s</font></b>";

}

}