navigate through sql records [RESOLVED]

Have a problem and can't put my finger on how to get it to work.

I'm making a webform, in the webform there are textbox controls. I'll use 3 for this example.

txtFirstName
txtMI
txtLastName

I also have a SQL table, (connections already created and working) where i have 3 columns: FirstName, MI, LastName. They also have a primary key called RecId which is just an autonumber.

I have 2 buttons: btnPrev and btnNext. See where I'm going with this? I want to click btnNext and have it put the values of the three columns in sql into the corrosponding 3 textboxes, and when I hit btnNext again, go to the next record, and so forth. When I hit btnPrev i want it to move to the previous record in the database.

I'm thinking I need to create a dataset holding this information, but getting it from the dataset to the txtboxes is stumping me.

PLEASE HELP!!! Thank you guys so much!! :wave:

This is a tough call...

Putting the results in a dataset will save you from querying the database each time, which if you're only show one row at a time is rather ridiculous. So a dataset is a good move here.

That said, if you have 100,000 rows that you are attempting to put into a dataset, that might cause problems for the server.

You're going to need to make the architecture a little bit more sophisticated.

You will need at least a stored proc that accepts as parameters a begin row and an end row.

In that way, you can say get the next 10 rows (or 15 or 20), and store those results into a dataset.

Then... you can either cache the dataset on the server, which may be best if this is a local project... or save the dataset to viewstate, which I would recommend regardless.

To save a dataset to viewstate however, you have to translate into XML using the WriteXML method and when the webpage posts back, you have to use ReadXML method to read the dataset back in.

And of course you save that XML in a property of your webpage that takes a string as an argument (because that's all XML is is a string of characters).

.... give some feedback...

So that means you know how to fill the dataset, right?

To fill the textboxes

VB Code:

TextBox1.Text = ds.Tables("TableName").Rows(intRowNumber).Item(intColumnNumber).ToString

Quote:

---

Originally posted by nemaroller

That said, if you have 100,000 rows that you are attempting to put into a dataset, that might cause problems for the server.

---

It won't be near that many, 200 at the most.

:)

and yes I know how to fill the dataset, its just getting the values into the textboxes, and then having the buttons work

er, hope i'm not too vague

THANKS FOR THE REPLYS! You guys are awsome

Did my code work for you?

Quote:

---

Originally posted by mendhak
Did my code work for you?

---

Everything but the last part, where .tostring comes in.

Only will let me put .gettype

hmmmm

That's probably because of late binding. It doesn't give you a error when you put ToString, does it? To be honest you shouldn't even need the ToString, just make sure you check the datatype so you don't try to use data that is DBNull and hose yourself;

If MyDataRow.Item("MyField").GetType() Is _
System.Type.GetType("System.String") Then

txtTextBox.text = MyDataRow.Item("MyField")
End if

ok doesn't need the tostring

worked perfectly!!! now i just need help telling it to go to the next record, and i have about 12 txtboxes to fill. should i make a class maybe? or am i heading in the totally wrong direction

Quote:

---

Originally posted by drpcken
ok doesn't need the tostring

worked perfectly!!! now i just need help telling it to go to the next record, and i have about 12 txtboxes to fill. should i make a class maybe? or am i heading in the totally wrong direction

---

No, it doesn't need ToString, but I always put it there. Late binding allows it, so you can just type it in there, you wont' face a problem.


OK, about the second part of your question: Unlike ADO in VB6, you don't "go" to the next record. The dataset is a collection of tables, containing a collection of rows, containing a collection of values in columns.

So you have to run a loop.

For example, when the user clicks on the NEXT button, increment a intRowNumber by 1, and then run the same line of code again.

This should also give you an idea: Place the code to fill the form in a subroutine/function, passing a variable to it, telling it which row you want.

Comprende vouz?

Yes I understand, but I think I've found another problem here.

I'm having trouble importing the System.Windows.Forms namespace.

I did some research, and found you can't import that namespace into a Web Application in VB.net.

Is this true???

Is there a workaround for this?

You guys have been awsome, thanks for all your help!

Ok i'm on a roll here, but have question.

txtMI.Text = DsProspectsFamilies_Contact1.Tables("ProspectsFamilies_Contact").Rows("0").Item("Middle_Initial")

the Middle_Initial column can have NULL values, When i run it, and one of them indeed does have a NULL value, i get the error

Cast from type 'DBNull' to type 'String' is not valid.

How can i tell it to put a blank value in the txtbox if the field is NULL

THANKS!!

Got it :)

If DsProspectsFamilies_Contact1.Tables("ProspectsFamilies_Contact").Rows("0").Item("Middle_Initial") Is DBNull.Value Then
txtMI.Text = ""
Else
txtMI.Text = DsProspectsFamilies_Contact1.Tables("ProspectsFamilies_Contact").Rows("0").Item("Middle_Initial").Ge tType.ToString
End If

Quote:

---

Originally posted by drpcken
Yes I understand, but I think I've found another problem here.

I'm having trouble importing the System.Windows.Forms namespace.

I did some research, and found you can't import that namespace into a Web Application in VB.net.

Is this true???

Is there a workaround for this?

You guys have been awsome, thanks for all your help!

---

I'm assuming you have this one solved as well?

Yes I did. You can't import System.Windows.Forms into a Web Application apparently.

Will bind to each control, then use a variable to =+ through the records I think.

THanks alot though!

Quote:

---

Originally posted by drpcken
Yes I did. You can't import System.Windows.Forms into a Web Application apparently.

---

No.. you cannot because Windows.Forms is meant for fat-client development.

However, you CAN make a .NET applet using a Windows.Forms.UserControl..... see reference link below..

http://forums.devshed.com/showpost.p...99&postcount=5

Thank you I'll check that out!