Click here to Skip to main content
16,014,392 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have an application that is supposed to take a decimal input, push into a stored procedure and return a char value for use in the application.

For some reason, all of my other stored procedures seem to be working fine, however this one particular SP is not working at all.

Originally, the input was in the form of a dropdown list, however the values in the list were incorrect.

For example: 0.975 in the SQL table would show up as 1.000 in the drop down list.
1.025 somehow became 1.050. 1.075 became 2.000

So I tossed out the drop down list in favor of a text box. I still am not receiving proper return values. Actually, I am not getting any values back at all.

Here is my code:

Application Page
ASP.NET
<asp:Label ID="Label2" runat="server" Text="Select RZD: "></asp:Label>
     <asp:TextBox ID="rzdTB" runat="server"></asp:TextBox>
<asp:Label ID="rzdFSLabel" runat="server" Text="Label"></asp:Label>


Code Behind
C#
fileNameGenerator fNG = new fileNameGenerator();
fNG.rzd = Convert.ToDouble(rzdTB.Text);
fNG.fileGenerator();
rzdFSLabel.Text = fNG.rzdfsOUT;

Label13.Text = fNG.bcfsOUT + fNG.rzdfsOUT + fNG.anglefsOUT + fNG.powerfsOUT;
lBO.fileName = fNG.bcfsOUT + fNG.rzdfsOUT + fNG.anglefsOUT + fNG.powerfsOUT;


fileNameGenerator
HTML
//declare rzd variable - convert to double
//declare rzdSymbol as the output
c2.Parameters.AddWithValue("@rzd", SqlDbType.Float).Value =rzd;
SqlParameter rzdSymbol = new SqlParameter("@rzdSymbol", SqlDbType.VarChar);
rzdSymbol.Size = 2;
rzdSymbol.Direction = ParameterDirection.Output;
c2.Parameters.Add(rzdSymbol);

//open the connection string and execute the sproc
conn.Open();
c1.ExecuteNonQuery();
c2.ExecuteNonQuery();
c3.ExecuteNonQuery();
c4.ExecuteNonQuery();
conn.Close();

bcfsOUT = c1.Parameters["@bcFS"].Value.ToString();

//string sdfsOUT to label2
rzdfsOUT = c2.Parameters["@rzdSymbol"].Value.ToString();


Stored Procedure
SQL
ALTER PROCEDURE dbo.getRZDSymbol
	(
		@rzd decimal(10,5),
		@rzdSymbol varchar(2) OUTPUT
	)
AS
	SET NOCOUNT ON
	SELECT @rzdSymbol = rzdSymbol
	FROM fileSymbolTable
	WHERE rzd = @rzd


thank you for the help.
Posted
Comments
virusstorm 25-Jun-15 15:25pm    
Have you executed the stored procedure in management studio and verified that it is working as expected?
Member 11792770 25-Jun-15 18:02pm    
I have not and am not quite sure how to execute this in SSMS.

What I have done is checked the connection and tried a baseic select query:

Select rzdSymbol
From fileSymbolTable
Where rzd = @rzd

This query works perfectly fine.

On top of that, my other queries work just fine.

ALTER PROCEDURE dbo.getBaseCurveSymbol
(
@bc decimal(5,3),
@bcFS varchar(2) OUTPUT
)
AS
SET NOCOUNT ON
SELECT @bcFS = baseCurveSymbol
FROM fileSymbolTable
WHERE baseCurve = @bc


This query above SHOULD be exactly the same, at least from my eyes it is.

When the program executes, this stored procedure works and returns the expected characters.

You may call me stumped if you like.
Member 11792770 25-Jun-15 18:22pm    
I forgot to mention, I CAN test the query in Visual Studio.

The query seems to work if I put in the rzd value and clear out the "NULL" or "DEFAULT" in the rzdSymbol.
ZurdoDev 25-Jun-15 16:11pm    
You'll need to debug it and narrow down where the issue is.
Member 11792770 25-Jun-15 18:25pm    
I don't suppose you could give me some ideas how to debug this?

If I go line by line in the program, the best I can do is follow the code as it progresses, but once the stored procedure goes...I'm not sure how to check.

1 solution

had a deep look at the c2 parameter while debugging. I kept seeing "FLOAT" as the rzd datatype. That seemed rather odd so I began investigating where exactly I was pulling data from. TO make matters more confusing, I have two databases on my local machine and one on a development server on our network. I believed I was still hooked into the local machine, but it turns I was pulling from the networked db.

Drop the table on the network, re-imported from the local machine and BAM!

All of a sudden it works. Imagine that...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900