CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

 CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:

  • Microsoft OLE DB Provider for SQL Server (0x80040E07) Conversion failed when converting the varchar value 'myusername' to data type int (or) The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    82
  • Date Created
    Tuesday, December 28, 2010
  • Last Updated
    Tuesday, March 15, 2011
  • This Article Has been Viewed
    3899 times
  • Short Desc
    When creating your SQL Select Query statement, you will sometimes use the wrong column for your Query, this will result in the following error. This can also happen if you try to insert text into a datetime column.
  • Details
    Creating your SQL Select Query, you sometimes use the wrong Column (or) wrong variable.
    When this happens, you get the following error.

    Scenario #1:
     
    Microsoft OLE DB Provider for SQL Server (0x80040E07)
    Conversion failed when converting the varchar value 'myusername' to data type int.
    /Header.asp, line 39


    Scenario #2:
     
    Microsoft OLE DB Provider for SQL Server error '80040e07'
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    /logout.asp, line 17
  • Recreate Issue
    To recreate this issue:

    Scenario #1:
    <%
    myVal = ProtectSQL(request.querystring("id"))
    sql.commandtext="select colnum, coltext, coltext from table1 where coltext=?"
    sql.Parameters.Append sql.CreateParameter("@colnum", adInteger, adParamInput, , myVal)
    %>


    In the above example, we have our coltext which is of data type varchar as are Column to use for our Query, but in our Parameter we are using our colnum which is an integer


    Scenario #2:
    <%
    myVal = ProtectSQL(request.querystring("id"))
    sql.commandtext="update table1 set uDate=? where id=?"
    sql.Parameters.Append sql.CreateParameter("@LoggedTime", advarchar, adParamInput, 50, date()&" - "&time())
    sql.Parameters.Append sql.CreateParameter("@id", adInteger, adParamInput, , myVal)

    %>


    In the above, the code is correct, but the database column for LoggedTime was set to data type datetime.
  • Resolve Issue
    To correct this issue.

    Scenario #1:
    <%
    myVal = ProtectSQL(request.querystring("id"))
    sql.commandtext="select colnum, coltext, coltext from table1 where colnum=?"
    sql.Parameters.Append sql.CreateParameter("@colnum", adInteger, adParamInput, , myVal)
    %>


    As you can see here, we have removed the coltext and replaced it with the correct column of colnum, this corresponds with parameter and the variable that we have created.

    Scenario #2:
    <%
    myVal = ProtectSQL(request.querystring("id"))
    sql.commandtext="update table1 set uDate=? where id=?"
    sql.Parameters.Append sql.CreateParameter("@LoggedTime", advarchar, adParamInput, 50, date()&" - "&time())
    sql.Parameters.Append sql.CreateParameter("@id", adInteger, adParamInput, , myVal)

    %>



    In the database, we set the LoggedTime's data type to VarChar(50)
    This is the changed that needed to take place in order for this code to work.

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles