Stored Procedure Not Returning Same Value As Inline Sql VB.Net 2008

slbibs · March 10th, 2010, 03:15 PM

Hi, I am attempting to pass a parameter into a Sub. I have the following:

conn = connection string
dt = datatable
sqlCmd = sql command
sqlDa = sqlDataAdapter(sqlcmd)

Pertinent Code:

Code:

Sub getInfo(byVal txtName) 'where txtName is a value passed from a textbox on frmOther
 
Dim sqlCmd As New SqlCommand("Exec myProcedure ", Conn)
 
sqlCmd.Parameters.AddWithValue("@myName", "txtName")
Dim sqlDa As New SqlDataAdapter(sqlCmd)
 
sqlDa.Fill(dt)
 
frmOther.docTableAdapter.Fill(frmOther.DataSet.doc, txtName.ToString)
'Set form objects to returned values
frmOther.txtName.Text = dt.Rows(i)("Name").ToString
 
End Sub

Here is the Stored Procedure (pertinent info):

Code:

@myName AS varchar(50) = " "
 
AS
BEGIN
 
SELECT blah, blah
FROM mytable s,
WHERE s.tName = @myName
 
END

The EXACT same code (I'm really using) will work if my sqlCmd contains the actual sql code instead of the stored procedure. The only difference is that I am hard coding s.tName = 'someName' instead of passing it as a parameter. I am assuming my problem lies where I am passing the parameter to the stored procedure but everything I have tried (to this point) has failed. Any help is truly appreciated!

Thanks

Imar · March 10th, 2010, 03:43 PM

Hi there,

Take a look at this:

sqlCmd.Parameters.AddWithValue("@myName", "txtName")

You're not passing the value of txtName, but passing it as a literal...

Cheers,

Imar

slbibs · March 10th, 2010, 03:58 PM

Thanks Imar for the quick reply! I had actually tried that previously but to no avail as it returns the same "wrong" record whether or not I am passing a literal or value of. It is currently set to pass the value of per your suggestion.

I am connecting to the database (using the stored procedure) but it is not finding the appropriate record(s).

However if i execute the Sproc within Management Studio, it returns that actual records on the execution but it also gives me the following:

Code:

SELECT 'Return Value' = @return_value
 
And the resulting value of this is 0

I have not set @return_value anywhere in my Sproc...could this be a culprit? I can try to parse out confidential information and post the actual Sproc (if this would help) but again everything works on its own but if I try to pass the parameter (calling the Sproc via the Exec), I do not get back what i was expecting.

Imar · March 10th, 2010, 04:20 PM

And what record does it return? Does it return any data at all?

There are a few issues with your code that make it hard to track this down, especially for me. Try fixing that before you move on:

1. Strongly type your method parameters. What is txtName? Is it a string? Then make it explicit:

Sub getInfo(ByVal txtName As String)

That way, you can be sure you're not passing an entire TextBox which, using ToString, results in the literal TextBox instead of the actual value.

2. Don't use Exec. AFAIK, you don't need it.

3. What is i? Does it have the correct value?

Once you fix these issues, try turning on the SQL Profiler and see what gets sent to the database. Alternatively, select the input parameter:

SELECT @txtName FROM SomeTable

so you can what you're passing to it.

Debug the code and see what is being passed and what is being returned.

Also, this may help:http://msdn.microsoft.com/en-us/libr...y8(VS.71).aspx

And stored procedures have implicit return values if you don't specify them.

Cheers,

Imar

slbibs · March 10th, 2010, 04:49 PM

Imar,

These are excellent suggestions. I will try them out and if i find the culprit, I will make a notation of it in this thread.

Thanks for your time in this matter.

slbibs · March 10th, 2010, 06:17 PM

A brief update... it is now working as it should! I am not sure what went wrong (exactly) but I wanted to touch on the following:

I made my byVal (explicit)
Removed Exec from procedure call
i - is the number of rows returned from the query

When that didn't work, I went back to the drawing board to see what was wrong and the culprit ended up being my dataset Fill. Apparently during my haste to get the job done, I configured the Fill to look at the wrong Stored Procedure. That was why it was connecting but not returning what I was anticipating. Once I corrected that (and cleaned up my code, it worked as expected).

Thanks again for your help! You are a very good troubleshooter

Imar · March 11th, 2010, 04:16 AM

You're welcome....

Imar