INSERT statement error.

Discussion in 'ASP General' started by BaWork, Oct 23, 2006.

  1. BaWork

    BaWork Guest

    I have the following to insert a new record:

    <%
    ..

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "DSN=qqqqq;"
    SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    fee_amount & "')"
    Set RS = Conn.Execute(SQLStmt)
    set rs=nothing

    ..
    %>

    The "fee_amount" is a number value that has a default value associated
    with that field in the Access DB.

    When I submit the form WITHOUT a value in the "fee_amount" form field, I
    get the following error:

    "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
    criteria expression."

    That error typically means something like letters are are trying to be
    inserted into a field that has a number data type.

    In this case, no value has been given, when isn't the default value
    being used when the INSERT statement is run?

    Thanks.

    Brett
    BaWork, Oct 23, 2006
    #1
    1. Advertising

  2. BaWork

    Mike Brind Guest

    "BaWork" <> wrote in message
    news:...
    >I have the following to insert a new record:
    >
    > <%
    > .
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "DSN=qqqqq;"
    > SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    > SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    > fee_amount & "')"
    > Set RS = Conn.Execute(SQLStmt)
    > set rs=nothing
    >
    > .
    > %>
    >
    > The "fee_amount" is a number value that has a default value associated
    > with that field in the Access DB.
    >
    > When I submit the form WITHOUT a value in the "fee_amount" form field, I
    > get the following error:
    >
    > "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
    > expression."
    >
    > That error typically means something like letters are are trying to be
    > inserted into a field that has a number data type.
    >
    > In this case, no value has been given, when isn't the default value being
    > used when the INSERT statement is run?
    >
    > Thanks.
    >
    > Brett
    >


    You are passing a string in your SQL for the fee_amount. What is the
    datatype for the fee_amount field in your db?

    --
    Mike Brind
    Mike Brind, Oct 23, 2006
    #2
    1. Advertising

  3. You have quotes around it. So it thinks you are inserting an empty string
    (or perhaps a space).

    Numeric values shouldn't have quotes around them.

    And since you are generating your string dynamically, maybe you could only
    include that column conditionally (e.g. when there is a value).

    A



    "BaWork" <> wrote in message
    news:...
    >I have the following to insert a new record:
    >
    > <%
    > .
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "DSN=qqqqq;"
    > SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    > SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    > fee_amount & "')"
    > Set RS = Conn.Execute(SQLStmt)
    > set rs=nothing
    >
    > .
    > %>
    >
    > The "fee_amount" is a number value that has a default value associated
    > with that field in the Access DB.
    >
    > When I submit the form WITHOUT a value in the "fee_amount" form field, I
    > get the following error:
    >
    > "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
    > expression."
    >
    > That error typically means something like letters are are trying to be
    > inserted into a field that has a number data type.
    >
    > In this case, no value has been given, when isn't the default value being
    > used when the INSERT statement is run?
    >
    > Thanks.
    >
    > Brett
    >
    Aaron Bertrand [SQL Server MVP], Oct 23, 2006
    #3
  4. BaWork

    Mike Brind Guest

    "Mike Brind" <> wrote in message
    news:...
    > "BaWork" <> wrote in message
    > news:...
    >>I have the following to insert a new record:
    >>
    >> <%
    >> .
    >>
    >> Set Conn = Server.CreateObject("ADODB.Connection")
    >> Conn.Open "DSN=qqqqq;"
    >> SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    >> SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    >> fee_amount & "')"
    >> Set RS = Conn.Execute(SQLStmt)
    >> set rs=nothing
    >>
    >> .
    >> %>
    >>
    >> The "fee_amount" is a number value that has a default value associated
    >> with that field in the Access DB.
    >>
    >> When I submit the form WITHOUT a value in the "fee_amount" form field, I
    >> get the following error:
    >>
    >> "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
    >> expression."
    >>
    >> That error typically means something like letters are are trying to be
    >> inserted into a field that has a number data type.
    >>
    >> In this case, no value has been given, when isn't the default value being
    >> used when the INSERT statement is run?
    >>
    >> Thanks.
    >>
    >> Brett
    >>

    >
    > You are passing a string in your SQL for the fee_amount. What is the
    > datatype for the fee_amount field in your db?
    >
    > --
    > Mike Brind
    >


    Oh, and other things you should be aware of:

    1. Don't use a DSN to connect to Access. Use the OLEDB Provider.
    www.connectionstrings.com
    2. Don't create a recordset to insert records
    http://www.aspfaq.com/show.asp?id=2191
    3. Avoid messing around with delimiting values by using a saved queries
    (which also helps protect against SQL injection) rather than dynamic SQL
    statements.
    http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

    --
    Mike Brind
    Mike Brind, Oct 23, 2006
    #4
  5. BaWork

    BaWork Guest

    Are you saying it should be this:

    SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "',' &
    fee_amount & ')"

    When I do that, I get a "[Microsoft][ODBC Microsoft Access Driver]
    Syntax error in INSERT INTO statement." error message.

    Thanks.

    Brett

    Aaron Bertrand [SQL Server MVP] wrote:
    > You have quotes around it. So it thinks you are inserting an empty string
    > (or perhaps a space).
    >
    > Numeric values shouldn't have quotes around them.
    >
    > And since you are generating your string dynamically, maybe you could only
    > include that column conditionally (e.g. when there is a value).
    >
    > A
    >
    >
    >
    > "BaWork" <> wrote in message
    > news:...
    >
    >>I have the following to insert a new record:
    >>
    >><%
    >>.
    >>
    >>Set Conn = Server.CreateObject("ADODB.Connection")
    >>Conn.Open "DSN=qqqqq;"
    >>SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    >>SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    >>fee_amount & "')"
    >>Set RS = Conn.Execute(SQLStmt)
    >>set rs=nothing
    >>
    >>.
    >>%>
    >>
    >>The "fee_amount" is a number value that has a default value associated
    >>with that field in the Access DB.
    >>
    >>When I submit the form WITHOUT a value in the "fee_amount" form field, I
    >>get the following error:
    >>
    >>"[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
    >>expression."
    >>
    >>That error typically means something like letters are are trying to be
    >>inserted into a field that has a number data type.
    >>
    >>In this case, no value has been given, when isn't the default value being
    >>used when the INSERT statement is run?
    >>
    >>Thanks.
    >>
    >>Brett
    >>

    >
    >
    >
    BaWork, Oct 23, 2006
    #5
  6. BaWork

    BaWork Guest

    Mike,

    The data type is "number".

    Thanks for the other information. I'll look into it later today.

    Brett

    Mike Brind wrote:
    > "Mike Brind" <> wrote in message
    > news:...
    >
    >>"BaWork" <> wrote in message
    >>news:...
    >>
    >>>I have the following to insert a new record:
    >>>
    >>><%
    >>>.
    >>>
    >>>Set Conn = Server.CreateObject("ADODB.Connection")
    >>>Conn.Open "DSN=qqqqq;"
    >>>SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount) "
    >>>SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "','" &
    >>>fee_amount & "')"
    >>>Set RS = Conn.Execute(SQLStmt)
    >>>set rs=nothing
    >>>
    >>>.
    >>>%>
    >>>
    >>>The "fee_amount" is a number value that has a default value associated
    >>>with that field in the Access DB.
    >>>
    >>>When I submit the form WITHOUT a value in the "fee_amount" form field, I
    >>>get the following error:
    >>>
    >>>"[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
    >>>expression."
    >>>
    >>>That error typically means something like letters are are trying to be
    >>>inserted into a field that has a number data type.
    >>>
    >>>In this case, no value has been given, when isn't the default value being
    >>>used when the INSERT statement is run?
    >>>
    >>>Thanks.
    >>>
    >>>Brett
    >>>

    >>
    >>You are passing a string in your SQL for the fee_amount. What is the
    >>datatype for the fee_amount field in your db?
    >>
    >>--
    >>Mike Brind
    >>

    >
    >
    > Oh, and other things you should be aware of:
    >
    > 1. Don't use a DSN to connect to Access. Use the OLEDB Provider.
    > www.connectionstrings.com
    > 2. Don't create a recordset to insert records
    > http://www.aspfaq.com/show.asp?id=2191
    > 3. Avoid messing around with delimiting values by using a saved queries
    > (which also helps protect against SQL injection) rather than dynamic SQL
    > statements.
    > http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
    >
    > --
    > Mike Brind
    >
    >
    BaWork, Oct 23, 2006
    #6
  7. > Are you saying it should be this:
    >
    > SQLStmt = SQLStmt & "VALUES ('" & main_cat & "','" & cat_fee & "',' &
    > fee_amount & ')"


    No, I am saying:

    SQLStmt & " SELECT '" & main_cat & "', '" & cat_fee & ", " & fee_amount

    However, that dangling , will cause an error if fee_amount is empty.

    A
    Aaron Bertrand [SQL Server MVP], Oct 23, 2006
    #7
    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. Kathy Burke
    Replies:
    6
    Views:
    618
    Munsifali Rashid
    Aug 13, 2003
  2. compuglobalhypermeganetz0r

    adapter update problem Syntax error in INSERT INTO statement.

    compuglobalhypermeganetz0r, Dec 8, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    811
    compuglobalhypermeganetz0r
    Dec 8, 2003
  3. Saber
    Replies:
    5
    Views:
    511
    Jo Inferis
    Jul 20, 2004
  4. eric dugal

    Syntax error in insert statement

    eric dugal, Jul 31, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,606
    eric dugal
    Aug 1, 2004
  5. J. Muenchbourg
    Replies:
    3
    Views:
    230
    Aaron Bertrand - MVP
    Sep 30, 2003
Loading...

Share This Page