VB.NET calling stored procedure, nothing returned.

Hello Experts,

I am attempting to convert a .NET 1.1 application to .NET 2.0. Part of this conversion is calling a stored procedure from VB.NET to retrieve 3 fields for a document selected from a drop down. The call worked without incident in the old environment (SQL 2000 & .NET 1.1). However, now I get nothing returned from the stored proc.
 
The VB.net code:

Using cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
                Try
                    Dim cmd As New Data.SqlClient.SqlCommand()
                    cmd.Connection = cn
                    cmd.CommandText = MyStoredProc1"
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@WebApplication", SqlDbType.NVarChar, 10)
                    cmd.Parameters("@WebApplication").Value = "MyWebApp"
                    cmd.Parameters.Add("@LIP_Name", SqlDbType.NVarChar, 50)
                    cmd.Parameters("@DOC_Name").Value = DOC_Name

                    cn.Open()

                    Dim myReader As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
                    If myReader.HasRows Then
                        Do While myReader.Read
                            Response.Write("{0}: {l:C}", myReader(0), myReader(1))
                        Loop
                    End If
                Catch ex As Exception
                    Throw
                Finally
                    cn.Close()
                End Try
            End Using

The reader returns no rows at all. I have verified the connection is good and the vars being used are populated with valid values for which I should get returned data.

Thoughts?  

thanks!
dcrosleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joel CoehoornDirector of Information TechnologyCommented:
Not sure if it will fix the problem, but I can clean up your code a bit.  In summary:
There's no need for the try block, since the Using statement guarantees the connection will be closed and you just re-throw any exception anyway.  If you structure the loop different there's no need for the "If myReader.HasRows".  I also condensed some of the code to create the command.  Most signficantly, you were missing a " with the procedure name, but that's probably just a copy/paste error.
Using cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
    Dim cmd As New Data.SqlClient.SqlCommand("MyStoredProc1", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@WebApplication", SqlDbType.NVarChar, 10).Value = "MyWebApp"
    cmd.Parameters.Add("@LIP_Name", SqlDbType.NVarChar, 50).Value = Doc_Name
        
    cn.Open()
 
    Dim myReader As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
    While myReader.Read
        Response.Write("{0}: {l:C}", myReader(0), myReader(1))
    End While
             
End Using

Open in new window

0
dcrosleyAuthor Commented:
Hi jcoehoorn,

That certainly does clean up the code. But still having the same problem, nothing is returned. I took a closer look at the command values. The parameters have this error:

In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

What does this mean? Is this why I'm not receiving output?

thanks!
0
Joel CoehoornDirector of Information TechnologyCommented:
I had to google that message.  You've probably already done the same, and I did find a few things, but nothing really conclusive.  I learned it could show up if you read from a datareader, dataset, or datarow before it is filled, but I don't think that's the case.  I also saw some things to suggest that it's a bug in the .Net CLR or there's a security issue if the code in question depends on several private assemblies, which also seems not to be the case.

To debug this, I would move that code to a simple console app where it's easier to set break points.  Then change response.write to console.write and put a break point there.  When you reach the break point, check the values of myReader(0), myReader(1), and everything associated with them (including the connection state and any properties of myReader that look helpful) in the debugger.
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

dcrosleyAuthor Commented:
I had also, but didn't find anything which helped.

When I break the value of myReader(0) & myReader(1), I get an error message: "Invalid attempt to read when no data is present.".  Seems this would be due to nothing being returned, which likely has something to do with the parameter issue listed above.

Thoughs?
0
Joel CoehoornDirector of Information TechnologyCommented:
Just to do something different, you could try leaving the command type as the default, and use this for your command text: "MyStoredProc1 @WebApplication, @LIP_Name".   But I'm just grasping now.  What you have *should* work, assuming a normal running SQL Server and that your connection string and stored procedure are correct.
0
dcrosleyAuthor Commented:
Trying that, I'm getting an error stating it could not find the stored procedure. Looks like it's searching for the entire string to be the SP name.

I know the db connection & string are good.

I am not 100% sure of the stored procedure as I'm new to SP's.

Here it is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Letter_Folder_S2_SP]
@WebApplication VARCHAR(10),
@LIP_Name VARCHAR(50)
AS
SELECT Doc_Folder,
      Doc_Name,
      Form
FROM LetterTranslation
WHERE WebApplication=@WebApplication
AND LIP_Name = @LIP_Name



Does it look ok?  The name is Letter_Folder_S2_SP - which is what I'm actually calling from the vb.net code, not MyStoredProcedure as stated in the code snippet above.
0
Joel CoehoornDirector of Information TechnologyCommented:
It looks okay.  You could try running it from query analyzer, but I don't think it will help:  If the procedure threw an error we'd get a database exception telling us about the error.  If it just didn't return any rows the first call to myReader.Read would return false and we'd never reach the response.write line.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dcrosleyAuthor Commented:
It isn't returning any rows at all.
0
dcrosleyAuthor Commented:
Holy cow....turned out to be a pretty simple / stupid mistake. I had to rebuild a dropdown from which the "LIP_Name" field was being populated after the conversion to 2.0. I had assigned the wrong field from the db to the value of the dropdown, but used the correct field on my stored procedure call.

All is good....thanks for your help, your last comment about getting no rows tripped a light..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.