sql question

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?

Re: sql question

"Jen" wrote in message
news:ee$hKlZJHHA.4712@TK2MSFTNGP04.phx.gbl...
> 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'

--
Mike Brind

Re: sql question

> 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'
>
> --
> Mike Brind

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'.

Re: sql question

Jen wrote:
>> 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'
>>
>> --
>> Mike Brind
>
> 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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: sql question

> 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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



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.

Re: sql question

Jen wrote:
>
> 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




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: sql question

> 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, "'", "''") + ""+"'"