Using INSERT, then DELETE from another table

Question

jcdisciple 0 Newbie Poster

16 Years Ago

I am trying to build a simple web database for my church. We have a list of items that we want people to choose from. I want to display on the web page what items have been chosen, and remove that item from the drop down list in my Sponsorship form.

The Drop Down Option list & the Items Still Needed list are being driven by the same table in the database.

The Sponsorship form works great inserting data into the Sponsors table, and my 'itemChosen' list works.

My goal is to DELETE the itemsNeeded from my ItemList table when the itemChosen has been inserted into my Sponsors table.

(ItemChosen is the same as ItemsNeeded) ( identical data values )

I have tried adding the following SQL:

sSQL = "INSERT into Sponsors (itemChosen, name, phone, email) values ('" & itemChosen & "', '" & name & "', '" & phone & "', '" & email & "') [B]AND DELETE * FROM ItemList WHERE itemChosen = itemsNeeded[/B]"

, but its giving me an error stating

"Missing semicolon (;) at end of SQL statement."

So, I add the semicolon to this:

'execute the SQL
connection.execute(sSQL);

which gives me a new error of:

Expected end of statement

/wwwroot/MexicoTripItemSignupProcess.asp, line 37

connection.execute(sSQL);
-----------------------------^

####################################

My two tables are:

ItemList
Field: id, itemsNeeded

Sponsors
Field: id, itemChosen,name,phone,email

I have attached my pages for review. I really believe its something so incredibly easy, but I can't figure it out.

CODE#########################

<%@ Language="VBScript" %>
<% Option Explicit %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Thank You.</title>
</head>
<body>
<%

'declare your variables
Dim itemChosen, name, phone, email
Dim sConnString, connection, sSQL

'Receiving values from Form, assign the values entered to variables
itemChosen = Request.Form("itemChosen")
name = Request.Form("name")
phone = Request.Form("phone")
email = Request.Form("email")

'declare SQL statement that will query the database
sSQL = "INSERT into Sponsors (itemChosen, name, phone, email) values ('" & itemChosen & "', '" & name & "', '" & phone & "', '" & email & "')"

'define the connection string, specify database driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("..\db\mexicotrip.mdb")

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)

Response.Write "<center><div style=""text-align: center; padding: 25px 25px; width:250px"">"
Response.Write "<h2 style=""text-align:center"">Thank You</h2>"
Response.Write "</div></center>"
Response.Redirect("./MexicoTripList.asp")

'Done. Close the connection object
connection.Close
Set connection = Nothing
%>
</body>
</html>

asp

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

MexicoTrip.zip (23.04 KB)

2 Contributors
2 Replies
169 Views
9 Hours Discussion Span
Latest Post 16 Years Ago Latest Post by jcdisciple

dickersonka 104 Veteran Poster

16 Years Ago

You need a semicolon at the end of the sql statement

itemsNeeded"  & "';"

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jcdisciple 0 Newbie Poster · Answer 1 · 2008-08-27T04:58:53+00:00

Thanks for the prompt reply. That might have worked, but in the end I see that I had my statement wrong to begin with.

Here's what I did to make it work -

'declare your variables
Dim itemChosen, itemsNeeded, name, phone, email
Dim sConnString, connection, sSQL

'Receiving values from Form, assign the values entered to variables
itemChosen = Request.Form("itemChosen")
name = Request.Form("name")
phone = Request.Form("phone")
email = Request.Form("email")

'declare SQL statement that will query the database
sSQL = "INSERT into Sponsors (itemChosen, name, phone, email) values ('" & itemChosen &_
"', '" & name & "', '" & phone & "', '" & email & "')"

'define the connection string, specify database driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("mexicotrip.mdb")

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)

'################## Now We Delete the Item from Database

'Receiving values from Form, assign the values entered to variables
itemChosen = Request.Form("itemChosen")

'declare SQL statement that will query the database
sSQL = "DELETE '" & itemChosen & "' FROM ItemList WHERE itemsNeeded = '" & itemChosen & "'"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)