Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
| Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 11th, 2005, 08:54 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert into statement not working.

I can't seem to get this code to work. The page just won't display. When I comment out the sqlstring = code the page displays just fine so the error must be with that piece of code but I just can't see it. I also don't understand the set a = myconnection.Execute(sqlString) statement. It's from my textbook but I don't see how it connects.

Appreciate any good eyes out there.

Alison

dim myconnection
dim rsTitleList
dim connectstring
dim sqlString
dim lastname
dim firstname
dim phone
dim email
dim address
dim city
dim state
dim zip
dim product
dim oquantity
dim total_price
dim order_time
dim order_date
dim credit_card
dim card_no
dim exp_date

order_date = Date
order_time = Time
product = "Financial Mathematics Handbook"
response.write(product)

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("shopping.mdb")

set myconnection = Server.CreateObject("ADODB.Connection")
set rsTitleList = Server.CreateObject("ADODB.Recordset")

myconnection.open connectString
lastname = Request.Form("lastname")
firstname = Request.Form("firstname")
response.write(firstname)
address = Request.Form("address")
city = Request.Form("city")
state = request.form("state")
zip = request.form("zip")
phone = Request.Form("phone")
email = Request.Form("email")
credit_card = Request.Form("credit_card")
card_no = Request.Form("card_no")
exp_date = Request.Form("exp_date")
oquantity = Request.Form("oquantity")
response.write(oquantity)

total_price = oquantity * 10.95

sqlstring = " INSERT INTO buyer ( lastname, firstname, address, city, state, zip, email, phone,
credit_card, card_no, exp_date, product, oquantity, total_price, Order_date, Order_time) VALUES ('" & lastname &"',
'" & firstname & "', '" & address & "', '" & city & "', '" & state & "', '" & zip & "', '" & email & "',
'" & phone & "', '" & credit_card & "', '" & card_no & "', '" & exp_date & "', '" & product & "', '" & oquantity & "',
'" & total_price & "', '" & order_date & "', '" & order_time & "')"

<%
set a = myconnection.Execute(sqlString)
%>



 
Old January 12th, 2005, 03:00 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

The Set a = construct executes the SQL statement by using the Execute method of your open connection object.
The Set a is then used to store the results of the Sql statement in a forward-only read-only recordset. However, since you're executing an INSERT statement, there is no recordset to store, so this is a bit overkill.

myconnection.Execute(sqlString)

Should work equally well, so maybe you need to doubt your text book... ;)

To debug the SQL statement, write it out before you execute it and then stop the response, like this:
Code:
sqlstring = " INSERT INTO buyer ( lastname, firstname, address, city, state, zip, email, phone, <snip>

Response.Write()
Response.End
set a = myconnection.Execute(sqlString)
Then paste the SQL statement in the Access query designer to see how it runs.

Other things to check: Is the quantity a number in the database? If so, drop the quotes around oquantity.

When you ran this page, what error did you get?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Faultline by Silverchair (Track 3 from the album: Frogstomp) What's This?
 
Old January 13th, 2005, 08:36 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

unkilled the overkill; removed "" from date, time, total price and oquantity fields; pasted sql statement into access query (and it now works) but still get 500 error - unable to display page / internal server error.

Any other suggestions? revised code below.

really appreciate the help.
Alison

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Get order 1</title>
<meta name="Microsoft Theme" content="sandston 1011">
<meta name="Microsoft Border" content="tb">
</head>

<body>


<%

dim myconnection
dim rsTitleList
dim connectstring
dim sqlString
dim lastname
dim firstname
dim phone
dim email
dim address
dim city
dim state
dim zip
dim product
dim oquantity
dim total_price
dim order_time
dim order_date
dim credit_card
dim card_no
dim exp_date

order_date = Date
order_time = Time
product = "Financial Mathematics Handbook"
response.write(product)

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("shopping.mdb")

set myconnection = Server.CreateObject("ADODB.Connection")
set rsTitleList = Server.CreateObject("ADODB.Recordset")

myconnection.open connectString
lastname = Request.Form("lastname")
firstname = Request.Form("firstname")
address = Request.Form("address")
city = Request.Form("city")
state = request.form("state")
zip = request.form("zip")
phone = Request.Form("phone")
email = Request.Form("email")
credit_card = Request.Form("credit_card")
card_no = Request.Form("card_no")
exp_date = Request.Form("exp_date")
oquantity = Request.Form("oquantity")


total_price = oquantity * 10.95

sqlstring = " INSERT INTO buyer ( lastname, firstname, address, city, state, zip, email, phone,
credit_card, card_no, exp_date, product, oquantity, total_price, Order_date, Order_time) VALUES ('" & lastname &"',
'" & firstname & "', '" & address & "', '" & city & "', '" & state & "', '" & zip & "', '" & email & "',
'" & phone & "', '" & credit_card & "', '" & card_no & "', ' & exp_date & ', '" & product & "', ' & oquantity & ',
' & total_price & ', ' & order_date & ', ' & order_time & ')";

<%
myconnection.Execute(sqlString)
%>


<center>

Congratulations!
<p>You have successfully ordered the following item(s):</p>
<p>Financial Mathematics Handbook</p>
<p>We will ship it (them) to you within two business days.</p>
<p><a href="shopping.htm">Return to our book list.</a></p>
<p></p>

</center>


</body></html>

 
Old January 13th, 2005, 09:36 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar/ you can see what I'm trying to do by going to www.aqchase.com and clicking on shopping tab and order the first book. / Alison

 
Old January 14th, 2005, 02:39 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

A generic Server 500 error is not very helpful, as you probably found out by now. To get the real error message, follow the steps from this article:

http://Imar.Spaanjaars.Com/QuickDocId.aspx?QUICKDOC=264

You should then get a real error message. Can you post that here??

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old January 15th, 2005, 01:51 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This was very helpful... I actually had several errors and still don't have it working but I can at least see what's happening. I'll continue to work until I'm stuck again and then seek help.

You wrote a great article. Thanks
Alison

 
Old January 15th, 2005, 08:22 PM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/get_order1.asp, line 100

sqlstring = " INSERT INTO buyer ( lastname, firstname, address, city, state, zip, email, phone, credit_card, card_no, exp_date, product, oquantity) VALUES ('" & lastname &"', '" & firstname & "', '" & address & "', '" & city & "', '" & state & "', '" & zip & "', '" & email & "', '" & phone & "', '" & credit_card & "', '" & card_no & "', '" & exp_date & "', '" & product & "',' & oquantity & ');"

in the database, all are text except for oquantity, which is a number. When I do just the text fields, it works. When I then add oquantity, that's when I get the error. There's something just not right with oquantity. In the other page, the form uses the following statement:

<p>&nbsp;Quantity Ordered:<input type="number" name="oquantity" size="3"><p>

I know that this is beginner stuff but I just can't see it.
Your help appreciated.


 
Old January 16th, 2005, 06:40 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Instead of the string sqlstring, can you post its value?

E.g.

Response.Write(sqlstring)
Response.End

That way, you can debug the query much better. Did you try pasting the results in Access??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old January 16th, 2005, 10:52 AM
Authorized User
Points: 96, Level: 1
Points: 96, Level: 1 Points: 96, Level: 1 Points: 96, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

finally found problem.. instead of order_time = Time I needed to write this as order_time = ("#"& Time & "#")

Your advice to use response etc., use Access to debug, and turn off friendly HTTP notices was excellent... thank you.





Similar Threads
Thread Thread Starter Forum Replies Last Post
iff statement not working barmanvarn BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 3 January 23rd, 2009 04:25 PM
Simple but not working IF THEN statement nlicata Classic ASP Databases 6 June 6th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.