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

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/vms/save_edited_vendor.asp, line 30

got this error when i run the system. i think it is because the company_id is integer. i don't know the format for integer in ASP. the code are as follows:
------------------

<%
'declare variables
Dim Conn,DSN,rs,strsql
company_id=session("company_id")

company_name= Request.Form("companyname_text")
address= Request.Form("address_text")
postcode= Request.Form("postcode_text")
city= Request.Form("city_text")
state= Request.Form("state_list")
phone= Request.Form("phone_text")
fax= Request.Form("fax_text")
email= Request.Form("email_text")
website= Request.Form("website_text")
            
            Set Conn=Server.CreateObject("ADODB.Connection")
            Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/db1.mdb")            
            Set SQL= Server.CreateObject("ADODB.Recordset")
            SQL.Open "SELECT * From vendor WHERE company_id='"&company_id&"'",Conn,2,2 <--- line 30
                        
            SQL("company_name")=company_name
        SQL("address")=address
        SQL("postcode")=postcode
            SQL("city")=city
            SQL("state")=state
            SQL("phone")=phone
            SQL("fax")=fax
            SQL("email")=email
            SQL("website")=website
            SQL.Update
Conn.Close
Set Conn = Nothing      
%>

-----------------------

Thanks & Regards
firdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hongjunCommented:
try this

SQL.Open "SELECT * From vendor WHERE company_id="&company_id,Conn,2,2

hongjunCommented:
You do not need a quote for integer
firdAuthor Commented:
thanks but i got this error when i ignore the quote:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'company_id='.
/vms/save_edited_vendor.asp, line 30
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

gladxmlCommented:
try to check if the company_id variable has a value...

company_id=session("company_id")

try this

Set SQL= Server.CreateObject("ADODB.Recordset")
strsql = "SELECT * From vendor WHERE company_id=" &company_id
response.write strsql
response.end
SQL.Open strsql,Conn,2,2

It is much better if you will put the select statement in a variable so that when debuggin it would be much easier... This way you will see the resulting sql statement...

HTH...

HAppy programming...
gladxmlCommented:
for more information regarding your error try to check out the link might help...

http://www.adopenstatic.com/faq/80040e10.asp

Happy programming
hongjunCommented:
yes!
Display the value of company_id.


Cheers
hongjun
hongjunCommented:
firdAuthor Commented:
I tried ur solution but the page display this

SELECT * From vendor WHERE company_id=


if i use this company_id=session("company_id") does it means i display the value of company_id?

thanks 4 ur time
gladxmlCommented:
Right it will show the generated sql as you can see on the output it does not have a value to compare after the equal sign that is why you are gettin the error.

This means that

session("company_id")

does not have a value...

To check what I am talking about try this...

if company_id <> "" then
Set SQL= Server.CreateObject("ADODB.Recordset")
strsql = "SELECT * From vendor WHERE company_id=" &company_id
SQL.Open strsql,Conn,2,2

          SQL("company_name")=company_name
        SQL("address")=address
        SQL("postcode")=postcode
          SQL("city")=city
          SQL("state")=state
          SQL("phone")=phone
          SQL("fax")=fax
          SQL("email")=email
          SQL("website")=website
          SQL.Update
Conn.Close
Set Conn = Nothing    
else
response.write "company id does not have a value."
end if


HTH...

HAppy programming...
gladxmlCommented:
BTW is the problem not solve yet... I saw that you already close a question similar to this which is posted by hongjun on anothe link.

babuno5Commented:
hi try this out it will work
<%
'declare variables
Dim Conn,DSN,rs,strsql
company_id=session("company_id")
response.write company_id
response.end


Now if u see the comanyId value then u can perform the SQL query as follows:

 Set Conn=Server.CreateObject("ADODB.Connection")
          Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/db1.mdb")          
          Set SQL= Server.CreateObject("ADODB.Recordset")
          SQL.Open "SELECT * From vendor WHERE company_id=" & Cint(company_id),Conn,2,2



And if u dont get the value of the comany_id then check your previous form whether the value exists or not
randeepsCommented:
It  seems that you have forgot to set the Session("company_id") in a previous page or your session must have timedout when running/executing this page.


Pls make sure that you assign the company_id to Session("company_id") where necessary in your site before coming to this page.

Also just for the record, the SQL server implicitly converts character/string data to integer ... so even if you have the single quote in your sql statement, it will still work provided you have assigned the company_id to the Session("company_id").
firdAuthor Commented:
thanks hongjun, gladxml, babuno & randeeps. I forgot to set the session("company_id") in the previous page. but i still got a problem in the same coding after i set the session in the previous page. the coding seems like working successfully but when i check the database, all fields are empty except the company_id(primary key). thanks again for ur help guys!
randeepsCommented:

1. first of all, please check in your html code that your form tag has method="post" explicitly set and not method=get (or without the method attrib)

<form name="FormSubmit" method="Post"  .... >


2. Pls Response.write "SELECT * From vendor WHERE company_id='"&company_id&"'" from the page geting error and paste the result.

3. It is not recommended to open a recordset to update a record... instead send a DML command i.e. UPDATE/INSERT/DELETE for the relevant task

so it should be

strSQLUpdate = "UPDATE vendor SET  company_name = '" & company_name & "', address = '" & address & "', postcode='" & postcode & "', city='" & city & "', state='" & state & "', phone='" & phone & "', fax='" & fax & "', email='" & email & "', website='" & website & "'; "

Conn.Execute strSQLUpdate
Conn.Close
Set Conn = Nothing    

(you do not need recordset for the update)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
firdAuthor Commented:
thanks guys for ur help. appreciated!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.