Solved

Index + 1 not updating SQL Table

Posted on 2011-03-12
12
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

738 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