How to fetch values from mysql and use it in a loop?

[DavidTigerch]

Hi all i got an application that takes url value from users .The user types the url in textbox and click a sommand button and ...

i have list of urls in my database i want my program fetch the url value from my database and use it instead..

So if i have 20 url records in mysql db i want my Private Sub Command1_Click(Index As Integer) runs 20 times and takes the url from db and pass it to rest of function. I be happy if some tell me how i can do that.Thanks

Private Sub Command1_Click(Index As Integer)

Select Case Index
    Case 0:
        If txtURL.Text <> "" Then
            [B]RichTextBox1.Text = Inet1.OpenURL(txtURL.Text, icString)[/B]
        End If
    
    Case 1:
        End
End Select

Command4_Click ' calliing the remaining function within the loop
End Sub

Mysql connection

 Dim CNN As ADODB.Connection
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic66;USER=root;PASSWORD=;OPTION=3;"

CNN.Execute "select url from sheet1 "

 

[johnwm]

You need a recordset object to store the results of your query, then you can loop through the recordset. Assuming your table is called 'sheet1' and it contains a text field called 'url'

'Open connection
Dim CNN As ADODB.Connection
Dim RS as ADODB.Recordset
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic66;USER=root;PASSWORD=;OPTION=3;"
'get records to recordset
Set RS = CNN.Execute "select url from sheet1"
'Loop through to the end of file marker
Do While Not RS.EOF
RichTextBox1.Text = RichTextBox1.Text & Inet1.OpenURL(RS("url"), icString)
'get next record
RS.MoveNext
Loop
'tidy up
RS.Close
Set RS = Nothing
CNN.Close
Set Cnn = Nothing

That will put the whole lot in one RTB

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[DavidTigerch]

Many thanks for u reply . I tried the following but i get syntax error show in bold part :

Private Sub Command1_Click(Index As Integer)

'Open connection
Dim CNN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"
'get records to recordset
[b]Set RS = CNN.Execute "select url from sheet1"[/b] === syntax error


'Loop through to the end of file marker
Do While Not RS.EOF
RichTextBox1.Text = RichTextBox1.Text & Inet1.OpenURL(RS("url"), icString)

[b]Command2_Click 'calling other functions [/b]
'get next record
RS.MoveNext
Loop
'tidy up
RS.Close
Set RS = Nothing
CNN.Close
Set CNN = Nothing
End Sub

do u think i placed call to the rest of function(Command2_Click ) in correct possition since i want for each new url the complete functions work ?

Furthermoe i have another insert statement that looks like this in same applicaton at one of my functions :

..........
Dim CNN As ADODB.Connection
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic66;USER=root;PASSWORD=;OPTION=3;"

.........

May i know that if still i need to make the connection 2 times? and could u tell me what u mean by "That will put the whole lot in one RTB ".Thanks

 

[johnwm]

My apologies - I shouldn't type quickly and post without checking. The line should read:

Set RS = CNN.Execute("select url from sheet1", , adCmdText)

If you want the actions under Command2 to run for every record, then use

Command2 = True

instead of
[tt]Command2_Click 'calling other functions [/tt]

When you have finished using a Recordset and Connection, you should close them and set them to Nothing (as shown in the 'tidy up section in my post). It is up to you to decide whether to open the connection when the application starts and close it when the application ends, or whether to open and close it specifically each time you use it. If the database is used by many users then it's normal to close after each use. Otherwise you need to make sure that it's closed when the app shuts down.

RTB = RichTextBox

In order to make these threads useful to others later, it's best to keep one thread for one question, and start a new thread for a new question.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[DavidTigerch]

Many MAny thanks for u nice explanation. I tried but now it gives me this error:

Run-time error '35752'

URL is malformed

pointing at this line :

RichTextBox1.Text = RichTextBox1.Text & Inet1.OpenURL(RS("url"), icString)

Furthermore, i only wanted to put one url at at time from database to richtextbox not the whole urls from db. I want to process them one by one.I be happy if u let me know how to fix this problem.Thanks