farrukh
11-09-2011, 10:51 AM
HI All,
I need some help in my code , the code works fine when i just select * from table give the results from that table .But when i pass date crietia it does not reterive any data in excel sheet ?
Help me out passing the date in this sql query
Global sConnectStr As String
Dim dataGetter As New Data
Sub testconnection()
Dim subArray(0 To 5) As Variant
subArray(0) = "CONNECTION"
subArray(1) = "Provider=sqloledb;Network Library=DBMSSOCN;Data Source=localhost,1433;Initial Catalog=DB;User ID=user;Password=pass;Application Name=AVM"
subArray(2) = "FROM_DATETIME"
subArray(2) = "AS_OF_DATE"
subArray(3) = CDate("2/1/2011")
subArray(4) = "TO_DATETIME"
subArray(5) = CDate("5/4/2011")
MainExec subArray
End Sub
Sub MainExec(rawParams() As Variant)
Dim dStartDate As Date
Dim params As New Collection
Dim iii As Integer
For iii = 0 To UBound(rawParams) - 1
params.Add rawParams(iii + 1), rawParams(iii)
MsgBox rawParams(iii + 1) & " - " & rawParams(iii)
iii = iii + 1
Next iii
sConnectStr = "Provider=sqloledb;" & params("CONNECTION")
dStartDate = CDate(params("AS_OF_DATE"))
dEndDate = CDate(params("TO_DATETIME"))
Sheet1.Cells(2, 3) = dStartDate
Sheet1.Cells(2, 5) = dEndDate
Call FillNetworks(dStartDate)
Call CompData(dStartDate)
End Sub
Sub FillNetworks(dAsOfDate As Date)
Dim sColumnName As String
Dim Range As Excel.Range
Dim valForm As String
Dim sFieldSql As String
'*******Find Networks
Call dataGetter.OpenConnection(sConnectStr)
Call dataGetter.FillNetworks("NETWORK", dAsOfDate, 1, "O1", 100, 1)
Call dataGetter.CloseConnection
' *******And updateselection list
' Sheet1.Range("G2") = Null
' Set Range = Sheet1.Range("G2")
' valForm = "=$O$1:$O$40"
' Range.Validation.Delete
' Range.Validation.Add Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:=valForm
' Sheet1.Range("G2").Value = Sheet1.Range("O1").Value
End Sub
Sub CompData(dAsOfDate As Date)
Dim wSheet As Worksheet
Dim wProd As Worksheet
Dim dStartDate As Date
Dim dEndDate As Date
Dim sql As String
Dim sPotRangeUL As String
Dim dStartMonth As Date
Dim dStartYear As Date
sPotRangeUL = "A2"
'*******Get input data
dStartDate = DateValue(dAsOfDate)
dEndDate = dStartDate + 1
dStartMonth = DateValue("1-" & Month(dStartDate) & "-" & Year(dStartDate))
dStartYear = DateValue("1-jan" & "-" & Year(dStartDate))
Set wSheet = Worksheets("TEST")
'*******Open Connection
Call dataGetter.OpenConnection(sConnectStr)
sql = "select * FROM table" '& _
' "WHERE(STARTDATE >= '" & Format(dStartDate, "m/d/yyyy HH:MM:SS") & "') And (START_DATE < '" & Format(dStartDate + 1, "m/d/yyyy HH:MM:SS") & "')"
Application.DisplayAlerts = False
'*******Fill data
Call dataGetter.GetOperFieldRead(dStartDate, dEndDate, sql, wSheet.Index, sPotRangeUL, 0, 100)
Set recReportData = Nothing
Application.DisplayAlerts = True
Call dataGetter.CloseConnection
End Sub
Thanks
frexzz
I need some help in my code , the code works fine when i just select * from table give the results from that table .But when i pass date crietia it does not reterive any data in excel sheet ?
Help me out passing the date in this sql query
Global sConnectStr As String
Dim dataGetter As New Data
Sub testconnection()
Dim subArray(0 To 5) As Variant
subArray(0) = "CONNECTION"
subArray(1) = "Provider=sqloledb;Network Library=DBMSSOCN;Data Source=localhost,1433;Initial Catalog=DB;User ID=user;Password=pass;Application Name=AVM"
subArray(2) = "FROM_DATETIME"
subArray(2) = "AS_OF_DATE"
subArray(3) = CDate("2/1/2011")
subArray(4) = "TO_DATETIME"
subArray(5) = CDate("5/4/2011")
MainExec subArray
End Sub
Sub MainExec(rawParams() As Variant)
Dim dStartDate As Date
Dim params As New Collection
Dim iii As Integer
For iii = 0 To UBound(rawParams) - 1
params.Add rawParams(iii + 1), rawParams(iii)
MsgBox rawParams(iii + 1) & " - " & rawParams(iii)
iii = iii + 1
Next iii
sConnectStr = "Provider=sqloledb;" & params("CONNECTION")
dStartDate = CDate(params("AS_OF_DATE"))
dEndDate = CDate(params("TO_DATETIME"))
Sheet1.Cells(2, 3) = dStartDate
Sheet1.Cells(2, 5) = dEndDate
Call FillNetworks(dStartDate)
Call CompData(dStartDate)
End Sub
Sub FillNetworks(dAsOfDate As Date)
Dim sColumnName As String
Dim Range As Excel.Range
Dim valForm As String
Dim sFieldSql As String
'*******Find Networks
Call dataGetter.OpenConnection(sConnectStr)
Call dataGetter.FillNetworks("NETWORK", dAsOfDate, 1, "O1", 100, 1)
Call dataGetter.CloseConnection
' *******And updateselection list
' Sheet1.Range("G2") = Null
' Set Range = Sheet1.Range("G2")
' valForm = "=$O$1:$O$40"
' Range.Validation.Delete
' Range.Validation.Add Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:=valForm
' Sheet1.Range("G2").Value = Sheet1.Range("O1").Value
End Sub
Sub CompData(dAsOfDate As Date)
Dim wSheet As Worksheet
Dim wProd As Worksheet
Dim dStartDate As Date
Dim dEndDate As Date
Dim sql As String
Dim sPotRangeUL As String
Dim dStartMonth As Date
Dim dStartYear As Date
sPotRangeUL = "A2"
'*******Get input data
dStartDate = DateValue(dAsOfDate)
dEndDate = dStartDate + 1
dStartMonth = DateValue("1-" & Month(dStartDate) & "-" & Year(dStartDate))
dStartYear = DateValue("1-jan" & "-" & Year(dStartDate))
Set wSheet = Worksheets("TEST")
'*******Open Connection
Call dataGetter.OpenConnection(sConnectStr)
sql = "select * FROM table" '& _
' "WHERE(STARTDATE >= '" & Format(dStartDate, "m/d/yyyy HH:MM:SS") & "') And (START_DATE < '" & Format(dStartDate + 1, "m/d/yyyy HH:MM:SS") & "')"
Application.DisplayAlerts = False
'*******Fill data
Call dataGetter.GetOperFieldRead(dStartDate, dEndDate, sql, wSheet.Index, sPotRangeUL, 0, 100)
Set recReportData = Nothing
Application.DisplayAlerts = True
Call dataGetter.CloseConnection
End Sub
Thanks
frexzz