473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting SQL string to work in ASP

Hi again,

Thanks to those who helped before but I'm afraid I waisted your time and
effort(especially Bob's) by not detailing the way I
was trying to dynamically create the select statement. I thought if I showed
you a simpler 'hard coded' version it would be
easier to give pointers but all it did was remove the crucial elements. Here
is what I'm attempting to do.

.................................................. ..

Dim intWeightTotal
Dim strCountry
Dim strZone
Dim strWeightBand

intWeightTotal = Request.Form("weightTotal")
strCountry = Request.Form("country")

Select Case strCountry
Case "Australia"
strZone = "Australia"
Case "Austria"
strZone = "A"
Case "Botswanna"
strZone = "B"
Case Else
strZone = "Australia"
End Select

Select Case True
Case intWeightTotal < 251
strWeightBand = "250"
Case intWeightTotal < 501
strWeightBand = "500"
Case intWeightTotal < 751
strWeightBand = "750"
Case intWeightTotal < 1001
strWeightBand = "1000"
Case Else
strWeightBand = "2000"
End Select

Dim strSQL
Dim rsShipping

strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = " &
strZone & " AND WeightCategory = " & strWeightBand & ""

Set rsShipping = Server.CreateObject("ADODB.Recordset")
rsShipping.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("../ppdatabase.mdb")
rsShipping.Source = strSQL
rsShipping.CursorType = 0
rsShipping.CursorLocation = 2
rsShipping.LockType = 1
rsShipping.Open()

.................................................. ....

I get error messages from the browser indicating "Syntax error", "expected
end of statement" and "expected parameters" when I
add and remove quotes. I'm sure the problem has something to do with the
string variable generated in the select case and
referenced in the SQL but no end of 'playing' has made it work.

If I comment out all the recordset stuff and place something like this

<%= strZone & "<BR>" %>
<%= strWeightBand %>

in the <body> then the page runs and this

A
500

is displayed so I know both select cases work fine.

Any ideas on how to pass strZone and strWeightBand to the SQL?

B.T.W, I have no line breaks in my code. I'm just useless at formatting
Usenet messages. In the database, both ZoneName and WeightCategory are text
fields.

Regards

Penny.
Jul 22 '05 #1
5 1719
Penny wrote:
Hi again,

Thanks to those who helped before but I'm afraid I waisted your time
and effort(especially Bob's) by not detailing the way I
was trying to dynamically create the select statement. I thought if I
showed you a simpler 'hard coded' version it would be
easier to give pointers but all it did was remove the crucial
elements. Here is what I'm attempting to do.


I have the same advice I provided in my previous message (I knew this was
what you were trying to do - nobody hard-codes values into a query ... )

Anyways, nothing has changed. You are still creating a sql statement using
dynamic sql. Given that, your goal is still to create a sql statement that
will run without modification in your database environment (the Access Query
Builder). The only way to troubleshoot problems with that sql statement is
to write it to the Response object so you can see the statement you created:

Response.Write strSQL

THAT is what you (and we) need to see in order to figure out what is going
on. Hint: if you open your database in Access and create a query that works
correctly, you can switch to SQL View and SEE what the result of your
response.write is supposed to look like.
Go back and re-read my post, especially the links to previous posts I had
made on this topic. In addition to the advice about alternatives to using
dynamic sql, the first link provides detailed instructions in how to create
sql using dynamic sql.

By using dynamic sql, you are doing this the HARD way. And not only is it
the hard way, it is also the insecure way, in that it opens the door for
hackers to use sql injection to compromise your database and your site.
Granted, in this case, since you are never using the user's input directly
in your sql statement, this particular usage of dynamic sql will provide no
avenue for hackers to exploit. But, I'm pretty sure this is not the only
place you are using dynamic sql ...

Anyways, if you continue to attempt to use dynamic sql and are still having
problems you wish us to help with, you need to provide us with three pieces
of information:

1. The result of your "response.write strsql" statement
2. The exact text of the error message you received when attempting to use
the sql statement (if you made several attempts, you need to show us each
attempt - the statement, and the error it caused)
3. The datatypes of the fields in your query - we cannot create a sql
statement without knowing the datatypes of the fields involved, which you
can read about here:
http://groups-beta.google.com/group/...UTF-8&oe=UTF-8

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2
Perhaps you need single quotes one either side of the variables:

strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = '" &
strZone & "' AND WeightCategory = '" & strWeightBand & "'"
"Penny" <pe***@spampolice.com> wrote in message
news:42******@funnel.arach.net.au...
Hi again,

Thanks to those who helped before but I'm afraid I waisted your time and
effort(especially Bob's) by not detailing the way I
was trying to dynamically create the select statement. I thought if I showed you a simpler 'hard coded' version it would be
easier to give pointers but all it did was remove the crucial elements. Here is what I'm attempting to do.

.................................................. .

Dim intWeightTotal
Dim strCountry
Dim strZone
Dim strWeightBand

intWeightTotal = Request.Form("weightTotal")
strCountry = Request.Form("country")

Select Case strCountry
Case "Australia"
strZone = "Australia"
Case "Austria"
strZone = "A"
Case "Botswanna"
strZone = "B"
Case Else
strZone = "Australia"
End Select

Select Case True
Case intWeightTotal < 251
strWeightBand = "250"
Case intWeightTotal < 501
strWeightBand = "500"
Case intWeightTotal < 751
strWeightBand = "750"
Case intWeightTotal < 1001
strWeightBand = "1000"
Case Else
strWeightBand = "2000"
End Select

Dim strSQL
Dim rsShipping

strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = " &
strZone & " AND WeightCategory = " & strWeightBand & ""

Set rsShipping = Server.CreateObject("ADODB.Recordset")
rsShipping.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("../ppdatabase.mdb")
rsShipping.Source = strSQL
rsShipping.CursorType = 0
rsShipping.CursorLocation = 2
rsShipping.LockType = 1
rsShipping.Open()

.................................................. ...

I get error messages from the browser indicating "Syntax error", "expected
end of statement" and "expected parameters" when I
add and remove quotes. I'm sure the problem has something to do with the
string variable generated in the select case and
referenced in the SQL but no end of 'playing' has made it work.

If I comment out all the recordset stuff and place something like this

<%= strZone & "<BR>" %>
<%= strWeightBand %>

in the <body> then the page runs and this

A
500

is displayed so I know both select cases work fine.

Any ideas on how to pass strZone and strWeightBand to the SQL?

B.T.W, I have no line breaks in my code. I'm just useless at formatting
Usenet messages. In the database, both ZoneName and WeightCategory are text fields.

Regards

Penny.

Jul 22 '05 #3
Thanks Bob and Joe,

I did as you said Bob and got the page to show me the select statement being
generated. And as you said it did allow to play around with it and find a
way to work the single quotes in. Works nicely now.

What is SQL Injection?

Regards,

Penny
Jul 22 '05 #4
Penny wrote:
Thanks Bob and Joe,

I did as you said Bob and got the page to show me the select
statement being generated. And as you said it did allow to play
around with it and find a way to work the single quotes in. Works
nicely now.

What is SQL Injection?

It is a tactic that hackers can use to gain entry to and control of your
site:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.pdf

It depends on the use of dynamic sql. If parameters are used, sql injection
is not possible.
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.
Jul 22 '05 #5
Thanks for the info Bob,

"Ya gotta be vigilant!!" - George Costanza

Regards,

Penny.
Jul 22 '05 #6

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

Similar topics

2
by: Eyal | last post by:
Hey, I would appriciate if anyone can help on this one: I have a java object/inteface having a method with a boolean parameter. As I'm trying to call this method from a javascript it fails on...
18
by: lawrence | last post by:
If I'm pretty sure there is just one form on the page, can i do this? var myForm = document.forms; If I'm not sure about the form, is it safer to do this? if (document.forms) { var myForm =...
7
by: Sashi | last post by:
Two questions: (1) I can pull the text of an XML element as a string just fine using code as such: strSomeString = myXmlDoc.SelectSingleNode("/Element1/Element2/Element3",...
15
by: sara | last post by:
Hi I'm pretty new to Access here (using Access 2000), and appreciate the help and instruction. I gave myself 2.5 hours to research online and help and try to get this one, and I am not getting...
21
by: Gerry Abbott | last post by:
Hi All, If completed a script which parses a string into fragments (fields), then assigns these substrings into an array. I wish to turn this into a function to which i can pass the string....
8
by: Salad | last post by:
I designed a small app and I wanted to do a BrowseFolder (see http://www.mvps.org/access/api/api0002.htm), basically do a file open diaglog and select a directory/folder. The problem is that you...
17
by: Olivier Bellemare | last post by:
I've tried to make a function that returns the middle of a string. For example: strmid("this is a text",6,4); would return "is a". Here is my code: char *strmid(char *texte, int depart,...
5
by: Brian Henry | last post by:
I have a page which reads an article from the database it has 1 text box, 2 dropdown lists, and a longreat HTML text box. I load the information from the database when the page is set to edit mode...
3
by: tshad | last post by:
I have a file that I converted from VB.Net to C# that works fine in VB.Net when I compile but not in C# using the same libraries. The error I am getting is: PageInit.cs(9,7): error CS0138: A...
0
by: =?Utf-8?B?RmFicml6aW8gQ2lwcmlhbmk=?= | last post by:
I need to access classic ASP intrinsic objects and their properties from a ..net assembly wrapped to COM. The COM .net assembly is then instanciated from a classic ASP page with...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.