Using mysql query result in variable

[Wolxhound90]

Hey guys,

I'm just wondering if it's possibe to get the results from a query and then assign the results to a variable. So I will run a query which will return the names of the tables stored in the database, then I want them to be stored in a variable. So it would be something along the lines of:

Dim strFrom AS String = "FROM "

strQuery = "SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'Database1'"

con.Open()
theQuery.Connection = con
theQuery.CommandText = strQuery
theAdapter.SelectCommand = theQuery
theAdapter.Fill(theTable)
strFrom &= theTable
con.Close()

Is this the right way to do this? Or am I going completely wrong?

Thanks in advance :)

[|-|x]

line 10 is not quite right.

strFrom &= theTable

you would need to access the row and field inside the table object.
(sorry about this example, but can't recall exact vb syntax off hand, in c# it would be something like this...)

strFrom &= theTable.rows[0].cells[0]

This should grab the first value of the first row returned in the table, you can of course reference any valid row/column index.

Note: you would need to loop through the table rows to obtain all results if more than one is returned/required.

alternative method:
If you structure your initial query so that it will only return a single result item you could use strFrom &= theQuery.ExecuteScalar without the need for an adaptor or table object. But this will work with a query that returns only a signle record with a single field

[poojavb]

First declare the variables and then when u will use the select query make use of reader that will read the value and assign it to the variable

eg

Dim docid as String
Dim docfname as String
Dim doclname as String
'connection string
Try
    Dim myCommand As OleDbCommand
    myCommand = New OleDbCommand("SELECT DoctorID,FirstName,LastName FROM DoctorRegister", Connection)
    Dim reader As OleDbDataReader = myCommand.ExecuteReader
    While reader.Read
          docid = reader("DoctorID")
          docfname = reader("FirstName")
          doclname = reader("LastName")
    End While
    reader.Close()
 Catch ex As Exception
    MsgBox("Error Connecting to Database: " & ex.Message)
End Try

Hope this helps u....

[artemix22]

what are you trying to get? a Database name?

[poojavb]

If u need to find the database table names....use the query which u have used in place of the query what i have used and then save the column names in variable

[Wolxhound90]

Thanks for all your replies :)

I'm getting the table names that are stored in a database. The query I provided in my first post is the query I can use in phpmyadmin to return the table names.

What I'm trying to do is feed the table names into a string so it will be something along the lines of "FROM table1, table2, table3".

@Hearth - I'm not sure if you can do that kind of thing in vb, but if I put it into a while loop I might be able to find a way around it. The only problem is I can't access the server to test it until tomorrow so just kind of stabbing in the dark to guess what will work lol.

@poojavb - I think the code in your first reply would work for an Access database, but I'm using a mysql database. Having done some research, though, I found that the mysql plugin offers a similar function, so I'm going to try using that and see what happens. But I can't find any examples online where people have tried putting a query result straight into a string.

Thanks again for your ideas, I shall give them a try :)

[|-|x]

Try something like the below ... (this code assumes you have done the relevant sql object declarations)

Dim strFrom AS String = ""
strQuery = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'Database1'"
con.Open()
theQuery.Connection = con
theQuery.CommandText = strQuery
theReader = theQuery.ExecuteReader
While theReader.Read
    If strFrom = "" Then strFrom = "FROM " & theReader("TABLE_NAME")
    ELSE strFrom &= ", " & theReader("TABLE_NAME")
    End If
End While
theReader.Close()
con.Close()

[Wolxhound90]

Yes! This works perfectly.

Thank you so much for your help guys :)