none
VB 2010 SQLite and Excel

    Question

  • I am trying to make a VB application that can read and write at excel or at SQlite db.

    With the excel my application works fine, but when i tried to share my application 1 of my friends can open it and all the others no :/

    So i want to try with the SQlite but i dont know what i need for to support the sqlite at my VB project.

    I am using VB 2010 but i can also i can use C# :-P


    Thanks
    • Edited by Clud7 Saturday, May 05, 2012 1:15 PM
    Saturday, May 05, 2012 1:14 PM

Answers

  • Ok done :-)

    I was needed only 2 .dll files. You can get them from here:

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

    You must select the Precompiled Binaries for 64-bit or x32-bit for Windows (.NET Framework 4.0)

    After i use this code:

    'I set up my database connection properties here also the adapters

    Public cons As New SQLite.SQLiteConnection("Data Source =" + Application.StartupPath + "\dota2.db") Public cmd As New SQLite.SQLiteCommand Public sql As String Public strSQL As String Public dss As New DataSet Public das As New SQLite.SQLiteDataAdapter

    'I read from database
    
    Dim SQLreader As SQLite.SQLiteDataReader
    
            Form1.cons.Open()
            Form1.cmd = Form1.cons.CreateCommand()
    'I write the sql command
            Form1.cmd.CommandText = "select Name from heroes where id =" + id.ToString + " ;"
            SQLreader = Form1.cmd.ExecuteReader()
            DOTANAME.Text = String.Format(SQLreader(0))
            SQLreader.Close()
            Form1.cons.Close()

    'I save to the database
    
     Form1.cons.Open()
                        Form1.cmd = Form1.cons.CreateCommand()
                        Form1.cmd.CommandText = "UPDATE Heroes SET Name='" + DOTANAME.Text + "', GP=" + DOTAGP.Text + ", Wins=" + DOTAWINS.Text + ", Losses=" + DOTALOSSES.Text + ", Kills=" + DOTAKILLS.Text + ", Deaths=" + DOTADEATHS.Text + ", Assists=" + DOTAASSISTS.Text + " WHERE id =" + id.ToString + ";"
                        Form1.cmd.CommandType = CommandType.Text
                        Form1.cmd.ExecuteNonQuery()
                        Form1.cmd.Dispose()
                        Form1.cons.Close()




    • Edited by Clud7 Sunday, May 27, 2012 10:53 AM
    • Marked as answer by Clud7 Sunday, May 27, 2012 10:53 AM
    Sunday, May 27, 2012 10:52 AM

All replies

  • Does the one friend who can open the app with excel have office installed, and the others not? 
     
    I am not positive on SQLite, but I presume you need to package a runtime version of some form with your application that installs SQLite for those without it already.

    --
    Mike
    Saturday, May 05, 2012 4:21 PM
  • Does the one friend who can open the app with excel have office installed, and the others not? 
    I am not positive on SQLite, but I presume you need to package a runtime version of some form with your application that installs SQLite for those without it already.

    --
    Mike

    Yeap he has office installed. So if they should have the office installed we forget the excel.

    Any better idea about my application?

    My application should be able to accept game score data for different heroes and also to be able to show which is the best score and bla bla.
    With the Excel file that works fine for me but when we try to run the application at machine with openoffice installed i have lot of errors :/ and at non installed any office the application cant see the excel file.

    Is better to connect my application with online mysql server or to continue with SQlite?

    Thanks again.

    Saturday, May 05, 2012 6:42 PM
  • The only reason to go with a remote (online) database is if you want multiple users to store and potentially share/compare their scores.  I looked a bit at SQLite and it seems that would likely be a good solution for you if you are only trying to have a user compare their own game scores on a single machine.

    --
    Mike
    Sunday, May 06, 2012 2:47 AM
  • Here is a method to use Excel with VS2010/VB.NET

    Walkthrough: Embedding Type Information from Microsoft Office Assemblies (C# and Visual Basic) http://msdn.microsoft.com/en-us/library/ee317478.aspx#Y726

    MSDN Search http://social.msdn.microsoft.com/search/en-us?query=vs2010+embed+interop+types

    Item 9 http://www.simple-talk.com/dotnet/visual-studio/visual-studio-vb-2010-enhancements/


    KSG

    Sunday, May 06, 2012 1:10 PM
    Moderator
  • The only reason to go with a remote (online) database is if you want multiple users to store and potentially share/compare their scores.  I looked a bit at SQLite and it seems that would likely be a good solution for you if you are only trying to have a user compare their own game scores on a single machine.

    --
    Mike

    :-) can you share me any tutorial link or something for to connect VB with SQLite:?

    I have search a lot at the web but i get only for C++ tutorials :-/

    Monday, May 07, 2012 11:07 AM
  • Here is a method to use Excel with VS2010/VB.NET

    Walkthrough: Embedding Type Information from Microsoft Office Assemblies (C# and Visual Basic) http://msdn.microsoft.com/en-us/library/ee317478.aspx#Y726

    MSDN Search http://social.msdn.microsoft.com/search/en-us?query=vs2010+embed+interop+types

    Item 9 http://www.simple-talk.com/dotnet/visual-studio/visual-studio-vb-2010-enhancements/


    KSG

    Thanks but they should buy and install office :/

    And with open office i have errors so we forget this option. I think SQLite is good because is freeware and also will work at all. But i don't know how to start with SQLite :-(

    Monday, May 07, 2012 11:10 AM
  • See the below link for SQLite help. There is an ADO.NET provider (System.Data.SQLite) that you can use.

    http://sqlite.phxsoftware.com/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 07, 2012 1:06 PM
  • Ok done :-)

    I was needed only 2 .dll files. You can get them from here:

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

    You must select the Precompiled Binaries for 64-bit or x32-bit for Windows (.NET Framework 4.0)

    After i use this code:

    'I set up my database connection properties here also the adapters

    Public cons As New SQLite.SQLiteConnection("Data Source =" + Application.StartupPath + "\dota2.db") Public cmd As New SQLite.SQLiteCommand Public sql As String Public strSQL As String Public dss As New DataSet Public das As New SQLite.SQLiteDataAdapter

    'I read from database
    
    Dim SQLreader As SQLite.SQLiteDataReader
    
            Form1.cons.Open()
            Form1.cmd = Form1.cons.CreateCommand()
    'I write the sql command
            Form1.cmd.CommandText = "select Name from heroes where id =" + id.ToString + " ;"
            SQLreader = Form1.cmd.ExecuteReader()
            DOTANAME.Text = String.Format(SQLreader(0))
            SQLreader.Close()
            Form1.cons.Close()

    'I save to the database
    
     Form1.cons.Open()
                        Form1.cmd = Form1.cons.CreateCommand()
                        Form1.cmd.CommandText = "UPDATE Heroes SET Name='" + DOTANAME.Text + "', GP=" + DOTAGP.Text + ", Wins=" + DOTAWINS.Text + ", Losses=" + DOTALOSSES.Text + ", Kills=" + DOTAKILLS.Text + ", Deaths=" + DOTADEATHS.Text + ", Assists=" + DOTAASSISTS.Text + " WHERE id =" + id.ToString + ";"
                        Form1.cmd.CommandType = CommandType.Text
                        Form1.cmd.ExecuteNonQuery()
                        Form1.cmd.Dispose()
                        Form1.cons.Close()




    • Edited by Clud7 Sunday, May 27, 2012 10:53 AM
    • Marked as answer by Clud7 Sunday, May 27, 2012 10:53 AM
    Sunday, May 27, 2012 10:52 AM
  • Thank you Clud for a nice and clean tidy post,  and for sharing your solution. This is excellent :)

    Rob

    Wednesday, April 16, 2014 4:19 PM