Results 1 to 7 of 7

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

Hybrid View

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Resolved [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
    Last edited by Serigraphie; May 14th, 2015 at 01:11 PM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Needing your expertise - assign database value to array

    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

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Needing your expertise - assign database value to array

    Quote Originally Posted by .paul. View Post
    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.

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Re: Needing your expertise - assign database value to array

    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!

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Needing your expertise - assign database value to array

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

  6. #6

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Re: 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
    Last edited by Serigraphie; May 14th, 2015 at 01:12 PM.

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Re: 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
    Last edited by Serigraphie; May 14th, 2015 at 01:12 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width