using update and insert into together

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

General SQL Server Forums

New to SQL Server Programming

using update and insert into together

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-10-06 : 15:48:20

I am trying to do an update to a table and also insert a new record of the same data into another table (OT_Signup_Audit). I have tried repeating the update code below using sql_audit_ot = "insert into set....." as below and using rs_audit_ot.OPEN sql_ot, "DSN=BUERT_NT"
but I get a 500 error when running the asp page. Update works fine before adding the insert so I just have my insert wrong. Any help?


<%
	dim var_ot, sql_ot, sql_audit_ot, rs_ot, rs_ot_audit, ot_table, ot_screen
	set rs_ot = server.createobject("adodb.recordset")
	set rs_ot_audit = server.createobject("adodb.recordset")

//SQL UPDATE STATEMENT
sql_ot = "update ot_signup_temp set smond = '" & varsmond & "', smonn = '" & varsmonn & "', stued = '" & varstued & "', stuen = '" & varstuen & "', swedd = '" & varswedd 
sql_ot = sql_ot & "', swedn = '" & varswedn & "', sthud = '" & varsthud & "', sthun = '" & varsthun & "', sfrid = '" & varsfrid & "', sfrin = '" & varsfrin & "', ssatd = '" & varssatd 
sql_ot = sql_ot & "', ssatn = '" & varssatn & "', ssund = '" & varssund & "', ssunn = '" & varssunn 

sql_ot = sql_ot & "', umond = '" & varumond & "', umonn = '" & varumonn & "', utued = '" & varutued & "', utuen = '" & varutuen & "', uwedd = '" & varuwedd 
sql_ot = sql_ot & "', uwedn = '" & varuwedn & "', uthud = '" & varuthud & "', uthun = '" & varuthun & "', ufrid = '" & varufrid & "', ufrin = '" & varufrin & "', usatd = '" & varusatd 
sql_ot = sql_ot & "', usatn = '" & varusatn & "', usund = '" & varusund & "', usunn = '" & varusunn

sql_ot = sql_ot & "', emond = '" & varemond & "', emonn = '" & varemonn & "', etued = '" & varetued & "', etuen = '" & varetuen & "', ewedd = '" & varewedd 
sql_ot = sql_ot & "', ewedn = '" & varewedn & "', ethud = '" & varethud & "', ethun = '" & varethun & "', efrid = '" & varefrid & "', efrin = '" & varefrin & "', esatd = '" & varesatd 
sql_ot = sql_ot & "', esatn = '" & varesatn & "', esund = '" & varesund & "', esunn = '" & varesunn

sql_ot = sql_ot & "' WHERE ein = '" & request("xein")&"' and mondate = '" & varmondate & "'"


//SQL INSERT STATEMENT



rs_ot.OPEN sql_ot, "DSN=BUERT_NT"

%>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 15:58:15

You need to read up on SQL injection as your code is vulnerable to this. You should not be concatenating your queries together, instead you should be using parameterized queries or stored procedures (my preference).

I can't find your insert code in there. Don't we need that to help you?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-10-06 : 16:06:09

Something similar to below. I just want to insert the exact same info to an audit table that I am updating to the signup table.


sql_audit_ot = "insert into OT_Signup_Audit_Temp set smond = '" & varsmond & "', smonn = '" & varsmonn & "', stued = '" & varstued & "', stuen = '" & varstuen & "', swedd = '" & varswedd
sql_audit_ot = sql_adit_ot & "' WHERE ein = '" & request("xein")&"' and mondate = '" & varmondate & "'"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 16:07:49

Could you show us the output of sql_audit_ot? It is very, very hard to read concatenated code like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources