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.