Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP - Search DB where variable = request.form syntax

Posted on 2014-01-23
29
562 Views
Last Modified: 2014-01-27
I need to select a quantity out of my database where the product name variable matches on the database field.

What is wrong with my syntax?

mySQL2 = "SELECT quantity FROM inventory WHERE 'productname = Request.Form("productname")'"

Open in new window


I get this error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/triplels/insertDBsale.asp, line 41

mySQL2 = "SELECT quantity FROM inventory WHERE 'productname = Request.Form("productname")'"
----------------------------------------------------------------------------^

Open in new window

0
Comment
Question by:bschwarting
  • 12
  • 7
  • 6
  • +2
29 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39803872
mySQL2 = "SELECT quantity FROM inventory WHERE productname '"&Request.Form("productname")&"'"

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39803888
However, you should really scrub your data instead of just using data right from request.form or querystring to prevent sql injection.

'this is very basic
theProduct=Request.Form("productname")
theProduct=replace(theProduct,"'","") ' remove single quotes (you could replace with html entity)
theProduct=replace(theProduct,"<","&lt")
theProduct=replace(theProduct,">","&gt")
mySQL2 = "SELECT quantity FROM inventory WHERE productname '"&theProduct&"'"

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 39803889
I'm getting this error now with that change Scott:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'productname 'Test Product''.

/triplels/insertDBsale.asp, line 44
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39803913
My bad, forgot =

mySQL2 = "SELECT quantity FROM inventory WHERE productname = '"&Request.Form("productname")&"'"

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 39803918
ha, just found that as well.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39803956
ok, i changed it to this so I can get the quantity out, but it's blank:

mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&Request.Form("productname")&"'"

Response.Write "Current Qty - " & "<b><font color=blue>" & currentqty & "</b></font>" & "<br>"

Open in new window

0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39803988
as mentioned above, you should clean your data to prevent any kind of sql injection. The best way to do this is to use parameterized queries - this will ensure that your code is always safe from sql injection.

In your case, it would be a matter of downloading adovbs.inc, which is just a file of constants definition, and include it on your page. afterwards, you code becomes:

<!--#include virtual = "/includes/adovbs.asp" -->
<%
    set rs = Server.CreateObject("ADODB.RecordSet")
    set conn = Server.CreateObject("ADODB.Connection")

    mySQL2 = "SELECT quantity FROM inventory WHERE productname = ?"

        set cmd = Server.CreateObject("ADODB.Command")
        conn.Open connectionString 

        with cmd
            .ActiveConnection = conn
            .CommandText = sql
            .Parameters.Append  .CreateParameter( "productName", adVarChar, adParamInput, "50", Request("productname") )
        end with
        rs.Open cmd

        quantity = 0
        if not rs.BOF and not rs.EOF then quantity = rs("quantity")

        rs.Close
        conn.Close
        set rs = nothing
        set conn = nothing
%>

Open in new window


now you don't have to worry about quotes or anything else when you're building the sql.

** edit: added the include file. you'll need to change the path to wherever you store it to
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39804018
Make sure there is good data.  Try and run it with this.  You should see a what is entered.  If that looks good, comment out the testing code and try again.  I added the trim function to make sure there are no spaces.

First try the testing 1 script where you type in good data.  (comment out the testing 2).   Then do the opposite and comment out testing1 and try testing 2.

productname=trim(Request.Form("productname"))
' TESTING 1
productname="Type_in_a_good_product_name_to_test"

'TESTING 2
response.write "Product= "productname
response.end
' end testing
mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&productname&"'"

Open in new window

0
 

Expert Comment

by:anwarul
ID: 39804077
mySQL2 = "SELECT quantity FROM inventory WHERE productname = '" & Request.Form("productname")' & "'"

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804364
Scott,

I tried testing 1 and typed in a product name and it still came back blank, nut sure what is up.  Testing 2 had a syntax error i believe.

Anwarul,

I tried yours and it didn't work either.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39804377
and did you try my suggestion? out of all of them, it is the most complete answer you could ask for
0
 
LVL 58

Expert Comment

by:Gary
ID: 39804383
Are you actually executing the query between these two lines?

mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&Request.Form("productname")&"'"
' here should be your query execution
Response.Write "Current Qty - " & "<b><font color=blue>" & currentqty & "</b></font>" & "<br>"


Where are you setting currentqty is equal to the recordset value?
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804412
Cathal,

#1) Yes, I have mine in that order (this time), haha.

#2) "Where are you setting currentqty is equal to the recordset value?"
mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&productname&"'"

Will this not do it?
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804443
Big Monty,

I just tried yours and get an error on this line:
    mySQL2 = "SELECT quantity FROM inventory WHERE productname = ?"

I changed the ? to a product name in the DB, with the same error below:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/triplels/insertDBsale.asp, line 48
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39804460
my guess is you're not executing the command object properly. can you post the entire section of code where you are saving to the database?
0
 
LVL 58

Expert Comment

by:Gary
ID: 39804471
All this is doing
mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&productname&"'"

is creating a string - it is not accessing your database.
This should be followed by something like this, where your conn command has been set up with your DB parameters

Set rs=conn.Execute(mySQL2)


Then in your response.write
Response.Write "Current Qty - " & "<b><font color=blue>" & rs("currentqty") & "</b></font>" & "<br>"
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804472
Just as FYI, so you can see what I'm trying to accomplish.  I'm creating a basic inventory system.  This page is where you enter the "sale".  It checks the quantity in inventory, then will subtract 1 out as this sale is entered.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804533
Big Money, is this what you wanted?

I want to first insert the sale into the sales table, then check the inventory for the current quantity, then subtract 1 from it.

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\inetpub\wwwroot\triplels\database\triplels.mdb"
Set rs = Server.CreateObject ("ADODB.Recordset")

mySQL = "INSERT INTO sales ( Quantity, WhereSold, PaymentMethod, DateSold, CashSale, SalesComments, productname, ordernumber ) VALUES ( '" & Quantity & "','" & WhereSold & "','" & PaymentMethod & "','" & DateSold & "','" & CashSale & "','" & SalesComments & "','" & productname & "','" & ordernumber & "')" 

mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&productname&"'"

Con.Execute mySQL
Con.Execute mySQL2

Con.Close 
Set Con = Nothing 

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 39804570
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\inetpub\wwwroot\triplels\database\triplels.mdb"
Set rs = Server.CreateObject ("ADODB.Recordset")

mySQL = "INSERT INTO sales ( Quantity, WhereSold, PaymentMethod, DateSold, CashSale, SalesComments, productname, ordernumber ) VALUES ( '" & Quantity & "','" & WhereSold & "','" & PaymentMethod & "','" & DateSold & "','" & CashSale & "','" & SalesComments & "','" & productname & "','" & ordernumber & "')"

mySQL2 = "SELECT quantity FROM inventory as currentqty WHERE productname = '"&productname&"'"

Con.Execute mySQL

rs.open mySQL2,con
currentqty =rs("currentqty")


Con.Close
Set Con = Nothing
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804589
Cathal,

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/triplels/insertDBsale.asp, line 45
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 39804615
...
...
mySQL2 = "SELECT quantity FROM inventory WHERE productname = '"&Request.Form("productname")&"'"

rs.open mySQL2,con
if not rs.bof and not rs.eof then
     currentqty =rs("quantity")
end if

Request.Form("productname")
...

Open in new window


You seem to have gone from Request.Form("productname") to productname and nowhere I can see you are assigning the value.
You may be better to attach your page so we can see the big picture instead of small snippets that keep changing.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39804634
Double check  you have the field names correct.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804644
Cathal,

I have this above:
productname                                    = Request( "productname" )
productname                                    = Replace(  productname,"'","''" )
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39804660
Not directly related to your specific problem, but you should probably be entering in an item_id number into your sales table and not the item name.   You would have a related table of products with item_id (auto number), item_name, description and perhaps current price but it is good to keep a separate pricing table.   Typically, your order number is not something you enter but it is generated as an auto_number from your sales table.
0
 
LVL 58

Expert Comment

by:Gary
ID: 39804666
Try the amended code above.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804676
Boom, that worked!!!!  What was it?
0
 
LVL 58

Expert Comment

by:Gary
ID: 39804682
Magic!
0
 
LVL 1

Author Comment

by:bschwarting
ID: 39804700
Haha!  Thanks!
0
 
LVL 58

Expert Comment

by:Gary
ID: 39804719
It's just some corrections on syntax.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question