Networks

Question

Locked

VB 6 and SQL

By dilumd ·
I have developed an Inventory control system using Visual basic 6 and back end as Sql Server 2000.

When i try to access data through below mentioned SQL string,

rstGrn.Open "Select stgrnsub.grnqty From StGrnSub Inner join StGrnMain on stgrnsub.grnref = stgrnmain.grnref Where ((StGrnSub.grnicode = '" & sICode & "') And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt <= '" & dtOEdt & "')))", goConn, adOpenStatic, adLockPessimistic

it gives me an error. That is

Run-time error '2147217**3 (80040e07)'. The conversion of a char data type to a datetime data type resulted in an our-of-range datetime value.

So i changed the date format in My PC using control panel regional options from dd-mm-yyyy to yyyy-mm-dd. After this change above sql query worked fine.

My question is that, is there a way to run this sql query without changing the date format of the windows. (Im using win xp)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

error message

by aikimark In reply to VB 6 and SQL

Depending on your runtime environment, I think your original error message was caused by incorrect delimiters. For date literals, you need to use date delimiters.

Example
And (stgrnmain.grndt Between #" & dtOSdt & "# And #" & dtOEdt & "#)"

Note: I changed the comparison to Between for (usually) better performance.

Collapse -

Maybe I'm wrong but...

by arvin_d In reply to error message

I think the Hash (#) symbol used for dates is used within the syntax of VB itself? Say when comparing a date input by the user to another date.

When sending to SQL Server as part of a SQL string, we cannot send # right?

I don't think T-SQL has any # as part of its date formats.

Collapse -

# date delimiter

by aikimark In reply to Maybe I'm wrong but...

@arvin

Since we are making the call from VB6, we are passing the SQL through database layers that do some syntax checking and conversion to a standard ODBC (interop) format. As such, we need to use the proper delimiters to help this conversion process. To complicate matters, we are concatenating the locally formatted date as a string into the SQL statement. I've seen instances where a date string was treated as a numeric expression and the result of the numeric expression was passed into SQL that was passed to the database!
Example:
8/21/2009 might be a date value of a variable included in a SQL statement construction via concatenation. What got passed to the database was 0.00018962

If you were assigning date parameter values, then you wouldn't need to do this, since the parameters are of known data type.

Collapse -

Thank you

by dilumd In reply to # date delimiter

Thank you very much to all the replies

Related Discussions

Related Forums