[RESOLVED] Help needed about converting OLEDB to SQLite-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] Help needed about converting OLEDB to SQLite

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Resolved [RESOLVED] Help needed about converting OLEDB to SQLite

    Hello, I created an OLEDB database with relations, it works fine. I tried converting it to SQLite however I'm getting empty datagridview.

    CSHARP Code:
    1. private void Form1_Load(object sender, EventArgs e)
    2. {
    3.     // Compose the connection string.
    4.     string connect_string =
    5.         @"Data Source=Contacts.sqlite; Version=3; FailIfMissing=True; Foreign Keys=True;";
    6.  
    7.     // Create a DataAdapter to load the Addresses table.
    8.     DaAddresses = new SQLiteDataAdapter(
    9.         "SELECT * FROM Addresses", connect_string);
    10.  
    11.     // Create a DataAdapter to load the Addresses table.
    12.     DaTestScores = new SQLiteDataAdapter(
    13.         "SELECT * FROM TestScores", connect_string);
    14.  
    15.     // Create and fill the DataSet.
    16.     StudentDataSet = new DataSet();
    17.     DataTable dt = new DataTable("Addresses");
    18.     DataTable dt2 = new DataTable("TestScores");
    19.  
    20.     StudentDataSet.Tables.Add(dt);
    21.     StudentDataSet.Tables.Add(dt2);
    22.  
    23.     DaAddresses.Fill(dt);
    24.     DaTestScores.Fill(dt2);
    25.  
    26.     // Define the relationship between the tables.
    27.     DataRelation data_relation = new DataRelation(
    28.         "Addresses_TestScores",
    29.         StudentDataSet.Tables["Addresses"].Columns["ContactID"],
    30.         StudentDataSet.Tables["TestScores"].Columns["ContactID"]);
    31.     StudentDataSet.Relations.Add(data_relation);
    32.  
    33.     // Bind the DataGrid to the DataSet.
    34.     dgv.AutoGenerateColumns = true;
    35.     dgv.DataMember = StudentDataSet.Tables[0].TableName;
    36.     dgv.Update();
    37.     dgv.DataSource = StudentDataSet;
    38. }

    How can I show data on Datagriview?

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,270

    Re: Help needed about converting OLEDB to SQLite

    Immediately after the fill statements, check the .Rows.Count property of the datatables... see what it is...
    I'd also fill them first before adding them to the dataset, but I think that's more of a personal choice than anything wrong with what you're doing.
    Also dgv.Update shouldn't be necessary.
    And lastly, are you sure there's matching data in both tables? Or better still, is there data in the database?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Re: Help needed about converting OLEDB to SQLite

    How stupid of me, theres no data in the database! Thanks a lot.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,092

    Re: [RESOLVED] Help needed about converting OLEDB to SQLite

    The only part of that code that is really relevant to your question is the Fill calls. Either they are retrieving data or they aren't. You can determine that as tg suggested or get the value returned by Fill itself. If that's zero then that means that there's no data in the database that matches your query.

    By the way, there are a couple of obvious improvements you could make to that code. Firstly, by passing a connection string to the data adapter constructor you will end up with two separate connection objects that each get opened and closed. If you're query the same database twice then you should be creating one connection object, opening it, performing both queries and then closing it.

    Secondly, there's no need to create the DataTables and add them to the DataSet. You can call Fill and pass the DataSet and a table name and a DataTable will be created implicitly.
    csharp Code:
    1. var data = new DataSet();
    2.  
    3. using (var connection = new SQLiteConnection("connection string here"))
    4. using (var addressAdapter = new SQLiteDataAdapter("SELECT * FROM Addresses", connection))
    5. using (var testScoreAdapter = new SQLiteDataAdapter("SELECT * FROM TestScores", connection))
    6. {
    7.     connection.Open();
    8.  
    9.     addressAdapter.Fill(data, "Addresses");
    10.     testScoreAdapter.Fill(data, "TestScores");
    11. }
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    169

    Re: [RESOLVED] Help needed about converting OLEDB to SQLite

    Yes, I've fixed that connection problem. Thanks for the tips.

Tags for this Thread

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

Survey posted by VBForums.