problem with sqldatareader

Results 1 to 2 of 2

Thread: problem with sqldatareader

  1. #1
    Join Date
    Dec 1969

    Default problem with sqldatareader

    Basically I am executing the code below, which will return the identity of the record inserted into the database, but when I try to read the value of the identity, I get the following error?? Thanks in advance.<BR><BR>System.InvalidOperationException: Invalid attempt to read when no data is present. at System.Data.SqlClient.SqlDataReader.PrepareSQLReco rd(Int32 i) at System.Data.SqlClient.SqlDataReader.GetSqlString(I nt32 i) at System.Data.SqlClient.SqlDataReader.GetString(Int3 2 i) at ASP.Prod_Maint1_aspx.Page_Load(Object sender, EventArgs e)<BR><BR><BR><BR>sQuery = "exec proc_insert_product_dtls N" & sProd_Cd & ", N" & sProd_Desc & ", N" & sActive & ", N&#039;ENG&#039;"<BR> <BR>moComm1 = New SqlCommand(sQuery, DBConn.moConn)<BR>objDataReader = moComm1.ExecuteReader()<BR><BR>dim iIdentity as String<BR>iIdentity = objDataReader.GetString(0)

  2. #2
    Join Date
    Dec 1969

    Default Have to READ() first

    Unlike ADODB.Recordset, where the cursor is pointing to the first record when you first open/fill the RS, a DataReader is positioned *BEFORE* the first record.<BR><BR>So, indeed, there is no data present until you do the read.<BR><BR>The basic code for using a datareader is:<BR><BR>Do While rdr.Read( )<BR> whatever = rdr.GetString(0) <BR> ... etc. ...<BR>Loop<BR><BR>You do *NOT* use a MoveNext! The Read() call does the equivalent of movenext for you.<BR><BR>But now I have to ask:<BR><BR>I presume that what you are doing here is inserting a record and then getting back a single @@IDENTITY value?<BR><BR>So why are you expecting a STRING???? @@IDENTITY values are simple integers!<BR><BR>And, by the way, if all you need back is that single integer, then you don&#039;t need a datareader at all! Look into SQLCommand.ExecuteScalar or something like that...I forget the exact name, but it starts with "Execute" and indeed returns a single integer value, just for situations like this one.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts