OleDbDataReader with Stored Procedure producing Syntax Error

[KornGeek]

I'm very new to both ASP and ASP.Net. I'm working to convert an old ASP page to ASP.Net using VB. My code looks something like this:

1  <%@ Page Language="VB" Debug = True%>
2  <%@ Import Namespace="System.Data" %>
3  <%@ Import Namespace="System.Data.OleDb" %>
4
5  <%
6  Response.Buffer = True
7  %>
8  <script runat="server">
9  dim login as string
10 dim password as string
11 dim connstr as string
12 dim conn as OleDbConnection
13 dim objCommand as OleDbCommand
14 dim objReader as OleDbDataReader
15
16 sub Page_Load(Sender as Object, e as EventArgs)
17   login = Session("login")
18   password = Session("password")
19   Response.Clear
20   connstr = "Provider=SQLOLEDB;Initial Catalog=MyDataBase;UID=" & login & ";Pwd=" & password & ";"
21   conn = new OleDbConnection(connstr)
22   objCommand = new OleDbCommand("MyStoredProc 'x', 1", conn)
23   objCommand.CommandType = CommandType.StoredProcedure
24   objReader = objCommand.ExecuteReader
...

I'm getting an OleDBException on line 24 reading "Syntax error or access violation."

If I manually execute the stored procedure, it works and returns the correct records.

If I remove line 23, it runs, but objReader.RecordsAffected is set to -1, and it behaves as if no records were returned.

Any ideas what I'm doing wrong? I appreciate any assistance.

 

[DotNetGnat]

did you put the:

SET NOCOUNT ON

in your stored procedure...if not, put it as the first line...

-DNG

 

[KornGeek]

DotNetGnat,

Thanks for the suggestion. I tried it, but still got the same result.

By the way, in my original post, I left out the line where I state conn.open, but it's in my actual code. (lives between lines 21 and 22 of my posted code)

 

[DotNetGnat]

shouldnt you say:

objCommand = new OleDbCommand("[red]EXEC[/red] MyStoredProc 'x', 1", conn)

-DNG

 

[dvannoy]

also, you should really put your connection string in your web config file.

 

[KornGeek]

Thanks for the feedback.

dvannoy,
Again, I'm very new to this. What's the rationale for putting the connection string in the web config file?

DotNetGnat,
I'm basing my code on examples from Sam's Publishing's Teach Yourself ASP.Net in 21 Days. In these examples, it doesn't use EXEC.


Ok, now an update (and further confusion on my part). I changed the syntax for my stored procedure parameters from in-line to using the OleDbParameters collection. This resulted in similar behavior to removing line 23 of my listing above, even though I didn't remove that line.

My code was now returning what appeared to be an empty reader (based on the RecordsAffected property being set to -1). It was suggested to me that I should try ignoring this value and proceeding anyway. When I did that, it returned the records I wanted.

So, in short, I got it working, but there are a few things that don't make sense to me.

A) Why did it function when I used the OleDbParameters collection with a stored procedure, but not when I coded it in-line?
B) Why is the RecordsAffected property consistently showing -1?
C) Is there a reliable way to determine if no records are returned?

Also, when I went to extract the data from the reader, I was previously using this syntax:

objReader.GetString("FieldName")

However, I was getting an error (don't remember the wording).
I changed to this syntax:

objReader.GetString(0)

Is there some way to extract the fields based on the field names? Having to know the ordering of the fields seems like an overly manual and error-prone technique.

Thanks for all of the help.

 

[dvannoy]

in 1.1 web config settings below

<appSettings>
<add key="AnyNameYouWant" value="Data Source=x.x.x.x;Initial Catalog=YourDBname;User Id=blaaa;Password=blaaaa;" />
</appSettings>


now on your code behind

Dim cn As New SqlConnection(ConfigurationSettings.AppSettings("YourKeyValue"))

then open your connection etc...

also,if you using SQL server it might be a good idea to use
the sqlclient instead of oledb.

again, the above is only for 1.1, I am not yet on 2.0

 

[KornGeek]

dvannoy,
Thanks, I'm going to look into this idea.

I've been able to answer a few of my own questions.

The reason the RecordsAffected property wasn't doing what I expected is that it's not meant to. The HasRows property is what I should have been using. This also provides a way to determine if no records have been found.

Instead of relying the the ordinal number to get field data, I instead used this format:

objReader("FieldName")

It does exactly what I was seeking.