i think i might know why you don't understand my problem
the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.
wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)
when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).
I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.
if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.
Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.
Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;
I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.
Originally posted by nano_electronix
i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).
first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.
i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.
i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.