Incorrect syntax at my strsql coding

[dennis1989]

Im having problem running the program. Its say i have incorrect syntax at my strsql coding

Private Sub ComboBox1_Change()
 
    Dim rst As ADODB.Recordset
 
    'Initialize Recordset
    Set rst = New ADODB.Recordset
 
    On Error GoTo errhandle
 
 
    strSq1 = "SELECT DISTINCT activitydesc,materialid FROM activity_table " & _
                "WHERE  activityid in " & _
                "(Select distinct activityid from sample where(sampledt BETWEEN StartDt AND EndDT)"
 
        ' Get Start Date & End Date
 
        StartDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 00:00:00")
        EndDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 23:59:59")
 
        ' Re-Connect to Database
        MakeConnection
        If Module1.connectSQLSrv(strRegLogin, strRegPwd, strRegDB, strRegServer) Then
            ' Cursor and Lock Characteristic
            ' adOpenDynamic = high Concurrency & Scrollable
            ' adLockOptimistic = other users able to access data
            ' adCmdText = Command Type (Text)
 
            'Open Recordset
            rst.Open strSql, sqlConnection, adOpenDynamic, adLockOptimistic, adCmdText
 
 
            With rst
            If .RecordCount = 0 Then
            ComboBox1.Clear
            ComboBox1.AddItem rst![Request]
        Do While Not .EOF
            If Not IsNull(.Fields(0).Value) Then
            ComboBox1.AddItem (.Fields(0).Value)
            End If
            .MoveNext
        Loop
        Else
    Exit Sub
    End If
    End With
 
    Set rst = Nothing
 
    Else
 
            'Frm Login activate = asking for user login
            'MsgBox "Login Failed, please retry!", vbExclamation
            bolConnected = False
            Module1.MakeConnection
        End If
 
    Exit Sub
 
errhandle:
    MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error!"
    bolConnected = False<<< Having error on this line
    If Not sqlConnection Is Nothing Then
        Set sqlConnection = Nothing
    End If
End Sub

[debasisdas]

May i know why you have posted the code here ?

[ThatThatGuy]

now what do i understand by that code.... plz specify the problem youre facing

[debasisdas]

You need to frame the query string dynamically at run time by concatenating the variables. You need to understand when to use quotes "" and when not . Your variables are within quotes try to fix that. Assign value to the variables first and then concatenate to frame the string.

[dennis1989]

But the error indicated syntax error near the 'in'

strSql = "SELECT DISTINCT activitydesc,materialid FROM Activity" & _
"WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & "))"

this is my new coding

[vb5prgrmr]

Just jumping in here but there is no space between the word Activity and where in your last post (ActivityWhere) which would be an error near the "in" clause...



Good Luck

[dennis1989]

sorry can explain again cause im really very new in programming

[vb5prgrmr]

debug.print strSQL...(look at your string concatenation)



Good Luck

[dennis1989]

you mean the coding in my strSQL is wrong ? if wrong which part i need to edit ???

[vb5prgrmr]

Two posts up look between the parens...

[dennis1989]

what is string concatenation ? U mind helping me edit the coding? Thanks in advance

[ThatThatGuy]

@dennis1989
When you join two strings to form a new one is called string concatenation

[MikeTheBike]

Hi

Perhaps this is the problem

"(SELECT DISTINCT activityid FROM sample where sampledt BETWEEN #" & startdt & "# AND #" & enddt & "#)"

You need to delimit the dates with the # sign.

Also you need to cosider the date format, if you computer local date setting is not in American format (mm/dd/yy) then you will need to format the date to American the format to return the correct records.


Just a thought !?

MTB

[dennis1989]

thanks im trying it out now

[dennis1989]

By the way what does it means to delimit the date ? I have checked that my local setting of the date is correct

[dennis1989]

hmm i tried to delimit the date but it still have the same error
Incorrect Syntax near the keyword 'in'

[vb5prgrmr]

Argh! DENNIS!!!! Build your string, ADD THE DEBUG.PRING strSQL... WHAT DOES IT SAY???? Do you see where there MIGHT BE two words without a SPACE between them!!!????

[dennis1989]

sorry i will try it now .im really newbie in programming

[dennis1989]

i got error 3001 when i add debug.print. argument are of the wrong type

[vb5prgrmr]

Okay, then... use a message box to display the string...(then you can make a screen shot by ALT+PrntScrn)



Good Luck

[dennis1989]

u mean just show u the strsql string or the entire coding ?

[vb5prgrmr]

Just the string that you pass...

[BlackLibrary]

Here is the code you need instead.

On Error GoTo errhandle

' Get Start Date & End Date

StartDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 00:00:00")
EndDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 23:59:59")

strSq1 = "SELECT DISTINCT activitydesc,materialid FROM activity_table " & "WHERE activityid in (Select distinct activityid from sample where(sampledt BETWEEN " & StartDt & " AND " & EndDT &")"


' Re-Connect to Database

The issue is specifically with the variables.

1. You need to give them the right values first, -before- you build the SQL string.
2. You SQL string did not break out the variables from the string properly.

When you pass a SQL string to be executed, it passes the entire string as it computes. If you don't break out your variables properly, in this case, your date range variables, it will choke on them.

In this case, your date range was just the WORDS, StartDt and EndDt, when really you wanted the VALUE of StartDt and EndDt respectively.

[dennis1989]

Thanks for the help black library but i still have that syntax error at the keyword in "in"

strSql = "SELECT DISTINCT activitydesc,materialid FROM activity_table" & "WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & ")

[dennis1989]

i manage to solve the initial problem with ur coding but my coding is have error 31 on this code :

errhandle:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error!"
bolConnected = False
If Not sqlConnection Is Nothing Then
Set sqlConnection = Nothing
End If
End Sub

[dennis1989]

strSql = "SELECT DISTINCT activitydesc,materialid FROM Activity " & _
"WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & ")) " & _
"WHERE sampledt BETWEEN CONVERT(DATETIME, '01/18/2010', 102) AND CONVERT(DATETIME, '01/18/2010 23:59:59', 102))"


I revised my code yet i still have syntax error at keywords 23.
Can someone help me?
Thanks in advance

[debasisdas]

Display the strSql to a message box and post he screen shot.

[dennis1989]

Some changes to my project now, instead of using the combobox to display the value, i need to display the value in excel cells. Wonder if anyone can help me with the code on my first post. Thanks in advance