Delete record using variable name for table

Hi All,

I'm almost finished with this app.
I give users the ability to save query results in there own table in a MySQL DB.

They can go to "myfolder.asp" and see a Repeat Region of what's in the table. That works fine.
I have a button next to each record that will send them to "deletefromfolder.asp". Like this: "deletefromfolder.asp?FolderID=33"

This is where they will confirm the delete.
It shows the correct record that was chosen from the page before.
It's inside a form called "DeleteRecord".
I put a Delete Record Server Behavior (Dreamweaver) on the Submit button.

When I click submit I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error in your SQL syntax near 'where FolderID = 33' at line 1
/streetguide03/members/deletefromfolder.asp, line 82

Line 82 in in RED


I think it has something to do with the "UserName" variable, because when I substitute " & UserName" on line 53 for the actual name of the table(merlin). I works and deletes the record in the table merlin and redirects back to "myfolder.asp".

Here is the code (I cut out a bunch of HTML):


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/streetguide.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>

<% Request.QueryString("UserName")
Response.Write UserName


%>


<%
' *** Delete Record: declare variables

if (CStr(Request("MM_delete")) = "DeleteRecord" And CStr(Request("MM_recordId")) <> "") Then

MM_editConnection = MM_streetguide_STRING
MM_editTable = "streetguide. " & UserName
MM_editColumn = "FolderID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "myfolder.asp"

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Delete Record: construct an sql delete statement and execute it

If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then

' create the sql delete statement
MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId

If (Not MM_abortEdit) Then
' execute the delete
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
'/////////////////////////////////////////////////////////////////////////////////
' *** rsMember Recordset ***

Dim rsMember__strUserName
rsMember__strUserName = "xyz"
If (Session("MM_UserName") <> "") Then
rsMember__strUserName = Session("MM_UserName")
End If
%>
<%
Dim rsMember
Dim rsMember_numRows

Set rsMember = Server.CreateObject("ADODB.Recordset")
rsMember.ActiveConnection = MM_streetguide_STRING
rsMember.Source = "SELECT * FROM streetguide.members WHERE UserName = '" + Replace(rsMember__strUserName, "'", "''") + "'"
rsMember.CursorType = 0
rsMember.CursorLocation = 2
rsMember.LockType = 1
rsMember.Open()
rsMember_numRows = 0
UserName = rsMember.Fields.Item("UserName" ).Value

'////////////////////////////////////////////////////////////////////////////////
%>


<%
Dim rsDelete__MMColParam
rsDelete__MMColParam = "1"
If (Request.QueryString("FolderID") <> "") Then
rsDelete__MMColParam = Request.QueryString("FolderID")
End If
%>
<%
Dim rsDelete
Dim rsDelete_numRows

Set rsDelete = Server.CreateObject("ADODB.Recordset")
rsDelete.ActiveConnection = MM_streetguide_STRING
rsDelete.Source = "SELECT * FROM streetguide."& UserName & " WHERE FolderID = " + Replace(rsDelete__MMColParam, "'", "''") + ""
rsDelete.CursorType = 0
rsDelete.CursorLocation = 2
rsDelete.LockType = 1
rsDelete.Open()

rsDelete_numRows = 0
%>





<html>
<head>
<title>Directions Steet Guide - Members Home</title>
</head>

<body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onLoad="MM_preloadImages('../assets/images/buttons/signup_ovr.gif','../assets/images/buttons/home_ovr.gif','../assets/images/buttons/logout_ovr.gif')">
<div align="center">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="TopLeftBG">&nbsp;</td>
<td width="750"><div align="center"><img src="../assets/images/top_mid03.jpg" width="750" height="105"></div></td>
<td class="TopRightBG">&nbsp;</td>
</tr>
</table>
<p>&nbsp; </p>
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="DeleteRecord" id="DeleteRecord">
<p>&nbsp;<%=(rsDelete.Fields.Item("FolderID").Value)%> , <%=(rsDelete.Fields.Item("StreetName").Value)%> , <%=(rsDelete.Fields.Item("Block").Value)%> </p>
<p>&nbsp;</p>
<p>
<input type="submit" name="Submit" value="Delete">
</p>




<input type="hidden" name="MM_delete" value="DeleteRecord">
<input type="hidden" name="MM_recordId" value="<%= rsDelete.Fields.Item("FolderID").Value %>">
</form>
<p> <p><%=(rsMember.Fields.Item("UserName").Value)%></p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</td>
</tr>
</table>
<p align="left">&nbsp;</p>
</div>
</td>
<td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
<td width="750" valign="top" background="../assets/images/bottom.jpg"><div align="left"><img src="../assets/images/blank.gif" width="22" height="35" align="absmiddle"> <span class="BottomText">© 2003
Directions™</span><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><img src="../assets/images/blank.gif" width="450" height="35" align="middle"></font><span class="BottomText">Website
by Computer Intellect</span></div></td>
<td background="../assets/images/main_btm_outerBG.jpg">&nbsp;</td>
</tr>
</table>
</div>
</body>
</html>
<%
rsDelete.Close()
Set rsDelete = Nothing
%>
<%
rsMember.Close()
Set rsMember = Nothing
%>


thanks for any help

making a *Response.Write* of your SQL Query *BEFORE* you execute it, something like:

<%
...CODE...
MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId

'FOR DEBUG ONLY
Response.Write MM_editQuery & "<hr>"
Response.End

...CODE...

%>

NOTE: Once done, comment the two lines under the FOR DEBUG ONLY part.

the idea is that you copy/paste the output query result into queryAnalyzer(if using SQL Server) for example. Then execute it

Otherwise check the *MM_editTable* or *MM_editColumn * or MM_recordId variables

Hope this helps!
Sincerely

Vlince

Hi Vince.

When I submit I get this Response:

delete from streetguide. where FolderID = 35


I put that code right below this line:

MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId


It seems it doesn't know the table name.

Do you see it???

--->delete from streetguide. where FolderID = 35<---

Is your TABLE name called *streetguide.* notice the period at the end of your TABLE name!

somewhere in your code you either add a *period* or something happend, you must debug a little more until you find it!

Hope this helps!
Sincerely

Vlince

Streetguide is the DB name.

Ok let's start over...

When you make a *Response.Write* of your MM_editQuery variable what is it you get again?

From your previous post, you said you were getting:

*delete from streetguide. where FolderID = 35*

Now LOOK at the name of your table!

Is it *streetguide* OR *streetguide.*

Notice the *period* at the end?

So that's why(probably) your SQL Query isn't working...

Try and *remove* the period at the end and it should work!

Hope this helps!
Sincerely

Vlince

I Got It !!

I added this up near the top:

<%
UserName = Session("MM_UserName")
Response.Write UserName
%>


I realized I was already using a session variable to show the welcome message:
Welcome back merlin.


Thanks for all the help.

The error checking tip will be useful:
'FOR DEBUG ONLY
Response.Write MM_editQuery & "<hr>"
Response.End