VB.Net & SQLServer StoredProcedure

Peace

Hey,

I've got the following code to work for me at last, however i don't think its the best!

Basically what i want to do is connect to a sql server database and execute a stored procedure and take the results...and do whatever. However the actual executing the procedure and then the manipulation of the records seems terribly awkward to say the least.. this could just be my limited understanding of ADO.Net

        Dim mynum As Integer

        Dim SQLConnect As New OleDb.OleDbConnection()
        Dim SQLQuery As OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        SQLQuery = New OleDb.OleDbCommand()


        SQLConnect.ConnectionString = stConnectionString
        SQLConnect.Open()

        SQLQuery.CommandType = CommandType.StoredProcedure
        SQLQuery.CommandText = "ShowCustomers"
        SQLQuery.Connection() = SQLConnect
        SQLQuery.Parameters.Add("@MinCode", 1)
        SQLQuery.Parameters.Add("@MaxCode", 2)

        dr = SQLQuery.ExecuteReader
        dr.Read()
        mynum = dr.FieldCount()


        Console.WriteLine(dr(0))
        Console.WriteLine(dr(1))
        Console.WriteLine(dr(2))
        Console.WriteLine(dr(3))
        Console.WriteLine(dr(4))
        Console.WriteLine(dr(5))
        Console.WriteLine(dr(6))
        Console.WriteLine(dr(7))

StoredProcedures are new to me but i'm able to figure out pretty much whats going on....the major problem is accessing the data in dr - whats the best way to do it?

if i say "dr.NextResult()" the program throws and error saying
Err.Number -> 5 and the description is "No data exists for the Row/Column"

Whats the deal ehre...how do you get to the next row of results?

mynum i only put in there to satisfy my curiosity as to howmany columns were being returned.

Anyway, if theres a better way of doing it...believe me i'm all ears.

aidan_walsh

Are you working with VS.NET? In that case, in the same tabbox as your toolbox you should have a server explorer. If you can connect to your SQLServer database through this, you can drag your tables onto the form. This will create the connection for you. Next, right click on the connection and generate a dataset. This will import the data from the table and the ADD, UPDATE, DELETE, INSERT default stored procedures. I'm guessing that if you have specified any more they should also be included, or at least optional.

Peace

doodle,

i can't do that as its not a form i'm working on, its a console application. I could in theory change it to a form but i'd rather not! Although it would solve some problems for me....

amen

there are a few ways around this
simplest way is something like this
While dr.Read()
Console.WriteLine(dr.GetSqlValue(1))
End While

which basically keeps going until you have nothing left to read
btw I think the datareader keeps an open connection to the database until you are finished with it

mewso

The important concept is dr.Read(). The Read method reads the next row of data. thats why you use the Do while dr.Read() loop to go through each row.

Peace

Ah ok cool...what about dr.NextResult()? I was think thats what that call did!

bonkey

That would most likely be for when you have a stored procedure that contains multiple SELECT statements, thus generating multiple resultsets as its output.

Basically, Read() moves you to the next row in the current set of results. NextResult() moves you to the next set of results - if any.

jc