PDA

View Full Version : Help--Can't extract data from Mysql using cell content as select condition



Awesome_me
02-14-2017, 04:07 AM
Hi, guys, I tried to query data from Mysql DB using cell(3,3) as selecting condition but got no data.

I think the reason is can't locate current record after querying, but I don't know how to solve it. :crying::crying:

Anyone please help to check the code? Thanks!

Option Explicit
Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public strSql As String
Public jid As String


Public Function connectDB()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=data01;Port=3306;Database=my_db;User=user;Password=pass;Opti on=3;"
End Function




Sub request()
Dim iCols As Integer
Dim iRows As Integer
Dim i As Integer
With Sheets(1)
jid = Sheets(1).Cells(3, 3).Value
End With
strSql = "SELECT * FROM package WHERE JobID = 'jid'"
Call connectDB
rs.Open strSql, cn, adOpenDynamic
iRows = 11
i = 1
While Not rs.EOF
For iCols = 1 To rs.Fields.Count - 1
Sheets(1).Cells(iRows, 1) = i
Sheets(1).Cells(iRows, iCols + 1).Value = rs.Fields(iCols).Value
Next
rs.MoveNext
i = i + 1
iRows = iRows + 1

Sheets(1).Cells(iRows, 1) = "SUM"
Sheets(1).Cells(iRows, 2) = Application.WorksheetFunction.CountA(Sheets(1).Range(Cells(11, 2), Cells(iRows, 2)))
Sheets(1).Cells(iRows, 9) = Application.WorksheetFunction.Sum(Sheets(1).Range(Cells(11, 9), Cells(iRows, 9)))
Sheets(1).Cells(iRows, 10) = Application.WorksheetFunction.Sum(Sheets(1).Range(Cells(11, 10), Cells(iRows, 10)))

Wend

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


End Sub

JKwan
02-14-2017, 07:59 AM
Well, don't you want to do this? Your jid is inside quotes.....

strSql = "SELECT * FROM package WHERE JobID = '" & jid & "'"

Awesome_me
02-14-2017, 05:54 PM
Well, don't you want to do this? Your jid is inside quotes.....

strSql = "SELECT * FROM package WHERE JobID = '" & jid & "'"

:clap::clap::clap: Great!

Problem solved, thank you very much JKwan!