none
New to ADO.NET, have a simple question RRS feed

  • Question

  • Ok, I've spent the last seven years learning standard C++, and jumping to Visual Studio .NET has been crazy.


    That being said, I've been doing pretty well designing my GUI and whatnot. But now I've run into a problem:

    I want to use an Access DB to store the data my program generates. I already have a dummy DB setup and have it linked in my program using the OleDBConnection tool.

    The DB hierarchy looks like this:
    http://img183.imageshack.us/my.php?...accessdbnl4.png

    I already have everything in XML. If there is some way to send the XML version with the same hierarchy into the DB, I'd love to know about it. I'm not sure how typing and schema would work.

    Otherwise, if there's a way to manually put my strings and whatnot into each field as I generate them, I'd like to know that too. I can make it happen either way.

    I'd also like to check the DB as follows (psuedo code):

    Code Block

    if (currentcompanyexistsinDB = true)

    {

    if (currentalgorithmexistsinDB = true)

    {

    addXMLresults to current algorithm

    }

    else

    {

    create new algorithm in DB

    addXMLresults to new algorithm

    }

    }

    else

    {

    create new company in DB

    create new algorithm in DB

    addXMLresults to new algorithm

    }



    So I need some way of checking the DB to see if the strings exist.

    I need to know the following:
    1) Is there a way to send the data from XML directly into the Access DB using the OleDBConnection? If no, how should I do it? If yes, please tell me where I can learn about it.

    2) Is there a way to search the DB for individual fields? Where could I go to learn how?

    BTW, this program was written in Visual Studio 2003 .NET in Visual C++. If needed, I can just generate the XML file in this program and make a second program with VB.NET or C# to put the data into the DB. Any help would be appreciated.

    Thanks in advance,
    Jesse Musgrove
    Monday, January 7, 2008 4:10 PM

All replies

  • I'm not quite sure what you mean by "I already have everything in XML."  If you mean that you've already written code that serializes your objects into XML, and now you want to plop that XML into the database, I fear you may have some refactoring ahead of you.

     

    While you can, to a first approximation, do anything in C#, it's much easier to do things using the tools that the language gives you.  It's much easier to build a typed DataSet, use its TableAdapters to handle database selects, inserts, updates, and deletes, and use data binding to bind your GUI to the DataSet via a BindingSource than it is to write all of that code yourself.  (At least, once you know how all of the underlying technology works.)

     

    But say some more about your application.  I don't know enough about it to give concrete advice yet.

    Monday, January 7, 2008 6:03 PM
  •  Robert Rossney wrote:
    But say some more about your application.  I don't know enough about it to give concrete advice yet.


    It's like this...

    I get an algorithm from a company to test. All the GUI and DB stuff is independant of the algorithm. I use the program to test the algorithm by testing various "thresholds" on the algorithm. Each time, I get an FAR and FRR value. At the moment, I'm storing these values into XML when I get them, but I can change that.

    Now I want to take this information and make a nice report. So the idea was that I would use Crystal Reports (which at first glance looked like the right thing to do). But first, I need it in a database. I chose a Microsoft Access database since it seemed rather easy.

    Here's the problem: I have no clue how to take my information and put it into the .mdb. Every time I look at a tutorial or do a Google search I just get more confused. I've looked at dozens of possible 'solutions', but not a single one will give me an answer. I just want to know how to put a single scaler into the database, and I might be able to figure it out from there.

    As of right now, I've added the OleDBConnection object to my GUI. I've got it pointed to the database, and my program compiled and ran with the following statement:

    DBConnection->Open();

    So I believe that much is working. How do I search the database? How to I enter a new field? No matter how many times I google "Access Database Tutorials .NET", or "ADO.NET Access", or "for the love of God how do I edit an Access Database in Visual Studio .NET", I just can't get a clear answer.

    I really appreciate the response. I just wish I wasn't so new to all of this, it all sounds very cryptic to me.
    Monday, January 7, 2008 7:47 PM
  • Ok, now I feel like an idiot.

    After searching for a long time, I finally discovered something in the MSDN that helped a lot. As it turns out, SQL commands work on Access databases. I really have no idea what I'm doing, but that's a start.

    Anyway, now I'm getting some weird exception that I can't really figure out.

    The MSDN Code:
    Code Block

       OleDbDataReader* myReader = myCommand->ExecuteReader();
       try
       {
         while (myReader->Read())
        {
           Console::WriteLine(S"{0}, {1}", __box(myReader->GetInt32(0)), myReader->GetString(1));
        }
       }
       __finally
       {
        // always call Close when done reading.
        myReader->Close();
        // always call Close when done reading.
        myConnection->Close();
     }


    My Code:
    Code Block

                         OleDbDataReader* myReaderCompanies = myCommandCompanies->ExecuteReader();
                         bool newCompany = true, newAlgorithm = true;
                         try
                         {
                            while (myReaderCompanies->Read())
                            {
                                if (companyName = myReaderCompanies->GetString(0))
                                    newCompany=false;
                            }
                         }
                         __finally
                         {
                            myReaderCompanies->Close();
                         }
                         if (newCompany)
                         {
                             String* newCompanyQuery = S"INSERT INTO Companies(CompanyName, Address) VALUES (companyName, companyAddress)";
                             OleDbCommand* myCommandCompany = new OleDbCommand(newCompanyQuery, DBConnection);
                             myCommandCompany->ExecuteNonQuery();
                         }


    The exception tells me:
    An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

    Additional information: There is already an open DataReader associated with this Connection which must be closed first.


    I'm really confused here.
    Tuesday, January 8, 2008 3:34 PM
  • You probably have a DataReader open on this connection earlier in the code than what you've posted here.

     

    That blind try/finally block is going to cause you no end of grief, by the way.

    Tuesday, January 8, 2008 5:23 PM