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?
 
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.
 
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.
 
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.
 
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:
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
 
In your first post you have 2 datatable;QuestionDS ("questions") and QuestionDT. You are then copying all of QuestionDS ("questions") into QuestionDT.
 
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:
 
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:
 
Back
Top