none
Listbox value from sqlite

    Question

  • Trying to get a value out of the sqlite db put cant figure it out

    Tablename = Settting

    Rowname = Firmanavn

    Imports

    System.Data.SQLite

       

    PrivateSubListBox3_SelectedIndexChanged(sender AsObject, e AsEventArgs) HandlesListBox3.SelectedIndexChanged

           

    Dimmyconnection AsNewSQLiteConnection("Data Source=C:\mydb.sqlite;Version=3")

            myconnection.Open()

     

           

    Dimcommand AsNewSQLiteCommand("select * from Settings")

            command.Connection = myconnection

     

           

    Dimadapter AsNewSQLiteDataAdapter(command)

           

    Dimtable AsNewDataTable()

            adapter.Fill(table)

            ListBox3.DataSource = table

            ListBox3.DisplayMember =

    "FirmaNavn"


       

    EndSub

    End

    Class


    • Edited by Kristian Monday, February 11, 2019 7:19 PM
    Monday, February 11, 2019 7:17 PM

All replies

  • Hi

    You need to use the COLUMN name for the DisplayMember of the ListBox

     Assuming 'Firmanavn' is a column name in your DataTable, then this should work. (NOTE: if wanted, then ValueMember needs a valid column name also.)
       ListBox3.DataSource = myTable
        ' use COLUMN NAME
        ListBox3.DisplayMember = "FirmaNavn"
        ' you can if you wish, return another
        ' column value when user selects an
        ' item in the ListBox
        ' use COLUMN NAME
        ListBox3.ValueMember = "Last Name"



    Regards Les, Livingston, Scotland



    • Edited by leshay Monday, February 11, 2019 7:50 PM
    Monday, February 11, 2019 7:48 PM
  • Hi,

    I think you should put the following code in the Form load event.

    Dimmyconnection AsNewSQLiteConnection("Data Source=C:\mydb.sqlite;Version=3")
    
            myconnection.Open()
         
    Dimcommand AsNewSQLiteCommand("select * from Settings")
    
            command.Connection = myconnection
    
         
    Dimadapter AsNewSQLiteDataAdapter(command)
    
           
    Dimtable AsNewDataTable()
    
            adapter.Fill(table)
    
            ListBox3.DataSource = table
    
            ListBox3.DisplayMember =
    
    "FirmaNavn"

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 12, 2019 2:27 AM
    Moderator
  • FirmaNavn is the name of the Colum.

    Value member i domt need sinze there is only one value in every Colum Name.

    Saturday, February 16, 2019 12:06 PM
  • I do whant to  have it under the listbox, too not get lost in the code. the reasson is there is gonna be several listbox`s that is gonna show diffent data from different  tables whitinn the database file. under the table im only interested in one row 

    code i put here was for one row name "FirmaNavn" whitinn the table: Settings



    • Edited by Kristian Saturday, February 16, 2019 12:16 PM
    Saturday, February 16, 2019 12:11 PM
  • I do whant to  have it under the listbox, too not get lost in the code. the reasson is there is gonna be several listbox`s that is gonna show diffent data from different  tables whitinn the database file. under the table im only interested in one row 

    code i put here was for one row name  whitinn the table: Settings



    Hi

    First of all - please stick to normal practice,. A DataTable has rows and columns. Call a Column a Column and not a Row. You seem to insist that you have a Row named "Firmanavn", then you say it is a Column Name.

    From what you say here, my earlier answer seems to fit your requirements.



    Regards Les, Livingston, Scotland

    Saturday, February 16, 2019 12:58 PM
  • You need to have an open mind for the following and be willing to go a different route, otherwise stop here.

    I would suggest using master detail logic.

    In the following example I use SQL-Server. To get this to work for MySql

    • Change SqlConnection to SQLiteConnection (using your current connection
    • Change SqlDataAdapter to SQLiteDataAdapter (place your SELECT statement in place of mine)
    • Ignore in Sub New DatabaseServer and DefaultCatalog.
    • In LoadData ignore mHasException and mLastException.
    • Ignore Inherits SqlServerConnection

    Add the following code module for setting up master-details relations in LoadData

    Public Module DataRelationsExtensions
        ''' <summary>
        ''' Used to create a one to many relationship for a master-detail in a DataSet.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="masterTableName">master table</param>
        ''' <param name="childTableName">child table of master table</param>
        ''' <param name="masterKeyColumn">master table primary key</param>
        ''' <param name="childKeyColumn">child table of master's primary key</param>
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub SetRelation(
                               sender As DataSet,
                               masterTableName As String,
                               childTableName As String,
                               masterKeyColumn As String,
                               childKeyColumn As String)
    
            sender.Relations.Add(
                New DataRelation(String.Concat(masterTableName, childTableName),
                                 sender.Tables(masterTableName).Columns(masterKeyColumn),
                                 sender.Tables(childTableName).Columns(childKeyColumn)
                                 )
                )
    
        End Sub
    
    End Module
    

    Add a new class and insert the following

    Imports System.Data.SqlClient
    Imports System.Windows.Forms
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Public Class Operations
        Inherits SqlServerConnection
    
        Public Property MasterProduct As New BindingSource
        Public Property DetailsColor As New BindingSource
        Public Property DetailsSize As New BindingSource
        Public Sub New()
            DatabaseServer = "KARENS-PC"
            DefaultCatalog = "MasterRelationsDemo"
        End Sub
    
        Public Sub LoadData()
    
            Dim ds As New DataSet
    
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    
                Dim da As New SqlDataAdapter(
                    "SELECT id,Name, Description, '$' + CONVERT(NVARCHAR, [Retail]) AS RetailFormatted, " &
                    "Retail FROM Product ORDER BY NAME", cn)
    
                Try
    
                    da.Fill(ds, "Product")
                    Dim ProductDataTable As New DataTable
                    ProductDataTable = ds.Tables("Product")
    
    
                    da = New SqlDataAdapter("SELECT id,ProductId,Color FROM ProductColor", cn)
                    da.Fill(ds, "ProductColor")
                    Dim colorDataTable As New DataTable
                    colorDataTable = ds.Tables("ProductColor")
                    ds.SetRelation("Product", "ProductColor", "Id", "ProductId")
    
                    da = New SqlDataAdapter("SELECT id,Size,ProductColorId,DisplayOrder  FROM ProductSize", cn)
                    da.Fill(ds, "ProductSize")
                    Dim sizeDataTable As New DataTable
                    sizeDataTable = ds.Tables("ProductSize")
                    ds.SetRelation("ProductColor", "ProductSize", "Id", "ProductColorId")
                    MasterProduct.DataSource = ds
                    MasterProduct.DataMember = ds.Tables(0).TableName
    
                    DetailsColor.DataSource = MasterProduct
                    DetailsColor.DataMember = ds.Relations(0).RelationName
    
                    DetailsSize.DataSource = DetailsColor
                    DetailsSize.DataMember = ds.Relations(1).RelationName
    
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
                End Try
            End Using
        End Sub
    
    End Class
    

    In the form shown event (better to use the the Load event) setup as follows then in a button add code as shown to show how to get at any data.

    Public Class Form1
        Private ops As New Operations
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            ops.LoadData()
    
            ProductsListBox.DataSource = ops.MasterProduct
            ProductsListBox.DisplayMember = "Name"
            ColorsListBox.DataSource = ops.DetailsColor
            ColorsListBox.DisplayMember = "Color"
    
            SizesListBox.DataSource = ops.DetailsSize
            SizesListBox.DisplayMember = "Size"
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim productRow = CType(ProductsListBox.SelectedItem, DataRowView).Row
            Dim colorRow = CType(ColorsListBox.SelectedItem, DataRowView).Row
            Dim SizeRow = CType(SizesListBox.SelectedItem, DataRowView).Row
    
            MessageBox.Show(
                $"Product id: {productRow.Field(Of Integer)("id")} " &
                $"Color id: {colorRow.Field(Of Integer)("id")} Size id: {SizeRow.Field(Of Integer)("id")}")
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 16, 2019 2:21 PM
    Moderator
  • Ill keep that in mind about Column, abit of a language barrier there :)

    Problem was that i had another sql connection (didnt show that code part) and i did mess it up calling both connection the same thing.

    Imports System.Data.SQLite Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim myconnection As New SQLiteConnection("Data Source=C:mydb.sqlite;Version=3") myconnection.Open() Dim cmd As New SQLiteCommand cmd.Connection = myconnection cmd.CommandText = "Select * from ordre" Dim rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader Dim dt As New DataTable dt.Load(rdr) rdr.Close() myconnection.Close() DataGridView1.DataSource = dt Dim myconnection2 As New SQLiteConnection("Data Source=C:\mydb.sqlite;Version=3") Dim command2 As New SQLiteCommand("select * from Settings") command2.Connection = myconnection2 Dim adapter2 As New SQLiteDataAdapter(command2) Dim table2 As New DataTable() adapter2.Fill(table2) ListBox3.DataSource = table2 ListBox3.DisplayMember = "FirmaNavn" ListBox4.DataSource = table2 ListBox4.DisplayMember = "AvdPoststed" ListBox6.DataSource = table2 ListBox6.DisplayMember = "Orgnummer"

    End Sub End Class

    this is what the code looks like now.

    Now i just need to figure out the next step make checkbox read value: 0 uncheck 1 check from another part of the db and update value when i check ore uncheck

    Sunday, February 24, 2019 6:28 PM
  • Thanks for the replay 

    this code is abit over my skill level, but ill check it out to learn :)

    Sunday, February 24, 2019 6:31 PM
  • this is what the code looks like now.

    Now i just need to figure out the next step make checkbox read value: 0 uncheck 1 check from another part of the db and update value when i check ore uncheck

    Hi,

    like this?

    ....

    Dim d As DataTable = New DataTable()

    adapt.Fill(d) If d.Rows.Count > 0 Then Dim abc As CheckBox() = {CheckBox1, CheckBox2, CheckBox3} For i As Integer = 0 To abc.Length - 1 checkboxstr = abc(i).Text If d.Rows(0)("columnName").ToString() = 0 Then abc(i).Checked = True End If Next End If

    Best  Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 25, 2019 7:03 AM
    Moderator