|
 |
access_asp thread: Data type mismatch in criteria expression
Message #1 by "Tadd Brown" <tadese98@h...> on Wed, 11 Dec 2002 20:19:59
|
|
I keep getting Data type mismatch in criteria expression when I change the
data type for DateApproved field in the Access db from text to Date/Time.
I have serval asp pages. When I enter new information from the asp page
to the database I don't get error, but I get error when I try to
update/edit existing record. I have included two of the asp pages. I
appreciate your help.
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
Here is the the codes:
udateinclude.asp
------------------------------------------------------------------------
<%
'code to execute if update button is clicked
if Request.Form("submit") = "Update" then
strCategory = trim(Request.Form("Category"))
strManufacturer = trim(Request.Form("Manufacturer"))
strModel = trim(Request.Form("Model"))
strTestStatus = trim(Request.Form("TestStatus"))
strVendor = trim(Request.Form("Vendor"))
strCertifiedBy = trim(Request.Form("CertifiedBy"))
strDateApproved = trim(Request.Form("DateApproved"))
strOPFAC = trim(Request.Form("OPFAC"))
strLCCBRequestNbr = trim(Request.Form("LCCBRequestNbr"))
strCost = trim(Request.Form("Cost"))
strNotes = trim(Request.Form("Notes"))
if strErrMsg = "" then
if strCategory = "none" then
strErrMsg = "You must choose a CATEGORY"
elseif strTestStatus = "none" then
strErrMsg = "You must choose a TEST STATUS"
elseif strManufacturer = "" or len
(strManufacturer) < 2 then
strErrMsg = "You must enter a Manufacturer"
elseif strModel = "" or len(strModel) < 2 then
strErrMsg = "You must enter a Model"
end if
end if
'if test status is approved, approved date should be
populated
if strErrMsg = "" and strTestStatus = "1" then
if strDateApproved = "" then
strErrMsg = "The status for this item is
APPROVED, but you did not enter a <em>date approved</em>. <br> Enter an
estimated DATE APPROVED or change status"
elseif not isdate(strDateApproved) then
strErrMsg = "If this item has APPROVED
status, please enter a valid date <br>for DATE APPROVED"
end if
end if
'if test status is not approved, then there should be no
approved date
if strErrMsg = "" and strTestStatus <> "1" then
if strDateApproved <> "" then
strErrMsg = "You entered an APPROVED DATE
but the TEST STATUS is not approved"
end if
end if
'if approx cost is entered, make sure numeric
if strErrMsg = "" and strCost <> "" then
if not isnumeric(strCost) then
strErrMsg = "APPROX COST must be numeric"
end if
end if
'validate OPFAC if entered, should be numeric 5 digit
if strErrMsg = "" and strOPFAC <> "" then
for i = 1 to len(strOPFAC)
if instr(1,"1234567890",mid
(strOPFAC,i,1),vbtextcompare) = 0 then
strErrMsg = "OPFAC should only be
the 5 digit Unit OPFAC<br>(e.g. 12115)"
end if
next
end if
If strErrMsg = "" then
if strCost = "" then
strCost = 0
End if
strSQL = "Update Hardware SET " & _
"Category = " & vbQuote & strCategory &
vbQuote & _
", Manufacturer = " & vbQuote &
strManufacturer & vbQuote & _
", Model = " & vbQuote & strModel &
vbQuote & _
", TestStatus = " & vbQuote &
strTestStatus & vbQuote & _
", Vendor = " & vbQuote & strVendor &
vbQuote & _
", CertifiedBy = " & vbQuote &
strCertifiedBy & vbQuote & _
", DateApproved = " & vbQuote &
strDateApproved & vbQuote & _
", OPFAC = " & vbQuote & strOPFAC &
vbQuote & _
", LCCBRequestNbr = " & vbQuote &
strLCCBRequestNbr & vbQuote & _
", Cost = " & vbQuote & strCost & vbQuote
& _
", Notes = " & vbQuote & strNotes &
vbQuote & _
", LastUpdate = #" & date() & "#" & _
" WHERE HardwareRecordID = " & lngRecordID
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
objComm.Execute
set objComm = Nothing
strConfirm = "Your record has been updated"
end if
end if
'code to execute if "Go" button from displayupdate.asp is clicked
if Request.Form("submit") = "Go" then
if lngPickOne = "" then
Response.Write "<h3 align='center'><font
color='#ff0000'>You must choose a " & _
"Record, Click <font
color='#000000'>[Back]" & _
"</font> on your browser
to correct the problem</font></h3>"
Response.End
End if
lngPOCID = Request.Form("POCInfo")
set objComm = server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM Hardware WHERE HardwareRecordID
= " & lngPickOne
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
set objComm = Nothing
strCategory = objRS("Category")
strManufacturer = objRS("Manufacturer")
strModel = objRS("Model")
strTestStatus = objRS("TestStatus")
strVendor = objRS("Vendor")
strCertifiedBy = objRS("CertifiedBy")
'If to_dateobjRS("DateApproved")) then
If isDate(objRS("DateApproved")) then
strDateApproved = formatdatetime(objRS
("DateApproved"),vbshortdate)
Else
strDateApproved = objRS("DateApproved")
End if
strOPFAC = objRS("OPFAC")
strLCCBRequestNbr = objRS("LCCBRequestNbr")
strCost = objRS("Cost")
strNotes = objRS("Notes")
strLastUpdate = objRS("LastUpdate")
objRS.Close
set objRS = Nothing
End if
%>
<html>
<head>
<title></title>
</head>
<body>
<%
if Request.Form("submit") = "Go" then
set objComm = Server.CreateObject("ADODB.Command")
strSQL = "Select * FROM POC WHERE POCID = " & lngPOCID
objComm.ActiveConnection = strConnect
objComm.CommandText = strSQL
objComm.CommandType = adCmdText
set objRS = objComm.Execute
set objComm = nothing
Response.Write "<h3 align='center'>POC Name: <font
color='666666'>" & objRS("FirstName") & " " & objRS("LastName") & _
"</font><br>Phone: <font
color='666666'>" & objRS("POCPhone") & "</font> Ext: <font
color='666666'>" & objRS("POCExt") & _
"</font><br>Email: <font
color='666666'>" & objRS("POCEmail") & "</font><br>Unit: <font
color='666666'>" & objRS("POCUnit") & _
"</font> Location:
<font color='666666'>" & objRS("POCLocation") & "</font></h3>"
objRS.Close
set objRS = Nothing
end if
%>
<% 'display error message if strErrMsg variable has data
if strErrMsg <> "" then
%>
<table align="center" bgcolor="#ffffff" width="100%" border="1">
<div align="center"><font
color="#ff0000"><b><small>** ERROR **</small></b></div>
<tr>
<td align="center">
<%="<small><b><font
color='#ff0000'>" & strErrMsg & "</font></b></small>"%>
</td>
</tr>
</table>
<%elseif strConfirm <> "" then%>
<table align="center" bgcolor="#bbbbbb" width="100%" border="1">
<tr>
<td align="center">
<%="<b>" & strConfirm & "</b>"%>
<br>
<a href="selectUpdate.asp"><font
size=4><u>Update Another Record</u></font></a>
<br>
<a
href="LCCBHardwareMain.asp"><font size=4><u>Back to Main
Menu</u></font></a>
<%Response.End%>
</td>
</tr>
</table>
<%end if%>
<form action="update.asp" method="post" name="frmNew">
<br>
<table border="5" name="TableForHardwareInfo" width="100%">
<tr>
<td>
<h4 align="center">Update Record:</h4>
<%
Response.Write "<div align='center'><small>This record was Last
Updated on <font color='000000'><b>" & strLastUpdate
& "</b></font></small></div>"
%>
<table align="center" bgcolor="#bbbbbb" width="80%"
border="1">
<tr>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Category</b></font></small>
<br>
<%
'This code block creates recordset w/command object and populates
drop down list with category table contents
'declare variables to hold Category and a variable(vaField already
defined above) to iterate thru the recordset fields collection
dim strCat
strCat = "<option value='none'>Choose One</option>"
strSql = "Select * from Category;"
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
'concatenate the Cetgory records for users to choose from
and create the <option tags> with CategoryID value
do while not objRS.EOF
for each vaField in objRS.Fields
select case vaField.Name
case "CategoryID"
'checks to see the value
in case of error, same value will be displayed on page
strSelected = ""
if strCategory <> "none"
then
if clng
(strCategory) = vaField.Value then strSelected = "selected"
end if
'begins concatenation of
option and inserts the ID from the database in the value attribute
strCat = strCat & "<option
value='" & vaField.Value & "' " & strSelected & ">"
case "Category"
strCat = strCat &
vaField.Value
end select
next
strCat = strCat & "</option>"
objRS.MoveNext
loop
objRS.Close
set objRS = Nothing
set objComm = Nothing
Response.Write "<select style='width: 100%'
name='Category'>" & strCat & "</select>"
%>
</td>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Manufacturer</b></font></small>
<br>
<input maxlength="50" type="text"
name="Manufacturer" value="<%=strManufacturer%>"></input>
</td>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Model</b></font></small>
<br>
<input maxlength="50" type="text"
name="Model" value="<%=strModel%>"></input>
</td>
</tr>
<tr>
<td align="center">
<small><font
color="#ff0000"><b>Test Status</b></font></small>
<br>
<%
'This code block creates recordset w/command object and populates
drop down list with TestStatus table contents
'declare variables to hold TestStatus and a variable(vaField
already defined above) to iterate thru the recordset fields collection
dim strTest, blnTemp
strTest = "<option value='none'>Choose One</option>"
strSql = "Select * from TestStatus;"
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
'concatenate the Test Status records for users to choose
from and create the <option tags> with TestStatusID value
do while not objRS.EOF
for each vaField in objRS.Fields
select case vaField.Name
case "TestStatusID"
blnTemp = false
if vaField.Value <> 5 then
'checks to see the
value in case of error, same value will be displayed on page
strSelected = ""
if strTestStatus
<> "none" then
if clng
(strTestStatus) = vaField.Value then strSelected = "selected"
end if
'begins
concatenation of option and inserts the ID from the database in the value
attribute
strTest = strTest
& "<option value='" & vaField.Value & "' " & strSelected & ">"
else
blnTemp = true
end if
case "TestStatus"
blnTemp = false
if vaField.Value <> "All
Except Approved" then
strTest = strTest
& vaField.Value
else
blnTemp = true
end if
end select
next
if not blnTemp then
strTest = strTest & "</option>"
end if
objRS.MoveNext
loop
objRS.Close
set objRS = Nothing
set objComm = Nothing
Response.Write "<select style='width: 100%'
name='TestStatus'>" & strTest & "</select>"
%>
---------------------------------------------------------------------------
newinclude.asp
<%
'include file for a New HardWare Entry. this is the page where
users can
'enter new information.
'declare variables for SQL strings, Command object, recordset
object, and connection string, and other general variables
dim strSQL, objComm, objRS, strConnect, strSelected, strErrMsg, i,
j, Nbr
dim strMethod, vbQuote, strConfirm, strConfirmName
'declare variables for POC table/form
dim strPOCNameFirst, strPOCNameLast, strPOCPhone, strPOCExt,
strPOCEmail, strPOCExists, strPOCType
dim strPOCUnit, strPOCLocation
'declare variables for hardware entry table/form
dim strCategory, strManufacturer,strModel, strTestStatus,
strVendor, strCertifiedBy, strDateApproved
dim strOPFAC, strLCCBRequestNbr, strCost, strNotes
'set strMethod to empty, if user clicks submit, it will be assigned the
value "post" so we can use
'it later to tell other parts of the page that this is a post request
strMethod = ""
' if the user navigates to this page the first time this code is not
executed, if the user has
' clicked the submit button begin collecting the data from the form
if Request.ServerVariables("request_method") = "POST" then
'here we assign the data from the from on the page to variables. we will
use the variables to
'perform validation and to create an insert query to add the new records
to the database
strPOCExists = trim(Request.Form("POCExists"))
strPOCNameFirst = trim(Request.Form("POCNameFirst"))
strPOCNameLast = trim(Request.Form("POCNameLast"))
strPOCPhone = trim(Request.Form("POCPhone"))
strPOCExt = trim(Request.Form("POCExt"))
strPOCEmail = trim(Request.Form("POCEmail"))
strPOCUnit = trim(Request.Form("POCUnit"))
strPOCLocation = trim(Request.Form("POCLocation"))
strCategory = trim(Request.Form("Category"))
strManufacturer = trim(Request.Form("Manufacturer"))
strModel = trim(Request.Form("Model"))
strTestStatus = trim(Request.Form("TestStatus"))
strVendor = trim(Request.Form("Vendor"))
strCertifiedBy = trim(Request.Form("CertifiedBy"))
'strDateApproved = trim(Request.Form("DateApproved"))
strDateApproved = trim(Request.Form("DateApproved"))
strOPFAC = trim(Request.Form("OPFAC"))
strLCCBRequestNbr = trim(Request.Form("LCCBRequestNbr"))
strCost = trim(Request.Form("Cost"))
strNotes = trim(Request.Form("Notes"))
'initialize the error message variable, set method equal to post,
initialize vbQuote constant
'the vbQuote constant will make it easier to concatenate the quote(")
character in our
'SQL queries
strErrMsg = ""
'we need to set this value to post so we can use it later to tell the code
how the user accessed this page
strMethod = "post"
'set the vbQuote cariable to equal chr(34) which is the quote(")
character. we use this to simplify
'our SQL concatenation
vbQuote = chr(34)
'now we perform validation of fields and data and set up some variables
related to database
'queries(e.g. strPOCType)
'see if user entered new AND existing POC info, if so we prompt the user
to make a correction
if strPOCExists <> "none" and (strPOCNameFirst <> "" or
strPOCNameLast <> "" _
or strPOCPhone <> "" or strPOCExt <> "" or
strPOCEmail <> "") then
strErrMsg = "You chose an existing LCCB
POC <em>and</em> entered new info - please select existing <em>or</em>
enter a new record. " & _
"<br>You can
<em>update</em> existing POC info from the <a
href='LCCBHardwareMain.asp'><font color='333399'><u>main
menu</u></font></a>."
end if
'determine is POC is new or existing, and assign the strPOCType variable a
value that let's the program
'know if the POC record is new or existing. if it is a new POC we will
need to insert a new POC record
'later and then use the new POCID in the insert query for the harware
record
'if it is existing POC, we will use the POCID in the insert query
if strErrMsg = "" then
if strPOCExists <> "none" then
strPOCType = "POCExists"
else
strPOCType = "NewPOC"
end if
end if
'if user is entering a new POC record then make sure they enter all
required data
'validate first name
if strErrMsg = "" and strPOCType = "NewPOC" then
if strPOCNameFirst = "" or len(strPOCNameFirst) <
2 then
strErrMsg = "Invalid First Name, Please
enter a valid first name"
else
for i = 1 to len(strPOCNameFirst)
if instr
(1, "abcdefghijklmnopqrstuvwxyz' ",mid(strPOCNameFirst,i,1),
vbtextcompare) = 0 then
strErrMsg = "Invalid First
Name, Please enter a valid first name"
end if
next
end if
end if
'validate last name
if strErrMsg = "" and strPOCType = "NewPOC" then
if strPOCNameLast = "" or len(strPOCNameLast) < 2
then
strErrMsg = "Invalid Last Name, Please
enter a valid last name"
else
for i = 1 to len(strPOCNameLast)
if instr
(1, "abcdefghijklmnopqrstuvwxyz' ",mid(strPOCNameLast,i,1), vbtextcompare)
= 0 then
strErrMsg = "Invalid Last
Name, Please enter a valid last name"
end if
next
end if
end if
'validate phone number
if strErrMsg = "" and strPOCType = "NewPOC" then
if len(strPOCPhone) <> 0 then
for i = 1 to len(strPOCPhone)
if instr(1,".1234567890-() ",mid
(strPOCPhone,i,1),vbtextcompare) = 0 then
strErrMsg = "You entered
an invalid telephone number, Please re-enter the number.<br>(e.g. (301)
555-1234, 301-555-1234, 301.555.1234, 3015551234)"
end if
next
for j = 1 to len(strPOCPhone)
if instr(1,"1234567890",mid
(strPOCPhone,j,1),vbtextcompare) <> 0 then
Nbr = Nbr + 1
end if
next
if Nbr <> 10 then
strErrMsg = "You entered an
invalid Telephone number, Number of digits should total 10."
end if
else
strErrMsg = "You must enter a Telephone
number."
end if
end if
'validate extension
if strErrMsg = "" and strPOCExt <> "" then
if not isnumeric(strPOCExt) then
strErrMsg = "Invalid extension. Extension
must be numeric."
end if
end if
'validate email
if strErrMsg = "" and strPOCType = "NewPOC" then
if strPOCEmail = ""then
strErrMsg = "You must enter an email
address"
elseif instr(1,strPOCEmail, "@", vbtextcompare) =
0 then
strErrMsg = "Invalid email address -
Please re-enter"
elseif instr(1,strPOCEmail, "@", vbtextcompare) =
1 then
strErrMsg = "Invalid email address -
Please re-enter"
elseif instr
(1,strPOCEmail,".uscg.mil",vbtextcompare) = 0 then
strErrMsg = "Not a valid Coast Gaurd Email
address - Please re-enter"
end if
end if
'validate unit
if strErrMsg = "" and strPOCType = "NewPOC" then
if strPOCUnit = "" then
strErrMsg = "You must enter a Unit"
elseif len(strPOCUnit) < 2 then
strErrMsg = "Please enter a valid Unit"
else
'do nothing
end if
end if
'validate location
if strErrMsg = "" and strPOCType = "NewPOC" then
if strPOCLocation = "" then
strErrMsg = "You must enter a Location"
elseif len(strPOCLocation) < 5 then
strErrMsg = "Please enter a valid Location"
else
'do nothing
end if
end if
'if strErrMsg is empty after validating the POC information then we begin
the validation of the
'hardware record
'make sure Category, test status, manufacturer, and model are all
entered. they are all required
'for the hardware record
if strErrMsg = "" then
if strCategory = "none" then
strErrMsg = "You must choose a CATEGORY
under product info"
elseif strTestStatus = "none" then
strErrMsg = "You must choose a TEST STATUS
under product info"
elseif strManufacturer = "" or len
(strManufacturer) < 2 then
strErrMsg = "You must enter a Manufacturer
under product info"
elseif strModel = "" or len(strModel) < 2 then
strErrMsg = "You must enter a Model under
product info"
end if
end if
'if test status is approved, approved date should be
populated
if strErrMsg = "" and strTestStatus = "1" then
if strDateApproved = "" then
strErrMsg = "The status for this item is
APPROVED, but you did not enter a <em>date approved</em>. <br> Enter an
estimated DATE APPROVED or change status"
elseif not isdate(strDateApproved) then
strErrMsg = "If this item has APPROVED
status, please enter a valid date (date format)<br>for DATE APPROVED"
end if
end if
'if test status is not approved, then there should be no
approved date
if strErrMsg = "" and strTestStatus <> "1" then
if strDateApproved <> "" then
strErrMsg = "You entered an APPROVED DATE
but the TEST STATUS is not approved"
end if
end if
'if approx cost is entered, make sure numeric
if strErrMsg = "" and strCost <> "" then
if not isnumeric(strCost) then
strErrMsg = "APPROX COST must be numeric"
end if
end if
'validate OPFAC if entered, should be numeric 5 digit
if strErrMsg = "" and strOPFAC <> "" then
for i = 1 to len(strOPFAC)
if instr(1,"1234567890",mid
(strOPFAC,i,1),vbtextcompare) = 0 then
strErrMsg = "OPFAC should only be
the 5 digit Unit OPFAC<br>(e.g. 12115)"
end if
next
end if
end if
%>
<%'************************************************************************
***********
' the following include file contains the database connection info. it is
used for all
'pages that use a database connection. if the database info is changed,
the changes can be
'made to one file and all of the files that use the database will be
updated automatically.
'**************************************************************************
*****************
%>
<!-- #include file="datastore.asp" -->
<%
'if no errors then Insert new record for hardware and new
record for POC if strPOCType is not "Exists"
if strErrMsg = "" then
'if POC type is "exists" then insert new hardware
record with current POC id,
' otherwise insert POC record, then insert
hardware record with the new POCID
select case strPOCType
case "POCExists"
'this is the code that is executed in the
case of the user selecting an existing POC
'so we dont have to insert a new POC
record, the POCID is stored in the strPOCExists
'variable that we obtained from the
Request.Form("POCExists") object above, we use
'the value to assign a POC to the hardware
record
'an empty string will cause an error for a
numeric datatype in access so if it is
'empty, we assign the value zero(0) to the
cost
if strCost = "" then
strCost = 0
else
strCost = cdbl(strCost)
end if
'here we build our SQL string using the
variables we used to hold the values from the form
'and using the vbQuote constant we created
to simplify concatenation, we dont need to include
'the LastUpdate field for a new record
because it will insert the current date/time by default
strSQL = "INSERT INTO Hardware " &
_
"(Category, " & _
"Manufacturer, "
& _
"Model, " & _
"TestStatus, " & _
"Vendor, " & _
"CertifiedBy, " &
_
"DateApproved, "
& _
"OPFAC, " & _
"LCCBRequestNbr, " & _
"Cost, " & _
"POCID, " & _
"Notes) " & _
"VALUES (" & _
strCategory
& ", " & _
vbQuote &
strManufacturer & vbQuote & ", " & _
vbQuote &
strModel & vbQuote & ", " & _
vbQuote &
strTestStatus & vbQuote & ", " & _
vbQuote &
strVendor & vbQuote & ", " & _
vbQuote &
strCertifiedBy & vbQuote & ", " & _
vbQuote &
strDateApproved & vbQuote & ", " & _
vbQuote &
strOPFAC & vbQuote & ", " & _
vbQuote &
strLCCBRequestNbr & vbQuote & ", " & _
strCost & ", " & _
strPOCExists
& ", " & _
vbQuote &
strNotes & vbQuote & ") "
'create a command object to use for executing the
query
set objComm = Server.CreateObject
("ADODB.Command")
'here we assign the value of strConnect to the
activeconnection property of the command object.
'strConnect is given a value in the datastore.asp
include file above
objComm.ActiveConnection =
strConnect
'the commandtype property is adCmdText(for SQL
strings), the adCmdText constant is defined in the type library
'we included in the metadata tag above
objComm.CommandType = adCmdText
'assign the value of strSQL to the commandtext
property of the command object
objComm.CommandText = strSQL
'execute the command
objComm.Execute
'here we change the value of strSQL to another
query to create a recordset so we can send
'a confirmation message to the browser after we
add the record. this will only be sent if
'there are no errors(this is true after the run-
time error handlers are added to the program
'which is the last thing we will do)
'then we close and destroy the recordset and
command object to free up server resources
strSQL = "SELECT FirstName FROM
POC WHERE POCID =" & strPOCExists
objComm.CommandText = strSQL
set objRS = objComm.Execute
strConfirmName = objRS("FirstName")
objRS.Close
set objRS = Nothing
set objComm = Nothing
'this confirmation message will be displayed if
there are no errors found from our validation
'or from the run-time error handlers
strConfirm = "Your record has been
added to the database " & strConfirmName
case "NewPOC"
'this code will be executed if the user is
entering a New POC record. first we need to
'insert the new POC record, then we need to query
the POC table and get the POCID value
'of the new POC record, then we use the new POC
value in the insert query for the new hardware
'record
'an empty string will cause an error for a
numeric datatype in access so if it is
'empty, we assign the value zero(0) to the
cost
if strCost = "" then
strCost = 0
end if
'we assign a SQL string to strSQL that will insert
a new POC record
strSQL = "INSERT INTO POC " & _
"(FirstName, " & _
"LastName, " & _
"POCPhone, " & _
"POCExt, " & _
"POCEmail, " & _
"POCUnit, " & _
"POCLocation) "
& _
"VALUES (" & _
vbQuote &
strPOCNameFirst & vbQuote & ", " & _
vbQuote &
strPOCNameLast & vbQuote & ", " & _
vbQuote &
strPOCPhone & vbQuote & ", " & _
vbQuote &
strPOCExt & vbQuote & ", " & _
vbQuote &
strPOCEmail & vbQuote & ", " & _
vbQuote &
strPOCUnit & vbQuote & ", " & _
vbQuote &
strPOCLocation & vbQuote & ") "
'create a command object
set objComm = Server.CreateObject
("ADODB.Command")
'here we assign the value of strConnect to the
activeconnection property of the command object.
'strConnect is given a value in the datastore.asp
include file above
objComm.ActiveConnection =
strConnect
'the commandtype property is adCmdText(for SQL
strings), the adCmdText constant is defined in the type library
'we included in the metadata tag above
objComm.CommandType = adCmdText
'assign the value of strSQL to the commandtext
property of the command object
objComm.CommandText = strSQL
'execute the command
objComm.Execute
'now we change the SQL string to select the new
POCID that we just added to the database
strSQL = "SELECT POCID FROM POC
ORDER BY POCID Desc"
'now we assign the new SQL to the commandtext
property and execute the command
'we put the value of the largest POCID in the
variable strPOCExists, then we close
'and destroy the command and recordset objects.
we use strPOCExists in the insert
'query for the new hardware record
objComm.CommandText = strSQL
set objRS = objComm.Execute
strPOCExists = objRS("POCID")
objRS.Close
set objRS = Nothing
set objComm = Nothing
'this is the insert query for the new hardware
record
strSQL = "INSERT INTO Hardware " &
_
"(Category, " & _
"Manufacturer, "
& _
"Model, " & _
"TestStatus, " & _
"Vendor, " & _
"CertifiedBy, " &
_
"DateApproved, "
& _
"OPFAC, " & _
"LCCBRequestNbr, " & _
"Cost, " & _
"POCID, " & _
"Notes) " & _
"VALUES (" & _
strCategory
& ", " & _
vbQuote &
strManufacturer & vbQuote & ", " & _
vbQuote &
strModel & vbQuote & ", " & _
vbQuote &
strTestStatus & vbQuote & ", " & _
vbQuote &
strVendor & vbQuote & ", " & _
vbQuote &
strCertifiedBy & vbQuote & ", " & _
vbQuote &
strDateApproved & vbQuote & ", " & _
vbQuote &
strOPFAC & vbQuote & ", " & _
vbQuote &
strLCCBRequestNbr & vbQuote & ", " & _
strCost & ", " & _
strPOCExists
& ", " & _
vbQuote &
strNotes & vbQuote & ") "
'see explanation for these commands above
set objComm = Server.CreateObject
("ADODB.Command")
objComm.ActiveConnection =
strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
objComm.Execute
'the following code is used for a confirmation
message for the user, see further explanation
'above under 'CASE "POCExists"'
strSQL = "SELECT FirstName FROM
POC WHERE POCID =" & strPOCExists
objComm.CommandText = strSQL
set objRS = objComm.Execute
strConfirmName = objRS("FirstName")
objRS.Close
set objRS = Nothing
set objComm = Nothing
strConfirm = "Your record has been
added to the database " & strConfirmName
end select
end if
%>
<%'************************************************************************
***********
' the following metadata tag includes the ADO type library. including
this type library prevents
'us from having to define the ADO constants that are commonly used with
ADO and ASP
'**************************************************************************
*****************
%>
<!--METADATA TYPE="typelib"
FILE="c:\program files\common
files\system\ado\msado15.dll" -->
<html>
<head>
<title></title>
</head>
<body>
<br>
<h3 align="center"><font color="#aaaaaa">New Record</font></h3>
<%
'if validation failed and strErrMsg contains data then
'display error message and the HTML that follows this if...then clause
if strErrMsg <> "" then
%>
<table align="center" bgcolor="#cccccc" width="100%" border="1">
<div align="center"><font
color="#ff0000"><b><small>** ERROR **</small></b></font></div>
<tr>
<td align="center">
<%="<small><b><font
color='#ff0000'>" & strErrMsg & "</font></b></small>"%>
</td>
</tr>
</table>
<%
'if there is no eror message and the user clicked submit and there is a
confirmation message in the
'strConfirm variable then display this HTML
elseif strConfirm <> "" then%>
<table align="center" bgcolor="#bbbbbb" width="100%" border="1">
<tr>
<td align="center">
<%="<b>" & strConfirm & "</b>"%>
<br>
<a href="new.asp"><font
size=4><u>Add Another Record <small>(your POC record will be in the drop
down list)</small></u></font></a>
<br>
<a
href="LCCBHardwareMain.asp"><font size=4><u>Back to Main
Menu</u></font></a>
<%Response.End%>
</td>
</tr>
</table>
<%end if%>
<%
'this is the beginning of the HTML interface for the new hardware record
page
%>
<br>
<h4 align="center">
<font color="#333399">Entering a new record requires two
steps
<font color="#666666">(required items are in <font
color="#ff0000"><big>red</big></font>)</font>:
</font>
</h4>
<ol>
<li>Select your Point of Contact (POC)
record from the drop down list, <em>or</em> enter a new record if yours is
not in the list</li>
<li>Entering the pertinent product
information</li>
</ol>
<center>
<small>
<font color="#ff0000"><b>NOTE</b></font>:
Only <b>LCCB Representatives</b> are authorized to complete this form.
</small>
</center>
<br>
<form action="new.asp" method="post" name="frmNew">
<table border="5" name="TableForPOCInfo" width="100%">
<tr>
<td>
<h4 align="center">LCCB POC:</h4>
<table align="center" bgcolor="#bbbbbb" width="100%"
border="1">
<tr>
<td>
<%
'This code block creates populates a drop down list box with the
contents of the POC table from
'the database. this will allow the user to select their existing
POC record and prevent them from
'having to enter the data every time they enter a new record. the
code creates an HTML SELECT input type
'that uses the POCID as the value for each <option> tag and the
field values name, phone, ext, unit, and
'location for text of the drop-down
'declare variables to hold POC concatenation and a variable to
iterate thru the recordset fields collection
dim strPOC, vaField
strPOC = "<option value='none'>Select Existing or Enter
New Point of Contact Below</option>"
strSQL = "Select POCID, POCUnit, POCLocation, FirstName,
LastName, POCPhone, POCExt from POC ORDER BY FirstName;"
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
'concatenate the POC records for users to choose from and
create the <option tags> with POCID for value
do while not objRS.EOF
for each vaField in objRS.Fields
select case vaField.Name
case "POCID"
'checks the value, in case
of error, same value will be displayed on page
strSelected = ""
if strPOCExists <> "none"
then
if clng
(strPOCExists) = vaField.Value then strSelected = "selected"
end if
'begins concatenation of
option and inserts the ID from the database in the value attribute
strPOC = strPOC & "<option
value='" & vaField.Value & "' " & strSelected & ">"
case "POCExt"
if vaField.Value <> "" then
strPOC = strPOC
& " x " & vaField.Value
end if
case "FirstName"
strPOC = strPOC
& " " & vaField.Value
case "LastName"
strPOC = strPOC & " "
& vaField.Value
case else
strPOC = strPOC
& " " & vaField.Value
end select
next
strPOC = strPOC & "</option>"
objRS.MoveNext
loop
objRS.Close
set objRS = Nothing
set objComm = Nothing
Response.Write "<select style='width: 100%'
name='POCExists'>" & strPOC & "</select>"
'this is the end of the code that creates the drop-down list box for the
POC info
%>
</td>
</tr>
</table>
<%
'the rest of the HTML and ASP code is used to create the form that users
will fill out. ASP code
'is used as part of the input strings to ensure that the data already
entered by the user is re-entered
'in case of error.
%>
<br>
<table align="center" bgcolor="#bbbbbb" width="65%"
border="1">
<tr>
<td width="35%"><small><font
color="#ff0000"><b>First Name</b></font></small></td>
<td>
<input maxlength="20" type="text"
size="25" name="POCNameFirst" value="<%=strPOCNameFirst%>"></input>
</td>
</tr>
<tr>
<td width="25%"><small><font
color="#ff0000"><b>Last Name</b></font></small></td>
<td>
<input maxlength="20" type="text"
size="25" name="POCNameLast" value="<%=strPOCNameLast%>"></input>
</td>
</tr>
<tr>
<td width="25%"><small><font
color="#ff0000"><b>Phone</b></font></small></td>
<td>
<input type="text" maxlength="15"
size="15" name="POCPhone" value="<%=strPOCPhone%>">
Ext.
<input type="text" maxlength="7"
size="5" name="POCExt" value="<%=strPOCExt%>">
</td>
</tr>
<tr>
<td width="25%"><small><font
color="#ff0000"><b>Email</b></font></small></td>
<td>
<input type="text" maxlength="30"
size="30" name="POCEmail" value="<%=strPOCEmail%>"></input>
</td>
</tr>
<tr>
<td width="25%"><small><font
color="#ff0000"><b>Unit</b></font></small></td>
<td>
<input type="text" maxlength="30"
size="30" name="POCUnit" value="<%=strPOCUnit%>"></input>
</td>
</tr>
<tr>
<td width="25%"><small><font
color="#ff0000"><b>Location<br>(city, state)</b></font></small></td>
<td>
<input type="text" maxlength="30"
size="30" name="POCLocation" value="<%=strPOCLocation%>"></input>
</td>
</tr>
</table>
<br>
</td>
</tr>
</table>
<br>
<table border="5" name="TableForHardwareInfo" width="100%">
<tr>
<td>
<h4 align="center">Product Information:</h4>
<table align="center" bgcolor="#bbbbbb" width="80%"
border="1">
<tr>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Category</b></font></small>
<br>
<%
'This code block creates and populates drop down list with
category table contents. it is similar to the
'code to create a drop down list for existing POC records above.
this code allows the admin to enter a new
'category in the category table and have it update this page
automattically.
'declare variables to hold Category and a variable(vaField already
defined above) to iterate thru the recordset fields collection
dim strCat
strCat = "<option value='none'>Choose One</option>"
strSql = "Select * from Category;"
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
'concatenate the Cetgory records for users to choose from
and create the <option tags> with CategoryID value
do while not objRS.EOF
for each vaField in objRS.Fields
select case vaField.Name
case "CategoryID"
'checks to see the value
in case of error, same value will be displayed on page
strSelected = ""
if strCategory <> "none"
then
if clng
(strCategory) = vaField.Value then strSelected = "selected"
end if
'begins concatenation of
option and inserts the ID from the database in the value attribute
strCat = strCat & "<option
value='" & vaField.Value & "' " & strSelected & ">"
case "Category"
strCat = strCat &
vaField.Value
end select
next
strCat = strCat & "</option>"
objRS.MoveNext
loop
objRS.Close
set objRS = Nothing
set objComm = Nothing
Response.Write "<select style='width: 100%'
name='Category'>" & strCat & "</select>"
%>
</td>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Manufacturer</b></font></small>
<br>
<input maxlength="50" type="text"
name="Manufacturer" value="<%=strManufacturer%>"></input>
</td>
<td width="33%" align="center">
<small><font
color="#ff0000"><b>Model</b></font></small>
<br>
<input maxlength="50" type="text"
name="Model" value="<%=strModel%>"></input>
</td>
</tr>
<tr>
<td align="center">
<small><font
color="#ff0000"><b>Test Status</b></font></small>
<br>
<%
'This code block creates and populates drop down list with
TestStatus table contents. it is similar to the
'code for creating a drop list for existing POC records above
'declare variables to hold TestStatus and a variable(vaField
already defined above) to iterate thru the recordset fields collection
dim strTest, blnTemp
strTest = "<option value='none'>Choose One</option>"
strSql = "Select * from TestStatus;"
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText
objComm.CommandText = strSQL
set objRS = objComm.Execute
'concatenate the Test Status records for users to choose
from and create the <option tags> with TestStatusID value
do while not objRS.EOF
for each vaField in objRS.Fields
select case vaField.Name
case "TestStatusID"
'checks to see the
value in case of error, same value will be displayed on page
strSelected = ""
if strTestStatus
<> "none" then
if clng
(strTestStatus) = vaField.Value then strSelected = "selected"
end if
'begins
concatenation of option and inserts the ID from the database in the value
attribute
strTest = strTest
& "<option value='" & vaField.Value & "' " & strSelected & ">"
case "TestStatus"
strTest = strTest
& vaField.Value
end select
next
strTest = strTest & "</option>"
objRS.MoveNext
loop
objRS.Close
set objRS = Nothing
set objComm = Nothing
Response.Write "<select style='width: 100%'
name='TestStatus'>" & strTest & "</select>"
%>
<%
'the remaining HTML and ASP code just completes the building of the table
and form
%>
</td>
<td align="center">
<small><b>Vendor</b></small>
<br>
<input type="text" name="Vendor"
value="<%=strVendor%>"></input>
</td>
<td align="center">
<small><b>Approx Cost
$</b></small>
<br>
<input type="text" name="Cost"
value="<%if strCost = "" then Response.Write 0 else Response.Write strCost%
>"></input>
</td>
</tr>
<tr>
<td align="LEFT">
<b><small>Date Approved (mm/dd/yy)</small></b>
<br>
<input type="text"
name="DateApproved" value="<%=strDateApproved%>">
</td>
<td align="center">
<small><b>OPFAC</b></small>
<br>
<input maxlength="5" type="text"
name="OPFAC" value="<%=strOPFAC%>"></input>
</td>
<td align="center">
<small><b>LCCB Request
Nbr</b></small>
<br>
<input type="text"
name="LCCBRequestNbr" value="<%=strLCCBRequestNbr%>"></input>
</td>
</tr>
<tr>
<td bgcolor="#cccccc" align="center">
</td>
<td align="center">
<small><b>Certified By</b></small>
<br>
<input type="text"
name="CertifiedBy" value="<%=strCertifiedBy%>">
</td>
<td bgcolor="#cccccc" align="center">
</td>
</tr>
<tr>
<td align="center" colspan="3">
<small><b>Notes &
Comments</b></small>
<br>
<textarea name="Notes" cols="40"
rows="5"><%=strNotes%></textarea>
</td>
</tr>
<tr>
<td align="center">
<input type="submit" name="submit"
value="Submit"></input>
</td>
<td bgcolor="#cccccc" align="center">
<small><a
href="lccbhardwaremain.asp"><font color="#333399"><u>Back to Main
Menu</u></font></a></small>
</td>
<td align="center">
<input type="reset" name="reset"
value="Reset"></input>
</td>
</tr>
</table>
<br>
</td>
</tr>
</table>
</form>
<br><br>
</body>
</html>
Message #2 by "Tadd Brown" <tadese98@h...> on Mon, 16 Dec 2002 15:16:16
|
|
I need help with the following question:
Thank you
> I keep getting Data type mismatch in criteria expression when I change
the
d> ata type for DateApproved field in the Access db from text to Date/Time.
> I have serval asp pages. When I enter new information from the asp page
t> o the database I don't get error, but I get error when I try to
u> pdate/edit existing record. I have included two of the asp pages. I
a> ppreciate your help.
> Error Type:
M> icrosoft JET Database Engine (0x80040E07)
D> ata type mismatch in criteria expression.
> Here is the the codes:
> udateinclude.asp
-> -----------------------------------------------------------------------
>
<> %
> 'code to execute if update button is clicked
> if Request.Form("submit") = "Update" then
>
> strCategory = trim(Request.Form("Category"))
> strManufacturer = trim(Request.Form("Manufacturer"))
> strModel = trim(Request.Form("Model"))
> strTestStatus = trim(Request.Form("TestStatus"))
> strVendor = trim(Request.Form("Vendor"))
> strCertifiedBy = trim(Request.Form("CertifiedBy"))
> strDateApproved = trim(Request.Form("DateApproved"))
> strOPFAC = trim(Request.Form("OPFAC"))
> strLCCBRequestNbr = trim(Request.Form("LCCBRequestNbr"))
> strCost = trim(Request.Form("Cost"))
> strNotes = trim(Request.Form("Notes"))
>
> if strErrMsg = "" then
> if strCategory = "none" then
> strErrMsg = "You must choose a CATEGORY"
> elseif strTestStatus = "none" then
> strErrMsg = "You must choose a TEST STATUS"
> elseif strManufacturer = "" or len
(> strManufacturer) < 2 then
> strErrMsg = "You must enter a Manufacturer"
> elseif strModel = "" or len(strModel) < 2 then
> strErrMsg = "You must enter a Model"
> end if
> end if
> 'if test status is approved, approved date should be
p> opulated
> if strErrMsg = "" and strTestStatus = "1" then
> if strDateApproved = "" then
> strErrMsg = "The status for this item is
A> PPROVED, but you did not enter a <em>date approved</em>. <br> Enter an
e> stimated DATE APPROVED or change status"
> elseif not isdate(strDateApproved) then
> strErrMsg = "If this item has APPROVED
s> tatus, please enter a valid date <br>for DATE APPROVED"
> end if
> end if
> 'if test status is not approved, then there should be no
a> pproved date
> if strErrMsg = "" and strTestStatus <> "1" then
> if strDateApproved <> "" then
> strErrMsg = "You entered an APPROVED DATE
b> ut the TEST STATUS is not approved"
> end if
> end if
> 'if approx cost is entered, make sure numeric
> if strErrMsg = "" and strCost <> "" then
> if not isnumeric(strCost) then
> strErrMsg = "APPROX COST must be numeric"
> end if
> end if
> 'validate OPFAC if entered, should be numeric 5 digit
> if strErrMsg = "" and strOPFAC <> "" then
> for i = 1 to len(strOPFAC)
> if instr(1,"1234567890",mid
(> strOPFAC,i,1),vbtextcompare) = 0 then
> strErrMsg = "OPFAC should only be
t> he 5 digit Unit OPFAC<br>(e.g. 12115)"
> end if
> next
> end if
>
> If strErrMsg = "" then
> if strCost = "" then
> strCost = 0
> End if
>
> strSQL = "Update Hardware SET " & _
> "Category = " & vbQuote & strCategory &
v> bQuote & _
> ", Manufacturer = " & vbQuote &
s> trManufacturer & vbQuote & _
> ", Model = " & vbQuote & strModel &
v> bQuote & _
> ", TestStatus = " & vbQuote &
s> trTestStatus & vbQuote & _
> ", Vendor = " & vbQuote & strVendor &
v> bQuote & _
> ", CertifiedBy = " & vbQuote &
s> trCertifiedBy & vbQuote & _
>
>
> ", DateApproved = " & vbQuote &
s> trDateApproved & vbQuote & _
>
>
>
> ", OPFAC = " & vbQuote & strOPFAC &
v> bQuote & _
> ", LCCBRequestNbr = " & vbQuote &
s> trLCCBRequestNbr & vbQuote & _
> ", Cost = " & vbQuote & strCost & vbQuote
&> _
> ", Notes = " & vbQuote & strNotes &
v> bQuote & _
> ", LastUpdate = #" & date() & "#" & _
> " WHERE HardwareRecordID = " & lngRecordID
>
>
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> objComm.Execute
>
> set objComm = Nothing
> strConfirm = "Your record has been updated"
> end if
>
> end if
>
>
>
> 'code to execute if "Go" button from displayupdate.asp is clicked
> if Request.Form("submit") = "Go" then
>
> if lngPickOne = "" then
> Response.Write "<h3 align='center'><font
c> olor='#ff0000'>You must choose a " & _
> "Record, Click <font
c> olor='#000000'>[Back]" & _
> "</font> on your browser
t> o correct the problem</font></h3>"
> Response.End
> End if
>
> lngPOCID = Request.Form("POCInfo")
> set objComm = server.CreateObject("ADODB.Command")
> strSQL = "SELECT * FROM Hardware WHERE HardwareRecordID
=> " & lngPickOne
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> set objComm = Nothing
>
> strCategory = objRS("Category")
> strManufacturer = objRS("Manufacturer")
> strModel = objRS("Model")
> strTestStatus = objRS("TestStatus")
> strVendor = objRS("Vendor")
> strCertifiedBy = objRS("CertifiedBy")
>
> 'If to_dateobjRS("DateApproved")) then
>
> If isDate(objRS("DateApproved")) then
>
> strDateApproved = formatdatetime(objRS
(> "DateApproved"),vbshortdate)
>
> Else
> strDateApproved = objRS("DateApproved")
> End if
>
> strOPFAC = objRS("OPFAC")
> strLCCBRequestNbr = objRS("LCCBRequestNbr")
> strCost = objRS("Cost")
> strNotes = objRS("Notes")
> strLastUpdate = objRS("LastUpdate")
>
> objRS.Close
> set objRS = Nothing
>
> End if
> %>
> <html>
<> head>
<> title></title>
<> /head>
> <body>
> <%
> if Request.Form("submit") = "Go" then
>
> set objComm = Server.CreateObject("ADODB.Command")
>
> strSQL = "Select * FROM POC WHERE POCID = " & lngPOCID
> objComm.ActiveConnection = strConnect
> objComm.CommandText = strSQL
> objComm.CommandType = adCmdText
> set objRS = objComm.Execute
> set objComm = nothing
>
> Response.Write "<h3 align='center'>POC Name: <font
c> olor='666666'>" & objRS("FirstName") & " " & objRS("LastName") & _
> "</font><br>Phone: <font
c> olor='666666'>" & objRS("POCPhone") & "</font> Ext: <font
c> olor='666666'>" & objRS("POCExt") & _
> "</font><br>Email: <font
c> olor='666666'>" & objRS("POCEmail") & "</font><br>Unit: <font
c> olor='666666'>" & objRS("POCUnit") & _
> "</font> Location:
<> font color='666666'>" & objRS("POCLocation") & "</font></h3>"
> objRS.Close
> set objRS = Nothing
e> nd if
%> >
> <% 'display error message if strErrMsg variable has data
> if strErrMsg <> "" then
%> >
> <table align="center" bgcolor="#ffffff" width="100%" border="1">
> <div align="center"><font
c> olor="#ff0000"><b><small>** ERROR **</small></b></div>
> <tr>
> <td align="center">
> <%="<small><b><font
c> olor='#ff0000'>" & strErrMsg & "</font></b></small>"%>
> </td>
> </tr>
> </table>
>
<> %elseif strConfirm <> "" then%>
> <table align="center" bgcolor="#bbbbbb" width="100%" border="1">
>
> <tr>
> <td align="center">
> <%="<b>" & strConfirm & "</b>"%>
> <br>
> <a href="selectUpdate.asp"><font
s> ize=4><u>Update Another Record</u></font></a>
> <br>
> <a
h> ref="LCCBHardwareMain.asp"><font size=4><u>Back to Main
M> enu</u></font></a>
> <%Response.End%>
> </td>
> </tr>
> </table>
<> %end if%>
>
>
> <form action="update.asp" method="post" name="frmNew">
> <br>
> <table border="5" name="TableForHardwareInfo" width="100%">
<> tr>
<> td>
> <h4 align="center">Update Record:</h4>
<> %
> Response.Write "<div align='center'><small>This record was Last
U> pdated on <font color='000000'><b>" & strLastUpdate
&> "</b></font></small></div>"
%> >
> <table align="center" bgcolor="#bbbbbb" width="80%"
b> order="1">
> <tr>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Category</b></font></small>
> <br>
<> %
> 'This code block creates recordset w/command object and
populates
d> rop down list with category table contents
>
> 'declare variables to hold Category and a variable(vaField
already
d> efined above) to iterate thru the recordset fields collection
> dim strCat
>
> strCat = "<option value='none'>Choose One</option>"
> strSql = "Select * from Category;"
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> 'concatenate the Cetgory records for users to choose from
a> nd create the <option tags> with CategoryID value
> do while not objRS.EOF
> for each vaField in objRS.Fields
> select case vaField.Name
> case "CategoryID"
> 'checks to see the value
i> n case of error, same value will be displayed on page
> strSelected = ""
> if strCategory <> "none"
t> hen
> if clng
(> strCategory) = vaField.Value then strSelected = "selected"
> end if
> 'begins concatenation of
o> ption and inserts the ID from the database in the value attribute
> strCat = strCat & "<option
v> alue='" & vaField.Value & "' " & strSelected & ">"
> case "Category"
> strCat = strCat &
v> aField.Value
> end select
> next
> strCat = strCat & "</option>"
> objRS.MoveNext
> loop
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
> Response.Write "<select style='width: 100%'
n> ame='Category'>" & strCat & "</select>"
%> >
> </td>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Manufacturer</b></font></small>
> <br>
> <input maxlength="50" type="text"
n> ame="Manufacturer" value="<%=strManufacturer%>"></input>
> </td>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Model</b></font></small>
> <br>
> <input maxlength="50" type="text"
n> ame="Model" value="<%=strModel%>"></input>
> </td>
> </tr>
> <tr>
> <td align="center">
> <small><font
c> olor="#ff0000"><b>Test Status</b></font></small>
> <br>
<> %
> 'This code block creates recordset w/command object and
populates
d> rop down list with TestStatus table contents
>
> 'declare variables to hold TestStatus and a variable(vaField
a> lready defined above) to iterate thru the recordset fields collection
> dim strTest, blnTemp
>
> strTest = "<option value='none'>Choose One</option>"
> strSql = "Select * from TestStatus;"
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> 'concatenate the Test Status records for users to choose
f> rom and create the <option tags> with TestStatusID value
> do while not objRS.EOF
> for each vaField in objRS.Fields
> select case vaField.Name
> case "TestStatusID"
> blnTemp = false
> if vaField.Value <> 5 then
> 'checks to see the
v> alue in case of error, same value will be displayed on page
> strSelected = ""
> if strTestStatus
<> > "none" then
> if clng
(> strTestStatus) = vaField.Value then strSelected = "selected"
> end if
> 'begins
c> oncatenation of option and inserts the ID from the database in the
value
a> ttribute
> strTest = strTest
&> "<option value='" & vaField.Value & "' " & strSelected & ">"
> else
> blnTemp = true
> end if
> case "TestStatus"
> blnTemp = false
> if vaField.Value <> "All
E> xcept Approved" then
> strTest = strTest
&> vaField.Value
> else
> blnTemp = true
> end if
> end select
> next
> if not blnTemp then
> strTest = strTest & "</option>"
> end if
> objRS.MoveNext
> loop
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
> Response.Write "<select style='width: 100%'
n> ame='TestStatus'>" & strTest & "</select>"
%> >
>
-> ------------------------------------------------------------------------
--
n> ewinclude.asp
>
> <%
> 'include file for a New HardWare Entry. this is the page where
u> sers can
> 'enter new information.
>
>
> 'declare variables for SQL strings, Command object, recordset
o> bject, and connection string, and other general variables
> dim strSQL, objComm, objRS, strConnect, strSelected, strErrMsg,
i,
j> , Nbr
> dim strMethod, vbQuote, strConfirm, strConfirmName
>
> 'declare variables for POC table/form
> dim strPOCNameFirst, strPOCNameLast, strPOCPhone, strPOCExt,
s> trPOCEmail, strPOCExists, strPOCType
> dim strPOCUnit, strPOCLocation
>
> 'declare variables for hardware entry table/form
> dim strCategory, strManufacturer,strModel, strTestStatus,
s> trVendor, strCertifiedBy, strDateApproved
> dim strOPFAC, strLCCBRequestNbr, strCost, strNotes
>
'> set strMethod to empty, if user clicks submit, it will be assigned the
v> alue "post" so we can use
'> it later to tell other parts of the page that this is a post request
> strMethod = ""
> ' if the user navigates to this page the first time this code is not
e> xecuted, if the user has
'> clicked the submit button begin collecting the data from the form
>
> if Request.ServerVariables("request_method") = "POST" then
> 'here we assign the data from the from on the page to variables. we
will
u> se the variables to
'> perform validation and to create an insert query to add the new records
t> o the database
>
> strPOCExists = trim(Request.Form("POCExists"))
> strPOCNameFirst = trim(Request.Form("POCNameFirst"))
> strPOCNameLast = trim(Request.Form("POCNameLast"))
> strPOCPhone = trim(Request.Form("POCPhone"))
> strPOCExt = trim(Request.Form("POCExt"))
> strPOCEmail = trim(Request.Form("POCEmail"))
> strPOCUnit = trim(Request.Form("POCUnit"))
> strPOCLocation = trim(Request.Form("POCLocation"))
> strCategory = trim(Request.Form("Category"))
> strManufacturer = trim(Request.Form("Manufacturer"))
> strModel = trim(Request.Form("Model"))
> strTestStatus = trim(Request.Form("TestStatus"))
> strVendor = trim(Request.Form("Vendor"))
> strCertifiedBy = trim(Request.Form("CertifiedBy"))
> 'strDateApproved = trim(Request.Form("DateApproved"))
>
> strDateApproved = trim(Request.Form("DateApproved"))
> strOPFAC = trim(Request.Form("OPFAC"))
> strLCCBRequestNbr = trim(Request.Form("LCCBRequestNbr"))
> strCost = trim(Request.Form("Cost"))
> strNotes = trim(Request.Form("Notes"))
>
'> initialize the error message variable, set method equal to post,
i> nitialize vbQuote constant
'> the vbQuote constant will make it easier to concatenate the quote(")
c> haracter in our
'> SQL queries
>
> strErrMsg = ""
> 'we need to set this value to post so we can use it later to tell the
code
h> ow the user accessed this page
> strMethod = "post"
> 'set the vbQuote cariable to equal chr(34) which is the quote(")
c> haracter. we use this to simplify
'> our SQL concatenation
> vbQuote = chr(34)
>
'> now we perform validation of fields and data and set up some variables
r> elated to database
'> queries(e.g. strPOCType)
'> see if user entered new AND existing POC info, if so we prompt the user
t> o make a correction
> if strPOCExists <> "none" and (strPOCNameFirst <> "" or
s> trPOCNameLast <> "" _
> or strPOCPhone <> "" or strPOCExt <> "" or
s> trPOCEmail <> "") then
>
> strErrMsg = "You chose an existing LCCB
P> OC <em>and</em> entered new info - please select existing <em>or</em>
e> nter a new record. " & _
> "<br>You can
<> em>update</em> existing POC info from the <a
h> ref='LCCBHardwareMain.asp'><font color='333399'><u>main
m> enu</u></font></a>."
>
> end if
> 'determine is POC is new or existing, and assign the strPOCType variable
a
v> alue that let's the program
'> know if the POC record is new or existing. if it is a new POC we will
n> eed to insert a new POC record
'> later and then use the new POCID in the insert query for the harware
r> ecord
'> if it is existing POC, we will use the POCID in the insert query
>
> if strErrMsg = "" then
> if strPOCExists <> "none" then
> strPOCType = "POCExists"
> else
> strPOCType = "NewPOC"
> end if
> end if
>
'> if user is entering a new POC record then make sure they enter all
r> equired data
> 'validate first name
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if strPOCNameFirst = "" or len(strPOCNameFirst) <
2> then
> strErrMsg = "Invalid First Name, Please
e> nter a valid first name"
> else
> for i = 1 to len(strPOCNameFirst)
> if instr
(> 1, "abcdefghijklmnopqrstuvwxyz' ",mid(strPOCNameFirst,i,1),
v> btextcompare) = 0 then
> strErrMsg = "Invalid First
N> ame, Please enter a valid first name"
> end if
> next
> end if
> end if
>
> 'validate last name
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if strPOCNameLast = "" or len(strPOCNameLast) < 2
t> hen
> strErrMsg = "Invalid Last Name, Please
e> nter a valid last name"
> else
> for i = 1 to len(strPOCNameLast)
> if instr
(> 1, "abcdefghijklmnopqrstuvwxyz' ",mid(strPOCNameLast,i,1),
vbtextcompare)
=> 0 then
> strErrMsg = "Invalid Last
N> ame, Please enter a valid last name"
> end if
> next
> end if
> end if
>
> 'validate phone number
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if len(strPOCPhone) <> 0 then
> for i = 1 to len(strPOCPhone)
> if instr(1,".1234567890-() ",mid
(> strPOCPhone,i,1),vbtextcompare) = 0 then
> strErrMsg = "You entered
a> n invalid telephone number, Please re-enter the number.<br>(e.g. (301)
5> 55-1234, 301-555-1234, 301.555.1234, 3015551234)"
> end if
> next
>
> for j = 1 to len(strPOCPhone)
> if instr(1,"1234567890",mid
(> strPOCPhone,j,1),vbtextcompare) <> 0 then
> Nbr = Nbr + 1
> end if
> next
>
> if Nbr <> 10 then
> strErrMsg = "You entered an
i> nvalid Telephone number, Number of digits should total 10."
> end if
> else
> strErrMsg = "You must enter a Telephone
n> umber."
> end if
> end if
>
> 'validate extension
>
> if strErrMsg = "" and strPOCExt <> "" then
> if not isnumeric(strPOCExt) then
> strErrMsg = "Invalid extension. Extension
m> ust be numeric."
> end if
> end if
>
> 'validate email
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if strPOCEmail = ""then
> strErrMsg = "You must enter an email
a> ddress"
> elseif instr(1,strPOCEmail, "@", vbtextcompare) =
0> then
> strErrMsg = "Invalid email address -
P> lease re-enter"
> elseif instr(1,strPOCEmail, "@", vbtextcompare) =
1> then
> strErrMsg = "Invalid email address -
P> lease re-enter"
> elseif instr
(> 1,strPOCEmail,".uscg.mil",vbtextcompare) = 0 then
> strErrMsg = "Not a valid Coast Gaurd Email
a> ddress - Please re-enter"
> end if
> end if
>
> 'validate unit
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if strPOCUnit = "" then
> strErrMsg = "You must enter a Unit"
> elseif len(strPOCUnit) < 2 then
> strErrMsg = "Please enter a valid Unit"
> else
> 'do nothing
> end if
> end if
>
> 'validate location
>
> if strErrMsg = "" and strPOCType = "NewPOC" then
> if strPOCLocation = "" then
> strErrMsg = "You must enter a Location"
> elseif len(strPOCLocation) < 5 then
> strErrMsg = "Please enter a valid Location"
> else
> 'do nothing
> end if
> end if
>
'> if strErrMsg is empty after validating the POC information then we
begin
t> he validation of the
'> hardware record
'> make sure Category, test status, manufacturer, and model are all
e> ntered. they are all required
'> for the hardware record
> if strErrMsg = "" then
> if strCategory = "none" then
> strErrMsg = "You must choose a CATEGORY
u> nder product info"
> elseif strTestStatus = "none" then
> strErrMsg = "You must choose a TEST STATUS
u> nder product info"
> elseif strManufacturer = "" or len
(> strManufacturer) < 2 then
> strErrMsg = "You must enter a Manufacturer
u> nder product info"
> elseif strModel = "" or len(strModel) < 2 then
> strErrMsg = "You must enter a Model under
p> roduct info"
> end if
> end if
>
> 'if test status is approved, approved date should be
p> opulated
>
> if strErrMsg = "" and strTestStatus = "1" then
> if strDateApproved = "" then
> strErrMsg = "The status for this item is
A> PPROVED, but you did not enter a <em>date approved</em>. <br> Enter an
e> stimated DATE APPROVED or change status"
> elseif not isdate(strDateApproved) then
> strErrMsg = "If this item has APPROVED
s> tatus, please enter a valid date (date format)<br>for DATE APPROVED"
> end if
> end if
>
> 'if test status is not approved, then there should be no
a> pproved date
>
> if strErrMsg = "" and strTestStatus <> "1" then
> if strDateApproved <> "" then
> strErrMsg = "You entered an APPROVED DATE
b> ut the TEST STATUS is not approved"
> end if
> end if
>
> 'if approx cost is entered, make sure numeric
> if strErrMsg = "" and strCost <> "" then
> if not isnumeric(strCost) then
> strErrMsg = "APPROX COST must be numeric"
> end if
> end if
>
> 'validate OPFAC if entered, should be numeric 5 digit
> if strErrMsg = "" and strOPFAC <> "" then
> for i = 1 to len(strOPFAC)
> if instr(1,"1234567890",mid
(> strOPFAC,i,1),vbtextcompare) = 0 then
> strErrMsg = "OPFAC should only be
t> he 5 digit Unit OPFAC<br>(e.g. 12115)"
> end if
> next
> end if
>
> end if
%> >
<>
%'************************************************************************
*> **********
'> the following include file contains the database connection info. it
is
u> sed for all
'> pages that use a database connection. if the database info is changed,
t> he changes can be
'> made to one file and all of the files that use the database will be
u> pdated automatically.
'>
**************************************************************************
*> ****************
%> >
<> !-- #include file="datastore.asp" -->
<> %
> 'if no errors then Insert new record for hardware and new
r> ecord for POC if strPOCType is not "Exists"
>
> if strErrMsg = "" then
>
> 'if POC type is "exists" then insert new hardware
r> ecord with current POC id,
> ' otherwise insert POC record, then insert
h> ardware record with the new POCID
>
> select case strPOCType
> case "POCExists"
>
> 'this is the code that is executed in the
c> ase of the user selecting an existing POC
> 'so we dont have to insert a new POC
r> ecord, the POCID is stored in the strPOCExists
> 'variable that we obtained from the
R> equest.Form("POCExists") object above, we use
> 'the value to assign a POC to the hardware
r> ecord
>
> 'an empty string will cause an error for a
n> umeric datatype in access so if it is
> 'empty, we assign the value zero(0) to the
c> ost
>
> if strCost = "" then
> strCost = 0
> else
> strCost = cdbl(strCost)
> end if
>
> 'here we build our SQL string using the
v> ariables we used to hold the values from the form
> 'and using the vbQuote constant we created
t> o simplify concatenation, we dont need to include
> 'the LastUpdate field for a new record
b> ecause it will insert the current date/time by default
>
> strSQL = "INSERT INTO Hardware " &
_>
> "(Category, " & _
> "Manufacturer, "
&> _
> "Model, " & _
> "TestStatus, " & _
> "Vendor, " & _
> "CertifiedBy, " &
_>
> "DateApproved, "
&> _
> "OPFAC, " & _
>
> "LCCBRequestNbr, " & _
> "Cost, " & _
> "POCID, " & _
> "Notes) " & _
> "VALUES (" & _
> strCategory
&> ", " & _
> vbQuote &
s> trManufacturer & vbQuote & ", " & _
> vbQuote &
s> trModel & vbQuote & ", " & _
> vbQuote &
s> trTestStatus & vbQuote & ", " & _
> vbQuote &
s> trVendor & vbQuote & ", " & _
> vbQuote &
s> trCertifiedBy & vbQuote & ", " & _
> vbQuote &
s> trDateApproved & vbQuote & ", " & _
> vbQuote &
s> trOPFAC & vbQuote & ", " & _
> vbQuote &
s> trLCCBRequestNbr & vbQuote & ", " & _
> strCost & ", " & _
> strPOCExists
&> ", " & _
> vbQuote &
s> trNotes & vbQuote & ") "
>
> 'create a command object to use for executing the
q> uery
> set objComm = Server.CreateObject
(> "ADODB.Command")
>
> 'here we assign the value of strConnect to the
a> ctiveconnection property of the command object.
> 'strConnect is given a value in the datastore.asp
i> nclude file above
>
> objComm.ActiveConnection =
s> trConnect
>
> 'the commandtype property is adCmdText(for SQL
s> trings), the adCmdText constant is defined in the type library
> 'we included in the metadata tag above
>
> objComm.CommandType = adCmdText
>
> 'assign the value of strSQL to the commandtext
p> roperty of the command object
>
> objComm.CommandText = strSQL
>
> 'execute the command
>
> objComm.Execute
>
>
> 'here we change the value of strSQL to another
q> uery to create a recordset so we can send
> 'a confirmation message to the browser after we
a> dd the record. this will only be sent if
> 'there are no errors(this is true after the run-
t> ime error handlers are added to the program
> 'which is the last thing we will do)
> 'then we close and destroy the recordset and
c> ommand object to free up server resources
>
> strSQL = "SELECT FirstName FROM
P> OC WHERE POCID =" & strPOCExists
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
> strConfirmName = objRS("FirstName")
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
>
> 'this confirmation message will be displayed if
t> here are no errors found from our validation
> 'or from the run-time error handlers
>
> strConfirm = "Your record has been
a> dded to the database " & strConfirmName
>
> case "NewPOC"
>
> 'this code will be executed if the user is
e> ntering a New POC record. first we need to
> 'insert the new POC record, then we need to query
t> he POC table and get the POCID value
> 'of the new POC record, then we use the new POC
v> alue in the insert query for the new hardware
> 'record
>
> 'an empty string will cause an error for a
n> umeric datatype in access so if it is
> 'empty, we assign the value zero(0) to the
c> ost
>
> if strCost = "" then
> strCost = 0
> end if
>
> 'we assign a SQL string to strSQL that will insert
a> new POC record
> strSQL = "INSERT INTO POC " & _
> "(FirstName, " & _
> "LastName, " & _
> "POCPhone, " & _
> "POCExt, " & _
> "POCEmail, " & _
> "POCUnit, " & _
> "POCLocation) "
&> _
> "VALUES (" & _
> vbQuote &
s> trPOCNameFirst & vbQuote & ", " & _
> vbQuote &
s> trPOCNameLast & vbQuote & ", " & _
> vbQuote &
s> trPOCPhone & vbQuote & ", " & _
> vbQuote &
s> trPOCExt & vbQuote & ", " & _
> vbQuote &
s> trPOCEmail & vbQuote & ", " & _
> vbQuote &
s> trPOCUnit & vbQuote & ", " & _
> vbQuote &
s> trPOCLocation & vbQuote & ") "
>
> 'create a command object
>
> set objComm = Server.CreateObject
(> "ADODB.Command")
>
> 'here we assign the value of strConnect to the
a> ctiveconnection property of the command object.
> 'strConnect is given a value in the datastore.asp
i> nclude file above
> objComm.ActiveConnection =
s> trConnect
>
> 'the commandtype property is adCmdText(for SQL
s> trings), the adCmdText constant is defined in the type library
> 'we included in the metadata tag above
> objComm.CommandType = adCmdText
>
> 'assign the value of strSQL to the commandtext
p> roperty of the command object
> objComm.CommandText = strSQL
>
> 'execute the command
> objComm.Execute
>
> 'now we change the SQL string to select the new
P> OCID that we just added to the database
> strSQL = "SELECT POCID FROM POC
O> RDER BY POCID Desc"
>
> 'now we assign the new SQL to the commandtext
p> roperty and execute the command
> 'we put the value of the largest POCID in the
v> ariable strPOCExists, then we close
> 'and destroy the command and recordset objects.
w> e use strPOCExists in the insert
> 'query for the new hardware record
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
> strPOCExists = objRS("POCID")
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
>
> 'this is the insert query for the new hardware
r> ecord
> strSQL = "INSERT INTO Hardware " &
_>
> "(Category, " & _
> "Manufacturer, "
&> _
> "Model, " & _
> "TestStatus, " & _
> "Vendor, " & _
> "CertifiedBy, " &
_>
> "DateApproved, "
&> _
> "OPFAC, " & _
>
> "LCCBRequestNbr, " & _
> "Cost, " & _
> "POCID, " & _
> "Notes) " & _
> "VALUES (" & _
> strCategory
&> ", " & _
> vbQuote &
s> trManufacturer & vbQuote & ", " & _
> vbQuote &
s> trModel & vbQuote & ", " & _
> vbQuote &
s> trTestStatus & vbQuote & ", " & _
> vbQuote &
s> trVendor & vbQuote & ", " & _
> vbQuote &
s> trCertifiedBy & vbQuote & ", " & _
> vbQuote &
s> trDateApproved & vbQuote & ", " & _
> vbQuote &
s> trOPFAC & vbQuote & ", " & _
> vbQuote &
s> trLCCBRequestNbr & vbQuote & ", " & _
> strCost & ", " & _
> strPOCExists
&> ", " & _
> vbQuote &
s> trNotes & vbQuote & ") "
>
>
> 'see explanation for these commands above
> set objComm = Server.CreateObject
(> "ADODB.Command")
> objComm.ActiveConnection =
s> trConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> objComm.Execute
>
> 'the following code is used for a confirmation
m> essage for the user, see further explanation
> 'above under 'CASE "POCExists"'
> strSQL = "SELECT FirstName FROM
P> OC WHERE POCID =" & strPOCExists
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
> strConfirmName = objRS("FirstName")
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
>
> strConfirm = "Your record has been
a> dded to the database " & strConfirmName
>
> end select
> end if
%> >
<>
%'************************************************************************
*> **********
'> the following metadata tag includes the ADO type library. including
t> his type library prevents
'> us from having to define the ADO constants that are commonly used with
A> DO and ASP
'>
**************************************************************************
*> ****************
%> >
<> !--METADATA TYPE="typelib"
> FILE="c:\program files\common
f> iles\system\ado\msado15.dll" -->
<> html>
<> head>
<> title></title>
<> /head>
> <body>
<> br>
<> h3 align="center"><font color="#aaaaaa">New Record</font></h3>
> <%
'> if validation failed and strErrMsg contains data then
'> display error message and the HTML that follows this if...then clause
> if strErrMsg <> "" then
%> >
> <table align="center" bgcolor="#cccccc" width="100%" border="1">
> <div align="center"><font
c> olor="#ff0000"><b><small>** ERROR **</small></b></font></div>
> <tr>
> <td align="center">
> <%="<small><b><font
c> olor='#ff0000'>" & strErrMsg & "</font></b></small>"%>
> </td>
> </tr>
> </table>
>
<> %
'> if there is no eror message and the user clicked submit and there is a
c> onfirmation message in the
'> strConfirm variable then display this HTML
e> lseif strConfirm <> "" then%>
> <table align="center" bgcolor="#bbbbbb" width="100%" border="1">
>
> <tr>
> <td align="center">
> <%="<b>" & strConfirm & "</b>"%>
> <br>
> <a href="new.asp"><font
s> ize=4><u>Add Another Record <small>(your POC record will be in the drop
d> own list)</small></u></font></a>
> <br>
> <a
h> ref="LCCBHardwareMain.asp"><font size=4><u>Back to Main
M> enu</u></font></a>
> <%Response.End%>
> </td>
> </tr>
> </table>
<> %end if%>
>
<> %
'> this is the beginning of the HTML interface for the new hardware record
p> age
%> >
>
> <br>
> <h4 align="center">
> <font color="#333399">Entering a new record requires two
s> teps
> <font color="#666666">(required items are in <font
c> olor="#ff0000"><big>red</big></font>)</font>:
> </font>
>
> </h4>
> <ol>
>
>
> <li>Select your Point of Contact (POC)
r> ecord from the drop down list, <em>or</em> enter a new record if yours
is
n> ot in the list</li>
> <li>Entering the pertinent product
i> nformation</li>
>
>
> </ol>
> <center>
> <small>
> <font color="#ff0000"><b>NOTE</b></font>:
O> nly <b>LCCB Representatives</b> are authorized to complete this form.
> </small>
> </center>
> <br>
> <form action="new.asp" method="post" name="frmNew">
<> table border="5" name="TableForPOCInfo" width="100%">
<> tr>
<> td>
> <h4 align="center">LCCB POC:</h4>
> <table align="center" bgcolor="#bbbbbb" width="100%"
b> order="1">
>
> <tr>
> <td>
<> %
> 'This code block creates populates a drop down list box with the
c> ontents of the POC table from
> 'the database. this will allow the user to select their
existing
P> OC record and prevent them from
> 'having to enter the data every time they enter a new record.
the
c> ode creates an HTML SELECT input type
> 'that uses the POCID as the value for each <option> tag and the
f> ield values name, phone, ext, unit, and
> 'location for text of the drop-down
>
> 'declare variables to hold POC concatenation and a variable to
i> terate thru the recordset fields collection
> dim strPOC, vaField
>
>
> strPOC = "<option value='none'>Select Existing or Enter
N> ew Point of Contact Below</option>"
> strSQL = "Select POCID, POCUnit, POCLocation, FirstName,
L> astName, POCPhone, POCExt from POC ORDER BY FirstName;"
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> 'concatenate the POC records for users to choose from and
c> reate the <option tags> with POCID for value
> do while not objRS.EOF
> for each vaField in objRS.Fields
> select case vaField.Name
> case "POCID"
> 'checks the value, in case
o> f error, same value will be displayed on page
> strSelected = ""
> if strPOCExists <> "none"
t> hen
> if clng
(> strPOCExists) = vaField.Value then strSelected = "selected"
> end if
> 'begins concatenation of
o> ption and inserts the ID from the database in the value attribute
> strPOC = strPOC & "<option
v> alue='" & vaField.Value & "' " & strSelected & ">"
> case "POCExt"
> if vaField.Value <> "" then
> strPOC = strPOC
&> " x " & vaField.Value
> end if
> case "FirstName"
> strPOC = strPOC
&> " " & vaField.Value
> case "LastName"
> strPOC = strPOC & " "
&> vaField.Value
> case else
> strPOC = strPOC
&> " " & vaField.Value
> end select
> next
> strPOC = strPOC & "</option>"
> objRS.MoveNext
> loop
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
> Response.Write "<select style='width: 100%'
n> ame='POCExists'>" & strPOC & "</select>"
'> this is the end of the code that creates the drop-down list box for the
P> OC info
%> >
> </td>
> </tr>
> </table>
<> %
'> the rest of the HTML and ASP code is used to create the form that users
w> ill fill out. ASP code
'> is used as part of the input strings to ensure that the data already
e> ntered by the user is re-entered
'> in case of error.
%> >
> <br>
> <table align="center" bgcolor="#bbbbbb" width="65%"
b> order="1">
>
> <tr>
> <td width="35%"><small><font
c> olor="#ff0000"><b>First Name</b></font></small></td>
> <td>
> <input maxlength="20" type="text"
s> ize="25" name="POCNameFirst" value="<%=strPOCNameFirst%>"></input>
> </td>
> </tr>
> <tr>
> <td width="25%"><small><font
c> olor="#ff0000"><b>Last Name</b></font></small></td>
> <td>
> <input maxlength="20" type="text"
s> ize="25" name="POCNameLast" value="<%=strPOCNameLast%>"></input>
> </td>
> </tr>
> <tr>
> <td width="25%"><small><font
c> olor="#ff0000"><b>Phone</b></font></small></td>
> <td>
> <input type="text" maxlength="15"
s> ize="15" name="POCPhone" value="<%=strPOCPhone%>">
> Ext.
> <input type="text" maxlength="7"
s> ize="5" name="POCExt" value="<%=strPOCExt%>">
> </td>
> </tr>
> <tr>
> <td width="25%"><small><font
c> olor="#ff0000"><b>Email</b></font></small></td>
> <td>
> <input type="text" maxlength="30"
s> ize="30" name="POCEmail" value="<%=strPOCEmail%>"></input>
> </td>
> </tr>
> <tr>
> <td width="25%"><small><font
c> olor="#ff0000"><b>Unit</b></font></small></td>
> <td>
> <input type="text" maxlength="30"
s> ize="30" name="POCUnit" value="<%=strPOCUnit%>"></input>
> </td>
> </tr>
> <tr>
> <td width="25%"><small><font
c> olor="#ff0000"><b>Location<br>(city, state)</b></font></small></td>
> <td>
> <input type="text" maxlength="30"
s> ize="30" name="POCLocation" value="<%=strPOCLocation%>"></input>
> </td>
> </tr>
> </table>
<> br>
<> /td>
<> /tr>
<> /table>
<> br>
<> table border="5" name="TableForHardwareInfo" width="100%">
<> tr>
<> td>
> <h4 align="center">Product Information:</h4>
> <table align="center" bgcolor="#bbbbbb" width="80%"
b> order="1">
> <tr>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Category</b></font></small>
> <br>
<> %
> 'This code block creates and populates drop down list with
c> ategory table contents. it is similar to the
> 'code to create a drop down list for existing POC records
above.
t> his code allows the admin to enter a new
> 'category in the category table and have it update this page
a> utomattically.
>
> 'declare variables to hold Category and a variable(vaField
already
d> efined above) to iterate thru the recordset fields collection
> dim strCat
>
> strCat = "<option value='none'>Choose One</option>"
> strSql = "Select * from Category;"
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> 'concatenate the Cetgory records for users to choose from
a> nd create the <option tags> with CategoryID value
> do while not objRS.EOF
> for each vaField in objRS.Fields
> select case vaField.Name
> case "CategoryID"
> 'checks to see the value
i> n case of error, same value will be displayed on page
> strSelected = ""
> if strCategory <> "none"
t> hen
> if clng
(> strCategory) = vaField.Value then strSelected = "selected"
> end if
> 'begins concatenation of
o> ption and inserts the ID from the database in the value attribute
> strCat = strCat & "<option
v> alue='" & vaField.Value & "' " & strSelected & ">"
> case "Category"
> strCat = strCat &
v> aField.Value
> end select
> next
> strCat = strCat & "</option>"
> objRS.MoveNext
> loop
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
> Response.Write "<select style='width: 100%'
n> ame='Category'>" & strCat & "</select>"
%> >
> </td>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Manufacturer</b></font></small>
> <br>
> <input maxlength="50" type="text"
n> ame="Manufacturer" value="<%=strManufacturer%>"></input>
> </td>
> <td width="33%" align="center">
> <small><font
c> olor="#ff0000"><b>Model</b></font></small>
> <br>
> <input maxlength="50" type="text"
n> ame="Model" value="<%=strModel%>"></input>
> </td>
> </tr>
> <tr>
> <td align="center">
> <small><font
c> olor="#ff0000"><b>Test Status</b></font></small>
> <br>
<> %
> 'This code block creates and populates drop down list with
T> estStatus table contents. it is similar to the
> 'code for creating a drop list for existing POC records above
>
> 'declare variables to hold TestStatus and a variable(vaField
a> lready defined above) to iterate thru the recordset fields collection
> dim strTest, blnTemp
>
> strTest = "<option value='none'>Choose One</option>"
> strSql = "Select * from TestStatus;"
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnect
> objComm.CommandType = adCmdText
> objComm.CommandText = strSQL
> set objRS = objComm.Execute
>
> 'concatenate the Test Status records for users to choose
f> rom and create the <option tags> with TestStatusID value
> do while not objRS.EOF
> for each vaField in objRS.Fields
> select case vaField.Name
> case "TestStatusID"
> 'checks to see the
v> alue in case of error, same value will be displayed on page
> strSelected = ""
> if strTestStatus
<> > "none" then
> if clng
(> strTestStatus) = vaField.Value then strSelected = "selected"
> end if
> 'begins
c> oncatenation of option and inserts the ID from the database in the
value
a> ttribute
> strTest = strTest
&> "<option value='" & vaField.Value & "' " & strSelected & ">"
> case "TestStatus"
> strTest = strTest
&> vaField.Value
> end select
> next
>
> strTest = strTest & "</option>"
> objRS.MoveNext
> loop
> objRS.Close
> set objRS = Nothing
> set objComm = Nothing
> Response.Write "<select style='width: 100%'
n> ame='TestStatus'>" & strTest & "</select>"
%> >
> <%
'> the remaining HTML and ASP code just completes the building of the
table
a> nd form
%> >
> </td>
> <td align="center">
> <small><b>Vendor</b></small>
> <br>
> <input type="text" name="Vendor"
v> alue="<%=strVendor%>"></input>
> </td>
> <td align="center">
> <small><b>Approx Cost
&> #36;</b></small>
> <br>
> <input type="text" name="Cost"
v> alue="<%if strCost = "" then Response.Write 0 else Response.Write
strCost%
>> "></input>
> </td>
> </tr>
> <tr>
> <td align="LEFT">
> <b><small>Date Approved (mm/dd/yy)</small></b>
> <br>
> <input type="text"
n> ame="DateApproved" value="<%=strDateApproved%>">
> </td>
> <td align="center">
> <small><b>OPFAC</b></small>
> <br>
> <input maxlength="5" type="text"
n> ame="OPFAC" value="<%=strOPFAC%>"></input>
> </td>
> <td align="center">
> <small><b>LCCB Request
N> br</b></small>
> <br>
> <input type="text"
n> ame="LCCBRequestNbr" value="<%=strLCCBRequestNbr%>"></input>
> </td>
> </tr>
> <tr>
> <td bgcolor="#cccccc" align="center">
>
> </td>
> <td align="center">
> <small><b>Certified By</b></small>
> <br>
> <input type="text"
n> ame="CertifiedBy" value="<%=strCertifiedBy%>">
> </td>
> <td bgcolor="#cccccc" align="center">
>
> </td>
> </tr>
> <tr>
> <td align="center" colspan="3">
> <small><b>Notes &
C> omments</b></small>
> <br>
> <textarea name="Notes" cols="40"
r> ows="5"><%=strNotes%></textarea>
> </td>
> </tr>
> <tr>
> <td align="center">
> <input type="submit" name="submit"
v> alue="Submit"></input>
> </td>
> <td bgcolor="#cccccc" align="center">
> <small><a
h> ref="lccbhardwaremain.asp"><font color="#333399"><u>Back to Main
M> enu</u></font></a></small>
> </td>
> <td align="center">
> <input type="reset" name="reset"
v> alue="Reset"></input>
> </td>
> </tr>
> </table>
<> br>
<> /td>
<> /tr>
<> /table>
> </form>
>
>
> <br><br>
<> /body>
<> /html>
>
>
>
>
>
Message #3 by "Ken Schaefer" <ken@a...> on Tue, 17 Dec 2002 11:16:07 +1100
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Tadd Brown" <tadese98@h...>
Subject: [access_asp] Re: Data type mismatch in criteria expression
: I need help with the following question:
:
: Thank you
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HAHAHAHAHAHAHAHA
Sorry, I couldn't resist. There's 1531 (!!!) lines of code that you posted.
I pasted it into Word, and it was 31 pages long! Do you think anyone's going
to read through it all?!? :-)
Try this:
http://www.adopenstatic.com/faq/80040e07.asp
Next time, just post the relevant bits of code (for example the ten lines
leading up to the one that is causing the error and any SQL statement you
may be using).
Cheers
Ken
|
|
|
View Cart
