Solved

Syntax error converting character string to smalldatetime data type

Posted on 2006-10-30
7
550 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

19 Experts available now in Live!

Get 1:1 Help Now