[Kifka]

Hi, I am developing a program for my uncle where I am storing his clients data using MySQL in a database. I have created a basic form where the data from the database is displayed in different textboxes presenting each field (e.g. company name or address). I have been able to connect to the database so far and can insert data from the form to the database but I am struggling to understand how to show different records. I have two buttons, previous and next where clicking them will do either go to the previous record or next one from the database. I have tried using a WHERE statement but it only then displays the next record and will just keep showing that particular one and not progress to the other one. I have looked for help on the internet but have not found much.

 
Dim dbCon As MySqlConnection
        Dim strQuery As String
        Dim SQLCmd As MySqlCommand
        Dim DR As MySqlDataReader


        dbCon = New MySqlConnection("server=*****; Database=*******; user id=******; password=******;")

        Try

            strQuery = "SELECT client.CompanyID, client.street, client.county, client.CompanyName, client.ClientForename, client.ClientSurname, client.PhoneNumber, client.town, client.postcode " & _
            "FROM client "


            SQLCmd = New MySqlCommand(strQuery, dbCon)

            dbCon.Open()
            DR = SQLCmd.ExecuteReader
            While DR.Read
                TextBox1.Text = DR.Item("CompanyID")
                TextBox2.Text = DR.Item("CompanyName")
                TextBox3.Text = DR.Item("ClientForename")
                TextBox4.Text = DR.Item("ClientSurname")
                TextBox5.Text = DR.Item("Street")
                TextBox6.Text = DR.Item("Town")
                TextBox7.Text = DR.Item("county")
                TextBox8.Text = DR.Item("postcode")
                TextBox9.Text = DR.Item("PhoneNumber")

            End While

            DR.Close()
            dbCon.Close()

        Catch ex As Exception
            MsgBox("Failure to connect!" & ex.Message)
        End Try

    End Sub 

This just shows my program connecting and displaying the first record. I am not experienced in VB so if my code looks rubbish please suggest ways to make it look better. I would extremely appreciate the help as it has been nagging me for the past few days.

[tlhIn`toq]

The 'next' record is subjective. Databases aren't flat files where 'next' is the next line.

Is 'next' the next alphabetically? Or the next higher UNIQUE id number for the client?

Generally speaking you want a unique ID number for each record. Then 'next' becomes as simple as selecting the next id. If you are looking AT id #5 then select id #6

References from my FAQ list:

Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes

---

Quote

20                TextBox1.Text = DR.Item("CompanyID")
21                TextBox2.Text = DR.Item("CompanyName")
22                TextBox3.Text = DR.Item("ClientForename")
23                TextBox4.Text = DR.Item("ClientSurname")
24                TextBox5.Text = DR.Item("Street")
25                TextBox6.Text = DR.Item("Town")
26                TextBox7.Text = DR.Item("county")
27                TextBox8.Text = DR.Item("postcode")
28                TextBox9.Text = DR.Item("PhoneNumber")

For the sake of your own sanity stop doing this 'first semester' stuff. Rename your GUI controls as soon as you put them on the form.

Some of my common tips (some may apply more than others to your specific style):

• You have to program as if everything breaks, nothing works, the cyberworld is not perfect, the attached hardware is flakey, the network is slow and unreliable, the harddrive is about to fail, every method will return an error and every user will do their best to break your software. Confirm everything. Range check every value. Make no assumptions or presumptions.
  
  
• Take the extra 3 seconds to rename your controls each time you drag them onto a form. The default names of button1, button2... button54 aren't very helpful. If you rename them right away to something like btnOk, btnCancel, btnSend etc. it helps tremendously when you make the methods for them because they are named after the button by the designer.btnSend_Click(object sender, eventargs e) is a lot easier to maintain than button1_click(object sender, eventargs e)
  
  
• You aren't paying for variable names by the byte. So instead of variables names of a, b, c go ahead and use meaningful names like index, timeOut, row, column and so on. You should avoid 'T' for the timer. Amongst other things 'T' is commonly used throughout C# for Type and this will lead to problems. There are naming guidelines you should follow so your code confirms to industry standards. It makes life much easier on everyone around you, including those of us here to help. If you start using the standards from the beginning you don't have to retrain yourself later.
  You might want to look at some of the naming guidelines. Its a lot easier to start with good habits than to break bad habits later and re-learn.
  
  
  • Guidelines for names
    
  • Naming guidelines
    
  • Design guidelines for classes
  
  
  
• Try to avoid having work actually take place in GUI control event handlers. It is better to have the GUI handler call other methods so those methods can be reused and make the code more readable. This is also how you can send parameters rather than use excessive global variables. Get in this habit even if you are using WinForms because WPF works a lot under the idea of "commands" and this will get you working towards that. Think of each gester, control click, menu option etc. as a command to do something such as a command to SAVE. It doesn't matter where the command comes from, all sources should point at the same target to do the actual saving.
  
  Spoiler

  
  

  // All of this different triggering mechinisms call the same single [il]SavePreferences()[/il] method
  
  btnSave(object sender, eventargs e)
  {
      SavePreferences(preferencesPath);
  }
  
  <KeyboardShortcut control+s bound to save>
  <Touchscreen guesture swiping from lowerleft to upperright bound to save>
  
  SaveMenuItem(object sender, eventargs e)
  {
      SavePreferences(@"C:\prefs\test.txt");
  }
  
  SaveContextMenu(object sender, eventargs e)
  {
      SavePreferences();// No parameter method overload
  }
  
  FormMain_Closing(object sender, eventargs e)
  {
      if (IsDirty) SavePreferences();
  }
  

  
  
  
• Don't replace lines of code that don't work. Instead comment them out and put your new attempts below that. This will keep you from re-trying the same ideas over and over. Also, when you come back to us saying "I've tried this 100 different ways and still can't get it", we can actually see what you tried. So often a failed attempt is very very close and just needs a little nudge in the right direction. So if we can say "See what you did in attempt 3... blah blah" it helps a lot
  
  
  Spoiler

  // Try #1 - May 1, 0900hrs
  //    code
  //    code  
  //    code
  // Try #2 - May 2, 1700hrs   Okay, plan B.  What if I do it *this* way
  //    code
  //    code  
  //    code
  // Try #14 - May 3, 0500hrs after 5 cans of RedBull.  Maybe I should get some sleep. I can't think of anything else but this last idea
      code
      code  
      code
  
  

  
  If you are using Visual Studio you can select a block of lines and hit control+k control+c (Kode Comment) to comment it out. control+k control+u (Kode Uncomment) to uncomment a selected block.

This post has been edited by tlhIn`toq: 03 December 2012 - 08:49 AM

[zeeshanef]

when you do "While DR.Read", data reader goes through all records and last one will be shown in textboxes.
to get records you need to create List(of T).

1. Create a class:

Public Class Clients
    'Create properties as your data fields
    Public Property CompanyID As Integer
    Public Property CompanyName As String
    'and all your fields
End Class

2.
Create a class level List object, so that it will be accessible in your class:

Dim clientsList As New List(Of Clients)

3. in your loop:

While DR.Read
        Dim addClient As New Clients
        addClient.CompanyID = DR.Item("CompanyID")
        addClient.CompanyName = DR.Item("CompanyName")
       'and all your fields
       
       'Add object into list
        clientsList.Add(addClient)
End While

Now you have all records in clientsList.

your First record will be:

TextBox1.Text = clientsList(0).CompanyID
TextBox2.Text = clientsList(0).CompanyName

and last record will be:

TextBox1.Text = clientsList(clientsList.Count - 1).CompanyID
TextBox2.Text = clientsList(clientsList.Count - 1).CompanyName

This post has been edited by zeeshanef: 03 December 2012 - 08:51 AM

[Kifka]

Hi thank you guys for replying! I am sorry for the late reply.

tlhIn`toq, on 03 December 2012 - 08:47 AM, said:

The 'next' record is subjective. Databases aren't flat files where 'next' is the next line.

Is 'next' the next alphabetically? Or the next higher UNIQUE id number for the client?

Generally speaking you want a unique ID number for each record. Then 'next' becomes as simple as selecting the next id. If you are looking AT id #5 then select id #6

References from my FAQ list:

Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes

---

Quote

20                TextBox1.Text = DR.Item("CompanyID")
21                TextBox2.Text = DR.Item("CompanyName")
22                TextBox3.Text = DR.Item("ClientForename")
23                TextBox4.Text = DR.Item("ClientSurname")
24                TextBox5.Text = DR.Item("Street")
25                TextBox6.Text = DR.Item("Town")
26                TextBox7.Text = DR.Item("county")
27                TextBox8.Text = DR.Item("postcode")
28                TextBox9.Text = DR.Item("PhoneNumber")

For the sake of your own sanity stop doing this 'first semester' stuff. Rename your GUI controls as soon as you put them on the form.

Some of my common tips (some may apply more than others to your specific style):

• You have to program as if everything breaks, nothing works, the cyberworld is not perfect, the attached hardware is flakey, the network is slow and unreliable, the harddrive is about to fail, every method will return an error and every user will do their best to break your software. Confirm everything. Range check every value. Make no assumptions or presumptions.
  
  
• Take the extra 3 seconds to rename your controls each time you drag them onto a form. The default names of button1, button2... button54 aren't very helpful. If you rename them right away to something like btnOk, btnCancel, btnSend etc. it helps tremendously when you make the methods for them because they are named after the button by the designer.btnSend_Click(object sender, eventargs e) is a lot easier to maintain than button1_click(object sender, eventargs e)
  
  
• You aren't paying for variable names by the byte. So instead of variables names of a, b, c go ahead and use meaningful names like index, timeOut, row, column and so on. You should avoid 'T' for the timer. Amongst other things 'T' is commonly used throughout C# for Type and this will lead to problems. There are naming guidelines you should follow so your code confirms to industry standards. It makes life much easier on everyone around you, including those of us here to help. If you start using the standards from the beginning you don't have to retrain yourself later.
  You might want to look at some of the naming guidelines. Its a lot easier to start with good habits than to break bad habits later and re-learn.
  
  
  • Guidelines for names
    
  • Naming guidelines
    
  • Design guidelines for classes
  
  
  
• Try to avoid having work actually take place in GUI control event handlers. It is better to have the GUI handler call other methods so those methods can be reused and make the code more readable. This is also how you can send parameters rather than use excessive global variables. Get in this habit even if you are using WinForms because WPF works a lot under the idea of "commands" and this will get you working towards that. Think of each gester, control click, menu option etc. as a command to do something such as a command to SAVE. It doesn't matter where the command comes from, all sources should point at the same target to do the actual saving.
  
  Spoiler

  
  

  // All of this different triggering mechinisms call the same single [il]SavePreferences()[/il] method
  
  btnSave(object sender, eventargs e)
  {
      SavePreferences(preferencesPath);
  }
  
  <KeyboardShortcut control+s bound to save>
  <Touchscreen guesture swiping from lowerleft to upperright bound to save>
  
  SaveMenuItem(object sender, eventargs e)
  {
      SavePreferences(@"C:\prefs\test.txt");
  }
  
  SaveContextMenu(object sender, eventargs e)
  {
      SavePreferences();// No parameter method overload
  }
  
  FormMain_Closing(object sender, eventargs e)
  {
      if (IsDirty) SavePreferences();
  }
  

  
  
  
• Don't replace lines of code that don't work. Instead comment them out and put your new attempts below that. This will keep you from re-trying the same ideas over and over. Also, when you come back to us saying "I've tried this 100 different ways and still can't get it", we can actually see what you tried. So often a failed attempt is very very close and just needs a little nudge in the right direction. So if we can say "See what you did in attempt 3... blah blah" it helps a lot
  
  
  Spoiler

  // Try #1 - May 1, 0900hrs
  //    code
  //    code  
  //    code
  // Try #2 - May 2, 1700hrs   Okay, plan B.  What if I do it *this* way
  //    code
  //    code  
  //    code
  // Try #14 - May 3, 0500hrs after 5 cans of RedBull.  Maybe I should get some sleep. I can't think of anything else but this last idea
      code
      code  
      code
  
  

  
  If you are using Visual Studio you can select a block of lines and hit control+k control+c (Kode Comment) to comment it out. control+k control+u (Kode Uncomment) to uncomment a selected block.

@ tlhIn`toq,thank you for your links and suggestions as they have helped me and I was going to change the textbox names anyway I just forgot. I think I need to clear myself up a bit more, basically when you press the next button the database will query the next set of data for the next company which is the row beneath the current set of data showing. I have a primary key which is the company id and is unique to each company.

zeeshanef, on 03 December 2012 - 08:47 AM, said:

when you do "While DR.Read", data reader goes through all records and last one will be shown in textboxes.
to get records you need to create List(of T).

1. Create a class:

Public Class Clients
    'Create properties as your data fields
    Public Property CompanyID As Integer
    Public Property CompanyName As String
    'and all your fields
End Class

2.
Create a class level List object, so that it will be accessible in your class:

Dim clientsList As New List(Of Clients)

3. in your loop:

While DR.Read
        Dim addClient As New Clients
        addClient.CompanyID = DR.Item("CompanyID")
        addClient.CompanyName = DR.Item("CompanyName")
       'and all your fields
       
       'Add object into list
        clientsList.Add(addClient)
End While

Now you have all records in clientsList.

your First record will be:

TextBox1.Text = clientsList(0).CompanyID
TextBox2.Text = clientsList(0).CompanyName

and last record will be:

TextBox1.Text = clientsList(clientsList.Count - 1).CompanyID
TextBox2.Text = clientsList(clientsList.Count - 1).CompanyName

Thanks but can you explain it a bit clearer to me as I don't fully understand how to implement all of this. When I create the class I have to provide both a 'get' and 'set' to each property and I am not sure what I should follow on from this. Also once I have implemented the loop, do I put this:

TextBox1.Text = clientsList(0).CompanyID
TextBox2.Text = clientsList(0).CompanyName

into the next record button?

Sorry if I sound like I don't know anything but I want to learn so thanks />.

[Kifka]

Sorry to bump this but I really need the help anyway possible to understand it better!

[lucky3]

To build on zeeshanef's answer:

• you put number 1 (the class) somewhere in this namespace (could be inside the class you are working in (so you can have private class, visible only to using class), or you add the code outside the class you are using it).
  
• number 2 goes before while loop
  
• number 3 is while loop, just add data from DB to Clients class instance

Displaying items from the clientsList in textboxes is done by reading indexes of that list. So clientsList(0) represents first Clients object in clientsList, and .CompanyID is property of that object. Displaying CompanyName property of 7th object on the clientsList in txtCompanyName TextBox would look like: txtCompanyName.Text = clientsList(8).CompanyName, and so on...

This post has been edited by lucky3: 06 December 2012 - 07:52 AM

[Kifka]

lucky3, on 06 December 2012 - 07:51 AM, said:

To build on zeeshanef's answer:

• you put number 1 (the class) somewhere in this namespace (could be inside the class you are working in (so you can have private class, visible only to using class), or you add the code outside the class you are using it).
  
• number 2 goes before while loop
  
• number 3 is while loop, just add data from DB to Clients class instance

Displaying items from the clientsList in textboxes is done by reading indexes of that list. So clientsList(0) represents first Clients object in clientsList, and .CompanyID is property of that object. Displaying CompanyName property of 7th object on the clientsList in txtCompanyName TextBox would look like: txtCompanyName.Text = clientsList(8).CompanyName, and so on...

Hey thank you for replying. What I mean for number 1 is that I have to set 'Get' and 'Set' terms for each of my properties. I put the code in a class before all my subroutines but I am not sure if I actually have to write anything with the 'Get' and 'Set'. Also under my next button would I do something like

TextBox1.Text = clientsList(0)+1.CompanyID

or something similar so that when someone presses this the textboxes change to the next record each time they press it till the last record? Thanks a lot!

[Kifka]

I think I am nearly there so just a bit more guidance please! Have had this problem for 2 weeks now. bump

[zeeshanef]

1.
If your are using Visual Studio 2010 or greater, you can implement automatic property like:

Public Property CompanyID As Integer

else, you need to implement property like this:

private _companyID as Integer = 0
Property CompanyID As Integer 
    Get 
        Return _companyID
    End Get 
    Set(ByVal value As Integer)
        _companyID = value
    End Set 
End Property

See this Link:
http://msdn.microsof...y/dd293589.aspx

2.
In your next button you can do something like this:

TextBox1.Text = clientsList(n++).CompanyID

And you will also check, if Index is Out of Bound.

This post has been edited by zeeshanef: 10 December 2012 - 06:49 AM

[Kifka]

zeeshanef, on 10 December 2012 - 06:47 AM, said:

1.
If your are using Visual Studio 2010 or greater, you can implement automatic property like:

Public Property CompanyID As Integer

else, you need to implement property like this:

private _companyID as Integer = 0
Property CompanyID As Integer 
    Get 
        Return _companyID
    End Get 
    Set(ByVal value As Integer)
        _companyID = value
    End Set 
End Property

See this Link:
http://msdn.microsof...y/dd293589.aspx

2.
In your next button you can do something like this:

TextBox1.Text = clientsList(n++).CompanyID

And you will also check, if Index is Out of Bound.

OMG thank you I am nearly there! It basically works I am so grateful but I just have to deal with the last bit. I have

TextBox1.Text = clientsList(0 + 1).CompanyID

and all the other textboxes but basically could you elaborate further how to get it so that piece of code loops so that it will keep going until the last record is reached and then stop? I have tried changing the value after clientslist but I cannot figure it out. NEARLY THERE. You guys are really great but I cannot thank you so much if I get this last piece figured out! <3

[Kifka]

I think I need to create a loop to make it so that it loops until the last record is found but wouldn't that mean I have to create some sort of variable that represents the last record in the client list? Is there a way that I can have for example

CompanyID.Text = clientsList(1).CompanyID

and change something in the

clientsList(1)

with the 1 and change it from an integer to something else that can recognize the database? When pressing the button obviously it doesn't carry on through the records as it only changes from 0 to 1 but how do I keep it increasing every time you press it?