dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] passing values from database table to a set of variables

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    14

    Resolved [RESOLVED] passing values from database table to a set of variables

    hi,

    i want to pass the values from Buttons field to set of variable
    like:
    TempBarA1 = the value of record 1 in table
    TempBarA2 = the value of record 2 in table
    TempBarA3 = the value of record 3 in table
    ..... and so on

    database name : MyDb
    ID datatype : Number
    Buttons datatype : Short Text

    Name:  B1.png
Views: 152
Size:  9.1 KB


    Code:
    Public Property TempBarA1 As String
        Public Property TempBarA2 As String
        Public Property TempBarA3 As String
        Public Property TempBarA4 As String
        Public Property TempBarA5 As String
        Public Property TempBarA6 As String
        Public Property TempBarA7 As String
        Public Property TempBarA8 As String
        Public Property TempBarA9 As String
        Public Property TempBarA10 As String
        Public Property TempBarA11 As String
        Public Property TempBarA12 As String
        Public Property TempBarA13 As String
        Public Property TempBarA14 As String
        Public Property TempBarA15 As String
        Public Property TempBarA16 As String

    thank you for your help

  2. #2
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    153

    Re: passing values from database table to a set of variables

    A great start to lean sql (imho) is https://www.w3schools.com/sql/default.asp , take a look there and see if it helps you out
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    14

    Re: passing values from database table to a set of variables

    Thank you Goggy i'll check it

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,052

    Re: passing values from database table to a set of variables

    There are many ways to do this, and you'll be needing SQL for all of them, so that's a good tutorial. In general terms, you can do this with a raw SQL approach, or with one of a variety of "wizards". I always favor the raw SQL, myself, but others prefer other approaches. Which one is right for you...probably nobody knows, including you, at this point.

    The general raw SQL approach would probably be to use a datareader to go through each row in the table and do something with them. It's a bit hard to describe, as there doesn't appear to be much of a relationship between the rows in the table and the properties. It may actually make more sense to bring the entire database table into a datatable and work with that, instead. You can do that with a datareader, or with a dataadapter, so those are a couple key words you can look up. The advantage of a datatable is that you'd have a copy of the database table in memory, which means faster access if you have to get to it more than once. If you don't need to get to it more than once, the datareader would be superior, as it is a forward only, read only, way of reading data from a database (you can't go back to previous rows without creating a whole new datareader).
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    14

    Re: passing values from database table to a set of variables

    thank you Shaggy for your reply

    i came out with this hope it is the right one

    Code:
    Con.Open()
            Dim ds As New DataSet
            Dim dt As New DataTable
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter
    
            da = New OleDbDataAdapter("Select * from SpeedButtons", Con)
            da.Fill(dt)
            TempBar1 = dt.Rows(0).Item(1)
            TempBar2 = dt.Rows(1).Item(1)
            TempBar3 = dt.Rows(2).Item(1)
            TempBar4 = dt.Rows(3).Item(1)
            TempBar5 = dt.Rows(4).Item(1)
            TempBar6 = dt.Rows(5).Item(1)
            TempBar7 = dt.Rows(6).Item(1)
            TempBar8 = dt.Rows(7).Item(1)
            TempBar9 = dt.Rows(8).Item(1)
            TempBar10 = dt.Rows(9).Item(1)
            TempBar11 = dt.Rows(10).Item(1)
            TempBar12 = dt.Rows(11).Item(1)
            TempBar13 = dt.Rows(12).Item(1)
            TempBar14 = dt.Rows(13).Item(1)
            TempBar15 = dt.Rows(14).Item(1)
            TempBar16 = dt.Rows(15).Item(1)

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,052

    Re: passing values from database table to a set of variables

    Yes, that will work. There are a few general points that can be made:

    1) Any time you work with a database, things can go unexpectedly wrong, so it's generally a good idea to wrap the database access in a Try..Catch block to trap errors. This may be even more true since you are using OleDb, which suggests that you may be using Access. If you are anything like me, you'll end up trying to read the table when you also have the table open in Access in design view...which will crash the program.

    2) You could have done about the same thing with a datareader and Command object:
    Code:
    Dim cmd as OleDb.OleDbCommand = Con.CreateCommand
    Using dr As DataReader = cmd.ExecuteReader
      Do While dr.Read
        'Each time through the loop will read the next line.
       Loop
    End Using
    Obviously, this is a bit more code than what you wrote, but a datareader is slightly faster than a dataadapter, so it can have slightly better performance. The dataadapter/datatable approach is best when you want to be able to either edit the data in the datatable, or if you want to be able to go through the datatable more than once.

    3) I was going to say that you could have used a loop, but that would have worked better if you had used an array of strings rather than a series of properties. Technically, a loop will be slightly slower, so what you have done will have the best performance, but the difference is too small to bother with these days.

    4) Eventually, you will want to turn Option Strict ON, which can be done for the project on Project | Properties on the Compile tab. You can also set it to on by default in the VS Options, though I forget where it is located. Option Strict ON simply disallows implicit data type conversions. Implicit conversions are always a bit slower than explicit conversions, though you'd have to do LOTS of them to notice (many thousands). However, implicit conversions also hide some bugs both subtle and obvious. In this case, you are implicitly converting objects to strings, which is a totally painless thing to do, but eventually you'll want to disallow such things. I was late to learning that, myself, but once learned....yeah, it's better.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    14

    Re: passing values from database table to a set of variables

    thank you Shaggy for all the points which was very useful to me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width