HELP pls get my code to work to populate textboxes and datagrids

[JustineAubrey]

Hi, I am creating a form application that should display information from one dataset into individual textboxes (ie. system name, system id, location name, location id). The textboxes populate upon the selection of a pair of cascading combo boxes. So, the user first selects a system name from the first combobox, then that selection will populate the second combo box for the locations of that system. (This means that each system may have more than one location.) Once the location is selected, each of the 4 textbox should populate with the corresponding data but I'm not quite sure how to write the code..

' Fill out Textboxes
        Dim con As MySqlConnection = New MySqlConnection("Server=fafsv-mysql;user id=root;Password=0263;Database=gsais")
        Dim cmd As MySqlCommand = New MySqlCommand("SELECT * FROM blsys_systems WHERE SystemName='" & LocationComboBox.Text.ToString & "'", con)
        con.Open()
        Dim myDA As MySqlDataAdapter = New MySqlDataAdapter(cmd)
        Dim ds As DataSet = New DataSet()

        Dim hold As Object
        hold = myDA.Fill(ds, "blsys_systems")
        SystemNameTextBox.Text = hold.SystemName
        LocationNameTextBox.Text =  hold.LocationName
        LocationIDTextBox.Text = hold.LocationID
        SystemIDTextBox.Text = hold.SystemID

I know that this is not right but I don't know what to do. Besides populating the textboxes, I also need a datagrid to populate. The data from the datagrid comes from a different dataset but is retrieved using the system ID. This code isn't working as well...

' Fill out first table
        Dim cmd2 As MySqlCommand = New MySqlCommand("SELECT SystemID FROM blsys_systems WHERE SystemName='" & LocationComboBox.Text.ToString & "'" & " AND '" & SystemComboBox.Text.ToString & "'", con)
        Dim myDB As MySqlDataAdapter = New MySqlDataAdapter(cmd2)
        Dim db As DataSet = New DataSet
        Dim dt As New DataTable
        Dim SystemID As String

        SystemID = myDB.Fill(db, "blsys_systems")
        Dim itcomponents As MySqlCommand = New MySqlCommand("SELECT * FROM blsys_itcomponents WHERE SystemID='" & SystemID & "'", con)
        Dim myIT As MySqlDataAdapter = New MySqlDataAdapter(itcomponents)
        Dim IT As Object = myIT.Fill(db, "blsys_itcomponents")
        Blsys_itcomponentsBindingSource = IT
        Blsys_itcomponentsDataGridView = Blsys_itcomponentsBindingSource.DataSource

I also tried to change lines 11-13 of the last code to:

myIT.Fill(db, "blsys_itcomponents")
        Blsys_itcomponentsDataGridView.DataSource = db
        Blsys_itcomponentsDataGridView.DataMember = "blsys_itcomponents"

None of it works... Please help!!!!!

[hericles]

For the first part of your question you don't need the object to hold the data. You can access the row and column of the dataTable in the dataset. I normally simplify this by using a dataTable instead.

Dim dt As new DataTable
myDA.Fill(dt)
SystemNameTextBox.Text = dt.Rows(0).Item(0)
LocationNameTextBox.Text =  dt.Rows(0).Item(1)
LocationIDTextBox.Text = dt.Rows(0).Item(2)
SystemIDTextBox.Text = dt.Rows(0).Item(3)

As for your second question the answer above may help.

Dim SystemID As String
SystemID = myDB.Fill(db, "blsys_systems")

Trying to allocate a filled dataSet to a string isn't correct. Just use

myDB.Fill(db, "blsys_systems")

to fill the table "blsys_systems" in the dataSet db and then access the Row(0).Item(0) to get the systemID out. You could also forego the whole dataAdapter approach and use executeScalar() function of the command object to return the string if you are only expecting one result.

[JustineAubrey]

Ok, so I'm working on the first part and I changed it to a data table but it gives me a runtime error saying that "There is no row at position 0". So I used and If-then statement:

If Not dt.Rows.Count > 0 Then
            MessageBox.Show("There is no data to display.")
         Else
        SystemNameTextBox.Text = dt.Rows(0).Item("SystemName").ToString
        SystemIDTextBox.Text = dt.Rows(0).Item("SystemID").ToString
        LocationNameTextBox.Text = dt.Rows(0).Item(" LocationName").ToString
        LocationIDTextBox.Text = dt.Rows(0).Item("LocationID").ToString
        End If

With this, the message box pops up twice even before the form loads, pops up twice again after I select from the system name combobox and once more after I select from the location Name combobox. After all this, the textboxes still do not populate. I commented out line 2 to stop the messagebox from constantly popping up and everything runs fine but the textboxes still do not populate. :(

[hericles]

Are you calling that code at a point before LocationComboBox.Text.ToString is set? It sounds like it is getting called when the page loads for the first time (and every page reload). What do you have in your Page_load() event?
Besides that, it sound alike the data table is empty. Can you confirm the SQL query is returning a result?

[M.Waqas Aslam]

hello !
check this code this will show some records in datagrid and some of them in textboxes as you mentioned in your snap shoot.

dim con as new sqlconnection("connection string")
dim da as new sqldataadapter("select * from table or your query",con)
dim dt as datatable 
da.fill(dt)
'take a bindingsource 
bindingsource1.datasource = dt
'and if you want to see all the records one by then then you can use binding navigator for it like in this way
'--------------------------------------------
bindingNavigator.datasource = bindingsource1
'--------------------------------------------
datagridview1.datasource= bindingsource1
with datagridview1
 .column(0).visible = false ' here you can give index of the field you dont want to show in grid and also you can give the column name in place of 0 like this column("recordid").visible= false
.
.
.
.
end with
'now here bind your textboxes 
txt1.databinding.add("text",bindingsource1,"fieldname of db",false)'now here you bind your textboxes ,if you want to bind combobox then write selectedvalue in place of text and change false to true.
'bind all you textboxes like this .

Hope this will solve your prob , or give you some idea.

Regards

[JustineAubrey]

Wow, thanks! That really helped. My application works now. Thanks so much