Insert records from user form to multiple tables in same database

[sham_]

Hi Experts, I need your help. I have a form that has multiple text fields that I need to insert the records to their each respective table. For example: The first name field needs to go the the name table, address goes to a seperate table, phone goes to a seperate table and so on.

Is this possible to do?

Thanks Much

Sham

[Guest]

I'd offer a solution but you didn't mention your query language so I don't want to waste time providing an example that you may not need. Lesson: mention your scripting and query language in your post if you want someone to provide you with an example.

[bnbwalt]

Regardless of the query language, the answer is yes.  You need to create separate insert queries for each table value group in the server side code that responds to the submit on the web from.

Walter

B & B Photography

[bnbwalt]

To be a bit more specific, it would look like this logically.

Insert into table A values for table A fields from form

Insert into table B values for table B fields from form

Insert into table C values for table C fields from form

If you are using transactions, highly recommended, surround the three statements with a start transaction and end transaction.  That way, if any one of the inserts fail, the database will remain in a coherent state because all will fail. 

Walt

B & B Photography

[sham_]

Thanks guys, I don't know much PHP so I waas hoping to use ASP with an access database.

[bnbwalt]

The same for ASP, JSP or PHP.  Just the code syntax is different for each language.  Are you using MySQL, Access or ?

In any case, create the three recordsets on the database and use insert queries.  If you don't know what one is, google insert query and you will see the syntax. 

Here is a good example out on the web

http://www.stardeveloper.com/articles/display.html?article=2000032601&page=1

In your case you would get the values for the inserts from the form data and only need to open the database connection once.

Good luck.

Walt

B & B Photography

[sham_]

Thanks Walt. This is what I have so far. Do you know if I can combine the

2 commands into 1? Also could you explain to me how to redirect the user after the information has been submitted?

****

<%
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Call_access_STRING
Command1.CommandText = "INSERT INTO tbl_Rd1_A1 (Ans1)  VALUES ('" + Replace(Command1__var1, "'", "''") + "') "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
%>
<%
set Command2 = Server.CreateObject("ADODB.Command")
Command2.ActiveConnection = MM_Call_access_STRING
Command2.CommandText = "INSERT INTO tbl_Rd1_A2 (Ans2)  VALUES ('" + Replace(Command2__var2, "'", "''") + "') "
Command2.CommandType = 1
Command2.CommandTimeout = 0
Command2.Prepared = true
Command2.Execute()
%>

******

I truly aprreciate your help in this

[bnbwalt]

The easies way is to use the redirect statement

Response.Redirect "URL"

You can google for more information.

I would suggest that you test to make sure the inserts worked and back out the changes that did not fail and redirect to a page that states the error occured and if it did not fail, then redirect to the next page in the process.

Good luck

Walt

B & B Photography

[bnbwalt]

I forgot to respond to your other question.  I don't believe you can combine them as inserts are considered atomic actions on a database.

Walt

B & B Photography

[bregent]

>Do you know if I can combine the2 commands into 1?

Create a stored procedure and call that instead.

[bnbwalt]

That would definitely work.  I would recommend that you getting it working as is first and then try the stored procedure route.  Less things to test at the same time 🙂

Walt