Writing to Two Tables at once

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

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

Gullanian,

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

Thanks,

Groq