Questions

VB 6 and SQL

+
0 Votes
Locked

VB 6 and SQL

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 '2147217913 (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)
  • +
    0 Votes

    You have just solved it, so why change it?

    +
    0 Votes
    dilumd

    Because it affects to other softwares like MYOBE. and other thing is that in most computers default date format is dd-mm-yyyy. So if i am develop a system that system must be perfect (at least 99% perfect).

    It is a headache to change this default settings.

    Anyway I have found a solution to do what i want. The way I pass date format is not fully correct. To sql server 2000 database we have to arrange the date to YYYYMMDD format and pass it to the table.

    Now everything is ok.

    Thank you very much your reply

    +
    0 Votes
    bruceanwyl

    First note that strings passed as date values to sql server are automatically converted to date values using the default date format of the sql server.

    The solution you mention relies on your discovery of the default date format of your sql server. This is not guaranteed to be the same in other installations.

    Use the following date format...
    19 Aug 2009
    obtained in VB, at the time of writing, using...
    Format(now, "d Mmm YYYY")

    The above date format will always be automatically and correctly converted no matter what the default date format of the database server.

    +
    0 Votes
    hineses

    Use the Format function on your date value e.g., format(dtOSdt, "yyyy-mm-dd").

    +
    0 Votes
    HGunter

    If you present dates to SQL in ISO format, or yyyymmdd, you should have no problems. Inserting separators will work, but it is not immune to the server's date format expectations, whereas just the numbers are immune to that.

    +
    0 Votes
    darryl.muller

    Because you do not control the computers settings that an application is installed on, I always ensure that the date is not ambigious, so I would do the following

    stgrnmain.grndt >= convert(datetime, '" & format(dtOSdt, "dd mmm yyyy") & "')

    No matter what settings are on the computer or the SQL server it always interprets dd mmm yyyy correctly.

    +
    0 Votes
    arvin_d

    Unambiguous date format is the best way to send to the server for execution, since there is no way it could be mis-interpreted.

    You could have a function in VB which "generates" the correct format (dd mmm yyyy) for any date that you pass to it, then you can simply use:

    ... (stgrnmain.grndt >= convert(datetime, '" & FormatDate(dtOSdt) & "') ...

    Or if your grndt field is of datetime data type, without the convert sql function, since the string is an unambiguous datetime and can be correctly implicitly converted to datetime:

    ... (stgrnmain.grndt >= '" & FormatDate(dtOSdt) & "') ...

    +
    0 Votes
    HarryJ

    Using a number only format used to give me problems, but a few years ago I changed all date references to use the "DD-MMM-YYYY" format and have had no problems since then.

    +
    0 Votes
    azeemsiddiqui

    You can use VB Format() function as following:

    Replace following part of your SQL

    And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt <= '" & dtOEdt & "')))",

    with

    And ((stgrnmain.grndt >= '" & Format(dtOSdt, "yyyy-MM-dd") & "') and (stgrnmain.grndt <= '" & Format(dtOEdt, "yyyy-MM-dd") & "')))",

    I hope it will also help.

    +
    0 Votes
    arvin_d

    using a format where the month is formatted as "mm" will run the risk of causing confusion depending on date settings on the Server.

    I think it is still better to specify month as mmm, this way, there is no ambiguity.

    if you really want to use "mm", then you need to use the "Convert" SQL function to specify the date format.

    eg
    Convert(Datetime, *date*, 102) for mm/dd/yyyy format

    Convert(Datetime, *date*, 103) for dd/mm/yyyy format (aka British date format)

    etc.

    eg
    ... stgrnmain.grndt >= Convert(Datetime, '" & Format(dtOSdt, "dd/mm/yyyy") & "', 103) ...

    +
    0 Votes
    aikimark

    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.

    +
    0 Votes
    arvin_d

    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.

    +
    0 Votes
    aikimark

    @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.

    +
    0 Votes
    dilumd

    Thank you very much to all the replies

  • +
    0 Votes

    You have just solved it, so why change it?

    +
    0 Votes
    dilumd

    Because it affects to other softwares like MYOBE. and other thing is that in most computers default date format is dd-mm-yyyy. So if i am develop a system that system must be perfect (at least 99% perfect).

    It is a headache to change this default settings.

    Anyway I have found a solution to do what i want. The way I pass date format is not fully correct. To sql server 2000 database we have to arrange the date to YYYYMMDD format and pass it to the table.

    Now everything is ok.

    Thank you very much your reply

    +
    0 Votes
    bruceanwyl

    First note that strings passed as date values to sql server are automatically converted to date values using the default date format of the sql server.

    The solution you mention relies on your discovery of the default date format of your sql server. This is not guaranteed to be the same in other installations.

    Use the following date format...
    19 Aug 2009
    obtained in VB, at the time of writing, using...
    Format(now, "d Mmm YYYY")

    The above date format will always be automatically and correctly converted no matter what the default date format of the database server.

    +
    0 Votes
    hineses

    Use the Format function on your date value e.g., format(dtOSdt, "yyyy-mm-dd").

    +
    0 Votes
    HGunter

    If you present dates to SQL in ISO format, or yyyymmdd, you should have no problems. Inserting separators will work, but it is not immune to the server's date format expectations, whereas just the numbers are immune to that.

    +
    0 Votes
    darryl.muller

    Because you do not control the computers settings that an application is installed on, I always ensure that the date is not ambigious, so I would do the following

    stgrnmain.grndt >= convert(datetime, '" & format(dtOSdt, "dd mmm yyyy") & "')

    No matter what settings are on the computer or the SQL server it always interprets dd mmm yyyy correctly.

    +
    0 Votes
    arvin_d

    Unambiguous date format is the best way to send to the server for execution, since there is no way it could be mis-interpreted.

    You could have a function in VB which "generates" the correct format (dd mmm yyyy) for any date that you pass to it, then you can simply use:

    ... (stgrnmain.grndt >= convert(datetime, '" & FormatDate(dtOSdt) & "') ...

    Or if your grndt field is of datetime data type, without the convert sql function, since the string is an unambiguous datetime and can be correctly implicitly converted to datetime:

    ... (stgrnmain.grndt >= '" & FormatDate(dtOSdt) & "') ...

    +
    0 Votes
    HarryJ

    Using a number only format used to give me problems, but a few years ago I changed all date references to use the "DD-MMM-YYYY" format and have had no problems since then.

    +
    0 Votes
    azeemsiddiqui

    You can use VB Format() function as following:

    Replace following part of your SQL

    And ((stgrnmain.grndt >= '" & dtOSdt & "') and (stgrnmain.grndt <= '" & dtOEdt & "')))",

    with

    And ((stgrnmain.grndt >= '" & Format(dtOSdt, "yyyy-MM-dd") & "') and (stgrnmain.grndt <= '" & Format(dtOEdt, "yyyy-MM-dd") & "')))",

    I hope it will also help.

    +
    0 Votes
    arvin_d

    using a format where the month is formatted as "mm" will run the risk of causing confusion depending on date settings on the Server.

    I think it is still better to specify month as mmm, this way, there is no ambiguity.

    if you really want to use "mm", then you need to use the "Convert" SQL function to specify the date format.

    eg
    Convert(Datetime, *date*, 102) for mm/dd/yyyy format

    Convert(Datetime, *date*, 103) for dd/mm/yyyy format (aka British date format)

    etc.

    eg
    ... stgrnmain.grndt >= Convert(Datetime, '" & Format(dtOSdt, "dd/mm/yyyy") & "', 103) ...

    +
    0 Votes
    aikimark

    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.

    +
    0 Votes
    arvin_d

    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.

    +
    0 Votes
    aikimark

    @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.

    +
    0 Votes
    dilumd

    Thank you very much to all the replies