ado question... 2

[SmileeTiger]

I am trying to open a access database and update a record using the following code but it seems to fail when I execute the SQL statement. Any idea what's the matter?

It fails on the line pRecordset->Open(bstrtSQL, vtMissing, adOpenForwardOnly,adLockOptimistic, adCmdUnknown);


void CRecoveringSeatsandReservationsDlg::OnRecover()
{
//Connecting to the database
CoInitialize (NULL);
_ConnectionPtr m_pConn;
HRESULT hr = m_pConn.CreateInstance(__uuidof(Connection));
if (FAILED( hr ))
AfxMessageBox( "Can't create an intance of ADO.Connection" );
else
{
if (FAILED( m_pConn->Open(
_bstr_t("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = E:\\Recovering\\Recovering Seats and Reservations\\Debug\\MainDatabase.mdb&quot,
_bstr_t( "" ), _bstr_t( "" ), adModeUnknown )))
AfxMessageBox( "Can't open datasource" ); //Couldn't connect to data source


//end of connecting

//Setting up SQL command
_CommandPtr pCommand;
pCommand.CreateInstance (__uuidof (Command));
pCommand->ActiveConnection = m_pConn; // Formerly opened connection pointer
//pCommand->CommandText = "INSERT INTO User (UserID, Name) VALUES (1, 'Storgt')";
CString Test="INSERT INTO User (UserID, Name) VALUES (1, 'Storgt')";

//Running the command
_RecordsetPtr pRecordset;
pRecordset.CreateInstance (__uuidof (Recordset));
pRecordset->CursorLocation = adUseClient;


CString zPath;
CString zSQL;
CString zPathToBackroomDB;
CString zIndex, zNode, zTemp;
_bstr_t bstrtSQL;
BSTR bstrValue;

zSQL= CString ("INSERT INTO User (UserID, Name) VALUES (1, 'Storgt')"

bstrValue = zSQL.AllocSysString();
bstrtSQL = bstrValue;
bstrValue = NULL;
pRecordset->PutRefActiveConnection(m_pConn);
pRecordset->Open(bstrtSQL, vtMissing, adOpenForwardOnly,adLockOptimistic, adCmdUnknown);

AfxMessageBox( "YEA" );
m_pConn->Close(); //Closingconnection
}


}

 

[jsaxon2]

Put a try block arount it and see what the error is.

try
{
pRecordset->Open(bstrtSQL, vtMissing, adOpenForwardOnly,adLockOptimistic, adCmdUnknown);

}
catch(_com_error &e) //We hit an error in SQL
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
CString strDescription = (LPCSTR)bstrDescription;
MessageBox(strDescription , bstrSource, MB_ICONERROR);

}

There may be some syntax errors, but you get the idea.

Hope it helps.

 

[Russie]

I'm no visual c++ expert but when I pass SQL statements using VB I have the end two characters the other way around:

"INSERT INTO User (UserID, Name) VALUES (1, 'Storgt');"
not ";

 

[SmileeTiger]

I get a 'Syntax error in INSERT INTO statement' come up.

Here is the newest copy of my code.

I tried to run this query in Access and it worked fine. I am REALLY confused.

CMH

void CRecoveringSeatsandReservationsDlg::OnRecoverII()
{

CString zPath;
CString zSQL;
CString zPathToBackroomDB;
CString zIndex, zNode, zTemp;
_ConnectionPtr adoConnection;
_RecordsetPtr adoRecordset;
_bstr_t bstrtSQL;
BSTR bstrValue;

CoInitialize (NULL);
// Initialize ADO
adoRecordset.CreateInstance(__uuidof(Recordset));

//connecting to the data source
_ConnectionPtr m_pConn;
HRESULT hr = m_pConn.CreateInstance(__uuidof(Connection));
if (FAILED( hr ))
AfxMessageBox( "Can't create an intance of ADO.Connection" );
else
{
if (FAILED( m_pConn->Open(
L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = E:\\Recovering\\Recovering Seats and Reservations\\Debug\\MainDatabase.mdb;Jet OLEDB:Engine Type=4",
L"",L"", adModeUnknown )))
AfxMessageBox( "Can't open datasource" ); //Couldn't connect to data source
else
{
//Setting up the command
zSQL = CString("INSERT INTO User (UserID, Name) VALUES ('1', 'Storgt')"
bstrValue = zSQL.AllocSysString();
bstrtSQL = bstrValue;
::SysFreeString(bstrValue);
bstrValue = NULL;

//Running the command
adoRecordset->PutRefActiveConnection(m_pConn);


try
{
MessageBox(bstrtSQL,NULL, MB_OK);
m_pConn->Execute(bstrtSQL, NULL, 0);



}
catch(_com_error &e) //We hit an error in SQL
{

_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
CString strDescription = (LPCSTR)bstrDescription;
MessageBox(strDescription , bstrSource, MB_ICONERROR);

}



//pRecordset->Open(bstrtSQL, vtMissing, adOpenStatic, adLockOptimistic, -1);
AfxMessageBox( "YEA" );
m_pConn->Close(); //Closing connection
// adoConnection->Close();
}
}
}

 

[SmileeTiger]

Even if I use
zSQL = CString("INSERT INTO User (UserID, Name) VALUES ('1', 'Storgt');"

It still does not work properly.

CMH

 

[JOLESEN]

If I remember right you cannot use single qoutes around the string.
You need to use 'double qoutes' and further you need to put a backslash in front om them to satisfy the compiler.
Further you don't terminate a SQL-Statement with semi-colon ';'

Try this :

zSQL = CString("INSERT INTO User (UserID, Name) VALUES (1, \"Storgt\&quot"

You might also need to put double qoutes around the first value to be inserted - but that depends on the column-type : A string-type will require double-qoutes.

/JOlesen

 

[SmileeTiger]

Jolesen:

I tried
zSQL = CString("INSERT INTO User (UserID, Name) VALUES (\"1\", \"Storgt\""

But it still doesn't work. Both UserID and Name are trext fields to simplify things for now.


CMH

 

[JOLESEN]

Sorry for the confusion :

I just double checked : Do NOT use double qoutes - only single qoutes.

Since both fields are text-fields surround both values with SINGLE qoutes.

/JOlesen

 

[jsaxon2]

jolesen is correct in his last post., use single quotes.

zSQL = CString("INSERT INTO User (UserID, Name) VALUES ('1', 'Storgt')"

 

[SmileeTiger]

It still doesn't work though. I wonder if the error message is missleading me?

 

[JOLESEN]

Ok, what error information do you get ?

/JOlesen

 

[SmileeTiger]

I get a 'Syntax error in INSERT INTO statement' come up.


That's it. The query looks correct though..

 

[jsaxon2]

Change your m_pConn->Execute(bstrtSQL, NULL, 0);
to

m_pConn->Execute(bstrtSQL, NULL, adCmdText);

 

[JOLESEN]

A very wild guess :

Try this :

_variant_t rowct;
m_pConn->Execute(bstrtSQL, &rowct, adExecuteNoRecords);

Another thing : Is your connection Read/Write
m_pConn -> Mode = adModeReadWrite;

/JOlesen

 

[SmileeTiger]

Neither of those worked either. I am so at a loss

 

[JOLESEN]

Ok,

How about som more error information :

catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());

char buf[4*1024];
sprintf(buf, "Com Error:\n Code=0x%08X\n Meaning=%s\n Source=%s\n Description=%s",
e.Error(),
e.ErrorMessage(),
(LPCSTR) bstrSource,
(LPCSTR) bstrDescription);
MessageBox(buf , "Error information", MB_ICONERROR);

}

Please let's see the whole stuff....

/JOlesen

 

[SmileeTiger]

Using that I get:

Com Error:
Code=0x80040E14
Meaning=IDispatch error #3092
Source=Microsoft JET Database Engine
Description=Syntax Error in INSERT INTO statement

 

[SmileeTiger]

*LAUGHS* You know what the problem was?

User is a reserved word!

When I simply insert square brackets around user it fixes the problem.


Thanks everyone.

 

[JOLESEN]

Think I' got it :

Try to rename the table from User to something else ... I think User is a reserved word.


/JOlesen

 

[JOLESEN]

Life sure isn't easy ....

Sleep well Tiger.

/JOlesen