Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> > I want to set a server up to be able to reference itself
> > via an alias in a 4-part name. Let us call the server
> > SQLSERVER and its alias BILLING. The motive for this is
> > to be able to, move the so-referenced databases to another
> > server when I want to and reference that truely remote
> > server by the BILLING alias.
> >
> > I have the alias set-up correctly and the server linked
> > via that alias with the proper security contexts.
> >
> > When I query:
> >
> > select * from [billing].master.dbo.sysdatabases
> >
> > I get the resultset I want.
> >
> > When I query:
> >
> > begin tran
> > select * from [billing].master.dbo.sysdatabases
> > rollback tran
> >
> > I get the following error:
> >
> > Server: Msg 7391, Level 16, State 1, Line 2
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > If I use BILLING to alias another server (a truely remote
> > server), the transactioned query works without error.
> >
> > I've came to understand this as a loopback linked server limitation
> > in that loopback linked servers cannot enlist in a distributed
> transaction.
> >
> > But, I made a table, x, on sqlserver.Master with one int column, x
> > I made a stored proc, spx, on sqlserver.Master that takes one parameter
> > and inserts it's value into table x
> >
> > On Sql Server I did the following
> >
> > Use Master
> > go
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 1
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 commit tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 1 and no errors so transaction worked.
> > go
> >
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 2
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 rollback tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 2 and no errors so transaction worked.
> > go
> >
> > begin tran
> > select * from [BILLING].Master.dbo.x
> > -- Saw same error as cited above as expected.
> > if @.@.trancount != 0 rollback tran
> >
> > What is going on? Transactions should have failed in stored proc usage
> > as well as a direct query if this were a loopback failure to enlist a
> > distributed transaction, shouldn't they?
> >
> >
> >
> >
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?
Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>
|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> transaction.
>

Looking for suggestions

I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.

I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?

Thanks in advance!

Global temporary table (GTT) (##tempTable) might not be the one you need

here is the downside of using GTT.

1. you cannot use it to cache Data for a long period of time - let say 1 day

If all the reference to GTT is drop then the data in it is also lost forever

2. GTT is visible to all stored procedure using it - thus concerrency issue

This wont cause you any problem if the data in it is readonly. meaning

You process only once for the entire day. if you'll be cjhanging the content

of GTT from time to time this will cause you headache

two recommendations:

1. if the resultset of P1 will have to live for at least 1 day and there will be no processing required for that span of time,

I would recommend a physical table instead.

2. if the result set of P1 is dynamic and P1 needs to be reused from time to time

I recommend that P1 be transformed into a table-valued function

|||

I totally agree. In fact, I would probably suggest (based on the phrase "lot of processing") that you create a couple of permanent tables. One to hold the results, and the other to keep either:

1. Users of the data that you expect to read the data. As these users read the data, delete their rows, when the last reader finishes, delete them.

2. An amount of time before the results are invalidated. So if you could run it daily, have it be invalidated at midnight, and then users of the data would do their select, and if no data was there, or it was past it's date, it would build the cache, and if the data was there, then fetch it.

If a lot of processing is not really a "lot" then you might use a table-valued function, but there are limitations, like no side effects, if there is any data being written in the process.