Insert data into correct Access field as a result of selection made

Hi,

What "issues" are you having?

FWIW  - if any field has an apostrophe in it the code will fail.

Also, check the data types for correctness.  If the DB is MS Access and the field event_date is a date/time field then you need to put # around the date value:
...' "& request.Form("event_date") &" '...

becomes
...# "& request.Form("event_date") &" #...

and no single quotes are needed around numeric fields.  If cle_credit is numeric then
...' "& request.Form("cle_credit") &" '...

becomes
... "& request.Form("cle_credit") &"...

In general, try the insert statement inside MS Access, get it correct then build it in ASP.

Regards,

Bill

Thank you. Does the SQL statement structure look correct?

I'm not an asp expert but I don't think you can use ELSE or End If.

If is evaluated or not on the next line.  The correct syntax might be:

if request.form("location") = "TPA" Then
SQL = "..."

if request.form("location") = "ORL" Then
SQL = "..."

if request.form("location") = "JAX" Then
SQL = "..."

if request.form("location") = "FTM" Then
SQL = "..."

conn.execute(SQL)

Next also seems unnecessary...

Regards,

Bill

I am trying to remove the single quotes from insertions into the database but cannot figure out how to do so. I tried using padas suggestion but that did not work. When I insert words containing single quotes, I get a Microsoft JET Database Engine error '80040e14' Syntax error.

I am using padas code suggestion to no avail. Any assistance is greatly appreciated. Thank you.

<%


select case request.form("location") 

	Case "TPA" 
		SQL = "insert into TPA (manager, program_title, event_date, event_time, event_duration, cle_credit, video, add_comments) values ('"& request.Form("manager") &"', ' "& request.Form("program_title") &" ', ' "& request.Form("event_date") &" ', ' "& request.Form("event_time") &"', ' "& request.Form("event_duration") &" ', ' "& request.Form("cle_credit") &" ', ' "& request.Form("video") &" ', ' "& request.Form("add_comments") &"')"

	Case "ORL" 
		SQL = "insert into ORL (manager, program_title, event_date, event_time, event_duration, cle_credit, video, add_comments) values ('"& request.Form("manager") &"', ' "& request.Form("program_title") &" ', ' "& request.Form("event_date") &" ', ' "& request.Form("event_time") &"', ' "& request.Form("event_duration") &" ', ' "& request.Form("cle_credit") &" ', ' "& request.Form("video") &" ', ' "& request.Form("add_comments") &"')"

	Case "JAX" 
		SQL = "insert into JAX (manager, program_title, event_date, event_time, event_duration, cle_credit, video, add_comments) values ('"& request.Form("manager") &"', ' "& request.Form("program_title") &" ', ' "& request.Form("event_date") &" ', ' "& request.Form("event_time") &"', ' "& request.Form("event_duration") &" ', ' "& request.Form("cle_credit") &" ', ' "& request.Form("video") &" ', ' "& request.Form("add_comments") &"')"

	Case "FTM" 
		SQL = "insert into FTM (manager, program_title, event_date, event_time, event_duration, cle_credit, video, add_comments) values ('"& request.Form("manager") &"', ' "& request.Form("program_title") &" ', ' "& request.Form("event_date") &" ', ' "& request.Form("event_time") &"', ' "& request.Form("event_duration") &" ', ' "& request.Form("cle_credit") &" ', ' "& request.Form("video") &" ', ' "& request.Form("add_comments") &"')"

	Case else
		SQL=""

end select

if SQL<>"" then

	strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\ncb9009\registration\db\ncb.mdb") & ""
	set conn = server.createobject("adodb.connection")
	conn.open strconn
	conn.execute(SQL)

 	response.redirect "thankyou.asp" 

 	else
 	response.write "There was an error: Invalid location."

 end if
  %> 

Select allOpen in new window