SGL Select Syntax help

Hi

I am a relative beginner with ASP and very weak on syntax, basically I
find something that works and modify it - but I have 1 SQL statement
which I cannot get to work.

I am using asp3.0 and VB Script

I have a querystring from another page with the url
http://localhost/gc6/www/LeaveGroup.asp?ContactID=82&groupID =1

I have written the following SQL statement (modified from one which
works OK elsewhere):

strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = "&
Replace(Request.QueryString("ContactID")) AND GroupID = "&
Replace(Request.QueryString("GroupID"))"

I get the error message:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/gc6/www/LeaveGroup.asp, line 34, column 149
strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = "&
Replace(Request.QueryString("ContactID")) AND GroupID = "&
Replace(Request.QueryString("GroupID"))"

Any further help would be appreciated.

Thanks Colin

Re: SGL Select Syntax help

Replace is for replacing - what are you wanting to replace ?

try this:

***********************
dim cID, gID

cID = Request.QueryString("ContactID")
gID = Request.QueryString("GroupID")

strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = " & _
cID & " AND GroupID = " & gID
************************

This assumes your ID's are numeric: if one or both are text then
you'll need to quote them

Eg:
strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = '" & _
cID & "' AND GroupID = '" & gID & "'"

Tim.


wrote in message
news:1122423618.816436.204570@z14g2000cwz.googlegroups.com.. .
> Hi
>
> I am a relative beginner with ASP and very weak on syntax, basically
> I
> find something that works and modify it - but I have 1 SQL statement
> which I cannot get to work.
>
> I am using asp3.0 and VB Script
>
> I have a querystring from another page with the url
> http://localhost/gc6/www/LeaveGroup.asp?ContactID=82&groupID =1
>
> I have written the following SQL statement (modified from one which
> works OK elsewhere):
>
> strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = "&
> Replace(Request.QueryString("ContactID")) AND GroupID = "&
> Replace(Request.QueryString("GroupID"))"
>
> I get the error message:
> Error Type:
> Microsoft VBScript compilation (0x800A0401)
> Expected end of statement
> /gc6/www/LeaveGroup.asp, line 34, column 149
> strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = "&
> Replace(Request.QueryString("ContactID")) AND GroupID = "&
> Replace(Request.QueryString("GroupID"))"
>
> Any further help would be appreciated.
>
> Thanks Colin
>

Re: SGL Select Syntax help

Hi Tim

That worked fine (I kept my DIM's rather than yours).

(I note I wrote SGL instead of SQL in title)

However, the record successfully deletes but I get the error message
"Row handle referred to a deleted row or a row marked for deletion"

I am using access 2002

Have you any further ideas?

The full code is shown below.

Thanks Colin (I am in quite a hurry, so I will also stsrt a new thread
for this).

<%
' LeaveGroup.asp is called by LeaveGroupSelect.asp and deletes the
selected record from the database-->

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGC1 'Holds the recordset for the record to be deleted
Dim strSQL 'Holds the SQL query to query the database
Dim GroupID
Dim ContactID

'Read in the GroupID to be deleted
GroupID = Request.QueryString("GroupID")

'Read in the ContactID to be deleted
ContactID = Request.QueryString("ContactID")

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

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("../databases/GC1.mdb")

'Create an ADO recordset object
Set rsGC1 = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the
database

strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = " & ContactID
& " AND GroupID = " & GroupID

'Set the lock type so that the record is locked by ADO when it is
deleted
rsGC1.LockType = 3

'Open the recordset with the SQL query
rsGC1.Open strSQL, adoCon

'Remove this group from the members list
rsGC1.Delete

'The response.write's below were used to verify the values below - when
setting up sql
' When activated they confirm that the correct values are being
modified / deleted
'and the correct record is actually deleted from the database - beu
then I get the error message
'Row handle referred to a deleted row or a row marked for deletion
%>
'<%Response.Write (rsGC1("ContactID"))%>

'<%Response.Write (rsGC1("GroupID"))%>

'<%Response.Write ContactID%>

'<%Response.Write GroupID%>

<%
'Reset server objects
rsGC1.Close
Set rsGC1 = Nothing
Set adoCon = Nothing

Response.Redirect "LeaveGroupconfirm.asp"
%>