[2005] Problems with adding data from a form to a database
This is written in.NET 1.1 and connected to a SQL 2000 Server Database
Code:
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
Code:
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
Code:
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.
Re: [2005] Problems with adding data from a form to a database
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
Re: [2005] Problems with adding data from a form to a database
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.
