oops, I missed that NotYes/No was a boolean and therefore does not have single quotes around it. Just for grins I made a few more changes for you. (like adding a javascript confirm on the delete, this can be found in the Page_Load event)
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
Dim conClsf As IDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("NOITrackingCopy.mdb") & ";")
Sub Page_Load(sender As Object, e As EventArgs)
cmdDelRec.Attributes.Add("onclick", _
"return confirm('Are you sure you want to delete?');")
End Sub
Sub byJobNum_Click(sender As Object, e As EventArgs)
If (txtJobNum.text = "") Then
Exit Sub
Else
Dim strSQL as String = "SELECT RefNum, JobName, [ExcavJob #], PavJobNum, " & _
"UtiJobNum, PermitNum, FiledOnDate, PayTraceNum, [NOTYes/No], NOTDate " & _
"FROM tblJobs " & _
"WHERE [ExcavJob #] = '" & txtJobNum.text & "' OR " & _
"PavJobNum = '" & txtJobNum.text & "' OR " & _
"UtiJobNum = '" & txtJobNum.text & "' ORDER BY RefNum;"
conClsf.Open()
Dim cmdMbrs As OleDbCommand = New OleDbCommand(strSQL,conClsf)
Dim rdrMbrs As IDataReader = cmdMbrs.ExecuteReader(CommandBehavior.CloseConnection)
Try
if rdrMbrs.Read then
txtJobName.text = rdrMbrs.Item("JobName")
txtReferNum.text = rdrMbrs.Item("RefNum")
if ((rdrMbrs.Item("ExcavJob #")) is System.DBNull.Value) then
txtExcavJobNum.text = ""
else
txtExcavJobNum.text = rdrMbrs.Item("ExcavJob #")
end if
if ((rdrMbrs.Item("PavJobNum")) is System.DBNull.Value) then
txtPavJobNum.text = ""
else
txtPavJobNum.text = rdrMbrs.Item("PavJobNum")
end if
if ((rdrMbrs.Item("UtiJobNum")) is System.DBNull.Value) then
txtUtilJobNum.text = ""
else
txtUtilJobNum.text = rdrMbrs.Item("UtiJobNum")
end if
if ((rdrMbrs.Item("PermitNum")) is System.DBNull.Value) then
txtPerNum.text = ""
else
txtPerNum.text = rdrMbrs.Item("PermitNum")
end if
if ((rdrMbrs.Item("FiledOnDate")) is System.DBNull.Value) then
txtNOIDate.text = ""
else
txtNOIDate.text = rdrMbrs.Item("FiledOnDate")
end if
If rdrMbrs.Item("NOTYes/No") = -1 Then chkNOTFiled.Checked = True
if ((rdrMbrs.Item("NOTDate")) is System.DBNull.Value) then
txtNOTDate.text = ""
else
txtNOTDate.text = rdrMbrs.Item("NOTDate")
end if
else
Label1.Text = txtJobNum.text & " Was Not Found"
end if
Catch Exc as Exception
Label1.Text = strSQL & "<br /><br />"
Label1.Text += Exc.ToString()
Finally
cmdMbrs.Dispose
conClsf.Close
txtJobNum.text = ""
txtRefNum.text = ""
cmdNewRec.Enabled = True
cmdSaveRec.Enabled = True
cmdDelRec.Enabled = True
End Try
End If
End Sub
Sub byRefNum_Click(sender As Object, e As EventArgs)
If (txtRefNum.text = "") Then
Exit Sub
Else
byRefNum(txtRefNum.text)
End If
cmdNewRec.Enabled = True
cmdSaveRec.Enabled = True
cmdDelRec.Enabled = True
End Sub
Sub byRefNum(ByVal RefNum As Single)
Dim strSQL As String = "SELECT RefNum, JobName, [ExcavJob #], PavJobNum, UtiJobNum, " & _
"PermitNum, FiledOnDate, PayTraceNum, [NOTYes/No], NOTDate " & _
"FROM tblJobs " & _
"WHERE RefNum = " & RefNum & " ORDER BY RefNum;"
conClsf.Open()
Dim cmdMbrs As OleDbCommand = New OleDbCommand(strSQL,conClsf)
Dim rdrMbrs As IDataReader = cmdMbrs.ExecuteReader(CommandBehavior.CloseConnection)
Try
if rdrMbrs.Read then
txtJobName.text = rdrMbrs.Item("JobName")
txtReferNum.text = rdrMbrs.Item("RefNum")
if ((rdrMbrs.Item("ExcavJob #")) is System.DBNull.Value) then
txtExcavJobNum.text = ""
else
txtExcavJobNum.text = rdrMbrs.Item("ExcavJob #")
end if
if ((rdrMbrs.Item("PavJobNum")) is System.DBNull.Value) then
txtPavJobNum.text = ""
else
txtPavJobNum.text = rdrMbrs.Item("PavJobNum")
end if
if ((rdrMbrs.Item("UtiJobNum")) is System.DBNull.Value) then
txtUtilJobNum.text = ""
else
txtUtilJobNum.text = rdrMbrs.Item("UtiJobNum")
end if
if ((rdrMbrs.Item("PermitNum")) is System.DBNull.Value) then
txtPerNum.text = ""
else
txtPerNum.text = rdrMbrs.Item("PermitNum")
end if
if ((rdrMbrs.Item("FiledOnDate")) is System.DBNull.Value) then
txtNOIDate.text = ""
else
txtNOIDate.text = rdrMbrs.Item("FiledOnDate")
end if
If rdrMbrs.Item("NOTYes/No") = -1 Then chkNOTFiled.Checked = True
if ((rdrMbrs.Item("NOTDate")) is System.DBNull.Value) then
txtNOTDate.text = ""
else
txtNOTDate.text = rdrMbrs.Item("NOTDate")
end if
else
Label1.Text = RefNum & " Was Not Found"
end if
Catch Exc as Exception
Label1.Text = strSQL & "<br /><br />"
Label1.Text += Exc.ToString()
Finally
cmdMbrs.Dispose
conClsf.Close
txtJobNum.text = ""
txtRefNum.text = ""
End Try
End Sub
Function MyInsert(ByVal RefNum As Single, ByVal JobName As String, ByVal ExcavJobNum As String, ByVal PavJobNum As String, ByVal UtiJobNum As String) As Integer
Dim SQL As String = "INSERT INTO tblJobs (RefNum, JobName, [ExcavJob #], PavJobNum, UtiJobNum) " & _
"VALUES (" & RefNum & ",'" & JobName & "','" & ExcavJobNum & "','" & PavJobNum & "','" & UtiJobNum & "')"
Dim dbCommand As IDbCommand = New OleDbCommand(SQL, conClsf)
Dim rowsAffected As Integer = 0
conClsf.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Catch Exc as Exception
Label1.Text = SQL & "<br /><br />"
Label1.Text += Exc.ToString()
Finally
conClsf.Close
End Try
Return rowsAffected
End Function
Sub NewRec_Click(sender As Object, e As EventArgs)
If MyInsert(txtReferNum.text, txtJobName.text, txtExcavJobNum.text, txtPavJobNum.text, txtUtilJobNum.text) > 0 Then
Label1.Text = "Success"
End If
End Sub
Sub SaveRec_Click(sender As Object, e As EventArgs)
If UpdateRec(txtReferNum.text, txtExcavJobNum.text, txtPavJobNum.text, txtUtilJobNum.text, txtJobName.text, txtPerNum.text, txtNOIDate.text, chkNOTFiled.Checked, txtNOTDate.text) > 0 Then
Label1.Text = txtReferNum.text & " has been updated"
byRefNum(txtReferNum.text)
Else
Label1.Text = txtReferNum.text & " has NOT been updated"
End If
End Sub
Function UpdateRec(ByVal refNum As Integer, ByVal excavJobNum As String, ByVal pavJobNum As String, ByVal utiJobNum As String, ByVal jobName As String, ByVal permitNum As String, ByVal filedOnDate As Date, ByVal nOTYesNo As Boolean, ByVal nOTDate As Date) As Integer
Dim SQL As String = "UPDATE tblJobs SET FiledOnDate = '" & FiledOnDate & "', UtiJobNum = '" & UtiJobNum & "', " & _
"NOTDate = '" & NOTDate & "', [NOTYes/No] = " & NOTYesNo & ", JobName = '" & JobName & "', "& _
"PavJobNum = '" & PavJobNum & "', PermitNum = '" & PermitNum & "', [ExcavJob #] = '" & ExcavJobNum & "'"& _
"WHERE (RefNum = " & refNum & ")"
Dim dbCommand As IDbCommand = New OleDbCommand(SQL, conClsf)
Dim rowsAffected As Integer = 0
conClsf.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Catch Exc as Exception
Label1.Text = SQL & "<br /><br />"
Label1.Text += Exc.ToString()
Finally
conClsf.Close
End Try
Return rowsAffected
End Function
Sub DeleteRec_Click(sender As Object, e As EventArgs)
If DeleteRec(txtReferNum.text) > 0 Then
Label1.Text = txtReferNum.text & " has been deleted"
txtRefNum.text = ""
End If
End Sub
Function DeleteRec(ByVal refNum As Integer) As Integer
Dim SQL As String = "DELETE FROM tblJobs WHERE (RefNum = " & RefNum & ")"
Dim dbCommand As IDbCommand = New OleDbCommand(SQL, conClsf)
Dim rowsAffected As Integer = 0
conClsf.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Catch Exc as Exception
Label1.Text = SQL & "<br /><br />"
Label1.Text += Exc.ToString()
Finally
conClsf.Close
End Try
Return rowsAffected
End Function
</script>
<html>
<head>
<title>NOI Tracking</title> <style type="text/css">.style1 {
FONT-WEIGHT: bold; FONT-SIZE: x-large
}
</style>
</head>
<body>
<form runat="server">
<table width="200" border="1">
<tbody>
<tr>
<td>
<div align="center"><span class="style1">NOI Tracking Page </span>
</div>
</td>
</tr>
<tr>
<td>
<table width="435" align="center" border="0">
<tbody>
<tr>
<td width="106">
<asp:TextBox id="txtJobNum" runat="server" MaxLength="4" TextMode="SingleLine" Width="50" Wrap="false"></asp:TextBox>
</td>
<td width="107">
<asp:Button id="SearchbyJobNum" onclick="byJobNum_Click" runat="server" Text="Search by Job Number"></asp:Button>
</td>
<td width="106">
<asp:TextBox id="txtRefNum" runat="server" MaxLength="8" TextMode="SingleLine" Width="50" Wrap="false"></asp:TextBox>
</td>
<td width="107">
<asp:Button id="SearchbyRefNum" onclick="byRefNum_Click" runat="server" Text="Search by Ref. Number"></asp:Button>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
<table width="592" align="center" border="0">
<tbody>
<tr>
<td width="586">
<asp:TextBox id="txtJobName" runat="server" Width="300" Wrap="false"></asp:TextBox>
Job Name
<asp:TextBox id="txtReferNum" runat="server" TextMode="SingleLine" Width="50" Wrap="false"></asp:TextBox>
Ref. #
</td>
</tr>
<tr>
<td>
<asp:TextBox id="txtExcavJobNum" runat="server" TextMode="SingleLine" Width="50" Wrap="false"></asp:TextBox>
Excav. Job #
<asp:TextBox id="txtPavJobNum" runat="server" Width="50" Wrap="false"></asp:TextBox>
Pav. Job #
<asp:TextBox id="txtUtilJobNum" runat="server" Width="50" Wrap="false"></asp:TextBox>
Util. Job #
</td>
</tr>
<tr>
<td>
<asp:TextBox id="txtPerNum" runat="server" TextMode="SingleLine" Width="180" Wrap="false"></asp:TextBox>
TXR Permit #
<asp:TextBox id="txtNOIDate" runat="server" TextMode="SingleLine" Width="100" Wrap="false"></asp:TextBox>
NOI Filed-On Date
</td>
</tr>
<tr>
<td>
<hr />
</td>
</tr>
<tr>
<td>
NOT Filed?
<asp:CheckBox id="chkNOTFiled" runat="server"></asp:CheckBox>
<asp:TextBox id="txtNOTDate" runat="server" TextMode="SingleLine" Width="100" Wrap="false"></asp:TextBox>
NOT Filed-On Date
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td>
<asp:Button id="cmdNewRec" onclick="NewRec_Click" runat="server" Text="Create New Record" Enabled="False"></asp:Button>
<asp:Button id="cmdSaveRec" onclick="SaveRec_Click" runat="server" Text="Save / Update Record" Enabled="False"></asp:Button>
<asp:Button id="cmdDelRec" onclick="DeleteRec_Click" runat="server" Text="Delete Record" Enabled="False"></asp:Button>
</td>
</tr>
</tbody>
</table>
<p>
<asp:Label id="Label1" runat="server"></asp:Label>
</p>
<p>
</p>
</form>
</body>
</html>