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