Link to home
Start Free TrialLog in
Avatar of UOC
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/computerservices/services/add_to_ars_user.asp, 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?



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)

Open in new window

Avatar of Wayne Barron
Wayne Barron
Flag of United States of America image

Untested but try and see if it will work now.

Carrzkiss
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")) & "')" 
connection.execute(sSQL)
 
elseif 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")) & "')" 
 
connection.execute(sSQL)
end if

Open in new window

Avatar of UOC
UOC

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?
Avatar of UOC

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.
ASKER CERTIFIED SOLUTION
Avatar of Wayne Barron
Wayne Barron
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of UOC

ASKER

Hi Carrzkiss,

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