[RESOLVED] Needing your expertise - assign database value to array

Dim conn As New OleDbConnection("connection string")
Dim cmd As OleDbCommand

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
cmd = New OleDbCommand(String.Format("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = {0} and CorrectAnswer = 'Y'", x), conn)
CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next

try this:

Code:

---

Dim conn As New OleDbConnection("connection string")
Dim cmd As OleDbCommand

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
    cmd = New OleDbCommand(String.Format("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = {0} and CorrectAnswer = 'Y'", x), conn)
    CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next

---

Thank you Paul! Will definitely try it on my next shift at work and will let you know and rate you as well if it works!

Quote:

---

Originally Posted by .paul.

try this:

Code:

---

Dim conn As New OleDbConnection("connection string")
Dim cmd As OleDbCommand

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
    cmd = New OleDbCommand(String.Format("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = {0} and CorrectAnswer = 'Y'", x), conn)
    CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next

---

---

I would tend to do that slightly differently:

Code:

---

Dim conn As New OleDbConnection("connection string")
Dim cmd As New OleDbCommand("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = @QuestionID and CorrectAnswer = 'Y'", conn)
Dim prm = cmd.Parameters.Add("@QuestionID", OleDbType.Integer)

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
    prm.Value = x
    CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next

---

Don't forget that the connection requires opening and closing too.

jmcilhinney's Parameters solution is the best choice for your app...

Dim conn As New OleDbConnection("connection string")
Dim cmd As OleDbCommand

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
cmd = New OleDbCommand(String.Format("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = {0} and CorrectAnswer = 'Y'", x), conn)
CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next

Dim conn As New OleDbConnection("connection string")
Dim cmd As OleDbCommand

Dim CorrectAnswer(19) As String
For x As Integer = 1 To 20
cmd = New OleDbCommand(String.Format("Select AnswerValue from tbl_ECM_Questionnaire where QuestionID = {0} and CorrectAnswer = 'Y'", x), conn)
CorrectAnswer(x - 1) = cmd.ExecuteScalar.ToString
Next