Here the solution
1. Create a type for tblQuestion in the database
CREATE TYPE [dbo].[tblQuestionType] AS TABLE(
[question] ntext NULL,
[creationdate] datetime NULL
)
2.Create a type for the tblAnswerChoice in the database
create TYPE [dbo].[tblAnswerChoiceType] AS TABLE(
[answerchoice] ntext NULL
)
3.Create a stored procedure to accept two table type parameters
create procedure [dbo].[spInsert] @dtquestion tblQuestionType READONLY,@dtanswerchoice tblAnswerChoiceType READONLY as
Begin
INSERT INTO [tblQuestion]
([question]
,[creationdate])
select [question],[creationdate] from @dtquestion
insert into tblAnswerChoice
(QID,AnswerChoice)
select @@IDENTITY,answerchoice
from @dtanswerchoice
End
GO
4.Now, your front end should be
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable odtQuestion = new DataTable();
odtQuestion.Columns.Add("qestion");
odtQuestion.Columns.Add("creationdate",typeof(DateTime));
odtQuestion.Rows.Add("Quesion A", dateTimePicker1.Value);
DataTable odtAnswerChoice = new DataTable();
odtAnswerChoice.Columns.Add("answerchoice");
odtAnswerChoice.Rows.Add("Choice 1");
odtAnswerChoice.Rows.Add("Choice 2");
odtAnswerChoice.Rows.Add("Choice 3");
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = "Data Source=SOLAP;database=Sample;user=sa;password=admin1990";
oConn.Open();
SqlCommand oCmd = oConn.CreateCommand();
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.CommandText = "SpInsert";
oCmd.Parameters.AddWithValue("@dtquestion", odtQuestion);
oCmd.Parameters.AddWithValue("@dtanswerchoice", odtAnswerChoice);
oCmd.ExecuteNonQuery();
MessageBox.Show("Suceess");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
I have tested this and it inserts tblquestion (hope its one row all the time) and the correspoding record into tblAnswerChoice.
Please note that the @@identity will have the last autonumber inserted into tblQuestion and hence its used in the tblAnswerChoice.
Also, you have to add a datetimepicker to the front end and i have used the value of it for the creation date
Hope this resolves you issue.
Bala