Consolidate two UPDATE statements into on

I'm working on a stored procedure to age balances. In short, here's the algo
rithm:
Payments get applied to the oldest 'bucket' first; in my example, BalanceOve
r180.
Is the value in the Payments column greater than or equal to the value in Ba
lanceOver180?
If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
If not, subtract Payments from BalanceOver180 and set Payments to 0.
Then go on to the next oldest 'bucket' (which would be Balance151To180) and
run the same two update statements.
There are a total of 7 columns against which payments need to be applied.
Here are the two statements that execute for each column:
UPDATE AgedBalances
SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
WHERE Payments >= BalanceOver180 AND Payments > 0
UPDATE AgedBalances
SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
WHERE Payments < BalanceOver180 AND Payments > 0
My question: Can these two statements be combined into one using the ABS fun
ction or the CASE construct
or something similar that I don't seem to be able to figure out?
Sample data:
BalanceOver180 Payments
Before: 200 500
After: 0 300
OR
Before: 600 500
After: 100 0
OR
Before: 400 400
After: 0 0
You get the picture.
As always, thanks in advance for all assistance.
Carl Imthurnsomething like that:
update <yor table>
set BalanceOver180 =
case
when
(Payments < BalanceOver180) then (BalanceOver180 - Payments)
when
(Payments >= BalanceOver180) then 0
end,
Payments =
case
when
(Payments < BalanceOver180) then 0
when
(Payments >= BalanceOver180) then (Payments - BalanceOver180)
end
where Payments > 0
--
Programmer
Let me know if it works for you :-))
"Carl Imthurn" wrote:

> I'm working on a stored procedure to age balances. In short, here's the al
gorithm:
> Payments get applied to the oldest 'bucket' first; in my example, BalanceO
ver180.
> Is the value in the Payments column greater than or equal to the value in
BalanceOver180?
> If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
> If not, subtract Payments from BalanceOver180 and set Payments to 0.
> Then go on to the next oldest 'bucket' (which would be Balance151To180) an
d run the same two update statements.
> There are a total of 7 columns against which payments need to be applied.
> Here are the two statements that execute for each column:
> UPDATE AgedBalances
> SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
> WHERE Payments >= BalanceOver180 AND Payments > 0
> UPDATE AgedBalances
> SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
> WHERE Payments < BalanceOver180 AND Payments > 0
> My question: Can these two statements be combined into one using the ABS f
unction or the CASE construct
> or something similar that I don't seem to be able to figure out?
> Sample data:
> BalanceOver180 Payments
> Before: 200 500
> After: 0 300
> OR
> Before: 600 500
> After: 100 0
> OR
> Before: 400 400
> After: 0 0
> You get the picture.
> As always, thanks in advance for all assistance.
> Carl Imthurn
>|||That worked - thank you very much Sergey.
Sergey Zuyev wrote:

> something like that:
> update <yor table>
> set BalanceOver180 =
> case
> when
> (Payments < BalanceOver180) then (BalanceOver180 - Payments)
> when
> (Payments >= BalanceOver180) then 0
> end,
> Payments =
> case
> when
> (Payments < BalanceOver180) then 0
> when
> (Payments >= BalanceOver180) then (Payments - BalanceOver180)
> end
> where Payments > 0

Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc

I would like to access a table in MS Access from a stored procedure on MS
SQL 2000. The MS Access DB is running on a diferent computer attached on the
same network. I think I need to create an ODBC connection from the MS SQL
2000 to the MS Access DB but not sure. Secondly how do I refer to the table
in the MS SQL stored proc?

Thanks for your input,

Joe SeamourYou need to add a linked server, and then you can query the Access database
directly. The BOL entry for sp_addlinkedserver shows an example, and the
topic "OLE DB Provider for Jet" has more details.

I've never set this up to use an Access DB on a different server myself, but
I think it should work if the MSSQL service account has permission to access
the share where the .mdb file is.

When the linked server is configured, you can query it like this.

SELECT * FROM MyLinkedServer...Table

Simon

"Joe Seamour" <jseamour@.attbi.com> wrote in message
news:1MYKa.33514$Bg.16441@.rwcrnsc54...
> I would like to access a table in MS Access from a stored procedure on MS
> SQL 2000. The MS Access DB is running on a diferent computer attached on
the
> same network. I think I need to create an ODBC connection from the MS SQL
> 2000 to the MS Access DB but not sure. Secondly how do I refer to the
table
> in the MS SQL stored proc?
> Thanks for your input,
> Joe Seamour

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
.Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.Hi
"IT Developer" wrote:
> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
..Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.
Hi
"IT Developer" wrote:

> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

ConnectionRead (InvalidParam()) error

I get the following error sometimes when I execute a store procedure from SQ
L
Query Analyzer. After a few more attempts, the query succeeds... How can I
troubleshoot this network error...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (Inv
alidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokeFirst thing to check, just like the message stated, "General network error.
Check your network documentation." The fact that it sometimes works and
sometimes not points to an unstable network.
hth
Quentin
"Shaila" <Shailaja @.discussions.microsoft.com> wrote in message
news:03329249-66CC-4806-91A9-D5434C3130D9@.microsoft.com...
>I get the following error sometimes when I execute a store procedure from
>SQL
> Query Analyzer. After a few more attempts, the query succeeds... How can I
> troubleshoot this network error...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

ConnectionRead (InvalidParam()) error

Somtimes I get the following error in SQL query Analyzer when executing a
store procedure. After a few attempts the query succeeds but how can i
resolve this issue...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (Inv
alidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokeHi,
This is due to the network that you are having. Due to network congestion u
might be facing this problem. Try to enhance the network and try again
"Shaila" wrote:

> Somtimes I get the following error in SQL query Analyzer when executing a
> store procedure. After a few attempts the query succeeds but how can i
> resolve this issue...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (I
nvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

ConnectionRead (InvalidParam()) error

Somtimes I get the following error in SQL query Analyzer when executing a
store procedure. After a few attempts the query succeeds but how can i
resolve this issue...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokeHi,
This is due to the network that you are having. Due to network congestion u
might be facing this problem. Try to enhance the network and try again
"Shaila" wrote:
> Somtimes I get the following error in SQL query Analyzer when executing a
> store procedure. After a few attempts the query succeeds but how can i
> resolve this issue...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

ConnectionRead (InvalidParam()) error

I get the following error sometimes when I execute a store procedure from SQL
Query Analyzer. After a few more attempts, the query succeeds... How can I
troubleshoot this network error...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokeFirst thing to check, just like the message stated, "General network error.
Check your network documentation." The fact that it sometimes works and
sometimes not points to an unstable network.
hth
Quentin
"Shaila" <Shailaja @.discussions.microsoft.com> wrote in message
news:03329249-66CC-4806-91A9-D5434C3130D9@.microsoft.com...
>I get the following error sometimes when I execute a store procedure from
>SQL
> Query Analyzer. After a few more attempts, the query succeeds... How can I
> troubleshoot this network error...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

ConnectionRead (InvalidParam()) error

I get the following error sometimes when I execute a store procedure from SQL
Query Analyzer. After a few more attempts, the query succeeds... How can I
troubleshoot this network error...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broke
First thing to check, just like the message stated, "General network error.
Check your network documentation." The fact that it sometimes works and
sometimes not points to an unstable network.
hth
Quentin
"Shaila" <Shailaja @.discussions.microsoft.com> wrote in message
news:03329249-66CC-4806-91A9-D5434C3130D9@.microsoft.com...
>I get the following error sometimes when I execute a store procedure from
>SQL
> Query Analyzer. After a few more attempts, the query succeeds... How can I
> troubleshoot this network error...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

ConnectionRead (InvalidParam()) error

Somtimes I get the following error in SQL query Analyzer when executing a
store procedure. After a few attempts the query succeeds but how can i
resolve this issue...
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broke
Hi,
This is due to the network that you are having. Due to network congestion u
might be facing this problem. Try to enhance the network and try again
"Shaila" wrote:

> Somtimes I get the following error in SQL query Analyzer when executing a
> store procedure. After a few attempts the query succeeds but how can i
> resolve this issue...
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broke

Connection.Open () ERROR ! Unable to find stored procedure sp_sdidebug.

Hi all,

When I try to open a connection it gives me error "Stored Procedure sp_sdidebug not found"

Can anyone help me in this respect?

I was running the same application with the SQL Server 2005 only earlier when it was running.But now after I reinstalled VS.NET & SQL Server I am getting this error.What could be the problem?

Please help... I am badly stuck up.

PAM

To rule out the obvious, have you double checked that sp_sdidebug exists on the server that your SQL connection is connecting to? Also, are you connecting to the same server?|||

You are right this stored procedure does not exist in <Database>/Programmability/Stored Procedures/

Is this the location where I should see for it or I need to look into Master?Even that is not there.I checked all other databases but this procedure is nowhere.

What can be done?I have already reinstalles Sql Server 2005.

& I am really stuck up in a stupid issue of connecting to database.

Please advise me something.

Pam

|||

I found the answer somewhere in online forums.The cause of the problem was that the sql server debugging was enabled in the debugging properties of the project.

thanks anyways.

pam

Connection.Execute & SQL Stored Procedure

I am hopeful that those of you with more experience in the compilation of
.asp, the connection object and MS SQL Server as the DB can help me out. I
have a web app that routinely calls stored procedures in a MS SQL DB.
In one web page, I make 6 calls with the Connection.Execute 'store
procedure'. Five of the six calls execute as expected. However; I have one
call in middle of the 6 that only executes the stored procedure partially.
This particular stored procedure does quite a bit of work within the DB, mak
e
3-4 select quiries and then a few updates to a single record. In this
procedure, one of SQL update statements is constructed in a loop to ensure
the appropriate fields are updated and no others. (The fields updated vary
with the input being used) This is where the procedure will "time out". I
have reduce/elimnated all the "debuggin" code that is no esential to the
store procedure. When the dubbuging print and select statements commented
out the procedure will execute further when called from the asp page.
Has anyone run across a situation where the .asp does not allow a stored
procedure to execute to completion before executing the next line of .asp
code? If so is there a way to force the Connection.Execute call to wait unti
l
the SQL server stored procedure has completed executing before processing th
e
next line of asp? Note the stored procedure does not explicitly return
anything to the asp code... also noteworthy, if the same stored procedure is
run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
procedure runs flawlessly and as expected to completion...
Anyone have an idea?Simon McLaren wrote:
> I am hopeful that those of you with more experience in the
> compilation of .asp, the connection object and MS SQL Server as the
> DB can help me out. I have a web app that routinely calls stored
> procedures in a MS SQL DB.
This would have been more on topic at .inetserver.asp.db. However, read on.

> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I
> have one call in middle of the 6 that only executes the stored
> procedure partially. This particular stored procedure does quite a
> bit of work within the DB, make 3-4 select quiries and then a few
> updates to a single record. In this procedure, one of SQL update
> statements is constructed in a loop to ensure the appropriate fields
> are updated and no others. (The fields updated vary with the input
> being used) This is where the procedure will "time out". I have
> reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements
> commented out the procedure will execute further when called from the
> asp page.
>
There are two timeout properties to be concerned with:
1. The IIS ScriptTimeout property, which controls the max time a page will
be allowed to execute on the server. This can be set globally using IIS
Manager to modify your website's application properties. It can also be set
at the page level using:
Server.ScriptTimeout = <some reasonable value>
2. The ADO Connection's CommandTimeout property. This controls the amount of
time a Command will allow a statement to execute. If set for a longer period
of time than the ScriptTimeout, the script may time out before the command
finishes executing.This is set via:
dim cn
set cn=createobject("adodb.connection")
cn.open ...
cn.CommandTimeout = <some reasonable value>
You should go to msdn.microsoft.com/library and read the documentation about
these properties.
Having said that, I need to add: Why call 6 procedures from ASP? One of the
main benefits of procedures is encapsulation, a huge benefit of which is
reducing the number of round trips to the database. You should create a new
procedure which accepts all the arguments required to call the other 6
procedures. This procedure calls the 6 procedures in turn and then returns
the overall result to the ASP client. One trip to the database. This can be
a huge performance booster.
I also need to add: you need to do some work to optimize the procedures you
have. You should not be allowing an asp page to time out waiting for
database activity to complete. Is there any way to take that time-consuming
activity offline?
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I'll try to answer to your question as much as I understood it.
First of all, ASP is not compiled, it is interpreted language. Ok, this has
nothing with your question but I had to say it :)
ADO Connection/Command object has CommandTimeout property which defines for
how long it will wait for SP to execute.
I did not work with ASP and ADO for a long time. I had that "luck" to switch
to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
advice you to:
-Remove print commands from your SP, it may confuse ADO
-Set "set nocount on" at the beging of your code
-Close connection as soon as you finish you finish your work with DB
-Use SQL OLE DB provider for SQL Server
http://msdn.microsoft.com/library/d...
qlprovspec.asp
I hope this help
Regards,
Marko Simic
"Simon McLaren" wrote:

> I am hopeful that those of you with more experience in the compilation of
> .asp, the connection object and MS SQL Server as the DB can help me out. I
> have a web app that routinely calls stored procedures in a MS SQL DB.
> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I have one
> call in middle of the 6 that only executes the stored procedure partially.
> This particular stored procedure does quite a bit of work within the DB, m
ake
> 3-4 select quiries and then a few updates to a single record. In this
> procedure, one of SQL update statements is constructed in a loop to ensure
> the appropriate fields are updated and no others. (The fields updated vary
> with the input being used) This is where the procedure will "time out".
I
> have reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements commented
> out the procedure will execute further when called from the asp page.
> Has anyone run across a situation where the .asp does not allow a stored
> procedure to execute to completion before executing the next line of .asp
> code? If so is there a way to force the Connection.Execute call to wait un
til
> the SQL server stored procedure has completed executing before processing
the
> next line of asp? Note the stored procedure does not explicitly return
> anything to the asp code... also noteworthy, if the same stored procedure
is
> run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
> procedure runs flawlessly and as expected to completion...
> Anyone have an idea?
>|||Bob and Simic,
Thanks for taking the time to respond. Bob to answer you question about why
the 6 SP calls... Originally it the six where all one procedure.
Progromatically, the six procedures achimplish "steps" of a process, all of
which in this application could be (and are) occassionally executed
independently. I have created an encapsulating SP that in turn calls the 6
seperate SP's, but this non-completion issue forced me to back away from tha
t
idea to try and isolate the "offending" procedure.
The Connection/Command timeouts have been set to 60seconds. This failure
happens much quicker than 60 seconds.
By trial and error last w I tried commenting out all "debugging" code (to
include print and select statement indented to allow me to see what was
happening and if dynamicly generated SQL statements where correct.) in the
offending stored procedure, figuring not that it might be confusing ADO, but
rather that is was adding to the execution time of the stored procedure. An
d
presto, execution to completion for the .asp page and no debuggin "insight"
from the query analyzer.
While I "stumbled" across the issue on my own, it was not until reading your
responses that I understood what the actual issue was. With "debugging code
"
commented out of the stored procedures, I believe I can move back to a singl
e
SP and trip to the database.
Thanks,
Simon
"Simic Marko" wrote:
> I'll try to answer to your question as much as I understood it.
> First of all, ASP is not compiled, it is interpreted language. Ok, this ha
s
> nothing with your question but I had to say it :)
> ADO Connection/Command object has CommandTimeout property which defines fo
r
> how long it will wait for SP to execute.
> I did not work with ASP and ADO for a long time. I had that "luck" to swit
ch
> to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
> advice you to:
> -Remove print commands from your SP, it may confuse ADO
> -Set "set nocount on" at the beging of your code
> -Close connection as soon as you finish you finish your work with DB
> -Use SQL OLE DB provider for SQL Server
> http://msdn.microsoft.com/library/d...sqlprovspec.asp
> I hope this help
> Regards,
> Marko Simic
>
> "Simon McLaren" wrote:
>

Connection timeout when generating recordset from a stored procedure in an ASP page

I'm trying to create a binding to a stored procedure on a SQL server. I can
create the database connection and view the tables and stored procedures.
When I try to create a stored procedure binding I can choose the connection
and the procedure. But when I check "Returns DataSet" and click Test... I
get the following Dreamweaver error after a minute or so:
A server timeout has occured. Here are the possible reasons.
1. Please make sure that the webserver is up and running.
2. Please verify that the ODBC DSN exists on the testing server.
Executing the stored procedure in SQL Query Analyzer returns results in less
than 5 seconds.
My webserver is up and running and the web.config file is the same on both
the local machine and testing server. The SQL server and webserver are
different machines. My stored procedure is below.
I can create datasets (querys) on the database tables. It's only when I try
to generate a dataset from a stored procedure that run into problems.
If it matters I'm using ASP.NET VB, SQL Server 2000, IIS 5.0, .NET 1.1 and
Dreamweaver MX 2004.
Please help, I'm at a standstill.
I get the same problem when using VB .NET to to create the ASP page. If I
use Coldfusion instead on ASP it works flawlessly. Only problem is I that
have a developers license for Coldfusion so this is not an acceptable
workaround.
Thanks in advance!! My stored procedure is below:
CREATE PROCEDURE proc_combinedDB
(@.platform varchar(40), @.server varchar(40),@.keyword varchar(50))
WITH RECOMPILE
AS
SELECT *
FROM [prtracker_classworks].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
UNION ALL
SELECT *
FROM [prtracker_mac bugs].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS]
LIKE @.server AND Details LIKE @.keyword
UNION ALL
SELECT *
FROM [Prtracker_Arizona State Edition].[dbo].[Problem Re
ports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
... // And so on for 15 more databases
RETURN
GOHi,
I am getting the same error using DWMX 2004 / ASP / VB.
Did you found a workaround ?
If so, your help is greatly apreciated.
axiaxi2003@.hotmail.com
A
amucino
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message466748.html

Connection still apperas in SQLServer Entreprise Manager over Locks / process ID

Hello,
i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.
I think i'm closing the connection well

Try

IfMe.ID > 0Then

Dim strSqlAsString = "SP_CHANGECLASSCONTENTS"

Dim sqlcommandAsNew SqlCommand(strSql,New SqlConnection(ConnStr))

sqlcommand.CommandType = CommandType.StoredProcedure

sqlcommand.Parameters.Add("@.PerformActivation", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PerformInactivation", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PerformDelete", SqlDbType.Bit).Value = 1

sqlcommand.Parameters.Add("@.PerformUndelete", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete)

sqlcommand.Parameters.Add("@.Class_ID", SqlDbType.Int).Value = _id

sqlcommand.Connection.Open()

sqlcommand.ExecuteNonQuery()

sqlcommand.Connection.Close()

Else

Err.Raise(10205, "CLASS", "CLASS ID is empty")

EndIf

Catch exAs Exception

_err = ex.Message

Return Err.Number

EndTry

Hi,

.NET Framework SQl data provider utilizes database connection pooling (there's one pool per unique connection string). When you close a SqlConnection, it is returned to this pool (there are min amount of connections in the pool ) and the hard connection to the db is not necessarily closed (connection ).
E.g this way usage of connections is kept efficient and opening & closing of "real connections" is kept minimum.
Here's more details
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

|||ok tks,
but what if i use many procedures like this one several times, and each one creates it's own connection. Even if i close the connection well it still appears several processes concerning this procedures in SQLSERVER. does this degrades the performance ?
tks|||

Hi,
see the previous link for exact details.
Anyways, it happens within the limits of the connection pool. If there are free connections in the pool, they are utilized to fullfill those which you instantiate and run e.g even if you instantiate new connections in code, the pool might be able to deal with them without increasing its size (the actual pooled connections are of type SqlInternalConnection not those which SqlConnection objects which you instantiate).
If the pool cannot deal with new connection requests, it starts creating new ones (within the max limit of the pool). If max limit is reached then new connection requests are queued. Note also that after a burst usage of connections, they are also dropped from the pool to the mimum limit when lifetime has expired or connectiion is severed.

E.g the pools are there to keep connection count in control in situation like these.

Connection Problem trying to execute stored procedure

I am running the following code and getting the error below:

Dim connectionstringAsString ="<%$ ConnectionStrings:ConnectionString %>"Dim ConnectionAsNew SqlClient.SqlConnection(connectionstring)Dim CommandAsNew SqlClient.SqlCommand("StoredProcedure9", Connection)

Command.CommandType = Data.CommandType.StoredProcedure

Command.Parameters.Add(

New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

Command.Parameters(

"@.bid_id").Value ="Testing"

Connection.Open()

Command.ExecuteNonQuery()

Connection.Close()

ERROR:

Format of the initialization string does not conform to specification starting at index 0.

I think my connectionstring is wrong, please help.

Thanks

CAB

Try changing:
Dim connectionstringAsString ="<%$ ConnectionStrings:ConnectionString %>"

To

Dim connectionstring as String = System.Configuration.ConnectionStrings("MyConnectionStringName").ConnectionString (or something similar to that...)

Which will grab the connectionstring out of your web.config and plug it in as the property in question. (I'm not too up on VB.NET 2.0 so I may be wrong - but pretty sure you can't use the <% %> (i.e. pre-processing tags) inside of compilable VB.NET code - i.e. I think that's your issue)
|||

Hi,

Thanks for your help, but now I am getting:

'ConnectionStrings' is not a member of 'Configuration'.

Any ideas?

|||Cabby, can you let us know which version of the .NET Framework you are using? 1.1 or 2.0?|||

Hi, I am using version 2.0

I have updated that line of code to

Dim connectionstringAsString = System.Configuration.ConfigurationManager.AppSettings("connectionstring")

and am now getting:

The ConnectionString property has not been initialized.

This seems to be a similar problem tohttp://forums.asp.net/thread/402876.aspx but I am not sure I understand what needs to be done.

Please help!

Thanks

CAB

|||That other link was for ASP.NET 1.1. See if this article helps:Configuration API Improvements in ASP.NET 2.0
|||

Thanks for the link. But that seems to be working fine. When I run:

<%

@.ImportNamespace="System.Configuration" %>

<%

@.ImportNamespace="System.Web.Configuration" %>

<

scriptrunat=serverlanguage=C#>publicvoidPage_Load(objectsource,EventArgse)

{

Response.Write("Connection String:"

+

ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

}

</

script>

<

html>

<

head><title>Retrieving Connection Strings</title>

</

head>

<

body>

<

formid="form1"runat="server"><div></div>

</

form>

</

body>

</

html>

I get:

Connection String:Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True

Which I assume means all is working with that page.

But when I run my code:

ProtectedSub DetailsView1_ItemInserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.DetailsViewInsertedEventArgs)

Dim connectionstringAsString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")Dim ConnectionAsNew SqlClient.SqlConnection(connectionstring)Dim CommandAsNew SqlClient.SqlCommand("StoredProcedure9", Connection)

Command.CommandType = Data.CommandType.StoredProcedure

Command.Parameters.Add(

New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

Command.Parameters(

"@.bid_id").Value ="Testing"

Connection.Open()

Command.ExecuteNonQuery()

Connection.Close()

Response.Redirect(

"Default.aspx")EndSub

I get:

The ConnectionString property has not been initialized.

Any ideas?

Thanks

|||

UPDATE:

I have changed the connection string to:

Dim

connectionstringAsString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString()This seems to be getting a bit further, now my error is:

Input string was not in a correct format.

which seems to be relating to the line:

Command.ExecuteNonQuery()

any ideas?

Thanks, CAB

|||

The problem is with my parameters, the stored procedure works fine if I dont try and pass it any parameters and remove :

Command.Parameters.Add(New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

I have realised that I can do this all in the stored procedure and not have to pass any variables so now all is working fine.

Thanks for all the help!!!!!

CABBY