Saving a SQL Query to a database table.

I've been troubleshooting a problem I've had for a couple of hours now and
am getting nowhere and hope someone here can help.
I have a car website under development that allows registered users to enter
search criteria when looking for cars. I want to allow users to save their
search and thought the easiest way would be to save the sql query string
directly to the database.
I store the sql string in an ASP session variable and then try to add this
session variable to the database but have not succeeded. A typical query
string looks like this:
select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
car_postcode LIKE '%br7%')
The field in the database is called search_sql and allow's NULL values.
The error message I am getting is:
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
After some testing, I have found the field does not like any spaces yet I
have other fields working fine with spaces so don't understand this...
Any help is greatly appreciated!!
Thanks,
Robin.1) Make sure that you use: replace(sqlString, "'", "''")
2) You should be able to add any text to the field as long as your length is
<= to max.

> I store the sql string in an ASP session variable and then try to add this
> session variable to the database but have not succeeded. A typical query
> string looks like this:
> select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
> sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
> car_postcode LIKE '%br7%')
> The field in the database is called search_sql and allow's NULL values.
> The error message I am getting is:
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> After some testing, I have found the field does not like any spaces yet I
> have other fields working fine with spaces so don't understand this...|||..inetserver.asp.db would have been a better group to post this question.
Robin Lawrie wrote:
> I've been troubleshooting a problem I've had for a couple of hours
> now and am getting nowhere and hope someone here can help.
> I have a car website under development that allows registered users
> to enter search criteria when looking for cars. I want to allow users
> to save their search and thought the easiest way would be to save the
> sql query string directly to the database.
> I store the sql string in an ASP session variable and then try to add
> this session variable to the database but have not succeeded. A
> typical query string looks like this:
> select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
> sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
> car_postcode LIKE '%br7%')
> The field in the database is called search_sql and allow's NULL
> values.
> The error message I am getting is:
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
> After some testing, I have found the field does not like any spaces
> yet I have other fields working fine with spaces so don't understand
> this...
>
I doubt that the spaces are the problem. It sounds as if you are using
dynamic sql to pass this string to the database so it is more likely that
the embedded apostrophes are the problem.
A better technique woulc be to use parameters to pass this string, either
via a stored procedure (recommended):
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
or using an explicit Command object to pass parameters to a string
containing parameter markers:
http://groups-beta.google.com/group...r />
2fee7804e
Dynamic sql should be avoided due to the problems with handling embedded
delimiters. The larger problem however is the danger of sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adv...l_injection.pdf
HTH,
Bob Barrows
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.|||Hi Bob,
Thanks for your excellent and prompt reply.
I'm new to SQL and haven't used Stored Procedures yet (I'm a bit scared of
them actually!) but followed your second suggestion of using an explicit
Command object and am really please to say that this worked!!
Thank you very much and in future I will post to the inetserver.asp.db
Thanks again.
Robin.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:Og95YJh6FHA.1036@.tk2msftngp13.phx.gbl...
> .inetserver.asp.db would have been a better group to post this question.
> Robin Lawrie wrote:
> I doubt that the spaces are the problem. It sounds as if you are using
> dynamic sql to pass this string to the database so it is more likely that
> the embedded apostrophes are the problem.
> A better technique woulc be to use parameters to pass this string, either
> via a stored procedure (recommended):
>
http://groups.google.com/groups?hl=...phx.gbl

> or using an explicit Command object to pass parameters to a string
> containing parameter markers:
>
http://groups-beta.google.com/group...2e36562fee7804e

>
> Dynamic sql should be avoided due to the problems with handling embedded
> delimiters. The larger problem however is the danger of sql injection:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/adv...l_injection.pdf
> HTH,
> Bob Barrows
> --
> 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.
>|||Robin Lawrie wrote:
> Hi Bob,
> Thanks for your excellent and prompt reply.
> I'm new to SQL and haven't used Stored Procedures yet (I'm a bit
> scared of them actually!)
Don't be. Here's a good starting point:
http://www.aspfaq.com/show.asp?id=2201
Bob Barrows
--
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.