none
using sqlink to get datatable RRS feed

  • Question

  • so after my last thread i'm now converting my app db from access to sqlite.

    Public Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    dim myDataTable  as datatable
             Dim SQL_SELECT As String = "Select * from companies"
            myDataTable = Con_SQLite(SQL_SELECT)
    
            Dim tempRow As DataRow
            For Each tempRow In myDataTable.Rows
    
                MySource.Add(tempRow("c_name"))
    
            Next
    
            With MyTextBox
                .AutoCompleteCustomSource = MySource
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
            
                .Visible = True
            End With
    
            MyTextBox.Select()
        End Sub
    
    Public Shared Function Con_SQLite(ByVal get_commandString As String)
            Dim mycon As New SQLiteConnection()
            Dim source As String = "data source= c:\vb\NoamDB.sqlite"
            Dim dt As DataTable = Nothing
            Dim ds As New DataSet
    
            Try
                Using con As New SQLiteConnection(source)
                    Using cmd As New SQLiteCommand(get_commandString, con)
                        con.Open()
                        Using da As New SQLiteDataAdapter(cmd)
                            da.Fill(ds)
                            dt = ds.Tables(0)
                        End Using
                    End Using
                    End Using
     Return dt
    
            Catch ex As Exception
                MsgBox(ex.ToString)
    
            End Try
    
        End Function

    with the old way of using accdb it works great.

    now, when i fill mytextbox (witch is suppose to be autofill) nothing happends, like the datatable is empty.

    Sunday, May 13, 2018 12:56 PM

Answers

  • Also, keep in mind that SQLite has both 32-bit and 64-bit versions of the assembly. If your app is running 64-bit (Platform of Any CPU or x64) then you need to use the 64-bit SQLite assembly. If the app is running 32-bit (Platform of x86) then you must use the 32-bit SQLite assembly.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by noampro8 Sunday, May 20, 2018 8:12 AM
    Monday, May 14, 2018 1:00 PM

All replies

  • Is the datatable empty?

    When you step through the code in debug, does execution enter the For Each loop?


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Sunday, May 13, 2018 1:20 PM
    Moderator
  • mmm... got an

    Exception thrown: 'System.IO.FileLoadException' in NoamOM.exe





    Sunday, May 13, 2018 1:34 PM
  • On what line?  con.Open() I'm guessing?  If so, is the file path correct?  Is the file a valid database file for SqlLite?

    Is there a reason for using SqlLite versus just using a local SQL db file (Add -> New Item -> Data -> Service-based Database)?


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Sunday, May 13, 2018 2:55 PM
    Moderator
  • mmm... got an

    Exception thrown: 'System.IO.FileLoadException' in NoamOM.exe





    You might want to change your connection string by adding the version if the error is on opening the database.

    https://www.connectionstrings.com/sqlite/


    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

    Sunday, May 13, 2018 3:41 PM
    Moderator
  • Yea if you have a database error then the table is not returned. 

    Let us first clean up your code removing all humbug.

    Public Class Form1
        Public Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            
    
            Dim myDataTable = Con_SQLite("Select * from companies")
    
            Dim tempRow As DataRow
            For Each tempRow In myDataTable.Rows
    
                MySource.Add(tempRow("c_name"))
    
            Next
    
            With MyTextBox
                .AutoCompleteCustomSource = MySource
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
    
                .Visible = True
            End With
    
            MyTextBox.Select()
        End Sub
    
        Public Shared Function Con_SQLite(ByVal get_commandString As String)
             Dim dt As New DataTable
            Try
                Using con As New SQLiteConnection("data source= c:\vb\NoamDB.sqlite; Version=3;")
                    Using da As New SQLiteDataAdapter(get_commandString, con)
    
                        da.Fill(dt)
                    End Using
                End Using
                Return dt
    
            Catch ex As Exception
                MsgBox(ex.ToString)
                Return Nothing
            End Try
    
        End Function
    End Class
    Without humbug it is always easier to make it running. 


    Success
    Cor




    Sunday, May 13, 2018 3:55 PM
  • Hi noampro8,

    I agree with Karen's point, you should use version.

     Dim filename As String = "C:\cplus\tutorials\c#\SQLite\MyDatabase.sqlite"
            Dim sql As String = "select * from friends;"
            Dim conn = New SQLiteConnection("Data Source=" & filename & ";Version=3;")
            Try
                conn.Open()
                Dim ds As DataSet = New DataSet()
                Dim da = New SQLiteDataAdapter(sql, conn)
                da.Fill(ds)
                conn.Close()
            Catch ex As Exception
                Throw
            End Try

    Best Regards,

    Cherry


    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, May 14, 2018 2:56 AM
    Moderator
  • thank you all for replying. how can i see the line of error? when i click debbug the app runs but it the info windows i see Exception thrown: 'System.IO.FileLoadException' in NoamOM.exe
    Monday, May 14, 2018 8:31 AM
  • Did you add a reference to your project via NuGet, if so was did you add it via this page on NuGet?

    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

    Monday, May 14, 2018 9:42 AM
    Moderator
  • YEP. downloaded the1.4.118 from the link you  gave
    • Edited by noampro8 Monday, May 14, 2018 11:21 AM
    Monday, May 14, 2018 10:09 AM
  • Did you do something what we don't know yet, otherwise it makes no sense to reply to you?

    Success
    Cor

    Monday, May 14, 2018 11:45 AM
  • YEP. downloaded the1.4.118 from the link you  gave

    You might consider trying a different version on the proper platform.

    https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


    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

    Monday, May 14, 2018 12:40 PM
    Moderator
  • thank you all for replying. how can i see the line of error? when i click debbug the app runs but it the info windows i see Exception thrown: 'System.IO.FileLoadException' in NoamOM.exe

    Can you set a debug break point in your code and when it stops on that break point step through it line by line. I would set the break point on a line prior to the Open statement.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 14, 2018 12:52 PM
  • Also, keep in mind that SQLite has both 32-bit and 64-bit versions of the assembly. If your app is running 64-bit (Platform of Any CPU or x64) then you need to use the 64-bit SQLite assembly. If the app is running 32-bit (Platform of x86) then you must use the 32-bit SQLite assembly.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by noampro8 Sunday, May 20, 2018 8:12 AM
    Monday, May 14, 2018 1:00 PM