Hello, I'm just starting to learn ASP and Javascript. I was testing out Riccardi's BigHit Video website, and was able to add customers into the database nicely. However, when I try to add an employee things start getting messy. I basically took the adding of a customer, and modified it to add a new employee. The customer's ID number was based on adding 1 to the largest ID already in the database. The ID for employees is their SSN, which has to be unique. I was trying to write code to make it check for duplicate SSNs, but it is giving me an error. Please guide me through this, and let me know my error. This is what the computer gives me:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ssn='.
/bighit/bighittools.js, line 141
...
function lookupCustomer(conn, id) {
var customerSQL="select * from Customer where accountId="+id;
var custSet = conn.Execute(customerSQL);
// check to see if any row was returned
var customer = new Object();
customer.accountId = new String(custSet("accountId"
));
customer.firstName = new String(custSet("firstName"
));
customer.lastName = new String(custSet("lastName")
);
customer.street = new String(custSet("street"));
customer.city = new String(custSet("city"));
customer.state = new String(custSet("state"));
customer.zipcode = new String(custSet("zipcode"))
;
return customer;
}
function lookupEmployee(conn, id) {
var employeeSQL="select * from Employee where ssn="+id;
var emplSet = conn.Execute(employeeSQL);
// <-------------------------
----------
- line 141, error is pointing to this. Why?
// check to see if any row was returned
var employee = new Object();
employee.ssn = new String(emplSet("ssn"));
employee.firstName = new String(emplSet("firstName"
));
employee.lastName = new String(emplSet("lastName")
);
return employee;
}
...
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--------
CODE:
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--------
<%@LANGUAGE="JScript"%>
<!-- makeemployee.asp -->
<html>
<!-- #include file="bighittools.js" -->
<%
startApplication("makeempl
oyee.asp")
;
// get parameter values
var employee;
employee = makeEmployee(Request);
with (employee) {
printHeader("New Employee Receipt","Welcome "+firstName+" "+lastName);
// connect to database
conn = Server.CreateObject("ADODB
.Connectio
n");
conn.Open("BigHitVideo");
// check for duplicates
dupId = conn.Execute("select ssn from Employee");
dupId = executeSQL("select ssn from Employee");
employee = lookupEmployee(conn,dupId)
;
if (employee.ssn == dupId) {// no new employee
Response.Write("<br>Cannot
add duplicate employee.<p>\n");
endApplication("makeemploy
ee.asp")
}
// insert employee
newEmplSQL = "insert into Employee "
+"(ssn, firstName, lastName)"
+" values("
+sqlString(ssn)+", '"
+sqlString(firstName)+"','
"
+sqlString(lastName)+"')";
//Response.write("<br>SQL:
"+newEmplSQL+"<br>");
//conn.Execute(newEmplSQL)
; // replaced with function call
executeSQL(newEmplSQL);
} // end with employee
// fetch employee information from the database
employee = lookupEmployee(conn,ssn);
if (employee.ssn != ssn) {// no new employee
Response.Write("<br>No new employee with account ID: "+ssn+"<p>\n");
employee = makeEmployee(Request);
}
printEmployeeTable(employe
e);
printFooter("addemployee.a
sp");
endApplication("makeemploy
ee.asp");
%>
</html>
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--------
Thank you.
Start Free Trial
