Solved

ASP - Search DB where variable = request.form syntax

Posted on 2014-01-23
29
554 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
Comment Utility
mySQL2 = "SELECT quantity FROM inventory WHERE productname '"&Request.Form("productname")&"'"

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
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
Comment Utility
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
My bad, forgot =

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

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
ha, just found that as well.
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
mySQL2 = "SELECT quantity FROM inventory WHERE productname = '" & Request.Form("productname")' & "'"

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
...
...
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
Comment Utility
Double check  you have the field names correct.
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
Cathal,

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

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
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
Comment Utility
Try the amended code above.
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
Boom, that worked!!!!  What was it?
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Magic!
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
Haha!  Thanks!
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
It's just some corrections on syntax.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now