problem referencing field in sqldatareader

[andrew]

I have a db with 4 fields (id,field1, field2,field3).
I populate a datareader (named dr) with this sql
select * from table where id=2

Now i want to pre-set a textbox with the info in field1.
Seems like this should work
TextBox5.text=dr("field1")
i keep getting this error
Exception Details: System.InvalidOperationException:
Invalid attempt to read when no data is present.

So the first assumption is i've selected a row that
doesn't exist or that has no info in field1 BUT if i bind
the datareader to a datagrid i see the following info

id field1 field2 field3
2 sdf dgfsdg Choose

where isn't my reference working?

 

[Miha Markic]

Hi andrew,

Did you invoke dr.Read() method?

 

[William Ryan]

Have you called DataReader.Read or moved inot the reader? The Web Grid can
bind to a DataReader, but at some point, it's got to be executed and walked
through if you are going to get values from it. The Grid saves you from the
while(dr.Read()){ } but if you want to set the text box to a given value,
that's the likely culprit.

HTH,

Bill

 

[Andrew]

No this is what i have

sub Page_Load (sender As Object, e As EventArgs)
Dim strSQL as String
Dim objConnection as SqlConnection
Dim objCommand as SqlCommand

strSQL = ""
strSQL = strSQL & "select * from scratch
where id=2"
objConnection = New SqlConnection("Data
Source=xx.xx.xx.xx;" _
& "Initial Catalog=dbname;User
Id=userid;Password=xxxxxxxxxxx;" _
& "Connect Timeout=15;Network
Library=dbmssocn;")

objCommand = New SqlCommand(strSQL,
objConnection)

objCommand.Connection.Open()
Dim dr As SqlDataReader =
objCommand.ExecuteReader()
dgGrid.datasource=dr
dgGrid.DataBind()
'TextBox5.text=dr("field1")
objCommand.Connection.Close()
end sub

 

[William Ryan]

Andrew, you aren't calling .Read so there's nothing in position. you can
call it once and that will at least position it to the first record..

BTW, as an aside, you may want to use a StringBuilder or just declare strSql

Dim strSql as String = "SELECT * FROM Scratch WHERE ID = 2" and if you are
running this on the web, Stored Procs are life savers.

HTH,

Bill

 

[Andrew]

Hmm ithought since there was only one record (b/c of
where id=2) that wasn't necessary.

what is the proper sytnax for the .read() method?
just dr.read()?

i am unfamilar with the string builder but i read you
loud and clear on the stored proc. I'm just trying to
acclimate to .net and once i start actually building
applications i'll defiently use a stored proc.

 

[William Ryan]

If you only have one rec, then just call the dr.Read(); after your fired
executeReader. You'll notice that on the desktop you can't bind a grid to a
datareader (or anything that I'm aware of). The web grid and I believe the
listbox/combobox gives you this handy feature, but textboxes don't...so you
have to move into the recordset.



As far as StringBuilder....
//use using system.Text and you can have the abbreviated syntax
System.Text.StringBuilder sql= new System.Text.StringBuilder();
sql.Append("SELECT *");
sql.Append("FROM SomeTable");
sql.Append("WHERE Something = Something");

HTH,

Bill