C / C++ / MFC

How to Insert a record using SQLExecute.

12-Oct-09 16:49

For DAO (MS-Access) classes, you should add following line to "stdafx.h" file.

<br />
#include <afxdao.h> // or afxdb.h for ODBC databases.<br />

Then, you might use CDaoDatabase/CDaoRecordset classes like below.

CDaoDatabase db;
db.Open(pszFileName, FALSE, FALSE, pszConnect);

CDaoRecordset rs(&db);
rs.Open(dbOpenSnapshot, pszSQL, dbReadOnly | dbForwardOnly);

//...

rs.Close();
db.Close();

Because you can not benefit from Doc/View support in a dialog, you can not use CRecordView (or CDaoRecordView for DAO) class within this type of project. If you need a "form like behavior" in a dialog, you need to implement yourself (or find an already implemented one, if any) by setting a connection between recordset and dialog (or its controls) with or without a document class.

janaswamy uday12-Oct-09 5:31

12-Oct-09 5:31

Hi,

I am not able to Insert a record in Acess Table. I am Using ODBC API.

::SqlExecute(...) it is failing to Insert Records.

Please help me out.

Also if i want to move a record to a particular position how can i acieve this. Ex Rs.Move(...) Wrapper in low level ODBC API Call.

Thanks in advance,

Uday.

David Crow12-Oct-09 5:39

David Crow

12-Oct-09 5:39

janaswamy uday wrote:
::SqlExecute(...) it is failing to Insert Records.

Why?

"Old age is like a bank account. You withdraw later in life what you have deposited along the way." - Unknown

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

janaswamy uday12-Oct-09 7:44

12-Oct-09 7:44

UpdateData(TRUE);
SQLHENV envHandle;
SQLHDBC ConnHandle;
SQLHSTMT StmtHandle;
SQLRETURN Ret;

SQLSMALLINT szConnStroutlen;

unsigned char szConnStrout[255];

Ret = ::SQLAllocEnv(&envHandle);
if(Ret != SQL_SUCCESS)
{
AfxMessageBox("Alloc Handle is Faile",MB_OK);
}

Ret = ::SQLAllocConnect(envHandle,&ConnHandle);
if(Ret != SQL_SUCCESS)
{
AfxMessageBox("Alloc Connect is Faile",MB_OK);
}

//Connect through the Data Base in Acess
Ret = ::SQLDriverConnect(ConnHandle,
NULL,(unsigned char *)"Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\Uday.mdb",
SQL_NTS,szConnStrout,sizeof(szConnStrout),&szConnStroutlen,SQL_DRIVER_NOPROMPT);
if(Ret != SQL_SUCCESS)
{
AfxMessageBox("SQLDriverConnect is Faile",MB_OK);
}

Ret = ::SQLAllocStmt(ConnHandle,&StmtHandle);
if(Ret != SQL_SUCCESS)
{
AfxMessageBox("SQLAllocStmt is Faile",MB_OK);
}

CString szResultant;
szResultant.Empty();
szResultant = "INSERT INTO Emp(Name,Address)VALUES (";
szResultant += "'";
szResultant += EmpName;
szResultant += "'";
szResultant += ",";
szResultant += "'";
szResultant += EmpAddress;
szResultant += "'";
szResultant += ")";

//--------> Here it is Failing. But if i hard code the Insert Statement is working Finde. But Appending to String Failed.

Ret = ::SQLExecDirect(StmtHandle,(SQLCHAR*)"INSERT INTO Emp(Name,Address)VALUES ('vijay','Saifabad')",SQL_NTS);
if(Ret != SQL_SUCCESS)
{
AfxMessageBox("SQLExecDirect is Faile",MB_OK);
}

David Crow12-Oct-09 7:47

David Crow

12-Oct-09 7:47

I asked "Why?" not "Show me a bunch of code."

janaswamy uday wrote:
CString szResultant;
szResultant.Empty();
szResultant = "INSERT INTO Emp(Name,Address)VALUES (";
szResultant += "'";
szResultant += EmpName;
szResultant += "'";
szResultant += ",";
szResultant += "'";
szResultant += EmpAddress;
szResultant += "'";
szResultant += ")";

Why not use:

szResultant.Format("INSERT INTO Emp(Name,Address) VALUES('%s','%s')", EmpName, EmpAddress);

janaswamy uday wrote:
//--------> Here it is Failing.

With what value?

janaswamy uday12-Oct-09 7:50

12-Oct-09 7:50

The Value is -1. Not able to Parse the Query String

Re: How to Insert a record using SQLExecute. [modified]

David Crow12-Oct-09 7:53

David Crow

12-Oct-09 7:53

janaswamy uday wrote:
Not able to Parse the Query String

That came back from SQLGetDiagRec()?

Have you compared szResultant with the string literal to see if there are any differences?

What happens if you use CString::Format() instead?

transoft12-Oct-09 11:09

transoft

12-Oct-09 11:09

Most likely the SQL statement is not right.

Try manually with your SQL string generated by your program and see it can insert records or not.

modified on Monday, October 12, 2009 5:25 PM

Ozer Karaagac12-Oct-09 15:05

12-Oct-09 15:05

After reading your subsequent posts, it seems to be a string type problem. That is, ANSI/UNICODE mismatch.
Try the following;

USES_CONVERSION;

Ret = ::SQLExecDirect(StmtHandle,(SQLCHAR*)T2A((LPTSTR)(LPCTSTR)szResultant),SQL_NTS);
if(Ret != SQL_SUCCESS)
{
    ...
}
...

janaswamy uday12-Oct-09 16:40

12-Oct-09 16:40

Dear Ozer Karaaagar,

I got it and very much thankfull to you. Let Me know one more API where it will move the Record to Specific Position. Let me know the solution to Move a Record to MoveFirst,MoveNext,MovePrevious and MoveLast. Which API must be used.

If i Have to Use ::SQLSetPos(...) API. Please give me the Steps to move to Absolute Position. Please give me the solution. It is Urgent.

Thanks & Regards,
Uday.

Ozer Karaagac12-Oct-09 17:59

12-Oct-09 17:59

I've seen AfxMessageBox() and UpdateData() functions in your code snippet, it's a MFC project as far as I understand.

So, Why won't you use CRecordset class of MFC? It provides ODBC data access and it has got all you need (Move*() functions and a limited SetAbsolutePosition()). This will simplify your job pretty much.

Anyway, for question about Move functions;
::SQLExtendedFetch() can be used with 2nd argument should be one of the SQL_FETCH_* constants (i.e. SQL_FETCH_NEXT, SQL_FETCH_PRIOR etc.)

However, you shouldn't expect to get accurate (absolute) sequential positioning in multiuser, dynamic SQL database environments. Bookmarks can also be used for that purpose.

janaswamy uday13-Oct-09 4:56

13-Oct-09 4:56

Thank u very much for ur good and valuble solution.

i am very much thank full to you.

uday.

Ozer Karaagac13-Oct-09 10:44