Request.QueryString problem 1

[ziggs]

I cannot get the Request.Query string to work on a particular database that I'm drawing from. I believe the problem is because the field in the database that I'm drawing from is two words. In this example, the two words for the field is Street Number. I tried a query string for the phone number since the name of the field is one word, phone. That worked, so I'm guessing my querystring doesn't like the two word format. Is there a fix I can set up.

BTW, I'm drawing off a MS Access doc for now. However, I will drawing off of the same databases in SQL Server later.

Here's my Select and querystring:

this does not work for

http://pdtest/pdpcad/ec-business3.asp?dig=1491:

SQL1 = "SELECT * From tbl_Business"
SQL1 = SQL1 & " Where Street Number = '" & (Request.QueryString ("dig&quot) & "'"
set rs = my_conn.Execute(SQL1)

Here's the error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Street Number = '''.

/TestServer/business.asp, line 39


This does work:

SQL1 = "SELECT * From tbl_Business"
SQL1 = SQL1 & " Where phone= '" & (Request.QueryString ("phone&quot) & "'"
set rs = my_conn.Execute(SQL1)

 

[earme]

Hi Ziggs,
Try
SQL1 = SQL1 & " Where (Street Number = '" & (Request.QueryString ("dig&quot) & "')"

Add () inside your Where statement. My only other thought is if Street Number is stored as an integer then you might want to add int(Request.QueryString ("dig&quot), because I think Form values always get passed as strings.

HTH,
Earme

 

[ziggs]

did not work. New error message is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(Street Name = '1491')'.

/TestServer/business.asp, line 39

It is an integer value. Good point. However, I still have the same problem when I use Street Name which is my next step. Street Name is a text field and same error message with the () suggestion.

Now, do I use integer as
SQL1 = SQL1 & " Where (Street Number = '" & int(Request.QueryString ("dig&quot) & "')"

 

[earme]

Hmmm, maybe try to get rid of the ' ' around the number.
So it becomes
SQL1 = SQL1 & " Where (Street Number = " & (Request.QueryString ("dig&quot) & &quot"

I'd still try the int too, and the way you presented it is correct.

HTH,
Earme

 

[ziggs]

To make this easier, I'm using the field Street Name instead of Street Number to avoid the integer possibility. Anyway, no luck with:

SQL = SQL1 & " Where (Street Name = "& (Request.QueryString ("street&quot) & &quot"

Same error message with
'(Street Name = main)'.

I hope I can figure this out, I can't change the database name on the production server. That would be the easiest fix. When I created it in Access, I did not have a problem.

 

[NiteCrawlr]

Try:
SQL1 = "SELECT * From tbl_Business"
SQL1 = SQL1 & " Where Street Number = " & cint(Request.QueryString ("dig&quot)

 

[link9]

Honestly, though, couldn't you just adjust the name in your database... It's not good practice to put spaces in there, anyway. It only causes headaches.

**notes all the headaches it's causing**

;-)

 

[ziggs]

link9, I'd love to change the database, but do not have control of the production DB. The system admin will not change it for me since he'd have to change a whole bunch of other non asp issues

NiteCrawlr

No luck with that either

 

[earme]

ok, maybe it's getting hung up on the space in "Street Name"?
Maybe try to put `` around that (` is the on just to the left of the 1 on your keyboard).

SQL = SQL1 & " Where (`Street Name` = "& (Request.QueryString ("street&quot) & &quot"

 

[ziggs]

No luck there

too few parameters. Expected 1

 

[RobV]

I think there are too many quotes in your code and you forgot to place []'s around the column name.

SQL = SQL1 & " Where (

`

Street Name

`[/code] = "& (Request.QueryString ("street&quot) & &quot"
[/code]

When you remove the quotes around 'street name' AND remove the space in your column name (or use [street name] to include the space in the column name) then you SQL should work.

The correct one would be

SQL1 = SQL1 & " Where ([Street Name] = '" &
Replace(Request.QueryString ("street"), "'", "''") & "')"

Yours,

Rob.

 

[ziggs]

Thanks RobV and all, the replace feature did the trick. Last problem and then I'll be done with this thread. I still have to draw off a field called "street number" which is a number field in MS access. Thus, the following does not work:

SQL1 = "SELECT * From tbl_Business "
SQL1 = SQL1 & " Where ([Street Number] = '" & Replace(Request.QueryString ("dig&quot, "'", "''&quot & "')"

I tried the cint feature as someone suggested earlier but I'm probably inserting it in the wrong place. I've tried several different ways without success. Thanks

 

[RobV]

Mm. I didn't notice that Street number would be a numeric value. In that case you can leave out quite a lot. The notation for SQL statements is a bit different for matching strings or values:

strings:

SELECT * FROM Table WHERE ColumnName = '<string content>'

values:

SELECT * FROM Table WHERE ColumnName = <value>

This means that the surrounding quotes in your SQL-statement should be removed:

SQL1 = "SELECT * From tbl_Business "
SQL1 = SQL1 & " Where ([Street Number] = " & Clng(Request.QueryString("dig")) & ")"

The CLng() is not required, but that makes your code more robust (it will raise an error if the querystring was not a numeric value and thus will beat a simple hacker).

The Replace statement is only required for matching string values. Mind that you have to enclose a string constant in SQL with single quotes. To make sure that the SQL is correct with strings containing the quote-escape themselves, you have to escape the quotes in the value, so that:

SQL1 = "SELECT * FROM tbl_Business WHERE ([street name] = 'Abbey's Road')

(which is illegal) becomes

SQL1 = "SELECT * FROM tbl_Business WHERE ([street name] = 'Abbey''s Road')

Which is as it should be

Note that double-single-quotes are treated as a single quote inside the string. Yours,

Rob.

 

[BeginnerProgrammer]

Try this:
SQL1 = "SELECT * From tbl_Business"
SQL1 = SQL1 & " Where tbl_Business.[Street Number] = " & "'" & Request.QueryString("dig") & "'"

Hope this helps.

 

[BeginnerProgrammer]

If that doesn't work try:

Myqrystr=request.querystring("dig")
SQL1 = "SELECT * From tbl_Business"
SQL1 = SQL1 & " Where tbl_Business.[Street Number] = " & "'" & Myqrystr & "'"

 

[Genimuse]

Just a note that the post you're replying to was from March of 2001.