executing a stored procedure

[bjr149]

When i exec my SQl server 2000 stored procedure in SQL server it gives me a table with like 10 entries. when i execute using datareader in VB, it gives me for Console.WriteLine(drAuthors.FieldCount) = 4 which is correct but when i try to loop through the record set there is not records?

VB
-----------------------------------------------------
ID = 1336
CC = "0010717"
money = "100.00"

Dim SqlServerConn As New SqlClient.SqlConnection(SQLConn)
SqlServerConn.Open()
STORPROC = New SqlClient.SqlCommand("dbo.p_get_dropdown", SqlServerConn)
STORPROC.CommandType = CommandType.StoredProcedure
STORPROC.Parameters.Add("@id", SqlDbType.Int)
STORPROC.Parameters("@id").Value = ID
STORPROC.Parameters.Add("@cc", SqlDbType.Char, 7)
STORPROC.Parameters("@cc").Value = CC
STORPROC.Parameters.Add("@money", SqlDbType.VarChar, 50)
STORPROC.Parameters("@money").Value = money
Dim drAuthors As SqlClient.SqlDataReader
drAuthors = STORPROC.ExecuteReader()
Console.WriteLine(drAuthors.FieldCount)
While drAuthors.Read()
MessageBox.Show("nothing" & drAuthors(0).ToString())
End While
drAuthors.Close()
SqlServerConn.Close()


SQL PROCEDURE
--------------------------
CREATE PROCEDURE dbo.p_get_dropdown
@id int, @cc char(7), @money varchar(50)
AS

.. . . . . .. .

SELECT * FROM #DROP_DOWN
order by drop_amt



Like is said when i "exec" this procedure in SQL server it returns like 10 rows. When i execute in VB it just returns the table with no rows but the 4 columns are there.

 

[mrdenny]

FieldCount gives you the number of fields returned not the number of records. RecordCount should give you the number of records. It doesn't always work correctly for stored procedures.

If you aren't getting any records back it doesn't like the data you are putting into your procs variables. You may be putting spaces or something.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mrdenny]

I forgot to mention you should run a trace while you are running the proc. That will show you what the SQL Server is getting from VB.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[bjr149]

that trace i run. where and how do i run it? when i execture the vb program it just runs it automatically. Where would i be able to monitor these variables and see whats being stored in them>?

 

[mrdenny]

Start up SQL Profiler and start a trace using it against the SQL Server. Then run your app. SQL Profiler will show you all the commands being executed against the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)