|
 |
asp_databases thread: Sql Update to Access overwrites database
Message #1 by tigger92083@y... on Tue, 30 Jan 2001 22:22:45 -0000
|
|
I am using the following code for my update and when I hit update it wipes
out the records on my application any help with this would be welcomed.
<%
upbtype = Replace(Request.Form("vbtype"), "'", "''")
upbname = Replace(Request.Form("name"), "'", "''")
upbaddress = Replace(Request.Form("vaddress"), "'", "''")
upbcity = Replace(Request.Form("vcity"), "'", "''")
upbstat = Replace(Request.Form("vstate"), "'", "''")
upbzip = Replace(Request.Form("vzip"), "'", "''")
upbphone = Replace(Request.Form("vphone"), "'", "''")
upbfax = Replace(Request.Form("vfax"), "'", "''")
upbpoc = Replace(Request.Form("vpoc"), "'", "''")
upbweb = Replace(Request.Form("vweb"), "'", "''")
upbid= CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
'*** Select Connection Method ***
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=e:\database\Lawndale.mdb;" & "Persist Security Info=False"
SQL = "Update biz_dir Set Business_type = '"&upbtype&"' WHERE ID
='"&upbid&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Name = '"&upbname&"' "
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Address = '"&upbaddress&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set City = '"&upbcity&"' "
MyConn.Execute(SQL)
SQL = "Update biz_dir Set State = '"&upbstat&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Zip = '"&upbzip&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Phone = '"&upbphone&"' "
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Fax = '"&upbfax&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set POC = '"&upbpoc&"'"
MyConn.Execute(SQL)
SQL = "Update biz_dir Set Web = '"&upbweb&"' "
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "index.asp"
%>
Message #2 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 03:46:04 -0000
|
|
You need to provide a where clause for each sql statement. However it would
be much better to do it in one shot.
SQL = "Update biz_dir Set Business_type = '" & upbtype & "', "
(Check to see if name has been updated and if it is set to null)
SQL = SQL & Name = '" & upbname & "', "
(Do the same for all the other fields)
...
SQL = SQL & " WHERE ID ='" & upbid & "'"
>From: tigger92083@y...
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Sql Update to Access overwrites database
>Date: Tue, 30 Jan 2001 22:46:44 -0800
>
>I am using the following code for my update and when I hit update it wipes
>out the records on my application any help with this would be welcomed.
><%
>upbtype = Replace(Request.Form("vbtype"), "'", "''")
>upbname = Replace(Request.Form("name"), "'", "''")
>upbaddress = Replace(Request.Form("vaddress"), "'", "''")
>upbcity = Replace(Request.Form("vcity"), "'", "''")
>upbstat = Replace(Request.Form("vstate"), "'", "''")
>upbzip = Replace(Request.Form("vzip"), "'", "''")
>upbphone = Replace(Request.Form("vphone"), "'", "''")
>upbfax = Replace(Request.Form("vfax"), "'", "''")
>upbpoc = Replace(Request.Form("vpoc"), "'", "''")
>upbweb = Replace(Request.Form("vweb"), "'", "''")
>upbid= CStr(Request.Form("ID"))
>
>
>Set MyConn=Server.CreateObject("ADODB.Connection")
>'*** Select Connection Method ***
>
>MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=e:\database\Lawndale.mdb;" & "Persist Security Info=False"
>
>SQL = "Update biz_dir Set Business_type = '"&upbtype&"' WHERE ID
>='"&upbid&"'"
>
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set Name = '"&upbname&"' "
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set Address = '"&upbaddress&"'"
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set City = '"&upbcity&"' "
>MyConn.Execute(SQL)
>
>SQL = "Update biz_dir Set State = '"&upbstat&"'"
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set Zip = '"&upbzip&"'"
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set Phone = '"&upbphone&"' "
>MyConn.Execute(SQL)
>
>SQL = "Update biz_dir Set Fax = '"&upbfax&"'"
>MyConn.Execute(SQL)
>SQL = "Update biz_dir Set POC = '"&upbpoc&"'"
>MyConn.Execute(SQL)
>
>SQL = "Update biz_dir Set Web = '"&upbweb&"' "
>MyConn.Execute(SQL)
>MyConn.Close
>Set MyConn = Nothing
>
>Response.Redirect "index.asp"
>%>
>
|
|
|
View Cart
