Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

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
0
vb9666
Asked:
vb9666
  • 3
  • 2
  • 2
1 Solution
 
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
 
jnhorstCommented:
Try this instead of your current declaration for the vntToday variable:

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

John
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now