SQL asp select syntax help

**Br@dley** · Nov 13 '05, 01:25 PM

Re: SQL asp select syntax help

iam247@gmail.co m wrote:[color=blue]
> 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("C ontactID")) AND GroupID = "&
> Replace(Request .QueryString("G roupID"))"
>
> 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("C ontactID")) AND GroupID = "&
> Replace(Request .QueryString("G roupID"))"
>
> Any help would be appreciated.
>
> Thanks Colin[/color]

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("C ontactID"))
Group = Replace(Request .QueryString("G roupID"))
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(R equest.Form("pi d"), "")

pass = regEx.Replace(R equest.Form("pa ss") , "")

cid = regEx.Replace(R equest.Form("co mpany") , "")

set reEx = nothing

--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

**iam247@gmail.com** · Nov 13 '05, 01:25 PM

Re: SQL asp select syntax help

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 LeaveGroupSelec t.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.QuerySt ring("GroupID")

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

'Create an ADO connection object
Set adoCon = Server.CreateOb ject("ADODB.Con nection")

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

'Create an ADO recordset object
Set rsGC1 = Server.CreateOb ject("ADODB.Rec ordset")

'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.Wri te (rsGC1("Contact ID"))%>
'<%Response.Wri te (rsGC1("GroupID "))%>

'<%Response.Wri te ContactID%>
'<%Response.Wri te GroupID%>

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

Response.Redire ct "LeaveGroupconf irm.asp"
%>

**Br@dley** · Nov 13 '05, 01:27 PM

Re: SQL asp select syntax help

iam247@gmail.co m wrote:[color=blue]
> 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"[/color]

Because you are trying to read field values from the record you just
deleted???
[color=blue]
> 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 LeaveGroupSelec t.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.QuerySt ring("GroupID")
>
> 'Read in the ContactID to be deleted
> ContactID = Request.QuerySt ring("ContactID ")
>
> 'Create an ADO connection object
> Set adoCon = Server.CreateOb ject("ADODB.Con nection")
>
> 'Set an active connection to the Connection object using a DSN-less
> connection
> adoCon.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath( "../databases/GC1.mdb")
>
> 'Create an ADO recordset object
> Set rsGC1 = Server.CreateOb ject("ADODB.Rec ordset")
>
> '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.Wri te (rsGC1("Contact ID"))%>
> '<%Response.Wri te (rsGC1("GroupID "))%>
>
> '<%Response.Wri te ContactID%>
> '<%Response.Wri te GroupID%>[/color]

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... ?
[color=blue]
> <%
> 'Reset server objects
> rsGC1.Close
> Set rsGC1 = Nothing
> Set adoCon = Nothing
>
> Response.Redire ct "LeaveGroupconf irm.asp"
> %>[/color]

--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

**iam247@gmail.com** · Nov 13 '05, 01:27 PM

Re: SQL asp select syntax help

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.
[color=blue][color=green]
> > '<%Response.Wri te (rsGC1("Contact ID"))%>
> > '<%Response.Wri te (rsGC1("GroupID "))%>
> >
> > '<%Response.Wri te ContactID%>
> > '<%Response.Wri te GroupID%>[/color][/color]

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 iam247@gmail.co m

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@dley** · Nov 13 '05, 01:27 PM

Re: SQL asp select syntax help

iam247@gmail.co m wrote:[color=blue]
> 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.[/color]

Sorry I shoulda mentioned that! :)
[color=blue][color=green][color=darkred]
>>> '<%Response.Wri te (rsGC1("Contact ID"))%>
>>> '<%Response.Wri te (rsGC1("GroupID "))%>
>>>
>>> '<%Response.Wri te ContactID%>
>>> '<%Response.Wri te GroupID%>[/color][/color][/color]
[color=blue]
> 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.[/color]

Posting here is probably beneficial for others and gives the opportunity
for people with better answers to provide them.
[color=blue]
> I can't quite work out your email address from the NG.[/color]

That's cause it's fake LOL.
[color=blue]
> If I can
> contact you directly please email me at <address removed>[/color]

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. myemailNOSPAM@m ailserver.com or myemailATmailse rverDOTcom

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).
[color=blue]
> 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[/color]

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@gmail.com** · Nov 13 '05, 01:27 PM

Re: SQL asp select syntax help

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:[color=blue]
> iam247@gmail.co m wrote:[color=green]
> > 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.[/color]
>
> Sorry I shoulda mentioned that! :)
>[color=green][color=darkred]
> >>> '<%Response.Wri te (rsGC1("Contact ID"))%>
> >>> '<%Response.Wri te (rsGC1("GroupID "))%>
> >>>
> >>> '<%Response.Wri te ContactID%>
> >>> '<%Response.Wri te GroupID%>[/color][/color]
>[color=green]
> > 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.[/color]
>
> Posting here is probably beneficial for others and gives the opportunity
> for people with better answers to provide them.
>[color=green]
> > I can't quite work out your email address from the NG.[/color]
>
> That's cause it's fake LOL.
>[color=green]
> > If I can
> > contact you directly please email me at <address removed>[/color]
>
> 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. myemailNOSPAM@m ailserver.com or myemailATmailse rverDOTcom
>
> 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).
>[color=green]
> > 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[/color]
>
> 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[/color]

SQL asp select syntax help

SQL asp select syntax help

Comment

Comment

Comment

Comment

Comment

Comment