ASP.NET and SQL - inserting data into more than one table?

Question

So I know similar questions have been asked before, but I couldn't find a clear answer for my specific situation. I'm using ASP.NET (in Visual Web Developer) and I need to insert data from one form into two separate tables. This is my data source:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
    DataFile="~/App_Data/courseinfo.mdb" 
    SelectCommand="SELECT * FROM [tableCourse], [tableFaculty]"
    InsertCommand="INSERT INTO [tableCourse] 
    ([department], [name_first], [name_last], [prefix], 
    [course_number], [credits], [title], [description])
    VALUES (?, ?, ?, ?, ?, ?, ?, ?); INSERT INTO [tableFaculty] ([name_first], [name_last], [phone], [email])
    VALUES (?, ?, ?, ?)">

So you see I've tried using two insert statements, and it just comes back with an error saying there are extra characters after the SQL statement. I've tried taking out the semi-colon and then it says I'm missing a semi-colon. Is it possible to insert to two tables at once using this control? And if not, how do I work around this?

UPDATE:

Okay, tried it in the codebehind, but I don't think I did it right, now it's giving me this error:

Server Error in '/CCC' Application. Index or primary key cannot contain a Null value. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Index or primary key cannot contain a Null value.

Source Error:

Line 87: 
Line 88:         AccessDataSource1.InsertCommand = "INSERT INTO [tableCourse] ([department], [name_first], [name_last], [prefix], [course_number], [credits], [title], [description]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
Line 89:         AccessDataSource1.Insert()
Line 90: 
Line 91:         AccessDataSource1.InsertCommand = "INSERT INTO [tableFaculty] ([name_first], [name_last], [phone], [email]) VALUES (?, ?, ?, ?)"

Line 89 is the one that's highlighted. So I'm thinking it's attempting to insert but for some reason the values are null, it isn't taking the values from the text boxes. I probably left something obvious out, I don't know, I'm really new at this.

Your code behind, is aware of only about 8 parameters not 16. — Jahan Zinedine
– Jahan Zinedine, Commented Dec 15, 2010 at 20:19
I am in agreement wit ncakmak. You should probably attempt this in the bode-behind rather than doing so in this fashion. It's very clumsy and error prone this way. Plus you have the issue of your parameters not being known by your code-behide as well. — Matthew Cox
– Matthew Cox, Commented Dec 15, 2010 at 22:00
Your code looks right. What is the name of your primary key field, and is it set to autoincrement? — Daniel Szabo
– Daniel Szabo, Commented Dec 16, 2010 at 1:06
For now I have prefix and course_number as the primary key fields for tableCourse, and name_first and name_last as the primary key fields for tableFaculty. Just using those to test it for now. — Sara
– Sara, Commented Dec 16, 2010 at 1:21

ncakmak · Accepted Answer · 2010-12-15 21:38:24Z

1

Why don't you give it a try at the codebehind?

AccessDataSource1.InsertCommand = "First INSERT Statement";
AccessDataSource1.Insert();

AccessDataSource1.InsertCommand = "Second INSERT Statement";
AccessDataSource1.Insert();

answered Dec 15, 2010 at 21:38

ncakmak

4,0645 gold badges22 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET and SQL - inserting data into more than one table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related