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
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