Need to press submit button two times to save records

[wsee]

Hi,

I need to press two times to save multiple records in different tables. I tried to use tran=con.begintransaction() to roll back transactions in case of error. But I didn't have the luck. So I tried in several sets of Try...catch...finally.

After clicking submit button the first time, the page postback (all new entries are kept) but record are not saved in database. After clicking the submit button again, it saves all records in.

Please advise how to improve the coding and fix the problem.

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim Count As Integer
Dim con As SqlConnection = New SqlConnection
Dim cmd As SqlCommand = New SqlCommand
Try

con.ConnectionString = "data source=J\SQLEXPRESS; initial catalog=YYY; user id=xxx; password=xxxxx"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "usp_UpdateGeneralData1"

With cmd.Parameters
.Add("@OppID", Data.SqlDbType.TinyInt).Value = Int32.Parse(lblOppID.Text)
.Add("@CustomerID", Data.SqlDbType.TinyInt).Value = ddlCustomer.SelectedIndex
.Add("@MarketEmpID", Data.SqlDbType.TinyInt).Value = ddlMarketing.SelectedIndex
.Add("@SalesMgrID", Data.SqlDbType.TinyInt).Value = ddlSalesMgr.SelectedIndex
.Add("@SalesRepID", Data.SqlDbType.TinyInt).Value = ddlSalesRep.SelectedIndex
.Add("@Project", Data.SqlDbType.VarChar).Value = txtProjectName.Text.ToString
.Add("@Socket", Data.SqlDbType.VarChar).Value = txtSocketName.Text.ToString
.Add("@MajorSegmentsID", Data.SqlDbType.Int).Value = ddlMajorSeg.SelectedIndex
.Add("@OtherMajor", Data.SqlDbType.VarChar).Value = txtOtherMajor.Text.ToString
.Add("@MinorSegmentsID", Data.SqlDbType.Int).Value = ddlMinorSeg.SelectedIndex
.Add("@OtherMinor", Data.SqlDbType.VarChar).Value = txtOtherMinor.Text.ToString
If txtEstEng.Text = "" Then
.Add("@EstEngDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstEngDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstEng.Text.ToString)
End If
If txtActEng.Text = "" Then
.Add("@ActEngDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActEngDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActEng.Text.ToString)
End If
If txtEstEval.Text = "" Then
.Add("@EstEvalDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstEvalDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstEval.Text.ToString)
End If
If txtActEval.Text = "" Then
.Add("@ActEvalDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActEvalDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActEval.Text.ToString)
End If
If txtEstDesign.Text = "" Then
.Add("@EstDesignDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstDesignDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstDesign.Text.ToString)
End If
If txtActDesign.Text = "" Then
.Add("@ActDesignDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActDesignDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActDesign.Text.ToString)
End If
If txtEstMark.Text = "" Then
.Add("@EstMarketIntroDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstMarketIntroDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstMark.Text.ToString)
End If
If txtActMark.Text = "" Then
.Add("@ActMarketIntroDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActMarketIntroDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActMark.Text.ToString)
End If
If txtEstVol.Text = "" Then
.Add("@EstVolProdDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstVolProdDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstVol.Text.ToString)
End If
If txtActVol.Text = "" Then
.Add("@ActVolProdDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActVolProdDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActVol.Text.ToString)
End If
If txtEstDDW.Text = "" Then
.Add("@EstDDWinDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@EstDDWinDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtEstDDW.Text.ToString)
End If
If txtActDDW.Text = "" Then
.Add("@ActDDWinDate", Data.SqlDbType.DateTime).Value = System.DBNull.Value
Else
.Add("@ActDDWinDate", Data.SqlDbType.DateTime).Value = DateTime.Parse(txtActDDW.Text.ToString)
End If
.Add("@Information", Data.SqlDbType.NVarChar).Value = txtInfo.Text.ToString
.Add("@MemUser", Data.SqlDbType.NVarChar).Value = User.Identity.Name.ToString
End With
cmd.Connection = con

con.Open()
cmd.ExecuteNonQuery()
'OppID = CInt(cmd.ExecuteScalar())
cmd.Parameters.Clear()
con.Close()
'Response.Redirect("EditGeneralData.aspx")
'Response.Write(OppID)

Catch ex As Exception

lblStatus.Text = "Error in updating General data. Please try again." + "<br>Error Message:" + ex.Message

Finally

con.Close()

End Try

cmd.CommandType = Data.CommandType.Text
cmd.CommandText = "SELECT COUNT(*) FROM General2_Table WHERE OppID = " & Int32.Parse(lblOppID.Text)
cmd.Connection = con
con.Open()
Dim n As Integer
n = CInt(cmd.ExecuteScalar())
con.Close()
Response.Write("n=" & n & "state" & con.State)
If n > 0 Then
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "usp_DeleteGeneralData2"
cmd.Parameters.Add("@OppID", Data.SqlDbType.Int).Value = Int32.Parse(lblOppID.Text)
cmd.Connection = con
Try
con.Open()
'cmd.ExecuteNonQuery()
cmd.ExecuteScalar()
'cmd.Parameters.Clear()
'con.Close()

Catch ex As Exception

lblStatus.Text = "Error in deleting General data. Please try again." + "<br>Error Message:" + ex.Message

Finally

cmd.Parameters.Clear()
con.Close()

End Try

End If

Try
For Count = 0 To lstIncum.Items.Count - 1
If lstIncum.Items.Item(Count).Selected And lstIncum.SelectedItem.Text.ToString <> "(None)" Then
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "usp_AddGeneralData2"
'Response.Write(Count & "--" & lstIncum.Items(Count).Value & ";id=" & Int32.Parse(lblOppID.Text) & ";sv=" & lstIncum.Items(Count).Value & ";user=" & User.Identity.Name.ToString & "<br>")
With cmd.Parameters
.Add("@OppID", Data.SqlDbType.Int).Value = Int32.Parse(lblOppID.Text)
.Add("@CompPartNoID", Data.SqlDbType.TinyInt).Value = lstIncum.Items(Count).Value
.Add("@State", Data.SqlDbType.NChar).Value = "I"
.Add("@MemUser", Data.SqlDbType.NVarChar).Value = User.Identity.Name.ToString
End With

End If
Next

cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
'cmd.Parameters.Clear()
'con.Close()

Catch ex As Exception

lblStatus.Text = "Error in adding Incumbent General data. Please try again." + "<br>Error Message:" + ex.Message

Finally

cmd.Parameters.Clear()
con.Close()

End Try

Try
For Count = 0 To lstNew.Items.Count - 1
If lstNew.Items.Item(Count).Selected And lstNew.SelectedItem.Text.ToString <> "(None)" Then
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "usp_AddGeneralData2"
'Response.Write(Count & "-" & lstNew.Items(Count).Value & "<br>")

With cmd.Parameters
.Add("@OppID", Data.SqlDbType.Int).Value = Int32.Parse(lblOppID.Text)
.Add("@CompPartNoID", Data.SqlDbType.TinyInt).Value = lstNew.Items(Count).Value
.Add("@State", Data.SqlDbType.NChar).Value = "N"
.Add("@MemUser", Data.SqlDbType.NVarChar).Value = User.Identity.Name.ToString
End With

End If
Next
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
'cmd.Parameters.Clear()
'con.Close()

Catch ex As Exception

lblStatus.Text = "Error in updating New General data. Please try again." + "<br>Error Message:" + ex.Message

Finally

cmd.Parameters.Clear()
con.Close()

End Try

End Sub

[serkan sendur]

where is your Page_Load event handler?

[wsee]

Below is the page_load event handler:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim OppID As Integer
OppID = Session("OppID")
lblOppID.Text = OppID
If Not IsPostBack Then
Dim con As SqlConnection = New SqlConnection
Dim cmd As SqlCommand = New SqlCommand
Dim dr As SqlDataReader

con.ConnectionString = "data source=J\SQLEXPRESS; initial catalog=YYYY; user id=xxxx; password=xxxxxx"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "usp_GetGeneral1Data"
'cmd.Parameters.Add("@OppID", Data.SqlDbType.Int).Value = lblOppID.Text
cmd.Parameters.Add("@OppID", Data.SqlDbType.Int).Value = Request.QueryString("OppID")
cmd.Connection = con
Try
con.Open()
'cmd.ExecuteNonQuery()
dr = cmd.ExecuteReader()
While dr.Read()
txtProjectName.Text = dr.Item("Project").ToString()
txtSocketName.Text = dr.Item("Socket").ToString()
txtEstEng.Text = dr.Item("EstEngDate").ToString()
txtActEng.Text = dr.Item("ActEngDate").ToString()
txtEstEval.Text = dr.Item("EstEvalDate").ToString()
txtActEval.Text = dr.Item("ActEvalDate").ToString()
txtEstDesign.Text = dr.Item("EstDesignDate").ToString()
txtActDesign.Text = dr.Item("ActDesignDate").ToString()
txtEstMark.Text = dr.Item("EstMarketIntroDate").ToString()
txtActMark.Text = dr.Item("ActMarketIntroDate").ToString()
txtEstVol.Text = dr.Item("EstVolProdDate").ToString()
txtActVol.Text = dr.Item("ActVolProdDate").ToString()
txtEstDDW.Text = dr.Item("EstDDWinDate").ToString()
txtActDDW.Text = dr.Item("ActDDWinDate").ToString()
txtInfo.Text = dr.Item("Information").ToString()
End While

Catch ex As Exception

lblStatus.Text = "Error found in loading data:" & ex.Message

Finally

cmd.Parameters.Clear()
'dr.Close()
con.Close()

End Try

End If

End Sub

[serkan sendur]

well, i believe the problem arise from the fact that your button click event executed after page load event. if you use session variables in such scenarios, this sequence of event execution causes problems like yours as well. put two break points one to the page load other to the button click, and click the button once and for the second time, follow the code execution, then probably you will see your expected values are not the same for the first and second time. besides, dont put so much code in your event handlers, instead put them in methods and call the methods from within the event handlers, that way it will be much easier to understand what your code does.