datareader trouble

Shell84 · April 4th, 2006, 05:12 PM

Hi all,

I'm trying to create a datareader that will bring back specific information based on the querystring that is passed on the page. however i am having trouble adding the querystring as a parameter in the datareader, it seems it is easy to add it if it is in a textbox but not a querystring.

Here is the code, i want it to bring back results based on the page it is on, eg details.aspx?cust_ID=1

Dim db As SqlConnection
Dim selectcustomer As SqlCommand
Dim dtrcustomers As SqlDataReader

db = New SqlConnection("connectionstring")
db.Open()
selectcustomer = New SqlCommand("Select * from customers Where cust_ID = '@cust_ID'", db)
selectcustomer.Parameters.AddWithValue("@cust_ID", cust_ID)
    dtrcustomers = selectcustomer.ExecuteReader()
        If dtrcustomers.HasRows Then
            While dtrcustomers.Read()
                Response.Write(CStr(dtrcustomers.Item("cust_order" )))
                Response.Write("<BR>")
            End While
        Else
            Response.Write("There are no customers")
        End If
        dtrcustomers.Close()
        db.Close()

rodmcleay · April 4th, 2006, 07:47 PM

I'm not too sure about adding parameters to text queries in that manner.
Try: ("Select * from customers Where cust_ID = '" + cust_ID + "'",db)
Adding Parameters in the manner you have is for Stored Proceedures, but I may be wrong, never tried or seen it like that before.
But also, If the cust_ID is a integer then the single quotes are not needed/permitted.
So it will be: ("Select * from customers Where cust_ID = " + cust_ID ,db)

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Shell84 · April 5th, 2006, 06:22 PM

Hi,

Many thanks for your reply. I'm using an example from a book and it made no mention of a stored procedure. So maybe it is not possible to add a querystring parameter as a parameter in a datareader. I just thought it was because you can have that kind of parameter if you use the sqldatasource.

Is there another way I can get at the information based on the cust_ID the page is on and output as a string? I'm new to asp.net 2.0 and i'm finding loads of neat controls and features i never knew existed!

Thanks

Michelle

rodmcleay · April 6th, 2006, 02:47 AM

The parameter does not effect the reader as such, but rather the query.
If it is in a book I am probably wrong about the parameter, It just didn't look right to me.
Your code looks ok aside from that to me, try this:

Code:

Dim db As SqlConnection
Dim selectcustomer As SqlCommand
Dim dtrcustomers As SqlDataReader

db = New SqlConnection("connectionstring")
db.Open()
selectcustomer = New SqlCommand("Select * from customers Where cust_ID = '" & cust_ID & "'", db)
    dtrcustomers = selectcustomer.ExecuteReader()
        If dtrcustomers.HasRows Then
            While dtrcustomers.Read()
                Response.Write(CStr(dtrcustomers.Item("cust_order")))
                Response.Write("<BR>")
            End While
        Else
            Response.Write("There are no customers")
        End If
        dtrcustomers.Close()
        db.Close()

and if that doesn't work try without the single quotes '.

Code:

selectcustomer = New SqlCommand("Select * from customers Where cust_ID = " & cust_ID, db)

it depends on the type of the database ID "cust_ID", is it integer or varchar, etc?
Check out this link for some more info on the different ways of getting data.
BUT there is nothing wrong with how you are trying to do it, just have to keep trying until you get it right.
http://samples.gotdotnet.com/quickst...soverview.aspx

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================