Solved

Syntax error converting character string to smalldatetime data type

Posted on 2006-10-30
7
553 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

776 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