synergydata
08-06-2008, 11:02 PM
I have this web form that needs to validate some accounting information contained in three separate text boxes and match that information against a single row of data within an SQL table for a valid accounting entry combination.
I have been able to validate a single textbox against a single column in the table but not three separate text boxes against a single row in a table.
The three text boxes are Obj_Rev_Srce, Job_Number and Orgn.
I created a select query on the SQL server to test the table and combination and that works fine, so therefore its in my code somewhere.
Here's the DBFunction I'm calling from the Code Behind Page:
Public Shared Function CheckJCBOCRRUU(ByVal strJobCode As String) As Boolean
Dim returnValue As Boolean
Dim connection As New SqlConnection()
Dim rs As SqlDataReader
connection.ConnectionString = bconn
Dim sSelect As String = " SELECT BOC, JOB_CODE, ORGANIZATION " _
& " FROM TRANS_CODE_VALIDATION " _
& " WHERE (BOC = @Obj_Rev_Srce) AND (JOB_CODE = @JOB_NUMBER) AND (ORGANIZATION = @ORGN) "
Dim cmdSelect As New System.Data.SqlClient.SqlCommand(sSelect, connection)
cmdSelect.Parameters.AddWithValue("@Obj_Rev_Srce", strJobCode)
cmdSelect.Parameters.AddWithValue("@JOB_NUMBER", strJobCode)
cmdSelect.Parameters.AddWithValue("@ORGN", strJobCode)
Dim err As String
Try
connection.Open()
rs = cmdSelect.ExecuteReader()
If rs.Read() Then
returnValue = True
End If
Catch ex As Exception
err = ex.Message.ToString
Finally
connection.Close()
End Try
Return returnValue
End Function
ASPX.VB Code Behind Page:
Protected Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitBtn.Click
Dim AAID As Integer
Dim AAInfo As New AAHeader
Dim AAHeaderID As Integer
Dim AADetailID As Integer
Dim AADetail As New AADetail
Dim sessAAID As Integer
If CheckJCBOCRRUU(JOB_NUMBER.TEXT) Then
'--- SET UP SESSION ITEMS ---'
Session.Item("Requestor_LName") = Requestor_LName.Text
Session.Item("Requestor_FName") = Requestor_FName.Text
Session.Item("Docload_Sec1") = Docload_Sec1.SelectedValue
Session.Item("RACA_Team") = RACA_Team.SelectedValue
Session.Item("Requestor_Phone") = Requestor_Phone.Text
Session.Item("RRUU") = RRUU.Text
Session.Item("Header_Description") = Header_Description.Text
Session.Item("Fiscal_Month") = Fiscal_Month.Text
Session.Item("Fiscal_Year") = Fiscal_Year.Text
Session.Item("Field_Approver_Name") = Field_Approver_Name.Text
Session.Item("Field_Approved_Date") = Field_Approved_Date.Text
Session.Item("Email_Address_of_Submitter") = userid.Text
'--- INSERT AA_HEADER INFORMATION INTO TABLE AND GET THE ID ---'
AAInfo.Requestor_LName = Requestor_LName.Text
AAInfo.Requestor_FName = Requestor_FName.Text
AAInfo.Requestor_Phone = Requestor_Phone.Text
AAInfo.RRUU = RRUU.Text
AAInfo.Field_Approver_Name = Field_Approver_Name.Text
AAInfo.Field_Approved_Date = Field_Approved_Date.Text
AAInfo.Email_Address_of_Submitter = userid.Text
AAInfo.Date_Submitted = Now()
'--- INSERT AA_DETAIL INFORMATION INTO TABLE AND GET THE ID ---'
AADetail.DOCLOAD_SEC1 = Docload_Sec1.SelectedValue
AADetail.RACA_Team = RACA_Team.SelectedValue
AADetail.BV_Date = Now()
AADetail.Header_Description = Header_Description.Text
AADetail.Fiscal_Month = Fiscal_Month.Text
AADetail.Fiscal_Year = Fiscal_Year.Text
AADetail.Line_No = ""
AADetail.Trans_Code = "BV" 'Trans_Code.SelectedValue
AADetail.Trans_Type = Trans_Type.SelectedValue
AADetail.Beg_BFY = Beg_BFY.Text
AADetail.Orgn = Orgn.Text
AADetail.Job_Number = Job_Number.Text
AADetail.Fund = Fund.Text
AADetail.PGMT = PGMT.Text
AADetail.Obj_Rev_Srce = Obj_Rev_Srce.Text
AADetail.Hours = HOURS.Text
AADetail.RPTG = RPTG.Text
AADetail.Line_Amount = Line_Amount.Text
AADetail.DOCUMENT_TOTAL = Line_Amount.Text
AADetail.Agreement_No = AGREEMENT_NO.Text
AADetail.Advance_FL = ADVANCE_FL.Text
AADetail.Vendor_ID = Vendor_ID.Text
AADetail.Line_Description = Line_Description.Text
AADetail.Email_Address_of_Submitter = userid.Text
AADetail.Date_Submitted = Now()
'--- COMPLETES THE HDR_EXP_REV_GL_IND BASED ON THE TRANS TYPE ---'
If AADetail.Trans_Type = "AC" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "AD" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "AV" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "CU" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "FG" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "FI" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "GA" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "NP" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "PJ" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "R3" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
ElseIf AADetail.Trans_Type = "RC" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "RE" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "RR" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "RT" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "SU" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "TO" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
ElseIf AADetail.Trans_Type = "TW" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
End If
If CStr(Session.Item("aaid")) = "" Then
'this is a first round, so insert into 1st table and second table by:
'call the two functions which do the inserts.
AAID = InsertAAHeader(AAInfo)
AADetail.AAID = AAID
AADetailID = InsertAAData(AADetail)
AAHeaderID = CStr(AAHeaderID)
sessAAID = CStr(AAID)
Session.Item("aaid") = sessAAID
'--- Send the ID from the first table insert to a hidden text box.
AAIDLabel.Text = sessAAID
AAIDLabel.Visible = True
Else
'The upper table has been inserted to, so insert only into 2nd table by:
'Call only 2nd function
AADetail.AAID = Session.Item("aaid")
AADetailID = InsertAAData(AADetail)
End If
If AADetailID <= 0 Then
InvalidJobCode.Text = "There was an error inserting this record. If this error persists, please contact the ASC B&F"
InvalidJobCode.CssClass = "error"
InvalidJobCode.Visible = True
Else
InvalidJobCode.Text = ""
InvalidJobCode.Visible = False
End If
Else
InvalidJobCode.Text = "THE BUD-ORG, JOB CODE AND BOC\REV ARE NOT THE RIGHT COMBINATION. THE COMBINATION MUST MATCH PLEASE RETRY!!! "
InvalidJobCode.CssClass = "error"
InvalidJobCode.Visible = True
JOB_NUMBER.Focus()
End If
''--- CLEAR DATA FIELDs ---'
''Session.Item("LINE_NO") = Nothing
'Session.Item("BEG_BFY") = Nothing
'Session.Item("ORGN") = Nothing
'Session.Item("JOB_NUMBER") = Nothing
'Session.Item("FUND") = Nothing
'Session.Item("PGMT") = Nothing
'Session.Item("OBJ_REV_SRCE") = Nothing
'Session.Item("HOURS") = Nothing
'Session.Item("PRTG") = Nothing
'Session.Item("LINE_AMOUNT") = Nothing
''Line_No.Text = ""
'Beg_BFY.Text = ""
'Orgn.Text = ""
'Job_Number.Text = ""
'Fund.Text = ""
'PGMT.Text = ""
'Obj_Rev_Srce.Text = ""
'HOURS.Text = ""
'RPTG.Text = ""
Line_Amount.Text = ""
'--- Troubleshooting the error in ID's
'--- Response.Write(Session.Item("aaid"))
'--- Session.Item("aaid") = sessAAID
'--- Response.Write(Session.Item("aaid"))
GridView1.DataBind()
End Sub
I have been able to validate a single textbox against a single column in the table but not three separate text boxes against a single row in a table.
The three text boxes are Obj_Rev_Srce, Job_Number and Orgn.
I created a select query on the SQL server to test the table and combination and that works fine, so therefore its in my code somewhere.
Here's the DBFunction I'm calling from the Code Behind Page:
Public Shared Function CheckJCBOCRRUU(ByVal strJobCode As String) As Boolean
Dim returnValue As Boolean
Dim connection As New SqlConnection()
Dim rs As SqlDataReader
connection.ConnectionString = bconn
Dim sSelect As String = " SELECT BOC, JOB_CODE, ORGANIZATION " _
& " FROM TRANS_CODE_VALIDATION " _
& " WHERE (BOC = @Obj_Rev_Srce) AND (JOB_CODE = @JOB_NUMBER) AND (ORGANIZATION = @ORGN) "
Dim cmdSelect As New System.Data.SqlClient.SqlCommand(sSelect, connection)
cmdSelect.Parameters.AddWithValue("@Obj_Rev_Srce", strJobCode)
cmdSelect.Parameters.AddWithValue("@JOB_NUMBER", strJobCode)
cmdSelect.Parameters.AddWithValue("@ORGN", strJobCode)
Dim err As String
Try
connection.Open()
rs = cmdSelect.ExecuteReader()
If rs.Read() Then
returnValue = True
End If
Catch ex As Exception
err = ex.Message.ToString
Finally
connection.Close()
End Try
Return returnValue
End Function
ASPX.VB Code Behind Page:
Protected Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitBtn.Click
Dim AAID As Integer
Dim AAInfo As New AAHeader
Dim AAHeaderID As Integer
Dim AADetailID As Integer
Dim AADetail As New AADetail
Dim sessAAID As Integer
If CheckJCBOCRRUU(JOB_NUMBER.TEXT) Then
'--- SET UP SESSION ITEMS ---'
Session.Item("Requestor_LName") = Requestor_LName.Text
Session.Item("Requestor_FName") = Requestor_FName.Text
Session.Item("Docload_Sec1") = Docload_Sec1.SelectedValue
Session.Item("RACA_Team") = RACA_Team.SelectedValue
Session.Item("Requestor_Phone") = Requestor_Phone.Text
Session.Item("RRUU") = RRUU.Text
Session.Item("Header_Description") = Header_Description.Text
Session.Item("Fiscal_Month") = Fiscal_Month.Text
Session.Item("Fiscal_Year") = Fiscal_Year.Text
Session.Item("Field_Approver_Name") = Field_Approver_Name.Text
Session.Item("Field_Approved_Date") = Field_Approved_Date.Text
Session.Item("Email_Address_of_Submitter") = userid.Text
'--- INSERT AA_HEADER INFORMATION INTO TABLE AND GET THE ID ---'
AAInfo.Requestor_LName = Requestor_LName.Text
AAInfo.Requestor_FName = Requestor_FName.Text
AAInfo.Requestor_Phone = Requestor_Phone.Text
AAInfo.RRUU = RRUU.Text
AAInfo.Field_Approver_Name = Field_Approver_Name.Text
AAInfo.Field_Approved_Date = Field_Approved_Date.Text
AAInfo.Email_Address_of_Submitter = userid.Text
AAInfo.Date_Submitted = Now()
'--- INSERT AA_DETAIL INFORMATION INTO TABLE AND GET THE ID ---'
AADetail.DOCLOAD_SEC1 = Docload_Sec1.SelectedValue
AADetail.RACA_Team = RACA_Team.SelectedValue
AADetail.BV_Date = Now()
AADetail.Header_Description = Header_Description.Text
AADetail.Fiscal_Month = Fiscal_Month.Text
AADetail.Fiscal_Year = Fiscal_Year.Text
AADetail.Line_No = ""
AADetail.Trans_Code = "BV" 'Trans_Code.SelectedValue
AADetail.Trans_Type = Trans_Type.SelectedValue
AADetail.Beg_BFY = Beg_BFY.Text
AADetail.Orgn = Orgn.Text
AADetail.Job_Number = Job_Number.Text
AADetail.Fund = Fund.Text
AADetail.PGMT = PGMT.Text
AADetail.Obj_Rev_Srce = Obj_Rev_Srce.Text
AADetail.Hours = HOURS.Text
AADetail.RPTG = RPTG.Text
AADetail.Line_Amount = Line_Amount.Text
AADetail.DOCUMENT_TOTAL = Line_Amount.Text
AADetail.Agreement_No = AGREEMENT_NO.Text
AADetail.Advance_FL = ADVANCE_FL.Text
AADetail.Vendor_ID = Vendor_ID.Text
AADetail.Line_Description = Line_Description.Text
AADetail.Email_Address_of_Submitter = userid.Text
AADetail.Date_Submitted = Now()
'--- COMPLETES THE HDR_EXP_REV_GL_IND BASED ON THE TRANS TYPE ---'
If AADetail.Trans_Type = "AC" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "AD" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "AV" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "CU" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "FG" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "FI" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "GA" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "NP" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "PJ" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "R3" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
ElseIf AADetail.Trans_Type = "RC" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "RE" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "RR" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "RT" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "R"
ElseIf AADetail.Trans_Type = "SU" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "G"
ElseIf AADetail.Trans_Type = "TO" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
ElseIf AADetail.Trans_Type = "TW" Then
AADetail.Hdr_Exp_Rev_Gl_Ind = "E"
End If
If CStr(Session.Item("aaid")) = "" Then
'this is a first round, so insert into 1st table and second table by:
'call the two functions which do the inserts.
AAID = InsertAAHeader(AAInfo)
AADetail.AAID = AAID
AADetailID = InsertAAData(AADetail)
AAHeaderID = CStr(AAHeaderID)
sessAAID = CStr(AAID)
Session.Item("aaid") = sessAAID
'--- Send the ID from the first table insert to a hidden text box.
AAIDLabel.Text = sessAAID
AAIDLabel.Visible = True
Else
'The upper table has been inserted to, so insert only into 2nd table by:
'Call only 2nd function
AADetail.AAID = Session.Item("aaid")
AADetailID = InsertAAData(AADetail)
End If
If AADetailID <= 0 Then
InvalidJobCode.Text = "There was an error inserting this record. If this error persists, please contact the ASC B&F"
InvalidJobCode.CssClass = "error"
InvalidJobCode.Visible = True
Else
InvalidJobCode.Text = ""
InvalidJobCode.Visible = False
End If
Else
InvalidJobCode.Text = "THE BUD-ORG, JOB CODE AND BOC\REV ARE NOT THE RIGHT COMBINATION. THE COMBINATION MUST MATCH PLEASE RETRY!!! "
InvalidJobCode.CssClass = "error"
InvalidJobCode.Visible = True
JOB_NUMBER.Focus()
End If
''--- CLEAR DATA FIELDs ---'
''Session.Item("LINE_NO") = Nothing
'Session.Item("BEG_BFY") = Nothing
'Session.Item("ORGN") = Nothing
'Session.Item("JOB_NUMBER") = Nothing
'Session.Item("FUND") = Nothing
'Session.Item("PGMT") = Nothing
'Session.Item("OBJ_REV_SRCE") = Nothing
'Session.Item("HOURS") = Nothing
'Session.Item("PRTG") = Nothing
'Session.Item("LINE_AMOUNT") = Nothing
''Line_No.Text = ""
'Beg_BFY.Text = ""
'Orgn.Text = ""
'Job_Number.Text = ""
'Fund.Text = ""
'PGMT.Text = ""
'Obj_Rev_Srce.Text = ""
'HOURS.Text = ""
'RPTG.Text = ""
Line_Amount.Text = ""
'--- Troubleshooting the error in ID's
'--- Response.Write(Session.Item("aaid"))
'--- Session.Item("aaid") = sessAAID
'--- Response.Write(Session.Item("aaid"))
GridView1.DataBind()
End Sub