CDaoDatabase And MS SQL

I want to connect to and update an MS SQL database in C++.  Where a user will fill out a windows form and give me the DSN, table, and column.  I will then take that info behind the scenes and insert some data to the database.


What I want to avoid is showing any windows dialog box besides my own.

I have a bunch of code that writes data to a DNS-Less MS Access database, but I want to expand that to work with MS SQL.

Thanks!
scanhelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DanRollinsCommented:
You indicate that you will allow the user to input a DSN... but then you also indicate that you want to use DSN-less connection.

If you have a DSN (as set up in the ODBC control panel) then it is very easy to work with databases using MFC.  Let me know if that is the case and I'll cover it.

If you do not have a DSN, then your best bet is probably to use ADO database access.   A very simple example of that is shown here:

   How to connect to sql server 2K?
   http://www.experts-exchange.com/Programming/Programming_Languages/Cplusplus/Q_20534401.html

Here is a project in which a guy wrapped that all up into an MFC object:

   ARSdb - Abstracted Database and Recordset Classes
   http://codeguru.earthweb.com/mfc_database/arsdb.shtml

You can also create an ATL data access object (which uses OLE DB), but that can be kind of ticky, what with all of the ATL stuff.

-- Dan
0
scanhelpAuthor Commented:
Dan,
Thanks for the reply.

If your read my orginal question I stated that I will allow for a DSN connection for SQL Server... but that I currently have code for DNS-Less connection for Access.

So I am wanting a way to connect to a SQL server with a DSN.
0
DanRollinsCommented:
In that case, you can use MFC CRecordset-derived objects.

Open the ClassWizard and click [Add Classs...] > New...
Set the name to CRsMyTable
Set the base class to CRecordset and click OK
Slect your DSN from the drop-down list
Select your tabl from the list

You now have an object that can read that table and you can access the fileds directly via program variables with names that match those in the database.  If you have a limited universe of tables that the user might want to acces, then repeat those steps for each one.

If you want  to be able to access *any* DSN and *any* table, then you can work withCDatabase and  CRecordset objects directly.  For instance:

#include <afxdb.h>

void CD26Dlg::OnButton1()
{
      CDatabase cDb;
      CString sConnect;
      CString sDSN= "MyDSN";
      CString sUser="MyUser";
      CString sPswd="MyPswd";
      CString sSQL= "SELECT * FROM Inquiry";

      sConnect.Format("DSN=%s;UID=%s;PWD=%s",
            (LPCSTR) sDSN,
            (LPCSTR) sUser,
            (LPCSTR) sPswd
      );

      cDb.OpenEx( sConnect, CDatabase::noOdbcDialog );

      CRecordset crs( &cDb );

      crs.Open( AFX_DB_USE_DEFAULT_TYPE, sSQL );

      CString sData;
      crs.GetFieldValue( "MyField", sData );

      MessageBox( sData, "The value of MyField in the first record  of MyTable is..." );
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

mahesh1402Commented:
Alternatively you may refer this example at codeproject :

http://www.codeproject.com/database/sqlconnect.asp <=====

MAHESH
0
mahesh1402Commented:
0
scanhelpAuthor Commented:
Dan,
Another good post.  But as my title said (and I should have been clearer), I'd like a solution with CDaoDatabase, not CDatabase.  Now I could convert my Dao code to CDatabase, but I think it would be easier to work with htat instead.

The rest of you that posted, not much help in the links you provided.
0
scanhelpAuthor Commented:
Dan,
You pointed me in the right direction, so you get the points.

But here is my final code for this to work:
CString      Sql;
AfxGetModuleState()->m_dwVersion = 0x0601; // Needed for Access 2000 and doesn't seem to hurt anything else.
AfxDaoInit();
CString sConnect;
CString sDSN= "testds";
CString sUser="test";
CString sPswd="test";

sConnect.Format("DSN=%s;UID=%s;PWD=%s",
              (LPCSTR) sDSN,
              (LPCSTR) sUser,
              (LPCSTR) sPswd
            );

db.Open("",FALSE,FALSE,_T(sConnect));
Sql = "SELECT * FROM ";
Sql.Insert(Sql.GetLength(), m_pSettings->struDatabaseInfo.strTable); //Finishes building my SQL string.
recset.Open(AFX_DAO_USE_DEFAULT_TYPE,Sql,dbSeeChanges); // I am assuming, would love a comment on this, that I can just put "test" (which is the table I am  using) instead of AFX_DAO_USE_DEFAULT_TYPE.

Thanks for your help.
0
DanRollinsCommented:
The DAO recordset handling, based on the JET database engine, is generally considered obsolete -- having been supplanted first by ODBC (MFC CDatabase/CRecordset), then ADO (ActiveX), then OLE DB -- that's why I didn't mention it.  However, I can't think of any reason not to use it if it suits your purposes.

There are three forms of the CDaoRecordset::Open() fn and you ar using the first one in which the first paramerte is an integer nOpenTYpe and the value AFX_DAO_USE_DEFAULT_TYPE lets the system choose automatically between dbOpenDynaset, dbOpenTable, and dbOpenSnapshot (my read indicates that AFX_DAO_USE_DEFAULT_TYPE will equate with dbOpenDynaset).

The other two forms expect the first parameter to be a pointer to a CDaoTableDef object and I have no experience working with that object.  It is covered here:
    DAO Tabledef: Using Tabledefs
    http://msdn.microsoft.com/library/en-us/vccore/html/_core_DAO_Tabledef.3a_.Using_Tabledefs.asp

Note that it is possible to insert data, delete records, etc. without bothering with recordsets by using the CDaoDatabase::Execute() function.  Just compose a valid SQL statement and pass it in.  For instance,

    sSQL= "INSERT INTO MyTable (lastName,firstName,city) VALUES ( 'Jones', 'Tim', 'Tulsa' )";
or
    sSQL= "DELETE FROM MyTable WHERE lastName= 'Jones' ";

    db.Execute( sSQL, dbSQLPassThrough );
0
scanhelpAuthor Commented:
Dan,
I had another question and figured it deserved its own question (don't want to milk these 500 points)... if you could comment I'd appreciate it.

http://www.experts-exchange.com/Databases/Q_21813246.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.