My update statement doenot work in ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

My update statement doenot work in ASP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 09:23:25

Goody writes "Hi There,

I am updating few coloums in my table tpdcustomer in asp , but it is not working. I cannot figure out my mistake .

Is there any mistake in the qoutes i have given

custorderno and ccapp are varchar,
orderno is numeric

this is my code.

jorderno=request.form("orderno")
jappcode=request.form("appcode")
jcustorderno=request.form("custorderno")

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=D:\Dir\xyz.com\data.dsn"

MySQL= ("UPDATE tpdcustomer SET ccapp='"&jappcode&"', custorderno='"&jcustorderno&"' WHERE orderno='"&jorderno&"' ")
MyConn.BeginTrans
MyConn.Execute MySQL
MyConn.CommitTrans
MyConn.Close

Appreciate if someone replies back and helps me"

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-02-19 : 09:53:53

MySQL= "UPDATE tpdcustomer SET ccapp='"&jappcode&"', custorderno='"&jcustorderno&"' WHERE orderno="&jorderno

if orderno is numeric then there should not be any quotes enclosing the value. It is always helpful if you provide any error message that was given.

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 10:03:35

the Code LarG gives should solve the problem.

but using Stored Procedures would be a better option

Create Procedure UpdateCust (@jappCode varchar(30),@jcustorderno varchar(30), @jorderno int)
Set nocount on
UPDATE tpdcustomer SET ccapp=@jappcode, custorderno=@jcustorderno WHERE orderno=@jorderno
Go

--------------------------------------------------------------

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-02-19 : 10:39:07

quote:

MyConn.BeginTrans
MyConn.Execute MySQL
MyConn.CommitTrans
MyConn.Close

You also don't need the transaction since you are executing only 1 statement.

lfmn
Posting Yak Master

141 Posts

Posted - 2002-02-19 : 12:15:35

I'm not sure why LarsG says that there should be no closing quotes on a numeric value. Although they are not required, I've haven't heard of them causing a problem. If I'm missing something, I would be glad to learn, but the following code would seem to show that quotes don't cause an error message.

create table #tmp (test int, somevalue varchar(30))
insert into #tmp values (1, 'value1')
insert into #tmp values (2, 'value2')

update #tmp set somevalue = 'newvalue' where test = 1
update #tmp set somevalue = 'anothernewvalue' where test = '2'

select * from #tmp

To troubleshoot your problem, try using the response.write command to output your MySQL variable. This will allow you to see the statement you are trying to execute.

cursors are like hammers - sometimes you have to use them, but watch your thumb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources