473,840 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(especial ly 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("w eightTotal")
strCountry = Request.Form("c ountry")

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 tblZoneShipping Rates WHERE ZoneName = " &
strZone & " AND WeightCategory = " & strWeightBand & ""

Set rsShipping = Server.CreateOb ject("ADODB.Rec ordset")
rsShipping.Acti veConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=" & Server.MapPath( "../ppdatabase.mdb" )
rsShipping.Sour ce = strSQL
rsShipping.Curs orType = 0
rsShipping.Curs orLocation = 2
rsShipping.Lock Type = 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 1742
Penny wrote:
Hi again,

Thanks to those who helped before but I'm afraid I waisted your time
and effort(especial ly 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.w rite 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 tblZoneShipping Rates WHERE ZoneName = '" &
strZone & "' AND WeightCategory = '" & strWeightBand & "'"
"Penny" <pe***@spampoli ce.com> wrote in message
news:42******@f unnel.arach.net .au...
Hi again,

Thanks to those who helped before but I'm afraid I waisted your time and
effort(especial ly 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("w eightTotal")
strCountry = Request.Form("c ountry")

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 tblZoneShipping Rates WHERE ZoneName = " &
strZone & " AND WeightCategory = " & strWeightBand & ""

Set rsShipping = Server.CreateOb ject("ADODB.Rec ordset")
rsShipping.Acti veConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=" & Server.MapPath( "../ppdatabase.mdb" )
rsShipping.Sour ce = strSQL
rsShipping.Curs orType = 0
rsShipping.Curs orLocation = 2
rsShipping.Lock Type = 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
6931
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 a type mismatch. It is positively because of the boolean(java primitive)parameter. It goes fine if I change this parameter to int or String. This inteface has a lot more methods which works fine, it is just the
18
8014
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 = document.forms; // more code here........ }
7
3830
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", myXmlNSMgr).InnerText;
15
2982
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 it. Simple database: I want to have a user enter Supply Orders (just for tracking purposes) by Item. The user may also enter a new item - "new" is a combination of Item, PartNumber and Vendor - they could have the
21
1768
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. However i don't know how to get the contents of the array out? I am used to passing parameters to a function, and getting a single result back. Is there a way to get multiple results from a function? Is it something to do with passing byVal or byRef?
8
3987
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 can't specify the starting folder. Stephan Leban provided a neat solution that does allow me to browse using a starting folder. I am using A97 and it works fine. However, I put it onto a system that uses AccessXP. And it chokes on...
17
14376
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, int longueur) { char *resultat = " "; char *temporaire = " "; int nbr;
5
1433
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 from a query string action=edit, now when i click on the update button it does the update event, the problem is, it still has the old unedited data in memory... and places the old stuff back into the database (saw this when debugging) the SQL is...
3
9576
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 using namespace directive can only be applied to namespaces; 'System.Web.HttpCookie' is a class not a namespace The code is:
0
1710
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 Server.CreateObject(). I'm trying to use the Microsoft Transaction Server this way: Type typeMtx = Type.GetTypeFromProgID("MTxAS.AppServer.1"); object mtxobject = Activator.CreateInstance(typeMtx); // Getting classic ASP object context
0
10916
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10598
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10299
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7022
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5872
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4495
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4071
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.