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?
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?