[2005] Problems with adding data from a form to a database

[Oas2009]

This is written in.NET 1.1 and connected to a SQL 2000 Server Database

  Private Sub btnSaveAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAdd.Click
   Call AddJob()
   If invalid Then
      lblErrMsg.Text="Correct errors indicated below, then try again."
      lblErrMsg.ForeColor = System.Drawing.Color.Red
      lblErrMsg.Font.Bold = True
            Exit Sub
        End If
        If blnLenError Then
            MsgBox("Job Description cannot exceed 2000 characters.")
            Exit Sub
        Else
            Call ClearForm()
            ddState.SelectedItem.Text = "FL"
        End If
    End Sub

Here is the Clear Form function

Private Sub ClearForm()
        'Reset TextBoxes on form 
        txtJobTitle.Text = ""
        lstJobCatg.Text = ""
        txtJobDesc.Text = ""
        txtCompanyName.Text = ""
        txtEmail.Text = ""
        txtAppURL.Text = ""
        txtAddress.Text = ""
        txtCity.Text = ""
        ddCounty.Text = ""
        txtZip.Text = ""
        txtAreaCode.Text = ""
        txtPhoneNum.Text = ""
        txtFaxNum.Text = ""
        txtNumOpenings.Text = ""
        txtHoursWeek.Text = ""
        txtDeadline.Text = ""
        Dim li As ListItem
        For Each li In chkList.Items
            li.Selected = False
        Next

Here is the add Job Function

Private Sub AddJob()
        invalid = False
        blnLenError = False
        empID = Session("empID")
        strEntryDate = Today()
        strJobTitle = Replace(Trim(txtJobTitle.Text), "'", "#")
        strJobCatg = Trim(lstJobCatg.SelectedItem.Text)
        strJobDesc = Replace(Trim(txtJobDesc.Text), "'", "#")
        strCompany = Replace(Trim(txtCompanyName.Text), "'", "#")
        strEmail = Trim(txtEmail.Text)
        strAppURL = Trim(txtAppURL.Text)
        If Left(strAppURL, 7) = "http://" Then
            Replace(strAppURL, "http://", "")
        End If
        strAddr = Replace(Trim(txtAddress.Text), "'", "#")
        strCity = Replace(Trim(txtCity.Text), "'", "#")
        strCounty = Trim(ddCounty.SelectedItem.Text)
        strState = Trim(ddState.SelectedItem.Text)
        strZip = Trim(txtZip.Text)
        strAreaCode = Trim(txtAreaCode.Text)
        strPhoneNum = Trim(txtPhoneNum.Text)
        strFax = Trim(txtFaxNum.Text)
        If Len(strFax) > 0 Then
            strFax = Trim(txtFaxNum.Text)
        Else
            strFax = ""
        End If
        strOpenings = Trim(txtNumOpenings.Text)
        strHoursWeek = Trim(txtHoursWeek.Text)
        strWorkStatus = Trim(ddWorkStatus.SelectedItem.Text)
        intDisplay = 1
        intDL = 0
        If checkboxDL.Checked Then
            intDL = 1
        Else
            intDL = 0
        End If
        Dim strDeadline = Trim(txtDeadline.Text)
        If Len(strDeadline) > 0 Then
            If Not IsDate(strDeadline) Then
                lblDeadline.ForeColor = System.Drawing.Color.Firebrick
                lblDeadline.Text = "Invalid entry.  Must be formatted as MM/DD/YYYY."
                invalid = True
                Exit Sub
            Else
                lblDeadline.ForeColor = System.Drawing.Color.MidnightBlue
                lblDeadline.Text = "MM/DD/YYYY (Leave blank for Continuous Listing)"
            End If
        Else
            strDeadline = ""
        End If
        Dim intSun = 0, intMon = 0, intTue = 0, intWed = 0, intThu = 0, intFri = 0, intSat = 0
        Dim i
        For i = 0 To 6
            If chkList.Items.Item(i).Selected Then
                Select Case i
                    Case 0
                        intSun = 1
                    Case 1
                        intMon = 1
                    Case 2
                        intTue = 1
                    Case 3
                        intWed = 1
                    Case 4
                        intThu = 1
                    Case 5
                        intFri = 1
                    Case 6
                        intSat = 1
                End Select
            End If
        Next
        If Not Validated() Then
            With lblErrMsg
                .Visible = True
                .Text = "Correct errors indicated below, then try again."
                .Font.Size = FontUnit.Small
                .Font.Bold = True
                .ForeColor = System.Drawing.Color.Red
            End With
            invalid = True
            Exit Sub
        Else
            lblErrMsg.Visible = False
            lblErrMsg.Text = ""
        End If
        strPhoneNum = Left(strPhoneNum, 3) & Right(strPhoneNum, 4)
        strFax = Left(strFax, 3) & Right(strFax, 4)
        Dim strSQL
        strSQL = "Insert into Jobs " & _
                   "(Emp_id, Email, JobTitle," & _
                   " JobCategory, JobDescription, DisplayContactInfo," & _
                   " CompanyName, Address, City, State, Zip," & _
                   " AreaCode, PhoneNum, Fax, ApplicationURL, Openings," & _
                   " HoursWeekly, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday," & _
                   " WorkStatus, DLRequired, AppDeadline, County, EntryDate)" & _
                   " values ('" & empID & "', '" & strEmail & "', '" & strJobTitle & _
                        "', '" & strJobCatg & "', '" & strJobDesc & "', '" & intDisplay & _
                        "', '" & strCompany & "', '" & strAddr & "', '" & strCity & "', '" & strState & "', '" & strZip & _
                        "', '" & strAreaCode & "', '" & strPhoneNum & "', '" & strFax & "', '" & strAppURL & "', '" & strOpenings & _
                        "', '" & strHoursWeek & "', '" & intSun & "', '" & intMon & "', '" & intTue & "', '" & intWed & _
                        "', '" & intThu & "', '" & intFri & "', '" & intSat & _
                        "', '" & strWorkStatus & "', '" & intDL & "', '" & strDeadline & "', '" & strCounty & "', '" & strEntryDate & "')"
        'Response.Write(strSQL & "<br />")
        conn1.Open()
        Dim objCmd = New SqlCommand(strSQL, conn1)
        If Validated() Then
            Try
                objCmd.ExecuteNonQuery()
            Catch ex As SqlException
                MsgBox(ex.Message & " AJ3 Please contact us for assistance.")
            Catch ex As Exception
                If Len(strJobDesc) < 2000 Then
                    blnLenError = True
                Else
                    MsgBox(ex.Message & " AJ4 Please contact us for assistance.")
                End If
            End Try
        End If
        conn1.Close()
    End Sub

The above function is called when the user wants the job is added and the employer wants to add another. After the job is added, this function is called to clear the form and repost a clean & clear version of the AddJob.aspx

My problem is that after the user has selected Save & Go Back or Save & Add Another, the ClearForm , transfers the values of form fields to the values of the objects. After the form has cleared all the fields, the values are still located in the variables waiting to be added to the database. However, during the postback, the form comes back clear and the information stored in the form variables are cleared without being added to the database.

[Help321]

Hi

You haven't really told us anything. Your code contains calls to functions and some label manipulation, that's it.

Try giving some further info, such as the ClearForm function and the AddJob function.

Cheers

[Oas2009]

Thanks, I've added both the ClearForm & the AddJob Function

The error occurs after ClearForm has return control to the Save & Add function call (1st set of code) and is posted back a new form.