ADO AddNew problem...

[Wayne Fuller]

The following code tries to add a new record to the database. I get the
following error when it happens:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.


Here is the code.
HRESULT CDataSource::AddNewBranch(long nNumber, BSTR bstrCity, BSTR bstrState)
{
HRESULT hr = Connect();

if ( SUCCEEDED(hr) )
{
_variant_t EMPTY_VARIANT(DISP_E_PARAMNOTFOUND, VT_ERROR);
_bstr_t bstrQuery("SELECT * FROM Branches");
_bstr_t bstrField1("Number");
_bstr_t bstrField2("Branch City");
_bstr_t bstrField3("Branch State");
_RecordsetPtr pRS;

try
{
hr = pRS.CreateInstance(_uuidof(Recordset));

if ( SUCCEEDED(hr) )
{
pRS->PutRefActiveConnection(m_Connection);
hr = pRS->Open(_variant_t(bstrQuery), EMPTY_VARIANT, adOpenForwardOnly, adLockOptimistic, adCmdText);

if ( SUCCEEDED(hr) )
{
SAFEARRAY * saField, * saValue;
SAFEARRAYBOUND aDim[1];
long nIndex[1];

aDim[0].lLbound = 0;
aDim[0].cElements = 3;
saField = SafeArrayCreate(VT_VARIANT, 1, &(aDim[0]));

if ( saField == NULL )
{
pRS->Close();

return E_OUTOFMEMORY;
}

saValue = SafeArrayCreate(VT_VARIANT, 1, &(aDim[0]));

if ( saValue == NULL )
{
pRS->Close();

return E_OUTOFMEMORY;
}

nIndex[0] = 0;
hr = SafeArrayPutElement(saField, &(nIndex[0]), &(_variant_t((const BSTR) bstrField1)));
nIndex[0] = 1;
hr = SafeArrayPutElement(saField, &(nIndex[0]), &(_variant_t((const BSTR) bstrField2)));
nIndex[0] = 2;
hr = SafeArrayPutElement(saField, &(nIndex[0]), &(_variant_t((const BSTR) bstrField3)));

if ( SUCCEEDED(hr) )
{
nIndex[0] = 0;
hr = SafeArrayPutElement(saValue, &(nIndex[0]), &(_variant_t((long) nNumber)));
nIndex[0] = 1;
hr = SafeArrayPutElement(saValue, &(nIndex[0]), &(_variant_t((const BSTR) bstrCity)));
nIndex[0] = 2;
hr = SafeArrayPutElement(saValue, &(nIndex[0]), &(_variant_t((const BSTR) bstrState)));

if ( SUCCEEDED(hr) )
{
VARIANT var1, var2;

var1.vt = VT_ARRAY | VT_VARIANT;
var2.vt = VT_ARRAY | VT_VARIANT;
var1.parray = saField;
var2.parray = saValue;
pRS->AddNew(_variant_t(var1), _variant_t(var2));
}
}

pRS->Close();
}
}
}
catch ( _com_error & e )
{
DisplayComError(e);

return e.Error();
}
}

// SysFreeString(bstrCity);
// SysFreeString(bstrState);

return hr;
}



Any help would be appreciated,

Wayne

[ALM]

I don't have time to explore into your problem but I do recommend that you turn ODBC tracing on so you can see exactly what the INSERT statement looks like that it's trying to execute.

Good luck!

[Wayne Fuller]

I appreciate your response, but I found the problem. I should have posted that I solved it. It was not the code at all, it was the database. I inheirited this project and I assumed the database was set up correctly. It was being used by a VB programmer, and I guess VB is very forgiving. The problem was that there was a field named Number which happends to be a name of the data type. I changed it to BranchNumber and it worked great. Thanks again for the post and I will try that ODBC tracing, that sounds like the way to go. If you read this, where does the output go, into the debug window?

Anyway thanks,

Wayne