473,898 Members | 2,914 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL asp select syntax help

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

Nov 13 '05 #1
6 2276
Br
ia****@gmail.co m 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("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


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
Nov 13 '05 #2
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"))%><p>
'<%Response.Wri te (rsGC1("GroupID "))%><p>

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

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

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

Nov 13 '05 #3
Br
ia****@gmail.co m 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 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"))%><p>
'<%Response.Wri te (rsGC1("GroupID "))%><p>

'<%Response.Wri te ContactID%><P>
'<%Response.Wri te 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.Redire ct "LeaveGroupconf irm.asp"
%>


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #4
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.Wri te (rsGC1("Contact ID"))%><p>
'<%Response.Wri te (rsGC1("GroupID "))%><p>

'<%Response.Wri te ContactID%><P>
'<%Response.Wri te 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.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

Nov 13 '05 #5
Br
ia****@gmail.co m 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.Wri te (rsGC1("Contact ID"))%><p>
'<%Response.Wri te (rsGC1("GroupID "))%><p>

'<%Response.Wri te ContactID%><P>
'<%Response.Wri te 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***********@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).
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
Nov 13 '05 #6
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.co m 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.Wri te (rsGC1("Contact ID"))%><p>
'<%Response.Wri te (rsGC1("GroupID "))%><p>

'<%Response.Wri te ContactID%><P>
'<%Response.Wri te 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***********@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).
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


Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
3085
by: sam | last post by:
When I run this SQL query: SELECT u.*, o.* FROM users u, orders o WHERE TO_DAYS(o.order_date) BETWEEN TO_DAYS('2003-09-20')-10 AND TO_DAYS('2003-09-20')+10
23
5704
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've reduced my form request to a simple text string entry, instead of my desired optional parameters. As i have been stuck with a single unfathomable glitch for over a year. Basically, if i enter queries such as ; "select * from table" "select * from...
9
3635
by: Kevin | last post by:
Hi, I am getting a syntax error Microsoft VBScript compilation error '800a03ea' Syntax error On the code below. The error references the "End Select" line Can anyone help me with what I am doing wrong? Thanks
1
595
by: Craig Stadler | last post by:
Can someone help with query syntax regarding IN/EXISTS.. I'm trying to do this: insert into table2 (field1) select field1 from table1 where field1 not in (select field1 from table2) delete from table1 where field1 in (select field1 from table2) 1. Insert field1 from table1 into table2 if it doesn't already exist there...
3
6477
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I COULD be wrong... :) I've tried the access group...twice...and all I get is "Access doesn't like ".", which I know, or that my query names are too long, as there's a limit to the length of the SQL statement(s). But this works when I don't try to...
2
10514
by: Michael Turner | last post by:
Hi Can anyone help me with my syntax please. This is not working. Set rs = conn.Execute("SELECT * FROM tblCompany WHERE company like '*" & txt_search & "*';") Thanks in advance Mick
1
5848
by: bpforte | last post by:
Hello, I need help with building query, basically I need to select all records from one table that don't exists in second table with status 1, but they can exists in second table with status 0, to be more complicated there can be in the same record in second table with status 0 and 1 (second table is something like log) in oracle I can do: select record from table_1 where record is not in (select record from table_2 where status=1)
17
3150
by: trose178 | last post by:
Good day all, I am working on a multi-select list box for a standard question checklist database and I am running into a syntax error in the code that I cannot seem to correct. I will also note that I am using Allen Browne's multi-select list box for a report as a guide. I should also note that my access skills are not the best so I may need some explaining on certain things. First let me give some background on the database: I have a...
2
3423
by: kxyz | last post by:
Hello everyone, I need help with a stored procedure or two. My stored procedures are supposed to check if a certain record exists. If it does exist, then I select everything from that row, as well as a value for the exit status of the SP. If the record doesn't exist, it selects an error message as the exit status. I've recreated the SP (while changing field names) and restructured the table, just to get straight to the point.
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11256
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10857
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10946
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9658
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8034
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6076
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4295
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.