|
First of all I want to apologize in advance if this is the wrong section to post this. I was looking for VBScript section and I wasn't able to find one. I have a Database (Access) and I'm trying to insert information in it. I keep getting the following error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/ordersconnection3.asp, line 33
There are around 19 fields. Some are defined as text, number, currency, date/time. How do I define the variables datatype in VBscrpit. On Visual Basic it was as easy as Dim X As Double. From what I've researched As in Dim is not allowed.
|
|
|
|
|
According to this[^], you can't.
This[^] indicates a cause may be the permissions on the folder.
This is all the help I can give you, unless you post some of the script that is giving the error.
I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image.
Stephen Hawking
|
|
|
|
|
<%
Dim Employee_ID, Customer_ID, Shipper_ID, Tax_Rate, Tax_Status, Status_ID
Dim Order_Date, Shipped_Date, Paid_Date
Dim Ship_Name, Ship_Address, Ship_city, Ship_State_Province, Ship_Zip_Postal_Code, Ship_Country_Region, Payment_Type, Notes
Dim Shipping_Fee, Taxes
Employee_ID=Request.Form("Employee_ID")
Customer_ID=Request.Form("Customer_ID")
Order_Date=Request.Form("Order_Date")
Shipped_Date=Request.Form("Shipped_Date")
Shipper_ID=Request.Form("Shipper_ID")
Ship_Name=Request.Form("Ship_Name")
Ship_Address=Request.Form("Ship_Address")
Ship_City=Request.Form("Ship_City")
Ship_State_Province=Request.Form("Ship_State_Province")
Ship_Zip_Postal_Code=Request.Form("Ship_Zip_Postal_Code")
Ship_Country_Region=Request.Form("Ship_Country_Region")
Shipping_Fee=Request.Form("Shipping_Fee")
Taxes=Request.Form("Taxes")
Payment_Type=Request.Form("Payment_Type")
Paid_Date=Request.Form("#Paid_Date#")
Notes=Request.Form("Notes")
Tax_Rate=Request.Form("Tax_Rate")
Tax_Status=Request.Form("Tax_Status")
Status_ID=Request.Form("Status_ID")
sSQL = "INSERT INTO Orders (Employee_ID, Customer_ID, Order_Date, Shipped_Date, Shipper_ID, Ship_Name, Ship_Address,
Ship_City, Ship_State_Province, Ship_Zip_Postal_Code, Ship_Country_Region, Shipping_Fee, Taxes, Payment_Type, Paid_Date,
Notes, Tax_Rate, Tax_Status, Status_ID) Values ('" & Employee_ID & "', '" & Customer_ID & "','" & Order_Date & "', '" &
Shipped_Date & "', '" & Shipper_ID & "', '" & Ship_Name & "', '" & Ship_Address & "','" & Ship_City & "', '" &
Ship_State_Province & "', '" & Ship_Zip_Postal_Code & "', '" & Ship_Country_Region & "', '" & Shipping_Fee & "','" & Taxes
& "', '" & Payment_Type & "', '" & Paid_Date & "', '" & Notes & "', '" & Tax_Rate & "', '" & Tax_Status & "', '" &
Status_ID & "')"
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/database/Examen.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
connection.execute(sSQL)
response.write "The form information was inserted successfully."
connection.Close
Set connection = Nothing
%>
I'm assuming that the problem lies withing the values part of the Insert into.
|
|
|
|
|
Take your single quotes out from around values that are supposed to be numeric
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
|
|
|
|
|
So leave dates as they are and only take the single quotes and leave ampersands? I tried taking the single quotes for numeric values and now I'm getting a syntax error.
|
|
|
|
|
AFAIK, in Access database SQL strings, dates are delimited by #. No delimiters for numbers and single quotes for strings.
HTH
|
|
|
|
|
Thank you for taking your time and helping me out. I did over 12 hours of "Research" on this last night and I couldn't get a straight answer from the internet. I'm still getting a data type mismatch when I input all the information.
I noticed I have two fields that I have never worked before. In access I have 2 fields that read memo as type and 2 that are in the currency format. Would I follow '" "' for memos and " " for currency or do they have a determined way to input data. Visual Studio debugger keeps telling me there's a problem with this certain line: connection.execute(sSQL).
My code reads as follows:
Employee_ID=Request.Form("Employee_ID")'Number *field in access*
Customer_ID=Request.Form("Customer_ID")'Number
Order_Date=Request.Form("Order_Date")'Date/Time
Shipped_Date=Request.Form("Shipped_Date")'Date/Time
Shipper_ID=Request.Form("Shipper_ID")'Number
Ship_Name=Request.Form("Ship_Name")'Text
Ship_Address=Request.Form("Ship_Address")'Memo
Ship_City=Request.Form("Ship_City")'Text
Ship_State_Province=Request.Form("Ship_State_Province")'Text
Ship_Zip_Postal_Code=Request.Form("Ship_Zip_Postal_Code")'Text
Ship_Country_Region=Request.Form("Ship_Country_Region")'Text
Shipping_Fee=Request.Form("Shipping_Fee")'Currency
Taxes=Request.Form("Taxes")'Currency
Payment_Type=Request.Form("Payment_Type")'Text
Paid_Date=Request.Form("Paid_Date")'Date/Time
Notes=Request.Form("Notes")'Memo
Tax_Rate=Request.Form("Tax_Rate")'Number
Tax_Status=Request.Form("Tax_Status")'Number
Status_ID=Request.Form("Status_ID")'Number
sSQL = "INSERT INTO Orders (Employee_ID, Customer_ID, Order_Date, Shipped_Date, Shipper_ID, Ship_Name, Ship_Address, Ship_City, Ship_State_Province, Ship_Zip_Postal_Code, Ship_Country_Region, Shipping_Fee, Taxes, Payment_Type, Paid_Date, Notes, Tax_Rate, Tax_Status, Status_ID) Values (" & Employee_ID & ", " & Customer_ID & ", #" & Order_Date & "#, #" & Shipped_Date & "#, " & Shipper_ID & ", '" & Ship_Name & "', '" & Ship_Address & "','" & Ship_City & "', '" & Ship_State_Province & "', '" & Ship_Zip_Postal_Code & "', '" & Ship_Country_Region & "', " & Shipping_Fee & ", " & Taxes & ", '" & Payment_Type & "', #" & Paid_Date & "#, '" & Notes & "', " & Tax_Rate & ", " & Tax_Status & ", " & Status_ID & ")"
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/database/Examen.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
connection.execute(sSQL)
response.write "The form information was inserted successfully."
connection.Close
Set connection = Nothing
%>
I don't expect for anyone to solve the problem for me, I'm just simply not experienced enough with html and asp vbscript quoting to figure this out on my own as I've been working on this all weekend long since Thursday and I've gotten nowhere. I've created around 7 connections and once I get this going, I feel confident enough that the others will work.
|
|
|
|
|
Memo fields are string types, and currency are number types - so follow the same format as those types when creating your string.
The best way to check if your SQL string works is to place a debug output after the string is created, copy that string over to the MS Access query builder and run it there. That should give you better insight on what the problem could be.
HTH
|
|
|
|
|
So I should not have a problem if the person uses the $00.00 format in currency? I have to go to class now, but as soon as I'm back I'm giving this a try, again a million thanks for taking your time.
|
|
|
|
|
Unfortunately, I am unable to provide any more assistance, as I do not know that much about this. The only other thing I can think of is to check the order of the fields in the database and make sure they match the SQL script.
EDIT: do what Wes said.
I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image.
Stephen Hawking
|
|
|
|
|
Scrap this and rewrite. There's a reason the command ADODB command object lets you create parameter objects. It's to avoid crappy, unsupportable and unmaintainable code like this. I'm refering specifically to the sSQL="..." concatenation nightmare you've created.
Start by reading these articles[^].
|
|
|
|
|
Can I just get the proper form to enter values? I know strings go as followed: '" & variable & "'to insert information. I just need numbers and date/time and I will be all set.
|
|
|
|
|
No. I gave you the list of stuff to read. It's now up to you to do the basic research and teach yourself this stuff. I'm not in the business of spoon-feeding people who can't do basic research.
|
|
|
|
|
I completely missed the link to the articles on your last post. I've been sitting here from 9 am till now (4 pm) trying to solve this issue so I'm out of ways to research this topoc. I do not intend for you or anyone to spoon feed me the answers. Thanks again for the links.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
