Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

SQL Server Row Doubling

James98765James98765 Posts: 87Member
I am not sure exactly what my problem is but I am making a project with several forms that works with SQL Server to manage cars and one of the forms deals with listing all the cars in a listbox so they can be opened. The only problem is that each time the form is opened when the project is still being run in debug mode the items keep doubling. Each time the form is closed and then opened the same items apear again but there are two items of each. Can anyone help me. Thanks!
James
James98765
jamesparsons@cox.net
http://www.programmersheaven.com/c/Settings/messageboard.htm

Comments

  • jbassmanpjbassmanp Posts: 160Member
    : I am not sure exactly what my problem is but I am making a project
    : with several forms that works with SQL Server to manage cars and one
    : of the forms deals with listing all the cars in a listbox so they
    : can be opened. The only problem is that each time the form is opened
    : when the project is still being run in debug mode the items keep
    : doubling. Each time the form is closed and then opened the same
    : items apear again but there are two items of each. Can anyone help
    : me. Thanks!
    : James
    : James98765
    : jamesparsons@cox.net
    : http://www.programmersheaven.com/c/Settings/messageboard.htm
    What is your code?.. Sounds as if you have an error. Show your work?
  • James98765James98765 Posts: 87Member
    Here is my code. I use a data adapter to count the rows in the database(CarDatabase) and then I use a Do Until Loop to add each row to the listbox (lbCars) one row at a time.

    [code]
    Public Class fclsOpenCar
    ' ----- Create a location for opening a connection to CarDatabase.
    Public CarDatabase As New System.Data.SqlClient.SqlConnection( _
    "Data Source=JamesSQLExpress;" & _
    "Initial Catalog=CarDatabase; Integrated Security = True")
    Private Sub fclsOpenCar_Load(etc.) Handles Me.Load
    CarDatabase.Open()
    Me.DisplayCars()
    End Sub
    Private Sub DisplayCars()
    Dim intCounter As Integer = 0
    Dim intSqlCountRows As Integer
    ' ----- Create DataAdapter to count rows of table for a loop.
    Dim m_daDataAdapter As New SqlClient.SqlDataAdapter
    Dim m_dtCarData As New DataTable
    m_daDataAdapter = _
    New SqlClient.SqlDataAdapter("SELECT * FROM CarData", CarDatabase)
    m_daDataAdapter.Fill(m_dtCarData)
    intSqlCountRows = m_dtCarData.Rows.Count()
    ' ----- Do loop until intCounter = Table CarData rows.
    Do Until intCounter = intSqlCountRows
    ' ---- Add an item from intCounter row to {lbCars}.
    lbCars.Items.Add(m_dtCarData.Rows(intCounter) _
    ("CarName"))
    ' ---- Add 1 to intCounter.
    intCounter += 1
    Loop
    End Sub
    Private Sub fclsOpenCar_FormClosed(etc.) Handles Me.FormClosed
    CarDatabase.Close()
    End Sub
    [/code]

    Each time I close and load the form while still running the project in debug mode the items of lbCars are doubled each time. For example when I first load the form the items would show this:

    [code]
    lbCars Items:
    Car 1
    Car 2
    Car 3
    [/code]

    This is correct except when I close the form and open it again it shows this:

    [code]
    lbCars Items:
    Car 1
    Car 2
    Car 3
    Car 1
    Car 2
    Car 3
    [/code]

    This is where my problem is. I have discovered that my data adapter is counting 6 rows unstead of the three that it should be counting. That is what I think the problem is. I don't know why it doubles the count each time. Any help would be great. Thanks!

    James
    James98765
    jamesparsons@cox.net
    http://www.programmersheaven.com/c/Settings/messageboard.htm
  • jbassmanpjbassmanp Posts: 160Member
    : Here is my code. I use a data adapter to count the rows in the
    : database(CarDatabase) and then I use a Do Until Loop to add each row
    : to the listbox (lbCars) one row at a time.
    :
    : [code]:
    : Public Class fclsOpenCar
    : ' ----- Create a location for opening a connection to CarDatabase.
    : Public CarDatabase As New System.Data.SqlClient.SqlConnection( _
    : "Data Source=JamesSQLExpress;" & _
    : "Initial Catalog=CarDatabase; Integrated Security = True")
    : Private Sub fclsOpenCar_Load(etc.) Handles Me.Load
    : CarDatabase.Open()
    : Me.DisplayCars()
    : End Sub
    : Private Sub DisplayCars()
    : Dim intCounter As Integer = 0
    : Dim intSqlCountRows As Integer
    : ' ----- Create DataAdapter to count rows of table for a loop.
    : Dim m_daDataAdapter As New SqlClient.SqlDataAdapter
    : Dim m_dtCarData As New DataTable
    : m_daDataAdapter = _
    : New SqlClient.SqlDataAdapter("SELECT * FROM CarData", CarDatabase)
    : m_daDataAdapter.Fill(m_dtCarData)
    : intSqlCountRows = m_dtCarData.Rows.Count()
    : ' ----- Do loop until intCounter = Table CarData rows.
    : Do Until intCounter = intSqlCountRows
    : ' ---- Add an item from intCounter row to {lbCars}.
    : lbCars.Items.Add(m_dtCarData.Rows(intCounter) _
    : ("CarName"))
    : ' ---- Add 1 to intCounter.
    : intCounter += 1
    : Loop
    : End Sub
    : Private Sub fclsOpenCar_FormClosed(etc.) Handles Me.FormClosed
    : CarDatabase.Close()
    : End Sub
    : [/code]:
    :
    : Each time I close and load the form while still running the project
    : in debug mode the items of lbCars are doubled each time. For example
    : when I first load the form the items would show this:
    :
    : [code]:
    : lbCars Items:
    : Car 1
    : Car 2
    : Car 3
    : [/code]:
    :
    : This is correct except when I close the form and open it again it
    : shows this:
    :
    : [code]:
    : lbCars Items:
    : Car 1
    : Car 2
    : Car 3
    : Car 1
    : Car 2
    : Car 3
    : [/code]:
    :
    : This is where my problem is. I have discovered that my data adapter
    : is counting 6 rows unstead of the three that it should be counting.
    : That is what I think the problem is. I don't know why it doubles the
    : count each time. Any help would be great. Thanks!
    :
    : James
    : James98765
    : jamesparsons@cox.net
    : http://www.programmersheaven.com/c/Settings/messageboard.htm
    a couple of things I've noticed. But, before we go into let ask "is your intention to list the names of the same cars in the data base, each and every time you want?". If this is what you want you are over programming. If you were doing some special listbox listing that changed (listing different items same listbox) from event to event your code would be okay with some changes...

    1. Lets do the car listing that stays the basic same... All you need here is the following:
    [code]
    Dim m_daDataSet As New SqlClient.SqlDataSet
    m_daDataAdapter.Fill(m_dtCarData)
    lbCars.datasource = m_daDataset.tables("CarName")
    lbCars.DisplayMember = "CarName"
    [code/]
    That is it
    However it you are doing changes in the item listing from say "car names" to "tires type needed" or "Engine Type", etc, etc. You must clear the list each and every time you call it or it will add the items to the old list.

    You can add the following code to yours at the very beginning of the sub routine to add items (this should work even on you present code, you just have too much usless code there):
    [code]
    dim u as integer
    'the 1000 could be larger or smaller depending on the possible size of you listing
    for u = 0 to 1000
    lbcars.items.clear()
    next
    [code/]

    Good luck James with your programming......
  • James98765James98765 Posts: 87Member
    Perfect thanks! I have to say this worked amazingly except I don't understand how it is working. What is the difference between using the loop and just saying:
    [code]
    lbCars.Items.Clear()
    [/code]

    Does lbCars.Items.Clear() only clear one item at a time from lbCars? I tried the lbCars.Items.Clear() a lot of different ways and it didn't work. Thanks!

    James
    James98765
    jamesparsons@cox.net
    http://www.programmersheaven.com/c/Settings/messageboard.htm
  • jbassmanpjbassmanp Posts: 160Member
    : Perfect thanks! I have to say this worked amazingly except I don't
    : understand how it is working. What is the difference between using
    : the loop and just saying:
    : [code]:
    : lbCars.Items.Clear()
    : [/code]:
    :
    : Does lbCars.Items.Clear() only clear one item at a time from lbCars?
    : I tried the lbCars.Items.Clear() a lot of different ways and it
    : didn't work. Thanks!
    :
    : James
    : James98765
    : jamesparsons@cox.net
    : http://www.programmersheaven.com/c/Settings/messageboard.htm

    Do you know what 'for...next..loops' do? It will attempt to clear your item list 1001 times in the: for u = 0 to 1000.
    and yes the lbCars.Items.Clear() works on one item at a time, so take care of all of them with the full statement. You can change the 1000 to what ever fits your needs. The cleanest code was the first one I gave you. Do you have a programming background in any other language.

  • James98765James98765 Posts: 87Member
    : : Perfect thanks! I have to say this worked amazingly except I don't
    : : understand how it is working. What is the difference between using
    : : the loop and just saying:
    : : [code]: :
    : : lbCars.Items.Clear()
    : : [/code]: :
    : :
    : : Does lbCars.Items.Clear() only clear one item at a time from lbCars?
    : : I tried the lbCars.Items.Clear() a lot of different ways and it
    : : didn't work. Thanks!
    : :
    : : James
    : : James98765
    : : jamesparsons@cox.net
    : : http://www.programmersheaven.com/c/Settings/messageboard.htm
    :
    : Do you know what 'for...next..loops' do? It will attempt to clear
    : your item list 1001 times in the: for u = 0 to 1000.
    : and yes the lbCars.Items.Clear() works on one item at a time, so
    : take care of all of them with the full statement. You can change the
    : 1000 to what ever fits your needs. The cleanest code was the first
    : one I gave you. Do you have a programming background in any other
    : language.
    :
    :
    That makes since. Thanks!
  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    : Do you know what 'for...next..loops' do? It will attempt to clear
    : your item list 1001 times in the: for u = 0 to 1000.
    : and yes the lbCars.Items.Clear() works on one item at a time, so
    : take care of all of them with the full statement. You can change the
    : 1000 to what ever fits your needs. The cleanest code was the first
    : one I gave you. Do you have a programming background in any other
    : language.
    :

    I am going to have to improve you on that.

    ListBox.Items.Clear() removes ALL items from the ListBox.
    If for some reason it doesn't, then it is most likely due to listbox events being handled that interfere with this behavior.

    So for clarity:
    [code]
    'Remove all items from the listbox:
    ListBox1.Items.Clear()

    'Add items here:"
    ...
    [/code]
    Is the correct code.

    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • James98765James98765 Posts: 87Member
    Thank you for your input.

    James
    James98765
    jamesparsons@cox.net
    http://www.programmersheaven.com/c/Settings/messageboard.htm
  • jbassmanpjbassmanp Posts: 160Member
    : Thank you for your input.
    :
    : James
    : James98765
    : jamesparsons@cox.net
    : http://www.programmersheaven.com/c/Settings/messageboard.htm

    Fellas I stand corrected... My code has both the single line:
    Listbox1.items.Clear() and on some special removal of array items not total I've used the 'for next'.

    If you only got one item removed then that is strange.


Sign In or Register to comment.