Inserting dataset with 3 related datatables

[Guest]

Greetings all,

Here's my situation:
Background: I have an application for that has company data and survey response data.
The survey questions, answers and responses are being stored in the database.
The answers can be either free form text or an answer stored in a table.

The Database schema:

Company |
-----------------------------------------------
CompanyID | Name | Address | email |
-----------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SurveyQuestions
-----------------------------------------------
QuestionID | Description
-----------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SurveyAnswers
----------------------------------------------------
AnswerID | Description | QuestionID (FK)
----------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SurveyResponses
------------------------------------------------------------------------------
ResponseID | CompanyID (FK) | QuestionID (FK) | AnswerID (FK)
------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SurveyResponseText
------------------------------------------------------------------------------
ResponseTextID | ResponseID (FK) | ResponseText
------------------------------------------------------------------------------

My problem is with inserting...
I have a custom dataset object to hold the the company and survey response data, that consists of 3 datatables (company, surveyresponse, surveyresponsetext) with datarelations set up for the 3.

Question is how do I perform the insert with a stored procedure (sql 2000)? Can I simple call a Update() on the SqlDataAdpater? or Fill()?

the company datatable has a single datarow, response tables have multiple datarows.

sample data:

______________________________________________________
Companyid | Name | Address | eMai
----------------------------------------------------------------------------
2 | my enterprise | 1 test street | (e-mail address removed)
----------------------------------------------------------------------------
______________________________________________________
ResponseID | QuestionID | AnswerID | CompanyI
----------------------------------------------------------------------------
1 | 1 | 2 |
----------------------------------------------------------------------------
______________________________________________________
ResponseTextID | ResponseText | ResponseI
-----------------------------------------------------------------------------
1 | We sell lots of stuff. |
-----------------------------------------------------------------------------

Any help is greatly apprecitated.

Thanks,
Justin

 

[Cor]

Hi Justin,

Same message I did send bellow

Maybe you can check some samples in this greath resource kit.

http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing it

http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir
I hope this helps a little bit?

Cor

"

 

[Guest]

Thanks Cor,
I'm not much of a VB programmer, so that doesn't quite help me. Is there a C# version

And the difference from the previous post is I'm concerned with Inserting not Updating. (Unless of course it works the same way) :

Regards
Justin

 

[Cor]

Hi Justin,

There is a resource kit that looks like this and holds also C# samples.

http://msdn.microsoft.com/asp.net/asprk/

It says aspnet but I saw that there are a lot of windowsform samples in it

I think your problem is just insterting rows, in a database. For that I
would not use the dataadapter (while I am one who advices it probably the
most in this newsgroups).

I would just do it is na kind of streaming way. This is a sample in VB.net,
I do not have it in C#. You should have to understand it because the only
difference is almost, the dim the {} and the ;. If you do not understand
it, say it, than Iwill translate it for you to C#.

\\\
Try
Dim strSQL As String = "INSERT INTO tblMessages (Problem, Answer) VALUES
(@Problem, @answer)"
Dim cmd As New SqlCommand(strSQL, Conn)
cmd.Parameters.Add(New SqlParameter("@Problem", SqlDbType.NVarChar,
200)).Value = Problem
md.Parameters.Add(New SqlParameter("@answer", SqlDbType.NVarChar,
2500)).Value = Answer
Conn.Open()
cmd.ExecuteNonQuery()
Conn.Close()
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "", MessageBoxButtons.OK,
MessageBoxIcon.Error)
Catch ex As Exception
frmStatusMessage.Close()
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK,
MessageBoxIcon.Error)
Finally
///

I hope this helps

Cor