Solved

Index + 1 not updating SQL Table

Posted on 2011-03-12
12
322 Views
Last Modified: 2012-05-11
Hello Experts,

The following code reads the question number (everything to the left of a comma) and is suppose to add 1 to the index (intSeq) of the question in a listbox (lbQuestions). Problem is its updating the Sequence field with 1 for the first entry then 0 for all the subsequent items. Here's my code:

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

        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 intSeq As Integer = lbQuestions.SelectedIndex
                    Dim intQ_Key As Integer = lbQuestions.Items(intSeq).ToString.Split(","c)(0)
                    sqlDA.UpdateCommand.CommandText = "UPDATE tblQuestionnaire SET Sequence = '" & intSeq + 1 & "'" & _
                    "WHERE Q_Key = '" & intQ_Key & "'"
                    sqlDA.UpdateCommand.ExecuteNonQuery()
                Next
            End If
        Catch err As SqlException
            MsgBox(err.Message)
        Finally
            cn.Close()
        End Try

Thanks in advance,
JackW9653
0
Comment
Question by:JackW9653
  • 6
  • 4
  • 2
12 Comments
 
LVL 6

Expert Comment

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

Author Comment

by:JackW9653
ID: 35118886
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
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35118917
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.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35119826
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.
0
 

Author Comment

by:JackW9653
ID: 35124274
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.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35124808
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:JackW9653
ID: 35140542
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
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35142923
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

Open in new window


0
 

Author Comment

by:JackW9653
ID: 35143526
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
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35195439
As mention before, that I don't have ".NET" to play around.
so, I'm give up on finding how to work with "For Each"

check and modified this code,  see if you can make it work

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 < lQuestions.Items.Count
		If lbQuestions.Items(i).Selected 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

Open in new window

0
 

Author Comment

by:JackW9653
ID: 35209466
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
0
 

Author Closing Comment

by:JackW9653
ID: 35209470
Thanks Joe.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now