ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

Using ASP to delete records on access 2000

Discuss Using ASP to delete records on access 2000 in the ASP Programming forum on Dev Shed.
Using ASP to delete records on access 2000 ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 5th, 2003, 02:00 PM
Pain Pain is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: Sheffield, UK
Posts: 94 Pain User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Send a message via ICQ to Pain
Using ASP to delete records on access 2000
Hey guys, could you possibly take a look at the following code and let me know whats going wrong with it? When I run the code (its part 2 of 2 screens, where part 1 is a form) it says that there is an error on line 32 (adoCmd.execute). I cant work out whats going wrong, coz everything else seems to work fine.

Please help, and thanks in advance for your time.

Chris

Code as follows:

<% @language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<html>
<head>
<title>Popup deleted</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<h1> Popup Deleted </h1>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoConn, adoCmd, strSQL, pid
' Get the values posted from the Form
pid = Request("pid")
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
adoConn.Open strConnection
strSQL = "DELETE FROM POPUP WHERE PID = '" & pid & "'"
' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = adoConn
' Execute the SQL on the Command object
adoCmd.Execute
' Tidy up
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
%>

<br>
<p> <a href="PSA_Index.htm">Start Over </a>
</body>
</html>
Reply With Quote
  #2  
Old September 5th, 2003, 02:23 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Ok, I'll make your life and code easier

Why oh why are you creating a Command Object ?

Command Object's are often used for SPROCs(Store Procedures) with output parameters and what not...

*All* you have is a simple DELETE query.

Don't create the extra overhead of the Command Object

Simply use the/your Connection object
Also note, that since your using/creating a DELETE query then NO RECORDSET are necessary

So...try this:

<%
Const adExecuteNoRecords = 128
Dim adoConn
Dim strSQL
Dim pid

' Get the values posted from the Form
pid = Request("pid")

strSQL = "DELETE FROM POPUP WHERE PID = '" & pid & "'"
'FOR DEBUG ONLY
'Response.Write strSQL & "<hr>"
'Response.End

Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open strConnection

adoConn.execute strSQL, adExecuteNoRecords

adoConn.Close
Set adoConn = Nothing
%>

That's it...simple, effecient, fast and clean


Now all you need to do is set the *strConnection* variable
and also make sure the PID field isn't numeric otherwise you'll have to *change* your DELETE query to:

strSQL = "DELETE FROM POPUP WHERE PID = " & CLng(pid)


You could also make a validation of the variable *pid* to make sure that there is a value in it, otherwise the SQL Query might crash...for example if *pid* is equal to the letter "A" when in fact it is expecting a number...or if it holds an apostrophe...or...or...

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #3  
Old September 5th, 2003, 02:33 PM
Pain Pain is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: Sheffield, UK
Posts: 94 Pain User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Send a message via ICQ to Pain
Wow thanks for such a prompt reply.
Reply With Quote
  #4  
Old September 5th, 2003, 02:41 PM
Pain Pain is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: Sheffield, UK
Posts: 94 Pain User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Send a message via ICQ to Pain
Eeek, im quick to return with bad news.

I've tried to use the code that you supplied, and I still get an error message. I have previously specified what strConnection is through my adoConn.asp include file.

The error message I get this time, again is on line 32 (adoConn.Open strConnection) and says:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/psa/delete_pm_conf.asp, line 32

the value of 'pid' is going to be a numeric (its an id number in the database), but trying both values ' & pid &' and & CLng(pid) dont work.

Any other suggestions?
Reply With Quote
  #5  
Old September 5th, 2003, 02:43 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Well if line 32 is the adoConn.Open strConnection

Then your error is in your strConnection variable

Try and see if the *string* inside the variable is correct!

Vlince
Reply With Quote
  #6  
Old September 5th, 2003, 03:09 PM
Pain Pain is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: Sheffield, UK
Posts: 94 Pain User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Send a message via ICQ to Pain
Ok feel free to start slapping me with fish any time (you'll have to be patient, only been doing this for 2 days or so - everything that university taught me seems to be horse sh!t and doesnt work), but...

..with a bit of tweaking, i've got it thus far:
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/psa/delete_pm_conf.asp, line 31

Code as you gave earlier:

Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open strConnection
adoConn.execute strSQL, adExecuteNoRecords <--**Line 31**
adoConn.Close
Set adoConn = Nothing

the connection string (strConnection) is defined in dbConn.asp as:

<%
Dim strDBVirtualPath, strDBLocation, strConnection
strDBVirtualPath = "PSA.mdb"
strDBLocation = Server.Mappath(strDBVirtualPath)
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBLocation
Response.Write Chr(13)
%>

Im not very good at this milarky - im meant to be a writer, not a programmer

Chris
Reply With Quote
  #7  
Old September 5th, 2003, 03:23 PM
Vlince Vlince is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Location: Canada, Quebec, Montreal
Posts: 410 Vlince User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Ok, I'll try and go easy on you since it's friday

Now...

Let's forget about the include files you have. In fact I'd like you to create a simple *NEW* asp page. Call it whatever you want...

then inside that page write this:


<%@ Language=VBScript %>

<%
Const adExecuteNoRecords = 128
Dim lngPin
Dim strSql
Dim oConn


lngPin = Trim(Request("pid"))
'FOR DEBUG ONLY
'Response.Write "-->" & lngPin & "<--<hr>"
'Response.End


'Validation of the *lngPin* variable
If lngPin = "" Then
Response.Write "No did not provide a pin number..."
Response.End
End If

'Let's validate if the *lngPin* is a numeric value
If NOT IsNumeric(lngPin) Then
Response.Write "The pin number must be a number..."
Response.End
End If

'Now let's create the SQL Query
'NOTICE that I didn't connect to my database yet...

strSql = "DELETE FROM POPUP WHERE PID = " & CLng(lngPin)
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End

'Ok...we are now ready to connect to your database...

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=admin;" & _
"Pwd="

'Once the connection is open, let's execute your Query
oConn.execute strSql, adExecuteNoRecords

oConn.Close
Set oConn = Nothing
%>

Now notice the first thing...I've prefixed the variable *lngPin* with lng because it tells *ME* the programmer to expect a number. since variables in VBScript aren't typed, prefixing them *will* help me understand what to expect in *that* variable.

Also, you'll need to change the value in the oConn.Open to put your own...

this is a link *YOU* must bookmark
http://www.able-consulting.com/ADO_Conn.htm

try that...
Also, UNCOMMENT the lines under 'FOR DEBUG ONLY sections in order to see what goes on *BEFORE* you actually execute your SQL Query!

Hope this helps!
Sincerely

Vlince
Reply With Quote
  #8  
Old September 5th, 2003, 03:43 PM
Pain Pain is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: Sheffield, UK
Posts: 94 Pain User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Send a message via ICQ to Pain
Thumbs up
Cheers mate.
Reply With Quote
  #9  
Old December 9th, 2003, 09:40 AM
Otuatail Otuatail is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 4 Otuatail User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 47 m 43 sec
Reputation Power: 0
This is what I do

Set connection = Server.CreateObject("ADODB.Connection")
Set rsDelete = Server.CreateObject("ADODB.Recordset")

dsn = "DSN=SampleDB2" *** I am using DSN for my database ****-*

connection.Open dsn
sql = strSQL = "DELETE FROM POPUP WHERE PID = '" & pid & "'"

rsDelete .Open sql, connection

Paul.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Using ASP to delete records on access 2000

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway. 
Stay green...Green IT