Hello fellow ASPers,

I'm trying to write to table inside an access database that's related by a column. The browser keep returning the following error;

=====================================

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

======================================

Heres my code:

<%
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("purchase.mdb")

sql_insert = "insert into tblOne (DateRequest, Site, DateNeeded, PurchaseType, Cost, SalesTax, ShippingHandling, ProgramArea, DescripItem, SubmitBy) values ('" & tblone_DateRequest & "', '" & tblone_Site & "', '" & tblone_DateNeeded & "', '" & tblone_PurchaseType & "', '" & tblone_Cost & "', '" & tblone_SalesTax & "', '" & tblone_ShippingHandling & "', '" & tblone_ProgramArea & "', '" & tblone_DescripItem & "', '" & tblone_SubmitBy & "')"

sql_insert2 = "insert into tblTwo (VendorName, VendorAddress, City, State, Zip) values ('" & tbltwo_VendorName & "', '" & tbltwo_VendorAddress & "', '" & tbltwo_City & "', '" & tbltwo_State & "', '" & tbltwo_Zip & "')"

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
con.Execute sql_insert2

' Done. Close the connection
con.Close
Set con = Nothing
%>

What am I doing wrong?  Any help would be greatly appreciated.

Thank you.

Groq

Data mismatch usualy means your trying to put a value into the database of a datatype which the field its going in isnt, for example loading a string into a integer data field.

Ive had a quick look at your code, in the insert statement you put the numeric variables like this:

'" & numericVar & "'

When for numeric ones it should be:

" & numericVar & "

The ' tells it its inserting a string

okay,

I made the suggested changes to my code but still no cigar, and I'm getting a new error message from the browser:

==============================

Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
=============================

I've double checked the database and all the field types are correct.  Oddly, data was added into the tables.  but the foriegn key of one of the tables didn't update from the primary key of the first table.  What am I doing wrong? Anyone?

thanks,

Groq

ok, here it is

============

sql_insert = "insert into tblOne (DateRequest, Site, DateNeeded, PurchaseType, Cost, SalesTax, ShippingHandling, ProgramArea, DescripItem, SubmitBy) values ('" & tblone_DateRequest & "', '" & tblone_Site & "', '" & tblone_DateNeeded & "', '" & tblone_PurchaseType & "', " & tblone_Cost & ", " & tblone_SalesTax & ", " & tblone_ShippingHandling & ", '" & tblone_ProgramArea & "', '" & tblone_DescripItem & "', '" & tblone_SubmitBy & "')"

sql_insert2 = "insert into tblTwo (VendorName, VendorAddress, City, State, Zip) values ('" & tbltwo_VendorName & "', '" & tbltwo_VendorAddress & "', '" & tbltwo_City & "', '" & tbltwo_State & "', '" & tbltwo_Zip & "')"

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
con.Execute sql_insert2

==========================

groq

Its hard to tell sorry.  go to borgs ASP section, and it shows an alternate method to insert into a database which is easier than a single line SQL query

Gullanian,

thanks for your help.  Who and what is borg?  Is this a member or website. Can you please provide a link?

Thanks,

Groq

groq wrote:  Who and what is borg?

A fiercesome deity... red sleep deprived eyes, large caffeine stained incisors... seldom bites children and small furry animals... known to mumble to him/itself.

http://www.webwiz.net/asp/default.asp - http://www.webwiz.net/asp/default.asp