Error Message

In writing a asp application which has a table update
The code for update is as shown
set Cmd1 = Server.CreateObject("ADODB.Connection")
 mydb1 = "DSN=listtables;uid=scott; pwd =tiger"
 Cmd1.open mydb1
my_sqlquery3 = " update listing set quantityintons = var1-var2 where itemnumber = '"&sessionitem&"'"

Cmd1.execute(my_sqlquery3)

I get the error
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid column name

/dblinking.asp, line 64
But when I comment out Cmd1.execute(my_sqlquery1) the error goes away. Why is this. What is causing the error. If you want complete code I can post it.
Ramani
ramani081399Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yury_DelendikCommented:
Try...
my_sqlquery3 = "UPDATE LISTING SET ""QUANTITYINTONS"" = ""VAR1"" - ""VAR2"" WHERE ""ITEMNUMBER"" = '" & sessionitem & "'"

Check, columns QUANTITYINTONS, VAR1, VAR2, ITEMNUMBER must present in table LISTING.
0
jamestthomasCommented:
Be sure have a quantityintons and an itemnumber on table listing.

Also, not being an Oracle person, what is var1-var2 is that 1 or 2 variables.  If the are variables you have to treat them as such in the SQL.  If the are table items, make sure the name is correct.
0
mgfranzCommented:
VBScript might be interpreting the SQl string which contains var1-var2 as a literal.  I think you want the SQL string to query the columns var1 and var2.

Mark
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

ramani081399Author Commented:
mgfranz,

var1 and var2 are two different variables and the update has to substravt var1-var2
0
mgfranzCommented:
Do the math before you run the query;

newvar = var1 - var2

Then your query string can look like this;

my_sqlquery3 = " update listing set quantityintons = newvar where itemnumber = '"&sessionitem&"'"

Mark
0
ramani081399Author Commented:
mgfranz,

I did thw math but still i get the error type mismatch.
could it be because i am getting var2 from a select statement which calculastes the sum as shown below
dim my_sqlquery2
my_sqlquery2 = "select sum(quantityintons) qty from getdatafrombids where item = '"&sessionitem&""
Cmd2.execute(my_sqlquery2)
 set Rs2 = server.CreateObject("ADODB.Recordset")
 Rs2.open my_sqlquery2, Cmd2,0
 if   NOT Rs2.EOF then       
 
 var2 = (Rs2("qty") )                
  end if
0
mgfranzCommented:
I suspect the '"&sessionitem&"" is not being passed...

Try writing out '"&sessionitem&""  What is it's value suppose to be?
0
Yury_DelendikCommented:
my_sqlquery3 = " update listing set quantityintons = var1-"&var2&" where itemnumber = '"&sessionitem&"'"
or
my_sqlquery3 = " update listing set quantityintons = "&(var1-var2)&" where itemnumber = '"&sessionitem&"'"

?
0
ramani081399Author Commented:
Yury_Delendik
I tried to get the datatype of var2 by using
Response.Write(var2.Type)
I get the following message now
Microsoft VBScript runtime error '800a01a8'

Object required: '75'

/dblinking.asp, line 56
If you see the number 75 is in quotes which I think it is interpreted as a varchar and when I compare var1 which is a number it gives a type mismatch error.So is there a way to type cast a char  to int.
 
0
Yury_DelendikCommented:
.. or
my_sqlquery3 = " update listing set quantityintons = "&var1&"-"&var2&" where itemnumber = '"&sessionitem&"'"
....
my_sqlquery3 = " update listing set quantityintons = "&(var1-CLng(var2))&" where itemnumber = '"&sessionitem&"'"


You may get the type of variable by VarType:
  Response.Write VarType(var2)

0
mgfranzCommented:
The single quotes around the '75' are part of the error message itself.  As Yury has stated, you need to find out what the type and values of var1 and var2 are before we can proceed.  Plus it is not necessary to cast a var char or int, ASP interprets the value literally as any one of the types listed here; http://msdn.microsoft.com/scripting/default.htm?/scripting/vbscript/doc/vsfctvartype.htm

Again, run Yury's string on the vars.

Response.Write VarType(var1)
Response.Write VarType(var2)

Mark
0
Vin32Commented:
1. put spaces on both sides of the ampersand symbol -- instead of this:

where itemnumber = '"&sessionitem&"'"

use

where itemnumber = '" & sessionitem & "'"


2. since your variable is NOT an object, the Type method would obviously fail.
0
ramani081399Author Commented:
mgfranz

I tried to do the arithmetic
var3 = var1 - var2

I get a error saying
Microsoft VBScript runtime error '800a000d'

Type mismatch

/dblinking.asp, line 52

But when I do a response.write(var1)
it writes as 80 which is correct and Response.Write(var2) It writes out correctly as 75.But the arithmetic of var1- var2 fails why?
Ramani
0
ramani081399Author Commented:
mgfranz

I solved the arithmetic problem of var1-var2 by saying CLng(var1)-CLng(var2) and the difference held in variable var3.
when I do a response.write(var3) it writes out the correct number

Now in the update statement I used
my_sqlquery3 = " update listing set quantityintons = var3 where itemnumber = '"&sessionitem&"'"
Cmd1.execute(my_sqlquery3)
I get the error
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid column name

/dblinking.asp, line 95

Instead for test purpouses if use a number say 80 or somenumber instead of var3 it works correctly. Is their something wrong which I am not able to see that is giving this error message.
ramani
0
mgfranzCommented:
Try this,
my_sqlquery3 = " update listing set quantityintons = '"&var3&"' where itemnumber = '"&sessionitem&"'"

You need to pass the var3 as a variable, I guess the column has a type Int, in your example you are passing in the literal "var3"  instead of "75".  If you pass the sql update;
my_sqlquery3 = " update listing set quantityintons = 75 where itemnumber = '"&sessionitem&"'"

what happens?

Mark
0
ramani081399Author Commented:
mgfranz,
 
when I do the update with
my_sqlquery3 = " update listing set quantityintons = 75 where itemnumber = '"&sessionitem&"'"
as told by you ,everything works out ok.But if I use '"&var3&"' i still get error message.
The datatype for var3 is declared to be a number.

Ramani
0
mgfranzCommented:
Post the code.
0
ramani081399Author Commented:
mgfranz,

Here is the code

<% @ Language=VBScript %>

<% Response.Buffer = true %>  
<!--#include file="adovbs.asp"-->

<%

dim Cmd1,Cmd2
dim mydb1,mydb2
dim var1, var2,var3
set Cmd1 = Server.CreateObject("ADODB.Connection")
 mydb1 = "DSN=listtables;uid=scott; pwd =tiger"
 Cmd1.open mydb1
 
 set Cmd2 = Server.CreateObject("ADODB.connection")
 mydb2 = "DSN=classifieddata;uid=scott; pwd =tiger"
 Cmd2.open mydb2
 
 
'oCmd1.CommandType = adCmdText
 'oCmd1.Prepared = true
dim my_sqlquery1,sessionitem,sessionquantity,my_sqlquery3
sessionitem = session("item")
sessionquantity = session("quantityintons")
my_sqlquery1 = "select quantityintons from listing where itemnumber = '"&sessionitem&"'"
 Cmd1.execute(my_sqlquery1)
'oCmd1.CommandText = "my_sqlquery1"
 set Rs1 = server.CreateObject("ADODB.Recordset")
 Rs1.open my_sqlquery1, Cmd1,0
 if  NOT Rs1.EOF then       
 'Rs1.MoveFirst
  'else
 var1 = CLng(Rs1("quantityintons"))
 end if
 'on db2
' oCmd2.CommandType = adCmdText
dim my_sqlquery2
my_sqlquery2 = "select to_number(sum(quantityintons)) qty from getdatafrombids where item = '"&sessionitem&"'"
 'oCmd2.Prepared = true
 'oCmd2.CommandText = "my_sqlquery2"
 Cmd2.execute(my_sqlquery2)
 set Rs2 = server.CreateObject("ADODB.Recordset")
 Rs2.open my_sqlquery2, Cmd2,0
 if   NOT Rs2.EOF then       
 'Rs2.MoveFirst
 'else
 var2 = CLng(Rs2("qty") )                
  end if
 
Response.Write(( var1))
Response.Write(( var2 ))
'Response.Write(CLng(var1))
'var3 =  (var1)- (var2)
Response.Write((var3))
if err.number > 0 Then
Response.write("VBScript Errors Occured:" & "<P>")
Response.write("Error Number=" & err.number & "<P>")
Response.write("Error Descr.=" & err.description & "<P>")
Response.write("Help Context=" & err.helpcontext & "<P>")
Response.write("Help Path=" & err.helppath & "<P>")
Response.write("Native Error=" & err.nativeerror & "<P>")
Response.write("Source=" & err.source & "<P>")
Response.write("SQLState=" & err.sqlstate & "<P>")
Else
Response.write("No VBScript Errors Occured" & "<P>")
End If
If Cmd1.errors.count> 0 then
Response.write("Database Errors Occured" & "<P>")
For counter= 0 to Cmd1.errors.count
Response.write("Error #" & Cmd1.errors(counter).number & "<P>")
Response.write("Error desc. -> " & Cmd1.errors(counter).description & "<P>")
Next
Else
Response.write "No Database Errors Occured!" & "<P>"
End If
If Cmd2.errors.count> 0 then
Response.write("Database Errors Occured" & "<P>")
For counter= 0 to Cmd2.errors.count
Response.write("Error #" & Cmd2.errors(counter).number & "<P>")
Response.write("Error desc. -> " & Cmd2.errors(counter).description & "<P>")
Next
Else
Response.write "No Database Errors Occured!" & "<P>"
End If



 'Response.Write(sessionitem)
 
If CLng(var1) > CLng(var2)  then

var3 = var1-var2
Response.Write(var3)
Response.Write(sessionitem)
my_sqlquery3 = "update listing set sessionquantity =  '"&var3&"'     where ITEMNUMBER = '"&sessionitem&"' "
Cmd1.execute(my_sqlquery3)

elseif  CLng(var1) = CLng(var2)  then  
my_sqlquery3 = "update listing set sessionquantity = 0 where ItemNumber = '"&sessionitem&"'"

Cmd1.execute(my_sqlquery3)

elseif CLng(var1)<CLng(var2) then

my_sqlquery3 = "update listing set Remarks = 'BidClosed' where ItemNumber = '"&sessionitem&"'"

Cmd1.execute(my_sqlquery3)

end if

%>



<%
Rs1.close
set Rs1 = nothing
Rs2.close
set Rs2 = nothing
Cmd1.close
set Cmd1 = nothing
Cmd2.close
set Cmd2 = nothing
%>

Ramani
0
mgfranzCommented:
Try this;

If CLng(var1) > CLng(var2)  then

var3 = CLng(var1)-CLng(var2)Response.Write(var3)
Response.Write(sessionitem)
my_sqlquery3 = "update listing set sessionquantity =  '"&var3&"' where ITEMNUMBER = '"&sessionitem&"' " 
Cmd1.execute(my_sqlquery3)


Does var3 get written out correctly after the math line?

Mark

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgfranzCommented:
Oops, that should be;

If CLng(var1) > CLng(var2)  then

var3 = CLng(var1)-CLng(var2)
Response.Write(var3)
Response.Write(sessionitem)
my_sqlquery3 = "update listing set sessionquantity =  '"&var3&"' where ITEMNUMBER = '"&sessionitem&"' " 
Cmd1.execute(my_sqlquery3)

of course...
0
Yury_DelendikCommented:
You must write:
....date listing set sessionquantity =  "&var3&" where ITEMNUM...
instead
....date listing set sessionquantity =  '"&var3&"' where ITEMNUM...

0
ramani081399Author Commented:
mgfranz,

Thank you very much for all the help. It works now. The mistake was not putting var3 = CLng(var1)-CLng(var2)
and the ampersand sign at var3.

Thanks


Ramani
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.