ASP - Search DB where variable = request.form syntax

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

LVL 1
bschwartingAsked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
...
...
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
mySQL2 = "SELECT quantity FROM inventory WHERE productname '"&Request.Form("productname")&"'"

Open in new window

0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
bschwartingAuthor Commented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
My bad, forgot =

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

Open in new window

0
 
bschwartingAuthor Commented:
ha, just found that as well.
0
 
bschwartingAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
anwarulCommented:
mySQL2 = "SELECT quantity FROM inventory WHERE productname = '" & Request.Form("productname")' & "'"

Open in new window

0
 
bschwartingAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
and did you try my suggestion? out of all of them, it is the most complete answer you could ask for
0
 
GaryCommented:
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
 
bschwartingAuthor Commented:
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
 
bschwartingAuthor Commented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
GaryCommented:
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
 
bschwartingAuthor Commented:
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
 
bschwartingAuthor Commented:
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
 
GaryCommented:
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
 
bschwartingAuthor Commented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Double check  you have the field names correct.
0
 
bschwartingAuthor Commented:
Cathal,

I have this above:
productname                                    = Request( "productname" )
productname                                    = Replace(  productname,"'","''" )
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
GaryCommented:
Try the amended code above.
0
 
bschwartingAuthor Commented:
Boom, that worked!!!!  What was it?
0
 
GaryCommented:
Magic!
0
 
bschwartingAuthor Commented:
Haha!  Thanks!
0
 
GaryCommented:
It's just some corrections on syntax.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.