PDA

Click to See Complete Forum and Search --> : Delete multiple Records with Checkbox

cheishing
02-12-2005, 09:18 PM
I'm trying to delete multiple records from a database by selecting from the checkbox. but there seems to be some error.

delUser.asp
--------------------------------------------------------------------
<%@LANGUAGE="VBSCRIPT"%><!--include virtual=/cmd/checklogin.asp-->
<!--include virtual=/cmd/connectdb.asp-->
<!--include virtual=/checklogin.asp-->
<!--#include virtual=/connectdb.asp-->



<%
Dim m_title, m_description

m_title = "Delete User"
m_description = "Use this page to delete User(s). TO Delete User(s), Check the Checkered Box and click the delete button to delete.."
%>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Mailing List Database - <%=m_title%></title>

<link href="../cmd.css" type="text/css" rel="stylesheet">
</head>
<body background="../images/detailsbg.gif" leftMargin="0" topMargin="0" marginwidth="0" marginheight="0">
<!--#include file=../header.htm-->

<form action="thankyou.asp" method="get" name="form1">

<table style="border-color:purple;border-style:solid; border-width:1;" border="0" align="center" cellpadding="5" cellspacing="1" width="100%">
<tr align="center">
<td width="6%"><strong>UserID</strong></td>
<td width="15%"><strong>UserName</strong></td>
<td width="17%"><strong>Designation</strong></td>
<td width="12%"><strong>Email address</strong></td>
<td width="13%"><strong>Role</strong></td>
<td width="14%"><strong>School </strong></td>
<td width="17%"><strong>SchoolCode</strong></td>

</tr>
<%
mySQL="SELECT m_userid, m_username, m_designation, m_school, m_schoolcode, m_emailaddress, m_role FROM CUsers"
set rsView = o_db.execute(mySQL)
%>
<% DO WHILE NOT rsView.EOF %>
<tr>
<td><div align="center">
<input name="cbDel" align="middle" type="checkbox" id="cbDel" value="<%=(rsView.Fields.Item("m_userid").Value)%>"></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_username").Value)%></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_designation").Value)%></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_emailaddress").Value)%></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_role").Value)%></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_school").Value)%></div></td>
<td><div align="center"><%=(rsView.Fields.Item("m_schoolcode").Value)%></div></td>


<%rsView.MoveNext()
LOOP
%>

</tr>
<tr>
<td colspan="8"> <input name="Delete" type="submit" value="Delete" ></td>
</tr>
</table>
</form>
<p>
</body>
</html>

===========================================================

thankyou.asp
-----------------------------------------------------------------

<%@LANGUAGE="VBSCRIPT"%><!--include virtual=/cmd/checklogin.asp-->
<!--include virtual=/cmd/connectdb.asp-->
<!--include virtual=/checklogin.asp-->
<!--#include virtual=/connectdb.asp-->
<%
Dim strSelection, arrSelection
strSelection = Request.QueryString("cbDel")
'Convert strSelection To a string
strSelection = CStr(strSelection)
arrSelection = Split(strSelection,",") '| This will retuen an array of sub-strings.

Dim i
Dim strQuery, objConn
strQuery = "DELETE FROM CUsers WHERE "

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & "m_userid = " & arrSelection(i)
Response.write(arrSelection(i))
Else
strQuery = strQuery & "m_userid = " & arrSelection(i)
End If
Next

set objConn = o_db.execute(strQuery)
%>

<%
Dim m_title, m_description
m_title = "Delete User"
m_description = "Use this page to delete User(s). TO Delete User(s), Check the Checkered Box and click the delete button to delete.."
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Mailing List Database - <%=m_title%></title>
<link href="../cmd.css" type="text/css" rel="stylesheet">

<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>
<body background="../images/detailsbg.gif" leftMargin="0" topMargin="0" marginwidth="0" marginheight="0">
<!--#include file=../header.htm--><p>

<body>
<p class="style1">THe User(s) has been Successfully Deleted.</p>
<p class="style1"><% response.Write(strQuery) %></p>
<p><a href="delUser.asp">back</a> </p>
</div>
</body>
</html>

===================================================

okie.. now the problem is i keep on getting this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'WHERE'.
/User/TMPoq3mqbtvvs.asp, line 39


can someone help me with it. thanks.
buntine
02-12-2005, 10:38 PM
Your missing some syntax in your SQL query. You will need to print it to the screen and have a look at it.

First, try replace the current loop with this in thankyou.asp:

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & " OR m_userid = " & arrSelection(i)
Response.write(arrSelection(i))
Else
strQuery = strQuery & "m_userid = " & arrSelection(i)
End If
Next

Regards.
cheishing
02-13-2005, 12:08 AM
oops.. is there somehting wrong with the loop? coz i checked 3 checkbox but the print statement only shows 2.

which is:
ADELEA ADELINEH

however when i place another response.write(strQuery) at the end of the whole loop it does print the corect number of users:

ADELEA ADELINEHDELETE FROM CUsers WHEREm_userid = ACACIAH OR m_userid = ADELEA OR m_userid = ADELINEH

so now wad should i do.
buntine
02-13-2005, 01:07 AM
There is no space between WHERE and the first operand.

Try this:

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & " OR m_userid = " & arrSelection(i)
Response.write(arrSelection(i))
Else
strQuery = strQuery & " m_userid = " & arrSelection(i)
End If
Next

Notice I have added a space in the last statement.

Regards.
cheishing
02-13-2005, 01:57 AM
hmm.. now there is this:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'ACACIAH'.
/User/test2.asp, line 37

izzit i forgot to put ' ' this sign?
buntine
02-13-2005, 03:48 AM
Oh. Yep, that would be a problem.

Here:

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & " OR m_userid = '" & arrSelection(i) & "'"
Response.write(arrSelection(i))
Else
strQuery = strQuery & " m_userid = '" & arrSelection(i) & "'"
End If
Next

We will get it eventually. ;)

Regards.
cheishing
02-13-2005, 07:50 PM
erm.. though there's no error msg but the records doesnt seems to be deleted!!!
buntine
02-13-2005, 09:19 PM
Use this:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.execute(strQuery)

And delete the current line. Which is: set objConn = o_db.execute(strQuery)

Regards.
cheishing
02-14-2005, 12:11 AM
hi there.. I dunno wad i did but i didnt include the codes from the previous post and now it works. but it only delete 1 record instead of all of them. here is my code:


=============================================================
<%@LANGUAGE="VBSCRIPT"%><!--include virtual=/cmd/checklogin.asp-->
<!--include virtual=/cmd/connectdb.asp-->
<!--include virtual=/checklogin.asp-->
<!--#include virtual=/connectdb.asp-->

<%
Dim m_title, m_description, m_ismenuitem

m_title = "Delete User"
m_description = "Use this page to delete User(s). TO Delete User(s), Check the Checkered Box and click the delete button to delete.."
m_ismenuitem = 1

dim o_rs

dim m_oid,m__title
dim m_username,m_userid,m_designation,m_emailaddress,m_role,m_properties,m_school,m_schoolcode

m_oid = Request("contactid")

if m_oid <> "" then
GetContactInfo m_oid
end if

%>

<%
Dim strSelection, arrSelection
strSelection = Request.QueryString("cbDel")
'Convert strSelection To a string
strSelection = CStr(strSelection)
arrSelection = Split(strSelection,",") '| This will retuen an array of sub-strings.

Dim i, objConn
Dim strQuery,strQuery2
strQuery ="DELETE FROM CUsers WHERE"

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & " OR m_userid = '" & arrSelection(i) & "'"
Response.write(arrSelection(i))
Else
strQuery = strQuery & " m_userid = '" & arrSelection(i) & "'"
End If
Next



Response.write(strQuery) & "<br>"
'set objConn = o_db.execute(strQuery)
'Dim objConn

set objConn = o_db.execute(strQuery)
%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Mailing List Database - <%=m_title%></title>
<link href="../cmd.css" type="text/css" rel="stylesheet">

<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>
<body background="../images/detailsbg.gif" leftMargin="0" topMargin="0" marginwidth="0" marginheight="0">
<!--#include file=../header.htm--><p>

<body>
<p class="style1">THe User(s) has been Successfully Deleted.</p>
<p class="style1"><% response.Write(strQuery) & "<br>" %></p>
<p><a href="delUser.asp">back</a> </p>
</div>
</body>
</html>
<%
o_db.Close
set o_db = nothing
%>
buntine
02-14-2005, 02:53 AM
Can you post the generated SQL query? I need to see what it looks like.
cheishing
02-14-2005, 04:00 AM
DELETE FROM CUsers WHERE m_userid ='YKTAN' OR m_userid =' YONGCF' OR m_userid =' YOWKP' OR m_userid =' YVONNET'


the above this the generated SQL. omg.. i'm so frustrated..:(
buntine
02-14-2005, 04:32 AM
Ok, you notice there is a space after the opening single quote on every userid except the first one?

One last edit and this "should" work. Change the line that Splits the string so it looks like this:

arrSelection = Split(strSelection, ", ") '| This will retuen an array of sub-strings.

I have added a space in there.

Regards.
cheishing
02-16-2005, 04:36 AM
hey.. thanks a million.. the code does works.