none
how to load data from local sqlite3 in VB.NET RRS feed

  • Question

  • hi guys..

    i maked database by programm DB Browser (SQLite)

    now how can i load the data from this db using vb.net

    Thursday, May 16, 2019 10:09 AM

Answers

All replies

  • hi guys..

    i maked database by programm DB Browser (SQLite)

    now how can i load the data from this db using vb.net

    I will give you an example of SQL Express , I think it's the same with small modifications

    Dim cs AS String = "your connection string"
    
    Using con As New SqlConnection(cs)
                con.Open()
                Using cmd As New SqlCommand
                    cmd.Connection = con
                    cmd.CommandText = "select VID,VName,Code,PurchaseDate,
                                       SortID,Sort,ProductCode,ProductName,
                                       CatID,Cat,Stock,Price,Total
                                       from Purchase
                                       where ID=@ID and CodeID=@CodeID"
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Val(txtID.Text)
                    cmd.Parameters.Add("@CodeID", SqlDbType.Int).Value = Val(txtCodeID.Text)
                    Using dtable As New DataTable, da As New SqlDataAdapter(cmd)
                        dtable.Load(cmd.ExecuteReader)
                        If dtable.Rows.Count > 0 Then
    
                            txtVID.Text = dtable.Rows(0).Item("VID").ToString
                            cmbGCat.Text = dtable.Rows(0).Item("VName").ToString
                            txtCode.Text = dtable.Rows(0).Item("Code").ToString
                            DateTimePicker1.Value = dtable.Rows(0).Item("PurchaseDate").ToString
                            txtSortID.Text = dtable.Rows(0).Item("SortID").ToString
                            cmbCarMod.Text = dtable.Rows(0).Item("Sort").ToString
                            cmbProductCode.Text = dtable.Rows(0).Item("ProductCode").ToString
                            cmbProductName.Text = dtable.Rows(0).Item("ProductName").ToString
                            txtCatID.Text = dtable.Rows(0).Item("CatID").ToString
                            cmbCategory.Text = dtable.Rows(0).Item("Cat").ToString
                            txtStock.Text = dtable.Rows(0).Item("Stock").ToString
                            txtStock1.Text = dtable.Rows(0).Item("Stock").ToString
                            txtPrice.Text = dtable.Rows(0).Item("Price").ToString
                            txtTotal.Text = dtable.Rows(0).Item("Total").ToString
                        End If
                    End Using
                End Using
            End Using

    By this way you can load your database table to your form

    Hope it helps 


    Regards From Egypt


    • Edited by Amr_Aly Thursday, May 16, 2019 12:44 PM
    Thursday, May 16, 2019 12:43 PM
  • Here is a good tutorial with code samples.

    https://www.codeproject.com/Articles/1210189/Using-SQLite-in-Csharp-VB-Net


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, May 16, 2019 12:51 PM
  • hi guys..

    i maked database by programm DB Browser (SQLite)

    now how can i load the data from this db using vb.net

    I will give you an example of SQL Express , I think it's the same with small modifications

    Dim cs AS String = "your connection string"
    
    Using con As New SqlConnection(cs)
                con.Open()
                Using cmd As New SqlCommand
                    cmd.Connection = con
                    cmd.CommandText = "select VID,VName,Code,PurchaseDate,
                                       SortID,Sort,ProductCode,ProductName,
                                       CatID,Cat,Stock,Price,Total
                                       from Purchase
                                       where ID=@ID and CodeID=@CodeID"
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Val(txtID.Text)
                    cmd.Parameters.Add("@CodeID", SqlDbType.Int).Value = Val(txtCodeID.Text)
                    Using dtable As New DataTable, da As New SqlDataAdapter(cmd)
                        dtable.Load(cmd.ExecuteReader)
                        If dtable.Rows.Count > 0 Then
    
                            txtVID.Text = dtable.Rows(0).Item("VID").ToString
                            cmbGCat.Text = dtable.Rows(0).Item("VName").ToString
                            txtCode.Text = dtable.Rows(0).Item("Code").ToString
                            DateTimePicker1.Value = dtable.Rows(0).Item("PurchaseDate").ToString
                            txtSortID.Text = dtable.Rows(0).Item("SortID").ToString
                            cmbCarMod.Text = dtable.Rows(0).Item("Sort").ToString
                            cmbProductCode.Text = dtable.Rows(0).Item("ProductCode").ToString
                            cmbProductName.Text = dtable.Rows(0).Item("ProductName").ToString
                            txtCatID.Text = dtable.Rows(0).Item("CatID").ToString
                            cmbCategory.Text = dtable.Rows(0).Item("Cat").ToString
                            txtStock.Text = dtable.Rows(0).Item("Stock").ToString
                            txtStock1.Text = dtable.Rows(0).Item("Stock").ToString
                            txtPrice.Text = dtable.Rows(0).Item("Price").ToString
                            txtTotal.Text = dtable.Rows(0).Item("Total").ToString
                        End If
                    End Using
                End Using
            End Using

    By this way you can load your database table to your form

    Hope it helps 


    Regards From Egypt



    Thursday, May 16, 2019 12:51 PM
  • Try to use 

    it directly in LoadData() like this

    sql_con = New SQLiteConnection("your connection string") 

    sql_con.Open()

    sql_cmd = New SQLiteCommand

    Or use my code with your connection string and modify it to fit your needs



    Regards From Egypt


    • Edited by Amr_Aly Thursday, May 16, 2019 6:21 PM
    Thursday, May 16, 2019 6:06 PM
  • Oh.... you want to fill a datagridview , you didn't mention that in your question this snippet will help you

    Using con As New SQLiteConnection(cs)
                con.Open()
                Using cmd As New SQLIteCommand
                    cmd.Connection = con
                    cmd.CommandText = "Select PurchaseDate As [Entrance Date],
                                                CodeID As [ID],
                                                Code As [Code],                                        
                                                VName as [Vendor Name],
                                                Cost as [Bill]
                                       from Purchase 
                                       ORDER BY PurchaseDate DESC"
    
                    Using adapter As New SQLiteDataAdapter(cmd), daset As New DataSet
                        adapter.Fill(daset, "Purchase")
                        DataGridView2.DataSource = daset.Tables("Purchase").DefaultView
    
            End Using
        End Using
    End Using
     

    Use the snippet as it is by using <<<<<< Using Statement >>>>>> it's more simple and comfortable 

    you don't need to close the connection "Using Statement" performs this task for you

     

    Regards From Egypt


    • Edited by Amr_Aly Thursday, May 16, 2019 6:31 PM
    Thursday, May 16, 2019 6:27 PM
  • hi guys..

    i maked database by programm DB Browser (SQLite)

    now how can i load the data from this db using vb.net

    https://www.youtube.com/watch?v=tBZA9mXLhos

    And it has good music to listen to too.

    Thursday, May 16, 2019 8:08 PM
  • Hi,

    see the format of the SQLite connection string

    https://www.connectionstrings.com/sqlite

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    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.

    Friday, May 17, 2019 9:16 AM
  • SOLVED!

    i just updated NUGET and it's work 100% thanks to all


    • Edited by Salar Music Saturday, May 18, 2019 6:35 PM
    • Marked as answer by Salar Music Saturday, May 18, 2019 6:35 PM
    Saturday, May 18, 2019 6:15 PM