• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30722
  • Last Modified:

Get value of cell in datatable (VB.net 2005), MYSQL

Hi,

I am using VS2005 and coding in VB. I want to get the value of a datatable row, first row, column called option1. I dont know how to get it... :( Any ideas, code below of what im trying to do. The line where i have dt.tables is the line thats highlighted as incorrect code, unsure how to get the value for option1 from the datatable :(

 Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt As New DataTable

        conn.ConnectionString = connectionstring

        myCommand.Connection = conn
        myCommand.CommandText = "SELECT report_name, report_option1, report_option2 from report_names WHERE report_category = '" & lstCategories.SelectedValue.ToString & "'"

        lblOption.Text = lstCategories.SelectedValue.ToString


        Try
            conn.Open()
            myCommand.ExecuteNonQuery()
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dt)


            lstReports.DataSource = dt
            lstReports.DisplayMember = "report_name"

            If dt.Tables(0).Rows(0).Item("option1") = "" Then
                txtOption1.Enabled = False
            Else

                lblOption.Text = dt.Tables(0).Rows(0).Item("option1")
                txtOption1.Enabled = True
            End If

         

        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)

        Finally
            conn.Dispose()
        End Try
0
superlative
Asked:
superlative
  • 7
  • 4
  • 2
1 Solution
 
Bob LearnedCommented:
Close.  That was syntax for a DataSet, not a DataTable:

If dt.Rows(0)("option1") = "" Then

Bob
0
 
SanclerCommented:
Except that there will be no field/column/item with the name "option1" ;-)

Look at the select statement.

I think you probably want

      If dt.Rows(0)("report_option1") = "" Then

Roger
0
 
Bob LearnedCommented:
Good eye, Roger, good eye!!

Bob
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
superlativeAuthor Commented:
hi i currently get a 'there is no row at position 0' with that code. however there is a value at it :(
any ideas?
0
 
SanclerCommented:
How do you know there is a value in it?  Is lstReports showing something?  Or what?

Roger
0
 
SanclerCommented:
Sorry, no.  I only do stuff via EE ;-)

And, incidentally, what is the first of these lines supposed to be doing?

            myCommand.ExecuteNonQuery()
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dt)

Can I suggest you, temporarily, stick the last of those lines in some reporting object, such as

            MsgBox(myAdapter.Fill(dt))

so you can see how many rows the adapter is actually returning.  Just in case (as the message you report suggests) it might be less than you expect.

Roger

PS.  In any event, I'm just off out, but if this needs any more follow up I imagine Bob or someone will be about.  If not, I'll check in again when I come back in an hour or so.
0
 
SanclerCommented:
Is this sorted now?

Roger
0
 
superlativeAuthor Commented:
Hey Roger,

Your code works, you are correct. However it appears my problem is to do with my query above that populates the datatable.

This is my MySQl query  text ,

 myCommand.CommandText = "SELECT report_name, report_option1, report_option2 from report_names WHERE report_category = '" & lstCategories.SelectedValue.ToString & "'"

Is .selectedvalue.tostring the option to use for a listbox. I have a listbox that has a bunch of categories. If i hardcode the above to be ..... WHERE report_category = 'Housekeeping'" it works, its as if the .selectedvalue.tostring doesnt get the text of the highlighted item in the listbox.  In the combo box i had used cbocategories.selectedtext for example.

Am I using the incorrect option here?
0
 
SanclerCommented:
It depends on how the listbox is bound.  SelectedValue should return the value from the datasource field that was set as .ValueMember in the datatable set as .DataSource.  If you want to use the value that comes from the field that was set as .DisplayMember (that is, what is actually displayed in the list) you could use code like this

        Dim drv As DataRowView = CType(lstCategories.SelectedItem, DataRowView)
        Dim Selection As String = drv(lstCategories.DisplayMember.ToString).ToString

That's a bit more convoluted than might really be necessary but, assuming the listibox is bound to a datatable, it should work even if your settings require explicity casting without you having to change a word of it.  And then you replace lstCategories.SelectedValue.ToString in your select statement with Selection.

Roger
0
 
superlativeAuthor Commented:
hi, can somebody tell me how to remove this questino from the public or remove my email address from one of the posts?

thanks.
0
 
SanclerCommented:
Post a request in Community Support

Roger
0
 
superlativeAuthor Commented:
hi roger, where is community support?
0
 
SanclerCommented:
Easiest way is to click on the "Report Abuse" link near the bottom right of the original question.  That will take you to community support with a specific link to this thread.

Roger
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now