Arrrgg...why can I load this datatable??

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
I have a small problem, which I've spend the whole day trying to work out, and need some help on. I'm retrieving data from two tables, and putting into a datatable.

Heres the code:

Try
'Open up the connection object
SQLtestconn = New SqlConnection
SQLtestconn.ConnectionString = connectionstring
SQLtestconn.Open()

strtext = ""
strtext = strtext & "SELECT Questions.Question_ID, Questions.Question, Answers.Answer_ID, Answers.Answer, Answers.CorrectAnswer, Answers.Quest_ID"
strtext = strtext & " FROM [Questions] INNER JOIN"
strtext = strtext & " Answers ON Questions.Question_ID = Answers.Quest_ID"
strtext = strtext & " WHERE (Questions.Test_ID = '" & TestID & "')"

Dim QuestionDA As New SqlDataAdapter(strtext, connectionstring)

'Use to load the data
Dim QuestionDS As New DataSet
Dim QuestionDT As New DataTable
Dim QuestionDSRow As DataRow
Dim QuestionNewRow As DataRow

QuestionDA.Fill(QuestionDS, "questions")
QuestionDT.Columns.Add("Question_ID", GetType(System.String))
QuestionDT.Columns.Add("Question", GetType(System.String))
QuestionDT.Columns.Add("Answer_ID", GetType(System.String))
QuestionDT.Columns.Add("Answer", GetType(System.String))
QuestionDT.Columns.Add("CorrectAnswer", GetType(System.String))
QuestionDT.Columns.Add("Quest_ID", GetType(System.String))

For Each QuestionDSRow In QuestionDS.Tables("questions").Rows()
QuestionNewRow = QuestionDT.NewRow()

QuestionNewRow("Question_ID") = QuestionDSRow("Question_ID")
QuestionNewRow("Question") = QuestionDSRow("Question")
QuestionNewRow("Answer_ID") = QuestionDSRow("Answer_ID")
QuestionNewRow("Answer") = QuestionDSRow("Answer")
QuestionNewRow("CorrectAnswer") = QuestionDSRow("CorrectAnswer")
QuestionNewRow("Quest_ID") = QuestionDSRow("Quest_ID")

MsgBox("Question: " & QuestionDSRow("Question"))
MsgBox("Answer: " & QuestionDSRow("Answer_ID") & " : " & QuestionDSRow("Answer"))

QuestionDT.Rows.Add(QuestionNewRow)
Next


Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
Finally
SQLtestconn.Close()
End Try

Question_ID is the question table
Quest_ID is from the answers table


Right, when I run the program is displays 'A column named Question_ID is already belongs to this datatable'. Both the tables Questions and Answers have a Question_ID which related the question to four possible choice answers, which I need, for displaying in the form.

I've tried changing the name of the column in the database, but it still says thats it brings the column back twice. I tried putting the questions and answers in different datatables and I get the same message. Is there anyway I can use the same column twice in a datatable?
 

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
Forgot to add, I'm doing this as a mobile application...which means using the Compact Framework.

Run the SQL and here is the data:

Question_ID Question Answer_ID Answer CorrectAnswer Quest_ID
1 What is OOP? 1 Object oriented programming allows allows you to construct programs which use objects Y 1
1 What is OOP? 2 OOP allows you to program easier N 1
1 What is OOP? 3 OOP is design methodology N 1
1 What is OOP? 4 Object oriented programming is a structured language N 1
2 What is a object 5 An object is a program theory N 2
2 What is a object 6 objects are self-contained entities which have a state and to which messages can be sent Y 2
2 What is a object 7 object is add-in to java N 2
2 What is a object 8 object is a design tool N 2

With this datatable I want to allow the user to answer each question with a multiple choice answer.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Um, ok. Why do you want to fill 2 datatables with the same info?
Which line are you getting the error on?

TPM

I'd also just have your connection open while you fill you dataset, it's not good to have connections open longer than you need.
 

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
The error occurs I think when it trys to add the Quest_ID to the datatable, because it knows Question_ID in the Question table has a relationship with the Quest_ID in the Answers table. So it breaks at:

QuestionNewRow("Quest_ID") = QuestionDSRow("Quest_ID")

I've tried using two datatables, and splitting the SQL statement to return the questions in one table and the answers in the other, but still got the same error. I really want to return everything in the same datatable, as I will need to use a loop to display the fields in the datatable onto the form.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Ok looping through a datatable copying it all to anothe datatable just doesn't make sense. Just fill them, it'll be so much faster, and less code.
 
Last edited:

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
Hmm sorry for the confusion....and I'm a newbie when it comes to vb.net but the sample code which I posted...it that of ONE datatable which I'm having problems to load data into. I did try using two datatables before however to no success. (I seriously do not explain complex things to well lol).

Is there any loading one datatable with two columns of the same name and related (this is what is causing the error)

Thanks for your comments

Kind Regards

Brenden
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
In your first post you have 2 datatable;QuestionDS ("questions") and QuestionDT. You are then copying all of QuestionDS ("questions") into QuestionDT.
 

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
hmm...isn't..QuestionDS ("questions") a dataset, I thought you needed to put all the records in a dataset first and then fill up a datatable. Are dataset and datatable the same thing?? :confused:
 

Brenden

Member
Joined
Mar 30, 2005
Messages
6
Programming Experience
3-5
Learn the difference between a dataset and datatable, and not bothering to copy the records again into the datatable, I'm use to using the old VB6 recordsets...bloody much easier :rolleyes:
 
Top Bottom