Problems with building sql statement

Discussion in 'ASP General' started by Jack, Mar 30, 2005.

  1. Jack

    Jack Guest

    Hi,
    I have a sql statement in asp page as below

    sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
    sql = sql & "TravelExpense, PersonnelExpense)"
    sql = sql & " VALUES('" & l_ENO & "', '" & l_Date & "', '" &
    l_contractedserviceexpense & "', '" & l_travelexpense & "', '" &
    personnelexpense & "')"

    The response.write on sql gives the following:

    INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
    TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')

    However when I am trying to open the recordset, it gives me the following
    error:

    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.

    I suspect the variables are not properly used to build the above statement.

    The ENo is of text type,
    EntryDate is of date type while
    the rest of the last three fields are of number type.

    I appreciate any help. Thanks in advance. Regards.
    Jack, Mar 30, 2005
    #1
    1. Advertising

  2. Jack wrote:
    > Hi,
    > I have a sql statement in asp page as below
    >
    > sql = " INSERT INTO tblExpense (ENO, EntryDate,
    > ContractedServiceExpense, " sql = sql & "TravelExpense,
    > PersonnelExpense)" sql = sql & " VALUES('" & l_ENO & "', '" &
    > l_Date & "', '" & l_contractedserviceexpense & "', '" &
    > l_travelexpense & "', '" & personnelexpense & "')"
    >
    > The response.write on sql gives the following:
    >
    > INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
    > TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5',
    > '10')
    >
    > However when I am trying to open the recordset, it gives me the
    > following error:
    >
    > Error Type:
    > Microsoft JET Database Engine (0x80040E07)
    > Data type mismatch in criteria expression.
    >


    You're having this problem because you are using dynamic sql. Since you have
    ignored my previous attempts to wean you off this dangerous crutch, study
    this:
    http://groups-beta.google.com/group.../c966c9c8eb98f331?hl=en&lr=&ie=UTF-8&c2coff=1

    Bob Barrows


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Mar 31, 2005
    #2
    1. Advertising

  3. Drop the ' quotes around numeric values.

    Access expects # around dates, not '

    You should also use YYYY-MM-DD format so that there is no ambiguity (is that
    Jan 3rd or Mar 1st?).

    Search www.aspfaq.com for 80040e07 and for yyyy-mm-dd

    A



    > INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
    > TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')
    >
    > However when I am trying to open the recordset, it gives me the following
    > error:
    >
    > Error Type:
    > Microsoft JET Database Engine (0x80040E07)
    > Data type mismatch in criteria expression.
    Aaron [SQL Server MVP], Mar 31, 2005
    #3
  4. Jack

    Jack Guest

    Thanks to both you Aaron and Bob for the help. Looks like time to wean off
    from dynamic sql. Let's see if I can do that this time. Regards.

    "Aaron [SQL Server MVP]" wrote:

    > Drop the ' quotes around numeric values.
    >
    > Access expects # around dates, not '
    >
    > You should also use YYYY-MM-DD format so that there is no ambiguity (is that
    > Jan 3rd or Mar 1st?).
    >
    > Search www.aspfaq.com for 80040e07 and for yyyy-mm-dd
    >
    > A
    >
    >
    >
    > > INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense,
    > > TravelExpense, PersonnelExpense) VALUES('1', '03/01/2005', '5', '5', '10')
    > >
    > > However when I am trying to open the recordset, it gives me the following
    > > error:
    > >
    > > Error Type:
    > > Microsoft JET Database Engine (0x80040E07)
    > > Data type mismatch in criteria expression.

    >
    >
    Jack, Mar 31, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,257
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    433
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    444
    David Browne
    Aug 21, 2003
  4. Jack
    Replies:
    2
    Views:
    137
  5. Jack
    Replies:
    4
    Views:
    161
Loading...

Share This Page