Index + 1 not updating SQL Table

You are declaring  Dim intSeq As Integer = lbQuestions.SelectedIndex within the For loop and it is getting reset for every question?

Tried that, see below:

Dim intSeq As Integer = lbQuestions.SelectedIndex + 1
        Dim intIndex As Integer = lbQuestions.SelectedIndex
        Dim objItem As Object
        Dim sqlDA As New SqlClient.SqlDataAdapter
        sqlDA.UpdateCommand = New SqlClient.SqlCommand
        sqlDA.UpdateCommand.Connection = cn
        sqlDA.UpdateCommand.CommandType = CommandType.Text

        Try
            If lbQuestions.SelectedItems.Count > 0 Then
                For Each objItem In lbQuestions.SelectedItems
                    Dim intQ_Key As Integer = lbQuestions.Items(intIndex).ToString.Split(","c)(0)
                    sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & intSeq & "'" & _
                    "WHERE Q_Key = '" & intQ_Key & "'"
                    sqlDA.UpdateCommand.ExecuteNonQuery()
                Next
            End If
        Catch err As SqlException
            MsgBox(err.Message)
        Finally
            cn.Close()
        End Try

Same results.
JackW9653

Just taking a quick look cause I'm in a hurry but I don't see where you are incrementing the variable. Also there is another variable inside the loop, Dim intQ_Key As Integer, it may belong there but I don't know. It will also get reset which as I said could be by design, just something to look at.

this block, it should try to get value using each selected item

            If lbQuestions.SelectedItems.Count > 0 Then
                For Each objItem In lbQuestions.SelectedItems

   you declare For Each "objItem" here
   so, you should obtain intSeq and intQ_Key from "objItem", not from lbQuestions.
   or at least, you must get reference number from "objItem" (for ex, array number)

   two lines below, MUST have something to do with objItem

                    Dim intSeq As Integer = lbQuestions.SelectedIndex
                    Dim intQ_Key As Integer = lbQuestions.Items(intSeq).ToString.Split(","c)(0)



  Note : since I don't have .NET on my machine, I can't give exact code to you.
             hope I point you out some clue.

Thanks for the reply Joe,

I tried several variation of using objItem as the source and nothing worked. I'm still getting 0 and 1 as indexes.

trying to read your code, and I'm not yet understand what are you trying to do

let say we have following 4 items in lbQuestions, 2 of them selected.
ListIndex   ListItems
0               1, This
1               3, is         (selected)
2               4, Hello
3               6, World  (selected)

what is your expected update query from above example? Is it as below?

UPDATE tblQuestionnaire SET Sequence = (1+1) WHERE Q_Key = 3
UPDATE tblQuestionnaire SET Sequence = (3+1) WHERE Q_Key = 6

Sorry for the delay was putting out another fire. The idea is for the sub to go through the SelectedItems in lbQuestions and add 1 to the Index. So yes your queries are correct.

This code works if only 1 item is selected at a time:

        Dim intSeq As Integer = lbQuestions.SelectedIndex + 1
        Dim intIndex As Integer = lbQuestions.SelectedIndex
        Dim intQ_Key As Integer = lbQuestions.Items(intIndex).ToString.Split(","c)(0)

        Dim strName As String = txtQ_Name2.Text
        Dim sqlDA As New SqlClient.SqlDataAdapter
        sqlDA.UpdateCommand = New SqlClient.SqlCommand
        sqlDA.UpdateCommand.Connection = cn
        sqlDA.UpdateCommand.CommandType = CommandType.Text

        Try
            For Each li As String In lbQuestions.SelectedItems
                sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & (intSeq) & "'" & _
                        "WHERE Q_Name = '" & strName & "' AND Q_Key = '" & intQ_Key & "'"
                sqlDA.UpdateCommand.ExecuteNonQuery()
                MsgBox(intQ_Key & "' - '" & intSeq & "' - '" & intIndex)
            Next
        Catch err As SqlException
            MsgBox(err.Message)
        Finally
            cn.Close()
        End Try

If more that one item is selected it just repeats the first value how ever many items are selected. In your example 3 , 2 would be repeated 2 times.

Hope this helps,

JackW9653

if so, as I comment earlier,
you have to get intSeq and Q_Key from li
since li is each item from SelectedItems collection

I'm not very sure of method/property available from li object
but it should be like this

Try
	For Each li As String In lbQuestions.SelectedItems
		' obtain value from li
		intSeq = li.IndexOf()
		Q_Key = li.ToString.Split(","c)(0)

		sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & (intSeq+1) & "'" & _
                        "WHERE Q_Key = '" & Q_Key & "'"

                sqlDA.UpdateCommand.ExecuteNonQuery()

	Next
Catch err As SqlException

Select allOpen in new window

Hi Joe, thanks for sticking with this. I tried several different methods but got errors. The code below compiles and runs but still only works for the first item.

        Dim intSeq As Integer = 0                  
        Dim intIndex As Integer = 0        
        Dim intQ_Key As Integer = 0        

        Dim strName As String = txtQ_Name2.Text
        Dim sqlDA As New SqlClient.SqlDataAdapter
        sqlDA.UpdateCommand = New SqlClient.SqlCommand
        sqlDA.UpdateCommand.Connection = cn
        sqlDA.UpdateCommand.CommandType = CommandType.Text

        Try
            For Each li As String In lbQuestions.SelectedItem
                intSeq = CInt(li.IndexOf(li + 1))
                intIndex = CInt(li.IndexOf(li))
                intQ_Key = CInt(li.ToString.Split(","c)(0))
                sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & (intSeq) & "'" & _
                        "WHERE Q_Name = '" & strName & "' AND Q_Key = '" & intQ_Key & "'"
                sqlDA.UpdateCommand.ExecuteNonQuery()
                MsgBox(intQ_Key & "' - '" & intSeq & "' - '" & intIndex)
            Next
        Catch err As SqlException
            MsgBox(err.Message)
        Finally
            cn.Close()
        End Try

As you can see I tried several different ways but no luck yet. Thanks again.

Jack

Thanks Joe for sticking with this. Here is the working VB.Net code:

If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        Dim s As Integer
        For s = 0 To Me.lbQuestions.Items.Count - 1
            Me.lbQuestions.SetSelected(s, True)
        Next s

        Dim intSeq As Integer
        Dim intQ_Key As Integer
        Dim i As Integer = 0

        Dim strName As String = txtQ_Name2.Text
        Dim sqlDA As New SqlClient.SqlDataAdapter
        sqlDA.UpdateCommand = New SqlClient.SqlCommand
        sqlDA.UpdateCommand.Connection = cn
        sqlDA.UpdateCommand.CommandType = CommandType.Text

        Try
            Do While i < lbQuestions.Items.Count
                If lbQuestions.Items(i).ToString.Length > 0 Then
                    intSeq = i
                    intQ_Key = CInt(lbQuestions.Items(i).ToString.Split(","c)(0))

                    sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & (intSeq + 1) & "'" & _
                    " WHERE Q_Name = '" & strName & "' AND Q_Key = '" & intQ_Key & "'"
                    sqlDA.UpdateCommand.ExecuteNonQuery()
                End If
                i = i + 1
            Loop
        Catch err As SqlException
            MsgBox(err.Message)
        Finally
            cn.Close()
        End Try

The change is this line:
If lbQuestions.Items(i).ToString.Length > 0 Then

Thanks again,
JackW9653

Thanks Joe.