|
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.
|
|
|
|
|
code for insurance management system oracle back end
|
|
|
|
|
Is this a statement, a question, or just some vague musings of an idle mind?
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Ah. You haven't installed the StudentTranslation App.
"code for insurance management system oracle back end" translates to "Do my homework for me. Now."
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
I want to create an SQL syntax using VB.Net which will allow me to filter data on a date field in my table in MySql with the current Date. The issue is I have the dates saved in the Table with Type VARCHAR(). One way is to convert the current Date to String and then compare. But is there any process by which I can convert the Table String field to Date format and then compare with current Date. Basically my SQL Syntax with be something like below:
strSql = "Select * from TBL1 where TBL_Date1 >= " + Now()
This will not work since TBL_DATE1 is a VARCHAR Type and NOW() is of Type DateTime
I am pretty new to .net and hence would like some help from you all.
|
|
|
|
|
It's a really bad idea to store dates as VARCHAR in your database, you should use the DATETIME type. However, you can convert it to a date value as described here[^].
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
SPSandy wrote: This will not work since TBL_DATE1 is a VARCHAR Type
- Dates are stored as a date, not as a varchar. That's what needs to be changed, not the query; converting the varchar to a date to do a comparison is a dirty hack, trying to work around a previous mistake. (If you move the database or the culture changes, things will break.)
- It's recommended to sanitize the query; use a DbParameter, the way it's now I could destroy your database.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
All your database are belong to us ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
I understand your advice and would like to go in the right way. But the problem is being new to VB.Net I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date before storing by using Format(Date.Parse(txtdt.Text), "yyyy/mm/dd"). But this is not working. I have been trying to find some solution from internet but not able to get anything. This prompted me to move ahead for the time being by using VarChar. But now I am stuck since comparison in VarChar does not give the right result 
|
|
|
|
|
SPSandy wrote: But the problem is being new to VB.Net
..that's not a problem, just a matter of time. Next year you'll be explaining the concept to someone else who is new
SPSandy wrote: I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date
Databases do not store date's in a specific notation, they store it as a number. Internally, it is just "n" numbers that have passed since januari 1st, 1900, (or some other date) with the time-part stored as a fraction. (Imagine the double 3693.5; that'd be 3693 days since the epoch, and .5 as the time, so probably 12:00 AM)
When you "ask" MySql for a date, it returns a Date type, a double - not a text with month-separators as a string. The problem with using a string, is that the datetime-functions do not know how to interpret it - they see a string, a text, not a datetime.
When you get a value from a textbox, that datetime is in the "users" locale; it's in a specific format, perhaps with daylight saving. You convert that to a "real" DateTime and store it in it's native format. (Otherwise we'd have to teach all the date-functions the differences between timezones and teach them how to read a date in a foreign notation!)
To sum it up; if you have the option of converting the field back to a DateTime, please do so; it'll save you from a lot of headaches in the future. Yes, I can see how it is a workaround for the problem you described, but the price for the trade-off is too high. If you're having trouble making it work, post the code and we'll have a look.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for the comment. I have finally been able to solve the issue. MySql does accept only yyyy/mm/dd when saving data. In case your data is in any other format then it will display an error.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
