Move next and back through rows using datareader

I have multiple dropdownlists each one filled with values from a specific column in the table. Also I have multiple textboxes corresponding to dropdownlists. For example, when I select an item from dropdownlistA, all the textboxes are filled with the first row values that contains that selected item and gives the number of rows containing this value……. In addition, I have 2 buttons one is Move Forward Button and the other is Move Previous…I am using a Record Set and don't know how to move next and back throughout the selected rows…could you help me please? I am using a vb codebehind… Thanks

A datareader (a recordset should not be used in ASP.NET) is strictly forward only. But, even if it wasn't, the real issue here is that you need to either store the results of your query in some kind of state, between requests, or requery the database to get the previous or the next result.

Please show us a bit of your code, and we'll likely be able to devise a good method.

|||

Thanks for your response,

I have 6 dropdownlists like the following,

PrivateSub fillDropDownList3()

Dim sqlStringAsString = "select * from ItemName"

Dim cmdAsNew SqlCommand(sqlString, conn)

cmd.CommandType = CommandType.Text

Dim drAs SqlDataReader

conn.Open()

dr = cmd.ExecuteReader

While dr.Read

Me.DropDownList3.Items.Add(dr(0))

EndWhile

dr.Close()

conn.Close()

PrivateSub DropDownList3_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles DropDownList3.SelectedIndexChanged

Dim countAsIntegerDim sqlStringAsString = " select * from ReceiptItems where ItemName=@.ItemName"Dim cmdAsNew SqlCommand(sqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.Add("@.ItemName",Me.DropDownList3.SelectedItem.Text)

Dim drAs SqlDataReader

conn.Open()

dr = cmd.ExecuteReader

While dr.Read()

count = count + 1

Me.TextBox3.Text = dr("Name")

Me.TextBox4.Text = dr("Dept")

Me.TextBox5.Text = dr("ItemName")

Me.TextBox6.Text = dr("ItemDescription")

Me.TextBox7.Text = dr("ItemSN")

Me.TextBox8.Text = dr("Date")

EndWhile

dr.Close()

conn.Close()

Me.TextBox2.Text = count

EndSub

but in the textbox corresponding to it, it just gives the first result. What I need is to know how to move to the next record, or get back to the previous one by clicking a button...

Thanks

|||Hi diana_j86,
I've reviewed your code and it seems i can somewhat figure out what you want.However, I need to clarify frist that: You query string

select * from receiptitems whereitemname=@.ItemName

would return more than one values. If i'm wrong, please ignore my suggestion and inform me.

As Norrkoping has said, sqldatareader is strictly forward only so you can NOT use it if you want to get a previous record. I suggest you use dataset(datatable) with sqldataadapter.
You code should look like this: (Sorry, i'm not familiar with vb.net , so c# instead)

page-scope variables:

DataTable tbl = new DataTable();
int count = 0; //the count of records
int current = 0; // current record showed in your text boxes

dropdownlistbox select change event handle: 

 SqlDataAdapter adpter =new SqlDataAdapter(); adpter.SelectCommand =new SqlCommand("select * from receiptItems where itemsname=@.ItemName"); adpter.SelectCommand.Parameters.Add("@.ItemName",dropdownlist3.SelectdItem.Text); adpter.Fill(tbl); count = tbl.Rows.Count;

 current=0; // ALWAYS SHOW THE FIRST ONE AFTER SLECT CHANGED textbox2.text=tbl[0][0];

 .................

Next_Button_Clicked Event handler: (Previous_Button_Clicked Event likewise)

textbox2.text=rows[current+1][0];

textbox3.text=rows[current+1][1];

....

Hope my suggestion helps

|||

Thanks, Bo Chen – MSFT.

Move data from SqlDataSource to a TextBox

I have a DataReader on a web page:<asp:SqlDataSourceID="ChangeInfoRecord"runat="server"ConnectionString........

I want to display the value of one of the returned field in a text box. What is the correct syntax? I tried

RolledFromDisplay.Text = ChangeInfoRecord("RolledFrom")

but that isn't it.

You can do something like this:

DataView dv = SqlDataSource1.Select(DataSourceSelectArguments.Empty)as DataView;TextBox1.Text = dv[0]["Name"].ToString();