SQL asp select syntax help

[iam247]

Hi

I am a relative beginner with ASP and weak on syntax for sql
statements. Basically I modify something which works.

I have tblGroupContact with two fields both long integer - ContactID &
GroupID

I am using asp3.0 and VB Script

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

With my poor syntax knowledge 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 help would be appreciated.

Thanks Colin

[Br]

ia****@gmail.com wrote:

Hi

I am a relative beginner with ASP and weak on syntax for sql
statements. Basically I modify something which works.

I have tblGroupContact with two fields both long integer - ContactID &
GroupID

I am using asp3.0 and VB Script

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

With my poor syntax knowledge 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 help would be appreciated.

Thanks Colin

First, I'd seperate out the components of your code so that you will get
a more accurate look at where the code fails.

eg.

Dim Contact as Long
Dim Group as Long
Dim strSQL as String
Contact = Replace(Request.QueryString("ContactID"))
Group = Replace(Request.QueryString("GroupID"))
strSQL = "SELECT * FROM tblGroupContact WHERE ContactID = " & Contact
AND GroupID = " & Group

Your replace statements seem to be missing arguments...

I used this....

'clean up input

dim regEX

set regEx = New RegExp

regEx.Global = true

regEx.Pattern = "[^0-9a-zA-Z]"

pid = regEx.Replace(Request.Form("pid"), "")

pass = regEx.Replace(Request.Form("pass") , "")

cid = regEx.Replace(Request.Form("company") , "")

set reEx = nothing

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[iam247]

Hi Bradley

That worked fine (slightly modified form another NG reply). 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

Full code below.

If you have any more suggestions for me, I would grealtly appreciate
your help.

I am in quite a hurry, and presume you are in bed now (I'm in UK), so I
will also post a new message.

Thanks Colin

<%
' 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"))%><p>
'<%Response.Write (rsGC1("GroupID"))%><p>

'<%Response.Write ContactID%><P>
'<%Response.Write GroupID%><P>

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

Response.Redirect "LeaveGroupconfirm.asp"
%>

[Br]

ia****@gmail.com wrote:

Hi Bradley

That worked fine (slightly modified form another NG reply). However,
the record successfully deletes but I get the error message

"Row handle referred to a deleted row or a row marked for deletion"
Because you are trying to read field values from the record you just
deleted???
I am using access 2002

Full code below.

If you have any more suggestions for me, I would grealtly appreciate
your help.

I am in quite a hurry, and presume you are in bed now (I'm in UK), so
I will also post a new message.

Thanks Colin

<%
' 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"))%><p>
'<%Response.Write (rsGC1("GroupID"))%><p>

'<%Response.Write ContactID%><P>
'<%Response.Write GroupID%><P>
I'm not sure I understand what you're saying but you can't try to read
field values from the record that you just deleted... ?
<%
'Reset server objects
rsGC1.Close
Set rsGC1 = Nothing
Set adoCon = Nothing

Response.Redirect "LeaveGroupconfirm.asp"
%>

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[iam247]

Hi Bradley

Thanks for your response again. I have now got it working.

Sorry if my enquiry was not clear.

The response.write's below were there to help me verify what was
happening to the variables. When actually running the page to delete
the record I thought the ' would comment the line and hence I thought I
was not trying to read the record - however I now realise the <%
effectively superceded the ' - hence the error.

With these deleted it works fine.

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

'<%Response.Write ContactID%><P>
'<%Response.Write GroupID%><P>

This is a college project, I have a few more pages to finish / modify.
Would you mind if I ask you some more questions?

Can I contact you directly? or do you prefer that I use the newsgroup.

I can't quite work out your email address from the NG. If I can contact
you directly please email me at ia****@gmail.com

I will only hassle you a few times over the next week - 10 days - then
I will be finished.

In any event, thanks for your help thus far.

Thanks Colin

[Br]

ia****@gmail.com wrote:

Hi Bradley

Thanks for your response again. I have now got it working.

Sorry if my enquiry was not clear.

The response.write's below were there to help me verify what was
happening to the variables. When actually running the page to delete
the record I thought the ' would comment the line and hence I thought
I was not trying to read the record - however I now realise the <%
effectively superceded the ' - hence the error.

With these deleted it works fine.
Sorry I shoulda mentioned that! :)

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

'<%Response.Write ContactID%><P>
'<%Response.Write GroupID%><P>

This is a college project, I have a few more pages to finish / modify.
Would you mind if I ask you some more questions?

Can I contact you directly? or do you prefer that I use the newsgroup.
Posting here is probably beneficial for others and gives the opportunity
for people with better answers to provide them.
I can't quite work out your email address from the NG.
That's cause it's fake LOL.
If I can
contact you directly please email me at <address removed>
Tut tut :)

It is a _big_ mistake to publish your email on a public forum especially
on Usenet. Email addresses are harvested automatically by spammers and
you'll find your inbox will fill up with junk. if you must publish your
address then disguise it by puttin gin some fake characters.

eg. my***********@mailserver.com or myemailATmailserverDOTcom

And even then it's wise to have a seperate web based "throw away" mail
account fo using in public forums (eg. if it starts filling up with junk
ditch it for a new one and publish it, disguised of course, in your
future posts).

I get zero spam in my two main, real accounts (work/private).
I will only hassle you a few times over the next week - 10 days - then
I will be finished.

In any event, thanks for your help thus far.

Thanks Colin

I'm no expert on ASP pages but I've managed to code an online app (like
a basic bank site but only reporting statements, managing logins, etc at
the moment).
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[iam247]

Hi Bradley

Thanks for your reply.

my iam247 email address is only used for newsgroups and I find gmail is
quite good at dealing with spam,if you had been willing to communicate
directly I would have done so via a different email ac., however I will
take your advice and disguise it in google.

Thanks again Colin

Br@dley wrote:

ia****@gmail.com wrote:

Hi Bradley

Thanks for your response again. I have now got it working.

Sorry if my enquiry was not clear.

The response.write's below were there to help me verify what was
happening to the variables. When actually running the page to delete
the record I thought the ' would comment the line and hence I thought
I was not trying to read the record - however I now realise the <%
effectively superceded the ' - hence the error.

With these deleted it works fine.

Sorry I shoulda mentioned that! :)

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

'<%Response.Write ContactID%><P>
'<%Response.Write GroupID%><P>

This is a college project, I have a few more pages to finish / modify.
Would you mind if I ask you some more questions?

Can I contact you directly? or do you prefer that I use the newsgroup.

Posting here is probably beneficial for others and gives the opportunity
for people with better answers to provide them.

I can't quite work out your email address from the NG.

That's cause it's fake LOL.

If I can
contact you directly please email me at <address removed>

Tut tut :)

It is a _big_ mistake to publish your email on a public forum especially
on Usenet. Email addresses are harvested automatically by spammers and
you'll find your inbox will fill up with junk. if you must publish your
address then disguise it by puttin gin some fake characters.

eg. my***********@mailserver.com or myemailATmailserverDOTcom

And even then it's wise to have a seperate web based "throw away" mail
account fo using in public forums (eg. if it starts filling up with junk
ditch it for a new one and publish it, disguised of course, in your
future posts).

I get zero spam in my two main, real accounts (work/private).

I will only hassle you a few times over the next week - 10 days - then
I will be finished.

In any event, thanks for your help thus far.

Thanks Colin

I'm no expert on ASP pages but I've managed to code an online app (like
a basic bank site but only reporting statements, managing logins, etc at
the moment).
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response