Help with webForms

[Ken]

Hello,

I have a webform that gets a record from SQL Server by using a stored
procedure.
The stored procedure uses 3 parameters in which after the record is found,
stored procedure #2 runs to update one of the columns called Status from
"incomplete " to "Pending"
Status is one of the parameters for stored procedure #1.
This all works fine, My problem is after the person is finished with the
record they then push the next button to get the next record.
Prior to gettting the next record I need it to execute stored procedure #3
to update the Status column from "Pending" to "Complete"
What it is doing is its getting the next record and then running stored
procedure #3 which causes the new record to be updated instead of the
previous.

Any help would be greatly appreciated!
CODE:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim cn As New ADODB.Connectio n
Dim Rs As ADODB.Recordset

cn.Open("Driver =SQL
Server;Server=x xxxx;Database=x xxxx;uid=xxxxx; pwd=xxxxx;")

Rs = New ADODB.Recordset
Rs.Open("Exec MyRecords" & "'" & Session("valTyp e") & "'" & ", '" &
Session("valTC" ) & "'" & ", '" & Session("valLan g") & "'", cn)
Session("valSer ial") = Rs.Fields("Seri al").Value

Dim RsPending As ADODB.Recordset
RsPending = New ADODB.Recordset
RsPending.Open( "Exec StatusPending " & "'" & Session("valSer ial") &
"'", cn, ADODB.CursorTyp eEnum.adOpenKey set,
ADODB.LockTypeE num.adLockOptim istic)
lblSSN.Text = Rs.Fields("SSN" ).Value
lblBWE.Text = Rs.Fields("Proc Date").Value
lblDate.Text = Rs.Fields("Rece ieveDate").Valu e
lblTime.Text = Rs.Fields("Proc essTime").Value
lblLang.Text = Rs.Fields("Lang uage").Value
lblUser.Text = Rs.Fields("User ").Value

End Sub
Private Sub btnNext_Click(B yVal sender As Object, ByVal e As
System.EventArg s) Handles btnNext.Click
Dim objConn As SqlConnection = New SqlConnection(" Data
Source=xxxxxxx; UID=xxxxx;PWD=x xxxx;")
Dim objStatusComple teCMD As SqlCommand = New
SqlCommand("Sta tusComplete", objConn)
objStatusComple teCMD.CommandTy pe = CommandType.Sto redProcedure

Dim PSerialNo As SqlParameter =
objStatusComple teCMD.Parameter s.Add("@Serial" , SqlDbType.Char, 12)
PSerialNo.Value = Session("valSer ial")

objConn.Open()

Dim myStatusC As SqlDataReader =
objStatusComple teCMD.ExecuteRe ader()
myStatusC.Read( )

myStatusC.Close ()
myStatusC = Nothing

objConnStatus.C lose()
objConnStatus = Nothing

End Sub

[Cor]

Hi Ken,

Have a look at "IsPostBack "
(It has to be in your page load event, every time a page is sended back it
is completly initialized).

Normaly the sentence in the load event from a webform is.

If not IsPostback then
...........
else

End if

As an advice, have also a look at the dataset, in my opinion you do not have
to change your code that much for it.

I hope this helps,

Cor

I have a webform that gets a record from SQL Server by using a stored
procedure.
The stored procedure uses 3 parameters in which after the record is found,
stored procedure #2 runs to update one of the columns called Status from
"incomplete " to "Pending"
Status is one of the parameters for stored procedure #1.
This all works fine, My problem is after the person is finished with the
record they then push the next button to get the next record.
Prior to gettting the next record I need it to execute stored procedure #3
to update the Status column from "Pending" to "Complete"
What it is doing is its getting the next record and then running stored
procedure #3 which causes the new record to be updated instead of the
previous.

Any help would be greatly appreciated!
CODE:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim cn As New ADODB.Connectio n
Dim Rs As ADODB.Recordset

cn.Open("Driver =SQL
Server;Server=x xxxx;Database=x xxxx;uid=xxxxx; pwd=xxxxx;")

Rs = New ADODB.Recordset
Rs.Open("Exec MyRecords" & "'" & Session("valTyp e") & "'" & ", '" & Session("valTC" ) & "'" & ", '" & Session("valLan g") & "'", cn)
Session("valSer ial") = Rs.Fields("Seri al").Value

Dim RsPending As ADODB.Recordset
RsPending = New ADODB.Recordset
RsPending.Open( "Exec StatusPending " & "'" & Session("valSer ial") & "'", cn, ADODB.CursorTyp eEnum.adOpenKey set,
ADODB.LockTypeE num.adLockOptim istic)
lblSSN.Text = Rs.Fields("SSN" ).Value
lblBWE.Text = Rs.Fields("Proc Date").Value
lblDate.Text = Rs.Fields("Rece ieveDate").Valu e
lblTime.Text = Rs.Fields("Proc essTime").Value
lblLang.Text = Rs.Fields("Lang uage").Value
lblUser.Text = Rs.Fields("User ").Value

End Sub
Private Sub btnNext_Click(B yVal sender As Object, ByVal e As
System.EventArg s) Handles btnNext.Click
Dim objConn As SqlConnection = New SqlConnection(" Data
Source=xxxxxxx; UID=xxxxx;PWD=x xxxx;")
Dim objStatusComple teCMD As SqlCommand = New
SqlCommand("Sta tusComplete", objConn)
objStatusComple teCMD.CommandTy pe = CommandType.Sto redProcedure

Dim PSerialNo As SqlParameter =
objStatusComple teCMD.Parameter s.Add("@Serial" , SqlDbType.Char, 12)
PSerialNo.Value = Session("valSer ial")

objConn.Open()

Dim myStatusC As SqlDataReader =
objStatusComple teCMD.ExecuteRe ader()
myStatusC.Read( )

myStatusC.Close ()
myStatusC = Nothing

objConnStatus.C lose()
objConnStatus = Nothing

End Sub

[Kenneth Adams]

Thank you very much! That worked!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!