Solved

Syntax error converting character string to smalldatetime data type

Posted on 2006-10-30
7
551 Views
Last Modified: 2013-12-03
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
Comment
Question by:vb9666
  • 3
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 17837216
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
 
LVL 35

Expert Comment

by:YZlat
ID: 17837234
the problem could arise when vntToday is blank
0
 
LVL 10

Expert Comment

by:jnhorst
ID: 17838294
Try this instead of your current declaration for the vntToday variable:

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

John
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:vb9666
ID: 17849674
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
 

Author Comment

by:vb9666
ID: 17849705
jnhorst-

How can vntToday every be blank if it is taking the current date?
0
 
LVL 10

Accepted Solution

by:
jnhorst earned 125 total points
ID: 17851249
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
 

Author Comment

by:vb9666
ID: 17860451
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need an intro to -- .Net SQL Authorization Manager 7 80
Handle null when using linq in this line 1 27
Name space syntax error 12 44
SQL Login 17 40
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now