sql question

[Jen]

Hello. I have a sql statement that should get all the records that match a
specific criteria; every record are assigned a textvalue like 12_2006 (as
for example this particular month, december 2006). I also have a drop-down
menu on this page where to filter which month should be shown on the page
based on these (text)values by request.querystring.
The problem is that (I guess) the value passed by the request.querystring
are of wrong format to the textfield in the table. Anyway, the error I get
is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression
'12_2006 = 12_2006'.

Here's the sql code:

<%
Dim rs_fakturalista__manad_ar
rs_fakturalista__manad_ar = month(now) & "_" & year(now)
If (request.querystring("manad_ar") <> "") Then
rs_fakturalista__manad_ar = request.querystring("manad_ar")
End If
%>
<%
Dim rs_fakturalista
Dim rs_fakturalista_numRows

Set rs_fakturalista = Server.CreateObject("ADODB.Recordset")
rs_fakturalista.ActiveConnection = MM_conn_test_STRING
rs_fakturalista.Source = "SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + " = " +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
rs_fakturalista.CursorType = 0
rs_fakturalista.CursorLocation = 2
rs_fakturalista.LockType = 1
rs_fakturalista.Open()

rs_fakturalista_numRows = 0
%>

I tried to dim rs_fakturalista__manad_ar as string but then I get
Expected end of statement

Anybody could help me with this?

 

[Mike Brind]

Hello. I have a sql statement that should get all the records that match a
specific criteria; every record are assigned a textvalue like 12_2006 (as
for example this particular month, december 2006). I also have a drop-down
menu on this page where to filter which month should be shown on the page
based on these (text)values by request.querystring.
The problem is that (I guess) the value passed by the request.querystring
are of wrong format to the textfield in the table. Anyway, the error I get
is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression
'12_2006 = 12_2006'.

Strings in SQL statements passed to Access should be delimited with
apostrophes. So the comparison for your WHERE clause should read like this:

'12_2006'='12_2006'

 

[Jen]

Strings in SQL statements passed to Access should be delimited with

apostrophes. So the comparison for your WHERE clause should read like
this:

'12_2006'='12_2006'

Changed the sql to:
rs_fakturalista.Source = "SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""

but now I get: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
query expression '12_2006'='12_2006'.

 

[Bob Barrows [MVP]]

Strings in SQL statements passed to Access should be delimited with
apostrophes. So the comparison for your WHERE clause should read
like this:

'12_2006'='12_2006'

Changed the sql to:
rs_fakturalista.Source = "SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""

but now I get: [Microsoft][ODBC Microsoft Access Driver] Syntax error
in query expression '12_2006'='12_2006'.

We cannot debug sql statements without seeing them. Good programming
practice involves assigning the statement to a variable: there is no
need to set the recordset's Source property directly. Assigning the
statement to a variable makes troubleshooting easier. Like this:

sql="SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run the page and look at the resulting sql statement in the browser
window. If you have built it correctly, you should be able to copy it
from the browser window, open your database in Access, paste it into the
SQL View of a Query Builder window and run it without modification
(unless wildcards are involved). Once you have a good sql statement,
comment out the response.write and response.end statements.

If you still cannot figure it out, show us the resulting sql statement,
as well as providing a few details about the faktura table (field names
and datatypes). This statement you are trying to run is very puzzling.
As written, it will return ALL the records in your table. You don't have
a field named "12_2006" do you? If so, this indicates poor database
design, given that you will need to modify the table design every year,
as well as modifying your application code. A better design involves
storing the data (month and year) in cells, not in metadata. IOW, you
should have a table to store month, year and data, each month comprising
its own row in the table.

Further points to consider:
Despite your good practice of using Replace to escape the apostrophes in
the data, your use of dynamic sql is leaving you vulnerable to hackers
using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Jen]

We cannot debug sql statements without seeing them. Good programming

practice involves assigning the statement to a variable: there is no
need to set the recordset's Source property directly. Assigning the
statement to a variable makes troubleshooting easier. Like this:

sql="SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run the page and look at the resulting sql statement in the browser
window. If you have built it correctly, you should be able to copy it
from the browser window, open your database in Access, paste it into the
SQL View of a Query Builder window and run it without modification
(unless wildcards are involved). Once you have a good sql statement,
comment out the response.write and response.end statements.

If you still cannot figure it out, show us the resulting sql statement,
as well as providing a few details about the faktura table (field names
and datatypes). This statement you are trying to run is very puzzling.
As written, it will return ALL the records in your table. You don't have
a field named "12_2006" do you? If so, this indicates poor database
design, given that you will need to modify the table design every year,
as well as modifying your application code. A better design involves
storing the data (month and year) in cells, not in metadata. IOW, you
should have a table to store month, year and data, each month comprising
its own row in the table.

Further points to consider:
Despite your good practice of using Replace to escape the apostrophes in
the data, your use of dynamic sql is leaving you vulnerable to hackers
using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

I honestly don't understand your answer, my english nor programming skills
are good enough. I'm sorry.

In Access, this sql statement filters the recordset so that only records
with the value 12_2006 in the (text)field manad_ar are shown:

SELECT faktura.Faktura_ID, etc, etc, etc, etc
FROM faktura
WHERE (((faktura.manad_ar)="12_2006"));

The last part "12_2006" should be transformed to a request.querystring
statement in an asp page. If the url would be
http://www.page?ar_manad=11_2006 then only records with the value 11_2006 in
the (text)field manad_ar would be shown. And this is my problem, don't know
how to do it.

Jen.

 

[Bob Barrows [MVP]]

I honestly don't understand your answer, my english nor programming
skills are good enough. I'm sorry.

In Access, this sql statement filters the recordset so that only
records with the value 12_2006 in the (text)field manad_ar are shown:

SELECT faktura.Faktura_ID, etc, etc, etc, etc
FROM faktura
WHERE (((faktura.manad_ar)="12_2006"));

So, let's go step-by-step. The above is the sql statement you need to
execute in the database, correct? So when you build the sql statement in
vbscript, and use response.write to write it to response, that statement
is what you should see in the browser. So let's go back to this:

sql="SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run this page and compare what you see in the browser window to the
statement that works. It is not the same, is it? it probably looks more
like this:
SELECT *
FROM faktura
WHERE 12_2006'='12_2006'

Correct? But that's not the statement you want to execute, is it? So you
need to change your vbscript statement so that it creates it correctly.
Start by doing this:
sql="SELECT * FROM faktura WHERE manad_ar='" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run the page.
Look at the resulting statement in the browser window.
Highlight and copy it to the clipboard.
Open your database in Access.
Clink into the Queries tab.
Click the button to create a new query in Design View
Close the Choose Tables dialog without selecting any tables
Switch to SQL View
Paste in the statement from the clipboard and attempt to run it
If it runs as expected, close Access, go back to the vbscript code and
comment out the Response.Write and Response.End statements.

If it does not run correctly, you will probably get a more informative
error message in Access than the one delivered by ADO. if you still
can't figure it out, post it back here.

When you are more comfortable with programming, go back and read the
links I provided about preventing sql injection. It's important.



Here are th

 

[Jen]

So, let's go step-by-step. The above is the sql statement you need to

execute in the database, correct? So when you build the sql statement in
vbscript, and use response.write to write it to response, that statement
is what you should see in the browser. So let's go back to this:

sql="SELECT * FROM faktura WHERE " +
Replace(rs_fakturalista__manad_ar, "'", "''") + "'" + "=" + "'" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run this page and compare what you see in the browser window to the
statement that works. It is not the same, is it? it probably looks more
like this:
SELECT *
FROM faktura
WHERE 12_2006'='12_2006'

Correct? But that's not the statement you want to execute, is it? So you
need to change your vbscript statement so that it creates it correctly.
Start by doing this:
sql="SELECT * FROM faktura WHERE manad_ar='" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""
Response.Write sql
Response.End
set rs_fakturalista = con.Execute(sql,,1)

Run the page.
Look at the resulting statement in the browser window.
Highlight and copy it to the clipboard.
Open your database in Access.
Clink into the Queries tab.
Click the button to create a new query in Design View
Close the Choose Tables dialog without selecting any tables
Switch to SQL View
Paste in the statement from the clipboard and attempt to run it
If it runs as expected, close Access, go back to the vbscript code and
comment out the Response.Write and Response.End statements.

If it does not run correctly, you will probably get a more informative
error message in Access than the one delivered by ADO. if you still
can't figure it out, post it back here.

When you are more comfortable with programming, go back and read the
links I provided about preventing sql injection. It's important.

Thank you so much Bob, I got it working thanks to your wery good
explanation.
The sql statement that worked is:
"SELECT * FROM faktura WHERE manad_ar='" +
Replace(rs_fakturalista__manad_ar, "'", "''") + ""+"'"