UOC
asked on
Insert data into 2 tables with different column names
I want to insert the same data into 2 tables but because I have to declare the variables it will not work as the fieklds have dfifferent names e.g. 'SURNAME' is the same as 'NSM_sname' and 'FIRST_NAME' is the same as 'NSM_fname'. See attached code.
I think this is the problem as each time I try to run the code I get the following error -
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Surname'.
/intranet/departments/comp uterservic es/service s/add_to_a rs_user.as p, line 90
Line 90 is second execute command attached.
If I remove the second INSERT command the code works
Any ideas how to resolve this?
I think this is the problem as each time I try to run the code I get the following error -
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Surname'.
/intranet/departments/comp
Line 90 is second execute command attached.
If I remove the second INSERT command the code works
Any ideas how to resolve this?
Dim SURNAME, FIRST_NAME, NSM_fname, NSM_sname
if request.form("NSM_fname") <>"" then
sSQL = "INSERT into staff (staff.SURNAME, staff.FIRST_NAME, staff.username, staff.usertype) values ('" & _
(Request.Form("NSM_sname")) & "', '" & (Request.Form("NSM_fname")) & "', '" & (Request.Form("username")) & "', '" & (Request.Form("intext")) & "')"
end if
connection.execute(sSQL)
if request.form("category") = "New Staff Member" then
sSQL = "insert into ars_NSM (NSM_fname, NSM_sname, NSM_position, NSM_title, NSM_emp_stat, uid) values ('" & _
(Request.Form("NSM_fname")) & ", '" & (Request.Form("NSM_sname")) & "', '" & (Request.Form("NSM_position")) & "', '" & (Request.Form("NSM_title")) & "', '" & (Request.Form("NSM_emp_stat")) & "', '" & (Request.Form("id")) & "')"
end if
connection.execute(sSQL)
ASKER
I didn't get an error this time but the insert into the ars_NSM table (the second insert) is not working (the first is). Is it because we are using an ELSEIF statement for the second insert when the first IF statement was valid?
ASKER
I have it inserting now - I had missed a quotation mark in the query - (Request.Form("NSM_fname") ) & ", should have been (Request.Form("NSM_fname") ) & "',
Anyway, insert now working except for the firstname and surname.
I have tried changing the field names to all be the same i.e. FIRST_NAME / FIRST_NAME instead of FIRST_NAME / NSM_fname but to no avil, it will still not insert the names although everything else does insert.
I've no idea what to do so any help appreciated.
Anyway, insert now working except for the firstname and surname.
I have tried changing the field names to all be the same i.e. FIRST_NAME / FIRST_NAME instead of FIRST_NAME / NSM_fname but to no avil, it will still not insert the names although everything else does insert.
I've no idea what to do so any help appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Carrzkiss,
That appears to have worked.
Thanks
That appears to have worked.
Thanks
Glad that I could help.
Keep up the great work.
Carrzkiss
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-
Author Comments:
Hi Carrzkiss,
That appears to have worked.
Thanks
Keep up the great work.
Carrzkiss
=-=-=-=-=-=-=-=-=-=-=-=-=-
Author Comments:
Hi Carrzkiss,
That appears to have worked.
Thanks
Carrzkiss
Open in new window