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 ba****@sprynet.com 6 2078
"BaWork" <ba****@sprynet.comwrote in message
news:ea**************@TK2MSFTNGP04.phx.gbl...
>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 ba****@sprynet.com
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
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" <ba****@sprynet.comwrote in message
news:ea**************@TK2MSFTNGP04.phx.gbl...
>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 ba****@sprynet.com
"Mike Brind" <us********@smap.comwrote in message
news:e8**************@TK2MSFTNGP02.phx.gbl...
"BaWork" <ba****@sprynet.comwrote in message
news:ea**************@TK2MSFTNGP04.phx.gbl...
>>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 ba****@sprynet.com
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/micro...d322b882a604bd
--
Mike Brind
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" <ba****@sprynet.comwrote in message
news:ea**************@TK2MSFTNGP04.phx.gbl...
>>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 ba****@sprynet.com
Mike,
The data type is "number".
Thanks for the other information. I'll look into it later today.
Brett
Mike Brind wrote:
"Mike Brind" <us********@smap.comwrote in message
news:e8**************@TK2MSFTNGP02.phx.gbl...
>>"BaWork" <ba****@sprynet.comwrote in message news:ea**************@TK2MSFTNGP04.phx.gbl...
>>>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 ba****@sprynet.com
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/micro...d322b882a604bd
--
Mike Brind
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: harborboy76 |
last post by:
Hi,
I am trying to insert a large number of rows into a table
inside a SPL. But every time, I run the SPL, the table is
locked because of the INSERT. When I tried to issue a COMMIT,
right after...
|
by: 73blazer |
last post by:
Hello,
I'm looking for a way to make some of my insert templates more readable
by placing comments in between the values. I cannot seem to find a way
to do this with DB2, is there a way? I'm...
|
by: kosta |
last post by:
hello! one of my forms communicates with a database, and is
supposed to add a row to a table using an Insert
statement... however, I get a 'oledb - syntax error'
exception... I have double...
|
by: Tim::.. |
last post by:
Can someone please tell me why I keep getting the following error from the
code below!
Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint...
|
by: Metal Dave |
last post by:
I do not understand the error handling of SQL Server here. Any error in
bulk insert seems to halt the current T-SQL statement entirely, rendering
it impossible to log an error. The first statement...
|
by: Jack |
last post by:
Hi,
I am gathering the input values to a form using Request.form method from the
processing page. After all the data is captured, I am building sql statement
out of it. Using a response.write...
|
by: mahajanvit |
last post by:
Hi one and all
I got this problem during my project. So in order to solve this I made
a very small application. I am trying to insert using SP and
sqldatasource control. I know that while using...
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am...
|
by: Maklar60 |
last post by:
I am attempting to execute an INSERT statement on my page but continually get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Incorrect syntax near '<'.
...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |