INSERT statement error.

[BaWork]

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
(e-mail address removed)

 

[Mike Brind]

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
(e-mail address removed)

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

 

[Aaron Bertrand [SQL Server MVP]]

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

 

[Mike Brind]

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
(e-mail address removed)

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

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

 

[BaWork]

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

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

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
(e-mail address removed)

 

[BaWork]

Mike,

The data type is "number".

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

Brett

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
(e-mail address removed)

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

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

 

[Aaron Bertrand [SQL Server MVP]]

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