|
 |
asp_databases thread: RE: UpdateStatement
Message #1 by Sam Clohesy <sam@e...> on Tue, 27 Nov 2001 11:41:38 -0000
|
|
Hi all,
I am trying to ammend a lot of records in a dbase, prefixing each telno with
a 0.
Here is my code:
Set rs = server.createobject("adodb.recordset")
strSQL = "SELECT CompanyTelNo FROM tblCompany WHERE NOT CompanyTelNo LIKE
'0%' AND siteid = 2"
rs.Open strSQL, conn, 1
while not rs.eof
Dim telno
telno = "0" & rs("companytelno")
Set objCmd = Server.CreateObject("ADODB.Command")
strSQL1 = "UPDATE tblCompany SET COMPANYTELNO='" & telno & "' WHERE SITEID
2 "
'Set the command object properties
Set objCmd.ActiveConnection = Conn
objCmd.CommandText = strSQL1
objCmd.CommandType = 1
objCmd.Execute
response.write telno & "<br>"
%>
<%=rs("companytelno")%> <br>
<%
rs.MoveNext
wend
It has update all phone nos with the same phone no?!
Do I need to do a for each statement?
Any comment/suggestions much appreciated
Thanks
Sam
-----Original Message-----
From: Anupama Nallari [mailto:ANallari@p...]
Sent: 26 November 2001 23:39
To: ASP Databases
Subject: [asp_databases] Response.redirect
I am using response.redirect to redirect the user to another page after data
entry. I insert a new record into a SQL database and after executing the
stored procedure to insert the record into the database I have the statement
response.redirect("openreq.asp"), this line of code does not work the user
is not redirected to the other page instead the same page comes up.
Any clue as to why this is happening?
Thanks,
Anu
$subst('Email.Unsub')
Message #2 by "Dallas Martin" <dmartin@z...> on Tue, 27 Nov 2001 06:55:52 -0500
|
|
Look at your WHERE clause. You have asked the DB to update ALL records WHERE
siteid=2!!!
Morevover, you can update each record correctly in one statement:
You Don't need any recordset objects.
"UPDATE tblCompany SET COMPANYTELNO = '0' + CompanyTelNo WHERE SITEID = 2"
This statement will UPDATE each record's CompanyTelNo with '0' +
CompanyTelCo WHERE siteid=2
So try this:
Connection.Execute("UPDATE tblCompany SET COMPANYTELNO = '0' + CompanyTelNo
WHERE SITEID = 2")
Dallas
----- Original Message -----
From: "Sam Clohesy" <sam@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, November 27, 2001 6:41 AM
Subject: [asp_databases] RE: UpdateStatement
>
> Hi all,
>
> I am trying to ammend a lot of records in a dbase, prefixing each telno
with
> a 0.
>
> Here is my code:
>
>
> Set rs = server.createobject("adodb.recordset")
> strSQL = "SELECT CompanyTelNo FROM tblCompany WHERE NOT CompanyTelNo LIKE
> '0%' AND siteid = 2"
> rs.Open strSQL, conn, 1
>
>
> while not rs.eof
> Dim telno
> telno = "0" & rs("companytelno")
>
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> strSQL1 = "UPDATE tblCompany SET COMPANYTELNO='" & telno & "' WHERE SITEID
> 2 "
>
> 'Set the command object properties
> Set objCmd.ActiveConnection = Conn
> objCmd.CommandText = strSQL1
> objCmd.CommandType = 1
> objCmd.Execute
>
> response.write telno & "<br>"
> %>
> <%=rs("companytelno")%> <br>
>
>
> <%
>
>
> rs.MoveNext
> wend
>
>
> It has update all phone nos with the same phone no?!
> Do I need to do a for each statement?
> Any comment/suggestions much appreciated
>
> Thanks
>
> Sam
>
>
>
>
>
> -----Original Message-----
> From: Anupama Nallari [mailto:ANallari@p...]
> Sent: 26 November 2001 23:39
> To: ASP Databases
> Subject: [asp_databases] Response.redirect
>
>
>
> I am using response.redirect to redirect the user to another page after
data
> entry. I insert a new record into a SQL database and after executing the
> stored procedure to insert the record into the database I have the
statement
> response.redirect("openreq.asp"), this line of code does not work the user
> is not redirected to the other page instead the same page comes up.
>
> Any clue as to why this is happening?
>
> Thanks,
> Anu
>
>
> $subst('Email.Unsub')
>
>
$subst('Email.Unsub')
>
Message #3 by "Helga Y. Anagnostopoulos" <helga@k...> on Tue, 27 Nov 2001 17:33:03 +0200
|
|
don't you have to put on the end of your while not statement a rs.movenext?
I think this may be the problem.
Let me know
Helga
-----Original Message-----
From: Sam Clohesy [mailto:sam@e...]
Sent: Tuesday, November 27, 2001 1:42 PM
To: ASP Databases
Subject: [asp_databases] RE: UpdateStatement
Hi all,
I am trying to ammend a lot of records in a dbase, prefixing each telno with
a 0.
Here is my code:
Set rs = server.createobject("adodb.recordset")
strSQL = "SELECT CompanyTelNo FROM tblCompany WHERE NOT CompanyTelNo LIKE
'0%' AND siteid = 2"
rs.Open strSQL, conn, 1
while not rs.eof
Dim telno
telno = "0" & rs("companytelno")
Set objCmd = Server.CreateObject("ADODB.Command")
strSQL1 = "UPDATE tblCompany SET COMPANYTELNO='" & telno & "' WHERE SITEID
2 "
'Set the command object properties
Set objCmd.ActiveConnection = Conn
objCmd.CommandText = strSQL1
objCmd.CommandType = 1
objCmd.Execute
response.write telno & "<br>"
%>
<%=rs("companytelno")%> <br>
<%
rs.MoveNext
wend
It has update all phone nos with the same phone no?!
Do I need to do a for each statement?
Any comment/suggestions much appreciated
Thanks
Sam
-----Original Message-----
From: Anupama Nallari [mailto:ANallari@p...]
Sent: 26 November 2001 23:39
To: ASP Databases
Subject: [asp_databases] Response.redirect
I am using response.redirect to redirect the user to another page after data
entry. I insert a new record into a SQL database and after executing the
stored procedure to insert the record into the database I have the statement
response.redirect("openreq.asp"), this line of code does not work the user
is not redirected to the other page instead the same page comes up.
Any clue as to why this is happening?
Thanks,
Anu
$subst('Email.Unsub')
$subst('Email.Unsub')
Message #4 by "Drew, Ron" <RDrew@B...> on Wed, 28 Nov 2001 18:22:33 -0500
|
|
Why not just update...you don't need a recordset
"UPDATE tblCompany SET CompanyTelNo = '0" & CompanyTelNo & "' WHERE SITEID
2"
-----Original Message-----
From: Sam Clohesy [mailto:sam@e...]
Sent: Tuesday, November 27, 2001 6:42 AM
To: ASP Databases
Subject: [asp_databases] RE: UpdateStatement
Hi all,
I am trying to ammend a lot of records in a dbase, prefixing each telno with
a 0.
Here is my code:
Set rs = server.createobject("adodb.recordset")
strSQL = "SELECT CompanyTelNo FROM tblCompany WHERE NOT CompanyTelNo LIKE
'0%' AND siteid = 2" rs.Open strSQL, conn, 1
while not rs.eof
Dim telno
telno = "0" & rs("companytelno")
Set objCmd = Server.CreateObject("ADODB.Command")
strSQL1 = "UPDATE tblCompany SET COMPANYTELNO='" & telno & "' WHERE SITEID
2 "
'Set the command object properties
Set objCmd.ActiveConnection = Conn
objCmd.CommandText = strSQL1
objCmd.CommandType = 1
objCmd.Execute
response.write telno & "<br>"
%>
<%=rs("companytelno")%> <br>
<%
rs.MoveNext
wend
It has update all phone nos with the same phone no?!
Do I need to do a for each statement?
Any comment/suggestions much appreciated
Thanks
Sam
-----Original Message-----
From: Anupama Nallari [mailto:ANallari@p...]
Sent: 26 November 2001 23:39
To: ASP Databases
Subject: [asp_databases] Response.redirect
I am using response.redirect to redirect the user to another page after data
entry. I insert a new record into a SQL database and after executing the
stored procedure to insert the record into the database I have the statement
response.redirect("openreq.asp"), this line of code does not work the user
is not redirected to the other page instead the same page comes up.
Any clue as to why this is happening?
Thanks,
Anu
---
You are currently subscribed to asp_databases as: samc@e... To
unsubscribe send a blank email to $subst('Email.Unsub')
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #5 by "Drew, Ron" <RDrew@B...> on Wed, 28 Nov 2001 18:23:34 -0500
|
|
Sorry my reply was incorrect using a varible...one below looks good
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Tuesday, November 27, 2001 6:56 AM
To: ASP Databases
Subject: [asp_databases] RE: UpdateStatement
Look at your WHERE clause. You have asked the DB to update ALL records WHERE
siteid=2!!!
Morevover, you can update each record correctly in one statement:
You Don't need any recordset objects.
"UPDATE tblCompany SET COMPANYTELNO = '0' + CompanyTelNo WHERE SITEID = 2"
This statement will UPDATE each record's CompanyTelNo with '0' +
CompanyTelCo WHERE siteid=2
So try this:
Connection.Execute("UPDATE tblCompany SET COMPANYTELNO = '0' + CompanyTelNo
WHERE SITEID = 2")
Dallas
----- Original Message -----
From: "Sam Clohesy" <sam@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, November 27, 2001 6:41 AM
Subject: [asp_databases] RE: UpdateStatement
>
> Hi all,
>
> I am trying to ammend a lot of records in a dbase, prefixing each
> telno
with
> a 0.
>
> Here is my code:
>
>
> Set rs = server.createobject("adodb.recordset")
> strSQL = "SELECT CompanyTelNo FROM tblCompany WHERE NOT CompanyTelNo
> LIKE '0%' AND siteid = 2" rs.Open strSQL, conn, 1
>
>
> while not rs.eof
> Dim telno
> telno = "0" & rs("companytelno")
>
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> strSQL1 = "UPDATE tblCompany SET COMPANYTELNO='" & telno & "' WHERE
> SITEID
> 2 "
>
> 'Set the command object properties
> Set objCmd.ActiveConnection = Conn
> objCmd.CommandText = strSQL1
> objCmd.CommandType = 1
> objCmd.Execute
>
> response.write telno & "<br>"
> %>
> <%=rs("companytelno")%> <br>
>
>
> <%
>
>
> rs.MoveNext
> wend
>
>
> It has update all phone nos with the same phone no?!
> Do I need to do a for each statement?
> Any comment/suggestions much appreciated
>
> Thanks
>
> Sam
>
>
>
>
>
> -----Original Message-----
> From: Anupama Nallari [mailto:ANallari@p...]
> Sent: 26 November 2001 23:39
> To: ASP Databases
> Subject: [asp_databases] Response.redirect
>
>
>
> I am using response.redirect to redirect the user to another page
> after
data
> entry. I insert a new record into a SQL database and after executing
> the stored procedure to insert the record into the database I have the
statement
> response.redirect("openreq.asp"), this line of code does not work the
> user is not redirected to the other page instead the same page comes
> up.
>
> Any clue as to why this is happening?
>
> Thanks,
> Anu
>
>
> samc@e...
> $subst('Email.Unsub')
>
>
> ---
> You are currently subscribed to asp_databases as: dmartin@z... To
> unsubscribe send a blank email to
$subst('Email.Unsub')
>
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
|
|
|
View Cart
