Syntax error converting character string to smalldatetime data type

I am randomly getting the following error:

"Syntax error converting character string to smalldatetime data type"

It is coming from the same code but doesn't always produce an error when the code runs.  It's very random when the error occurs.  It has something to do with the date criteria.  If I put in the actual date instead of using the variable, I do not get the error at all.  Can someone please tell me how to fix this??

Here is the code:

Public Sub LoadContracts(ByVal intAccountID_In As Integer)
        Dim oSQL As SqlCommand
        dim vntToday as Date = Now()

        ConnectDB()
        gStr = "SELECT Contract.ContractID, Contract.AcctID, Contract.StartDate, Contract.EndDate " & _
               "FROM Contract " & _
               "WHERE Contract.AcctID = '" & intAccountID_In & "' " & _
               "AND Contract.EndDate > '" & vntToday & "' "

        oSQL = New SqlCommand(gStr, oConn)
        gDTR = oSQL.ExecuteReader()
       
        oCount = GetCount(gDTR)
        gDTR = oSQL.ExecuteReader()
       
    End Sub
vb9666Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jnhorstConnect With a Mentor Commented:
Sorry about that... it should be:

DateTime.Now.ToShortDateString()

I have no idea how it could be blank, unless it is because of the error from my mistaken code.

John
0
 
YZlatCommented:
try

Public Sub LoadContracts(ByVal intAccountID_In As Integer)
        Dim oSQL As SqlCommand
        Dim vntToday As Date = Now()

        ConnectDB()
        gStr = "SELECT Contract.ContractID, Contract.AcctID, Contract.StartDate, Contract.EndDate " & _
               "FROM Contract " & _
               "WHERE Contract.AcctID = '" & intAccountID_In & "' " & _
               "AND Contract.EndDate > '" & CDate(vntToday) & "' "

        oSQL = New SqlCommand(gStr, oConn)
        gDTR = oSQL.ExecuteReader()

        oCount = GetCount(gDTR)
        gDTR = oSQL.ExecuteReader()

    End Sub
0
 
YZlatCommented:
the problem could arise when vntToday is blank
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
jnhorstCommented:
Try this instead of your current declaration for the vntToday variable:

Dim vntToday As String = DateTime.Now.ToShortDateTimeString()

John
0
 
vb9666Author Commented:
YZlat-

I tried your suggestiong but it didn't seem to fix the problem...the same behavior exists.  I can run the query one time and it's fine but then a few minutes later the same record produces the error.

jnhorst-

I tried your suggestion as well and got the following error:

 Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'ToShortDateTimeString' is not a member of 'Date'.

Source Error:

Line 274:        Dim oSQL As SqlCommand
Line 275:        'dim vntToday as Date = Now()
Line 276:        Dim vntToday As String = DateTime.Now.ToShortDateTimeString()
Line 277:        'use the global datareader
Line 278:
0
 
vb9666Author Commented:
jnhorst-

How can vntToday every be blank if it is taking the current date?
0
 
vb9666Author Commented:
I was also able to get it to work by doing the following:

        Dim oSQL As SqlCommand
        Dim dtToday as Date = Now()

        ConnectDB()
        gStr = "SELECT Contract.ContractID, Contract.AcctID, Contract.StartDate, Contract.EndDate " & _
               "FROM Contract " & _
               "WHERE Contract.AcctID = '" & intAccountID_In & "' " & _
               "AND Contract.EndDate > '" & dtToday.ToString("dd/MM/yyyy") & "' "

        oSQL = New SqlCommand(gStr, oConn)
        gDTR = oSQL.ExecuteReader()

        oCount = GetCount(gDTR)
        gDTR = oSQL.ExecuteReader()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.