error message help

-Dman100- · #1 (**permalink**) 09-06-04, 21:32

I'm receiving the following error message:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.
/mysite/delete.asp, line 121

It only happens when I try to go to the delete.asp page a second time. When I go to the page the first time it works, but when I go a second time I get the error?

I was trying to find some information online without much luck.

Any help is appreciated. Thanks in advance.
-Dman100-

rokslide · #2 (**permalink**) 09-06-04, 21:54

Okie, well you are build a sql string found the sounds of things and when you go to the page the second time you are concatenating to the exisiting string or something would be my guess...

If you post your code we can have a look and see what is going on and probably help more...

-Dman100- · #3 (**permalink**) 09-06-04, 22:23

Here is a portion of the code for the recordset that includes the SQL statement. I can include the additional code within the page that DMX generated if necessary.

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

Set rsDelete = Server.CreateObject("ADODB.Recordset")
rsDelete.ActiveConnection = MM_DBConn_STRING
rsDelete.Source = "SELECT * FROM dbo.tblLogin WHERE LoginID = " + Replace(rsDelete__MMColParam, "'", "''") + ""
rsDelete.CursorType = 0
rsDelete.CursorLocation = 2
rsDelete.LockType = 1
rsDelete.Open()

rsDelete_numRows = 0
%>

Thanks for your help!
-Dman100-

Seppuku · #4 (**permalink**) 09-06-04, 23:37

Quote:

Originally Posted by -Dman100-

<%
If (Request.QueryString("LoginID") <> "") Then
rsDelete__MMColParam = Request.QueryString("LoginID")
End If

Response.Write rsDelete__MMColParam
%>

Can you add the code in bold and try the process again? Then post back with the results of that output.

-Dman100- · #5 (**permalink**) 09-07-04, 00:02

I added the code you indicated and the output displays the loginID passed in the URL.

For example, if I choose to delete the user with a loginID of 5 then the output will display 5.

Does that information help?

Interestingly, the delete.asp page works only once then I receive the error as indicated in the first post. When I try to go to delete.asp page twice the error message occurs. If I close the browser, again I can go to the delete.asp age and delete a user, but then the error occurs again.

From the earlier post, it seems like the concatenating issue might be the problem.

I'm not sure?? Any ideas?

Thanks.
-Dman100-

Seppuku · #6 (**permalink**) 09-07-04, 10:55

Well, that's what I was getting at.. when you go to the delete.asp page a second time, is the value displayed "5,5"?

-Dman100- · #7 (**permalink**) 09-07-04, 11:31

No, when I go to the delete page a second time I still get the error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.
/mysite/delete.asp, line 122

It moves the error down a line, since I added a line of code, but other than that everything else is the same.

Does that mean it is something else?

-Dman100-

Seppuku · #8 (**permalink**) 09-07-04, 11:35

Add the code in bold and post back the results:

Code:

Set rsDelete = Server.CreateObject("ADODB.Recordset")
rsDelete.ActiveConnection = MM_DBConn_STRING
rsDelete.Source = "SELECT * FROM dbo.tblLogin WHERE LoginID = " + Replace(rsDelete__MMColParam, "'", "''") + ""
Response.Write rsDelete.Source
rsDelete.CursorType = 0
rsDelete.CursorLocation = 2
rsDelete.LockType = 1
rsDelete.Open()

-Dman100- · #9 (**permalink**) 09-07-04, 11:56

Here is the information displayed after selecting to delete the first entry in the database:

1SELECT * FROM dbo.tblLogin WHERE LoginID = 1

If I choose another loginID to be passed in, the statement will adjust accordingly....i.e. LoginID=17 then:

17SELECT * FROM dbo.tblLogin WHERE LoginID =17

I appreciate your help. Thank you.
-Dman100-

Seppuku · #10 (**permalink**) 09-07-04, 19:16

and you still get that same error? very odd... I have a feeling we're missing something.. can you post the entire page of code?

rokslide · #11 (**permalink**) 09-07-04, 19:18

Question. Are you doing a set rsDelete=nothing? if not add this to the code when you have finished using the rsDelete recordset. This should clear all values within this object and may solve your problem.

If it does not you might want to include the code for submitting this request as there might be something untoward in there...

Additionally,.. I assume line 122 is your rsDelete.Open. If it is I suggest this...

Code:

Set rsDelete = Server.CreateObject("ADODB.Recordset")
rsDelete.ActiveConnection = MM_DBConn_STRING
rsDelete.Source = "SELECT * FROM dbo.tblLogin WHERE LoginID = " + Replace(rsDelete__MMColParam, "'", "''") + ""
rsDelete.CursorType = 0
rsDelete.CursorLocation = 2
rsDelete.LockType = 1
on error resume next
rsDelete.Open()
if err.number <> 0 then
  Response.Write rsDelete.Source
  Response.end
end if
on error goto 0

-Dman100- · #12 (**permalink**) 09-07-04, 19:37

Hi rokslide,

Okay, I added the code you suggested to do an error check and this is what displayed when I attempted to delete a record a second time:

SELECT * FROM dbo.tblLogin WHERE LoginID = 30, 29

So, is the new login value concatenating to the existing string? Is that the problem?

The first time, I deleted the record with loginID of 30 then the second time I tried to delete the record with loginID 29. I thought that if I it was concatenating to the existing string it would display 3029?

If it is helpful, here is the code to both pages so you can see what I'm doing:

www.dwayneepps.com/download.htm

Thanks for your help!
-Dman100-

rokslide · #13 (**permalink**) 09-07-04, 19:45

In line 108 you are reseting your criteria to whatever is in the query string. This means that for you to have the value 30,29 in your criteria it must be being submitted but the admin.asp page.

You are using a variable called keepURL and I suspect that is where your problem lies.... I will look into it in more detail that you will probably find the fault before I will....