2. ASPRunnerPro General questions
  3. [SOLVED]  Edit form update query
This topic is locked
[SOLVED]

 Edit form update query

 		11/3/2011 4:05:14 AM
ASPRunnerPro General questions
A
 andres author

Good Day All,
I have two tables.
Table A

Descriptions : Part Number Description Qty
Field Names : Item,Itemdesc,Itemqty
Partno Descrition Qty

BRT10290 Widget1 10
Table B

Descriptions : Part Number Qty

Partno Qty

BRT10290 10
Table A information appears on my Edit Page
For Example : Item Desc Qty

BRT10290 Widget1 1
I have the following in the after edit event
Hardcoded values :
if Values("Itemqty") > "0" then

sql = "update Table B set qty=qty-4 where Partno = 'BRT10290'"

CustomQuery(sql)
This works a 100%
I would like to change the fixed values of Partno and qty to variables
if Values("Itemqty") > "0" then

sql = "update Table B set qty=qty-" & value("Itemqty")& " where Partno =" & values("Item")

CustomQuery(sql)
I get a error with this
I have also tried
if Values("Itemqty") > "0" then

sql = "update Table B set qty=qty-" & dict("Itemqty")& " where Partno =" & dict("Item")

CustomQuery(sql)
Still getting error

Any help or comments will be highly appreciated.
Regards
Andre
Sergey Kornilov admin 11/3/2011

Andre,
what error message do you get? Post full message here.
Is 'Table B' the actual table name?
A
 		andres author 11/4/2011



Andre,
what error message do you get? Post full message here.
Is 'Table B' the actual table name?



Hi Sergey,
Table A actual name Jobs

Table B Actual name Stock
sql = "update Stock set Qty = Qty - "& values("Item1Q")& "where Partno = " & values("Item1")

CustomQuery(sql)
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'ARG2382424004'.

/include/dal.asp, line 305
Partno and ARG2382424004 exists in Stock Table.
Strange as this works

sql = "update Stock set Qty = Qty - 1 where Partno = 'ARG2382424004' "

CustomQuery(sql)
Regards

Andre
Sergey Kornilov admin 11/4/2011

Andre,
make sure you add single quotes around text values in SQL Query.
Correct:

update Stock set Qty = Qty - 1 where Partno = 'ARG2382424004'


Incorrect:

update Stock set Qty = Qty - 1 where Partno = ARG2382424004
A
 		andres author 11/7/2011



Andre,
make sure you add single quotes around text values in SQL Query.
Correct:

update Stock set Qty = Qty - 1 where Partno = 'ARG2382424004'


Incorrect:

update Stock set Qty = Qty - 1 where Partno = ARG2382424004



Hi,
This works :

sql ="update Stock set Qty = Qty - 1 where Partno = 'G2382424004'"

CustomQuery(sql)
Not Working :

sql ="update Stock set Qty = Qty - 1 where Partno = " & dict("Item1")

CustomQuery(sql)

Error :

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'G2382424004'.
Not Working :

sql ="update Stock set Qty = Qty - 1 where Partno = " & dict("'Item1'")

CustomQuery(sql)

Error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

/include/dal.asp, line 305
What do I miss or do incorrectly
Thank you
Regards
Andre
P
 		Philip 11/7/2011

Andre,
you use:

sql ="update Stock set Qty = Qty - 1 where Partno = " & dict("Item1")


Try this:

sql ="update Stock set Qty = Qty - 1 where Partno = ' " & dict("Item1") & "'"


Philip
A
 		andres author 11/8/2011



Andre,
you use:

sql ="update Stock set Qty = Qty - 1 where Partno = " & dict("Item1")


Try this:

sql ="update Stock set Qty = Qty - 1 where Partno = ' " & dict("Item1") & "'"


Philip


Hi Philip,
Billions of thank you's to you. Solved my problem.
Regards
Andre