Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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)
%>



Reply With Quote
  #2 (permalink)  
Old January 12th, 2005, 03:00 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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?
Reply With Quote
  #3 (permalink)  
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>

Reply With Quote
  #4 (permalink)  
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

Reply With Quote
  #5 (permalink)  
Old January 14th, 2005, 02:39 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #6 (permalink)  
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

Reply With Quote
  #7 (permalink)  
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.


Reply With Quote
  #8 (permalink)  
Old January 16th, 2005, 06:40 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #9 (permalink)  
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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:34 AM.


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