p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: updating a table:what is the correct syntax for updating a table?


Message #1 by "Eric Van Camp" <eric.vancamp@c...> on Mon, 4 Dec 2000 16:44:46 -0000
i need to update a table, conditionally, saying i wish to update when a

condition is true..



Normally i use a recordset but one can do this using the command object to

execute an SQL INSERT statement

but i am not sure about the correct syntax for updating a table!





I am using the next coded with INSERT which is not correct!

	rem Create a command



	' HERE YOU CAN HAVE ERROR HANDLER

	'ON ERROR RESUME NEXT



	set adoCommand = Server.CreateObject("ADODB.Command")

	adoCommand.CommandType = adCmdText

	adoCommand.ActiveConnection = conn



	rem Constuct your sql query

	if temp1 = "Yes" then

	response.write"temp1=yes"

	stractif = -1

	else

	stractif = 0

	end if



	temp4=request.form("telbox")

	if temp4<>"on" then strtel=""

	temp5=request.form("faxbox")

	if temp5<>"on" then strfax=""

	temp6=request.form("btw")

	if temp6<>"on" then strbtw=""

	temp7=request.form("hr")

	if temp7<>"on" then strhr=""

	temp8=request.form("b1")

	if temp8<>"on" then strb1=""

	temp9=request.form("b2")

	if temp9<>"on" then strb3=""

	temp10=request.form("b4")

	if temp10<>"on" then strb4=""





	response.write "request.formcoyid="&request.form("coyid")

	response.write "typeid="&request.form'"typeid"

	strSQL="INSERT INTO bforms (coyid,typeid,papertype, addressid, tel, fax,

btw, hr ,b1 ,b2 ,b3 ,b4 ,description ,paper ,printcolors ,col1 ,col2 ,col3

,col4 ,col5,actif) VALUES ('"& request.form("coyId") &

"','"&request.form("bformid")&"','" &  request.form("typeid") & "',"&

request.form("addressid") & ",'" & strtel & "','" & strfax & "','"& strbtw

&"','"& strhr & "','"& strb1 &"','"& strb2 &"','"& strb3 &"','"& strb4

&"','"& request.form("description")&"','"& request.form("paper")&"','"&

request.form("printcolors")&"','"& request.form("col1")&"','"&

request.form("col2")&"','"& request.form("col3")&"','"&

request.form("col4")&"','"& request.form("col5")&"',"&stractif&")where

addressid="&addressid

	response.write strsql



	adoCommand.CommandText = strSQL

	adoCommand.Execute

	if ERR.NUMBER <> 0 then

	%>

	<BR>There must be an error</BR>

	<%



	else

	%>

	<%

	end if



 	'RELEASE RESSOURCES



what i know is that the insert statement is apparantly wrong cos you can not

use a where clause when using insert,

so i would like to use update?

Can anyone help me with the correct syntax??



Message #2 by Tobias Nielsen <Tobias@m...> on Mon, 4 Dec 2000 23:56:24 +0100
When you are using an ado object to access a database, it is very seldom

that you should use an "insert" startement for two basic reasons:

-First you in most cases you lose the code overview

-Second because the ado internally handles the insert statement a whole lot

faster than you possibly can do from asp, remember its scriptcode.



the best solution is to use a normal recordset to insert the details that

you want.



i would do the whole thing a bit like this (psoudo alike - i cant test the

code from where i am)



set adoRec = Server.CreateObject("ADODB.Recordset")

'Open the recordset as a normal table with update permissions

adoRec.Open "bforms",conn,adOpenKeyset,adLockOptimistic,adCmdTable



'now this litle line makes the whole difference

'prepares the recordset and makes a new - ready for use

'and you can loop it if you want



adoRec.AddNew



'two versions of binding - first early binding

adoRec!coyid       = request("coyId")

adoRec!typeid      = ...... etc

.... etc

.... etc

'then late binding

adoRec("papertype")   = request("addressid")

adoRec("addressid")   

'which one you prefer dosn't matter since its a scripting engine and it will

internally turn it in to late binding anyway



'and now the last bit which actually flush the new record and submits it in

the database

adoRec.update

adoRec.Close



i hope it works.

good luck





-----Original Message-----

From: Eric Van Camp [mailto:eric.vancamp@c...]

Sent: 5. december 2000 02:27

To: ASP Databases

Subject: [asp_databases] updating a table:what is the correct syntax for

updating a table?





i need to update a table, conditionally, saying i wish to update when a

condition is true..



Normally i use a recordset but one can do this using the command object to

execute an SQL INSERT statement

but i am not sure about the correct syntax for updating a table!





I am using the next coded with INSERT which is not correct!

	rem Create a command



	' HERE YOU CAN HAVE ERROR HANDLER

	'ON ERROR RESUME NEXT



	set adoCommand = Server.CreateObject("ADODB.Command")

	adoCommand.CommandType = adCmdText

	adoCommand.ActiveConnection = conn



	rem Constuct your sql query

	if temp1 = "Yes" then

	response.write"temp1=yes"

	stractif = -1

	else

	stractif = 0

	end if



	temp4=request.form("telbox")

	if temp4<>"on" then strtel=""

	temp5=request.form("faxbox")

	if temp5<>"on" then strfax=""

	temp6=request.form("btw")

	if temp6<>"on" then strbtw=""

	temp7=request.form("hr")

	if temp7<>"on" then strhr=""

	temp8=request.form("b1")

	if temp8<>"on" then strb1=""

	temp9=request.form("b2")

	if temp9<>"on" then strb3=""

	temp10=request.form("b4")

	if temp10<>"on" then strb4=""





	response.write "request.formcoyid="&request.form("coyid")

	response.write "typeid="&request.form'"typeid"

	strSQL="INSERT INTO bforms (coyid,typeid,papertype, addressid, tel,

fax,

btw, hr ,b1 ,b2 ,b3 ,b4 ,description ,paper ,printcolors ,col1 ,col2 ,col3

,col4 ,col5,actif) VALUES ('"& request.form("coyId") &

"','"&request.form("bformid")&"','" &  request.form("typeid") & "',"&

request.form("addressid") & ",'" & strtel & "','" & strfax & "','"& strbtw

&"','"& strhr & "','"& strb1 &"','"& strb2 &"','"& strb3 &"','"& strb4

&"','"& request.form("description")&"','"& request.form("paper")&"','"&

request.form("printcolors")&"','"& request.form("col1")&"','"&

request.form("col2")&"','"& request.form("col3")&"','"&

request.form("col4")&"','"& request.form("col5")&"',"&stractif&")where

addressid="&addressid

	response.write strsql



	adoCommand.CommandText = strSQL

	adoCommand.Execute

	if ERR.NUMBER <> 0 then

	%>

	<BR>There must be an error</BR>

	<%



	else

	%>

	<%

	end if



 	'RELEASE RESSOURCES



what i know is that the insert statement is apparantly wrong cos you can not

use a where clause when using insert,

so i would like to use update?

Can anyone help me with the correct syntax??





Message #3 by Stephane_Dattenny@D... on Tue, 5 Dec 2000 01:43:50 -0600
It's true, You could not use a WHERE in your INSERT SQL Statement, instead

if you use a SELECT sql query to fill a table with INSERT.



Why do you need to put a where addressid="&addressid ??



Best regards / Cordialement

 

Stephane Dattenny

Dell Computers - EMEA IT - VB and Web developer

Phone: +33 (0)4 99 75 49 88 



 





-----Original Message-----

From: Eric Van Camp [mailto:eric.vancamp@c...]

Sent: 05 December 2000 02:27

To: ASP Databases

Subject: [asp_databases] updating a table:what is the correct syntax for

updating a table?





i need to update a table, conditionally, saying i wish to update when a

condition is true..



Normally i use a recordset but one can do this using the command object to

execute an SQL INSERT statement

but i am not sure about the correct syntax for updating a table!





I am using the next coded with INSERT which is not correct!

	rem Create a command



	' HERE YOU CAN HAVE ERROR HANDLER

	'ON ERROR RESUME NEXT



	set adoCommand = Server.CreateObject("ADODB.Command")

	adoCommand.CommandType = adCmdText

	adoCommand.ActiveConnection = conn



	rem Constuct your sql query

	if temp1 = "Yes" then

	response.write"temp1=yes"

	stractif = -1

	else

	stractif = 0

	end if



	temp4=request.form("telbox")

	if temp4<>"on" then strtel=""

	temp5=request.form("faxbox")

	if temp5<>"on" then strfax=""

	temp6=request.form("btw")

	if temp6<>"on" then strbtw=""

	temp7=request.form("hr")

	if temp7<>"on" then strhr=""

	temp8=request.form("b1")

	if temp8<>"on" then strb1=""

	temp9=request.form("b2")

	if temp9<>"on" then strb3=""

	temp10=request.form("b4")

	if temp10<>"on" then strb4=""





	response.write "request.formcoyid="&request.form("coyid")

	response.write "typeid="&request.form'"typeid"

	strSQL="INSERT INTO bforms (coyid,typeid,papertype, addressid, tel,

fax,

btw, hr ,b1 ,b2 ,b3 ,b4 ,description ,paper ,printcolors ,col1 ,col2 ,col3

,col4 ,col5,actif) VALUES ('"& request.form("coyId") &

"','"&request.form("bformid")&"','" &  request.form("typeid") & "',"&

request.form("addressid") & ",'" & strtel & "','" & strfax & "','"& strbtw

&"','"& strhr & "','"& strb1 &"','"& strb2 &"','"& strb3 &"','"& strb4

&"','"& request.form("description")&"','"& request.form("paper")&"','"&

request.form("printcolors")&"','"& request.form("col1")&"','"&

request.form("col2")&"','"& request.form("col3")&"','"&

request.form("col4")&"','"& request.form("col5")&"',"&stractif&")where

addressid="&addressid

	response.write strsql



	adoCommand.CommandText = strSQL

	adoCommand.Execute

	if ERR.NUMBER <> 0 then

	%>

	<BR>There must be an error</BR>

	<%



	else

	%>

	<%

	end if



 	'RELEASE RESSOURCES



what i know is that the insert statement is apparantly wrong cos you can not

use a where clause when using insert,

so i would like to use update?

Can anyone help me with the correct syntax??





Message #4 by Robert Chartier <rchartierh@a...> on Tue, 05 Dec 2000 14:58:27 -0500
actually, i think this one is more of a system architecture judement call.



ive worked on systems that do it all three major ways:



1. generate the SQL in ASP & use ADO (connection) to execute it

2. connect to the table, use .ADDNEW & .UPDATE (like below)

3. use stored procedures



personally, if you can do it, i prefer #3 all the way.  It is the most 

reliable, secure, efficient (shifts some of the work the the database 

engine, and off of the over-worked asp engine)



as for #2, i very very rarely use it, and really prefer not to use it...









At 05:56 PM 12/4/00, you wrote:

>When you are using an ado object to access a database, it is very seldom

>that you should use an "insert" startement for two basic reasons:

>-First you in most cases you lose the code overview

>-Second because the ado internally handles the insert statement a whole lot

>faster than you possibly can do from asp, remember its scriptcode.

>

>the best solution is to use a normal recordset to insert the details that

>you want.

>

>i would do the whole thing a bit like this (psoudo alike - i cant test the

>code from where i am)

>

>set adoRec = Server.CreateObject("ADODB.Recordset")

>'Open the recordset as a normal table with update permissions

>adoRec.Open "bforms",conn,adOpenKeyset,adLockOptimistic,adCmdTable

>

>'now this litle line makes the whole difference

>'prepares the recordset and makes a new - ready for use

>'and you can loop it if you want

>

>adoRec.AddNew

>

>'two versions of binding - first early binding

>adoRec!coyid       = request("coyId")

>adoRec!typeid      = ...... etc

>.... etc

>.... etc

>'then late binding

>adoRec("papertype")   = request("addressid")

>adoRec("addressid")   

>'which one you prefer dosn't matter since its a scripting engine and it will

>internally turn it in to late binding anyway

>

>'and now the last bit which actually flush the new record and submits it in

>the database

>adoRec.update

>adoRec.Close

>

>i hope it works.

>good luck

>

>

>-----Original Message-----

>From: Eric Van Camp [mailto:eric.vancamp@c...]

>Sent: 5. december 2000 02:27

>To: ASP Databases

>Subject: [asp_databases] updating a table:what is the correct syntax for

>updating a table?

>

>

>i need to update a table, conditionally, saying i wish to update when a

>condition is true..

>

>Normally i use a recordset but one can do this using the command object to

>execute an SQL INSERT statement

>but i am not sure about the correct syntax for updating a table!

>

>

>I am using the next coded with INSERT which is not correct!

>         rem Create a command

>

>         ' HERE YOU CAN HAVE ERROR HANDLER

>         'ON ERROR RESUME NEXT

>

>         set adoCommand = Server.CreateObject("ADODB.Command")

>         adoCommand.CommandType = adCmdText

>         adoCommand.ActiveConnection = conn

>



>         rem Constuct your sql query

>         if temp1 = "Yes" then

>         response.write"temp1=yes"

>         stractif = -1

>         else

>         stractif = 0

>         end if

>

>         temp4=request.form("telbox")

>         if temp4<>"on" then strtel=""

>         temp5=request.form("faxbox")

>         if temp5<>"on" then strfax=""

>         temp6=request.form("btw")

>         if temp6<>"on" then strbtw=""

>         temp7=request.form("hr")

>         if temp7<>"on" then strhr=""

>         temp8=request.form("b1")

>         if temp8<>"on" then strb1=""

>         temp9=request.form("b2")

>         if temp9<>"on" then strb3=""

>         temp10=request.form("b4")

>         if temp10<>"on" then strb4=""

>

>

>         response.write "request.formcoyid="&request.form("coyid")

>         response.write "typeid="&request.form'"typeid"

>         strSQL="INSERT INTO bforms (coyid,typeid,papertype, addressid, tel,

>fax,

>btw, hr ,b1 ,b2 ,b3 ,b4 ,description ,paper ,printcolors ,col1 ,col2 ,col3

>,col4 ,col5,actif) VALUES ('"& request.form("coyId") &

>"','"&request.form("bformid")&"','" &  request.form("typeid") & "',"&

>request.form("addressid") & ",'" & strtel & "','" & strfax & "','"& strbtw

>&"','"& strhr & "','"& strb1 &"','"& strb2 &"','"& strb3 &"','"& strb4

>&"','"& request.form("description")&"','"& request.form("paper")&"','"&

>request.form("printcolors")&"','"& request.form("col1")&"','"&

>request.form("col2")&"','"& request.form("col3")&"','"&

>request.form("col4")&"','"& request.form("col5")&"',"&stractif&")where

>addressid="&addressid

>         response.write strsql

>

>         adoCommand.CommandText = strSQL

>         adoCommand.Execute

>         if ERR.NUMBER <> 0 then

>         %>

>         <BR>There must be an error</BR>

>         <%

>

>         else

>         %>

>         <%

>         end if

>

>         'RELEASE RESSOURCES

>

>what i know is that the insert statement is apparantly wrong cos you can not

>use a where clause when using insert,

>so i would like to use update?

>Can anyone help me with the correct syntax??

>

>

>

>---

>FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

>IN YOUR INBOX!

>Get the latest and best HTML, XML, and JavaScript tips, tools, and

>developments from the experts.  Sign up for one or more of EarthWeb's

>FREE IT newsletters at http://www.earthweb.com today!








Robert Chartier

Author, AspFree.com

xxx-xxx-xxxx

rchartierh@a...

http://www.aspfree.com/devlinks

http://www.aspfree.com/authors/robert

http://www.aspalliance.com/nothingmn



Message #5 by "Ken Schaefer" <ken@a...> on Wed, 6 Dec 2000 13:04:21 +1100
Um - where did you get this information from?



The "script code" stuff you talk about - I think you are getting a little

confused as to what is happening. ASP doesn't handle the insert, rather a

text string is handed by ASP to ADO, which in turn hands the string to the

database engine for processing. My take would be that this would be faster

than using an ADO Recordset object to issue commands to the database engine

to process.



Secondly, using the ADO Recordset requires you to instantiate two

objects...the recordset and the connection object. Using an SQL INSERT

statement requires only a connection object, and no recordset object as you

are returning no records. Since you are only using a single object, surely

the INSERT method is faster...



I'm open to contrary evidence though.



Cheers

Ken





> When you are using an ado object to access a database, it is very seldom

> that you should use an "insert" startement for two basic reasons:

> -First you in most cases you lose the code overview

> -Second because the ado internally handles the insert statement a whole

lot

> faster than you possibly can do from asp, remember its scriptcode.

>

> the best solution is to use a normal recordset to insert the details that

> you want.








  Return to Index