Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CDaoDatabase And MS SQL

Posted on 2006-04-12
10
Medium Priority
?
568 Views
Last Modified: 2013-11-20
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!
0
Comment
Question by:scanhelp
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 16441941
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
 

Author Comment

by:scanhelp
ID: 16442006
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
 
LVL 49

Accepted Solution

by:
DanRollins earned 2000 total points
ID: 16442784
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Expert Comment

by:mahesh1402
ID: 16442857
Alternatively you may refer this example at codeproject :

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

MAHESH
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16443633
0
 
LVL 3

Expert Comment

by:Raj_Kau
ID: 16443776
0
 

Author Comment

by:scanhelp
ID: 16445451
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
 

Author Comment

by:scanhelp
ID: 16446502
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 16448495
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
 

Author Comment

by:scanhelp
ID: 16448847
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question