ADO数据访问（好答案将给高分!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!）

[Miken]

使用Visual C++进行数据库访问和操作时（ADO):
1.一次读一条记录，并将记录的值根据字段值将内容取出；
2.怎样修改一条记录的某个字段的值；
3.怎样在控制面板的ODBC中登记ORACLE数据源？

附录：
答对一题100分
(oicq:48341170)

[wyzegg]

csdn中有无数的例子

[zhq2000]

老大，这些都是SQL的问题，不是ADO的问题！
1.
对于Oracle:
select * from tablename where your-conditions-here rownum < 2
如rownum 不对 ， 换成 row_num!
对于SQL Server:
select top 1 * from tablename where your-conditions-here

SQL Server 例子：
STDMETHODIMP CArrearageC::GetAt(long lngIDD, VARIANT bstrCase, VARIANT *CurrentRecordNum, VARIANT *RecordCount, LPDISPATCH *ppRecordset)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState())

// TODO: Add your implementation code here

HRESULT hr = S_OK;

if(ppRecordset == NULL)
return E_INVALIDARG;

*ppRecordset = NULL;

try{
_ConnectionPtr pAdoConnection;
_RecordsetPtr pAdoRecordset;

_bstr_t bstrSQL;
_variant_t vntRecordsAffected;
basic_stringstream < wchar_t > bssSQL;
long nIndex = 0;

pAdoConnection.CreateInstance(__uuidof(Connection));
pAdoRecordset.CreateInstance(__uuidof(Recordset));

pAdoConnection->CursorLocation = adUseClient;
pAdoConnection->Open( m_bstrConnectionString , (BSTR)NULL , (BSTR)NULL , -1);
pAdoRecordset->PutRefActiveConnection(pAdoConnection);
pAdoRecordset->CursorLocation = adUseClient;
//pAdoRecordset->MaxRecords = 1;

BSTR bsCase = NULL;

bssSQL << L"SELECT TOP 1 * FROM 表_欠费 WHERE " ;
_variant_t vntCase(bstrCase);
if(vtMissing != vntCase && (bstrCase.vt & VT_BSTR))
{
CString str = (LPCTSTR) (_bstr_t)vntCase;
str.TrimLeft();
str.TrimRight();
if(str.GetLength()>0)
{
bsCase = str.AllocSysString();
bssSQL << bsCase << L" AND " ;
}
}

bssSQL << L" (idd = " << lngIDD << L" ) ";
bssSQL << ends;

bstrSQL = bssSQL.str().c_str();

if(bsCase != NULL)
SysFreeString(bsCase);

TRACE1(_T("In %s::GetAt\n") , COMPONENT);
TRACE1(_T("SQL = %s\n\n") , (LPCTSTR)bstrSQL);

if( vtMissing != (*RecordCount))
{
long nCount;
hr = GetCount(bstrCase , &nCount);
if(FAILED(hr))
throw hr;
*RecordCount = _variant_t(nCount);
}

pAdoRecordset->Open(_variant_t(bstrSQL) , &vtMissing , adOpenStatic ,
adLockReadOnly , -1);

if(vtMissing != (*CurrentRecordNum))
{
if(!pAdoRecordset->EndOfFile)
{
long nIDD = (long) pAdoRecordset->Fields->Item["idd"]->GetValue();
hr = GetIndex(nIDD , bstrCase , &nIndex);
if(FAILED(hr))
throw hr;
}

*CurrentRecordNum = _variant_t(nIndex);
}

pAdoRecordset->PutRefActiveConnection(NULL);
pAdoRecordset->AddRef();
pAdoRecordset->QueryInterface(IID_IDispatch , (LPVOID*) ppRecordset);

if(m_spObjectContext != NULL)
m_spObjectContext->SetComplete();
}
catch(_com_error &e)
{
hr = e.Error();

if(m_spObjectContext != NULL)
m_spObjectContext->SetAbort();

try{
IEventPtr pEvent;
pEvent.CreateInstance(__uuidof(Event));
pEvent->Log( _bstr_t(COMPONENT) , _bstr_t(L"GetAt") , m_lngError ,
m_bstrErrorSource , m_bstrErrorDescription);
}
catch(...)
{

}

TRACE2(_T("\nError occured in %s::GetAt\nError Number : %d\n") , COMPONENT , hr);
TRACE3(_T("Source : %s\nDescription : %s\nErrorMessage : %s\n") ,
(LPCTSTR) e.Source() , (LPCTSTR) e.Description() , (LPCTSTR) e.ErrorMessage());

CString str = (LPCTSTR) e.Description();
if(str.GetLength()<1)
str = e.ErrorMessage();

if(str.GetLength()<1)
str = "Unknown";

AtlReportError(CLSID_ArrearageC , (LPCTSTR)str ,
IID_IArrearageC , hr);
}
catch(...)
{
if(m_spObjectContext != NULL)
m_spObjectContext->SetAbort();
}

return hr;
}

2.
Update tablename set thefield = thevalue where your-conditions-here

SQL Server 例子：
STDMETHODIMP CArrearageC::Update(BSTR bstrUnitID, BSTR bstrGetListDate, VARIANT bstrChargeDate, VARIANT bstrExchDateTime , VARIANT lngArrearageUseQty, VARIANT dblArrearageAmt, VARIANT lngArrearageDoorsCount, VARIANT lngArrearageResetFee, VARIANT boolArrearageTag, VARIANT dblReserve1, VARIANT dblReserve2, VARIANT dblReserve3, long *pRetVal)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState())

// TODO: Add your implementation code here

HRESULT hr = S_OK;

if(pRetVal == NULL)
return E_INVALIDARG;

*pRetVal = -1;

try{
_ConnectionPtr pAdoConnection;
_CommandPtr pAdoCommand;

_bstr_t bstrSQL;
BOOL bFirst = TRUE;
_variant_t vntRecordsAffected;
basic_stringstream < wchar_t > bssSQL;
CString strTemp;
BSTR bsCase = NULL;


pAdoConnection.CreateInstance(__uuidof(Connection));
pAdoCommand.CreateInstance(__uuidof(Command));

bssSQL << L"UPDATE 表_欠费 SET ";

if((vtMissing != bstrChargeDate) && (bstrChargeDate.vt & VT_BSTR))
{
strTemp = (LPCTSTR)(_bstr_t) _variant_t(bstrChargeDate);
strTemp.TrimLeft();
strTemp.TrimRight();
strTemp.MakeUpper();
if(!bFirst)
{
bssSQL << L" , ";
}

if((strTemp.GetLength() < 1) || (strTemp == "NULL"))
{
bssSQL << L" 收费日期 = NULL ";
}
else
{
bsCase = strTemp.AllocSysString();
bssSQL << L" 收费日期 = '" << bsCase << L"' ";
}

bFirst = FALSE;
}

if(bsCase != NULL)
{
SysFreeString(bsCase);
bsCase = NULL;
}

if((vtMissing != bstrExchDateTime) && (bstrExchDateTime.vt & VT_BSTR))
{
strTemp = (LPCTSTR) (_bstr_t) _variant_t(bstrExchDateTime);
strTemp.TrimLeft();
strTemp.TrimRight();
if(strTemp.GetLength()>0)
{
bsCase = strTemp.AllocSysString();
bssSQL << L" 交易时间 = '" << bsCase << L"' ";
bFirst = FALSE;
}
}

if(bsCase != NULL)
{
SysFreeString(bsCase);
bsCase = NULL;
}

if((vtMissing != lngArrearageUseQty) &&
((lngArrearageUseQty.vt & VT_I4) || (lngArrearageUseQty.vt & VT_I2) ||
(lngArrearageUseQty.vt & VT_UI1)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 欠费用量 = " << (long) _variant_t(lngArrearageUseQty);
bFirst = FALSE;
}

if((vtMissing != dblArrearageAmt) &&
((dblArrearageAmt.vt & VT_I4) || (dblArrearageAmt.vt & VT_I2) ||
(dblArrearageAmt.vt & VT_UI1) || (dblArrearageAmt.vt & VT_R8) ||
(dblArrearageAmt.vt & VT_R4)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 欠费金额 = " << (double) _variant_t(dblArrearageAmt);
bFirst = FALSE;
}

if((vtMissing != lngArrearageDoorsCount) &&
((lngArrearageDoorsCount.vt & VT_I4) || (lngArrearageDoorsCount.vt & VT_I2) ||
(lngArrearageDoorsCount.vt & VT_UI1)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 欠费户数 = " << (long) _variant_t(lngArrearageDoorsCount);
bFirst = FALSE;
}

if((vtMissing != boolArrearageTag) && ((boolArrearageTag.vt & VT_BOOL) ||
(boolArrearageTag.vt & VT_UI1) || (boolArrearageTag.vt & VT_I4) ||
(boolArrearageTag.vt & VT_I2)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 欠费标志 = " << (long) _variant_t(boolArrearageTag);
bFirst = FALSE;
}


if((vtMissing != lngArrearageResetFee) &&
((lngArrearageResetFee.vt & VT_I4) || (lngArrearageResetFee.vt & VT_I2) ||
(lngArrearageResetFee.vt & VT_UI1)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 复置金额 = " << (long) _variant_t(lngArrearageResetFee);
bFirst = FALSE;
}

if((vtMissing != dblReserve1) &&
((dblReserve1.vt & VT_R4) || (dblReserve1.vt & VT_R8)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 保留1 = " << (double) _variant_t(dblReserve1);
bFirst = FALSE;
}

if((vtMissing != dblReserve2) &&
((dblReserve2.vt & VT_R4) || (dblReserve2.vt & VT_R8)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 保留2 = " << (double) _variant_t(dblReserve2);
bFirst = FALSE;
}

if((vtMissing != dblReserve3) &&
((dblReserve3.vt & VT_R4) || (dblReserve3.vt & VT_R8)))
{
if(!bFirst)
{
bssSQL << L" , ";
}
bssSQL << L" 保留3 = " << (double) _variant_t(dblReserve3);
bFirst = FALSE;
}

if(bFirst)
{
return hr;
}

bssSQL << L" WHERE 单元号 = '" << bstrUnitID << L"' AND 取单日期 = '"
<< bstrGetListDate << L"' " << ends;

bstrSQL = bssSQL.str().c_str();

TRACE1(_T("\nIn %s::Update\n") , COMPONENT);
TRACE1(_T("\tSQL = %s\n\n") , (LPCTSTR)bstrSQL);

pAdoConnection->Open( m_bstrConnectionString , (BSTR)NULL , (BSTR)NULL , -1);
pAdoCommand->ActiveConnection = pAdoConnection;
pAdoCommand->CommandText = bstrSQL;
pAdoCommand->CommandType = adCmdText;

pAdoCommand->Execute( &vntRecordsAffected , &vtMissing , adCmdText );

*pRetVal = (long) vntRecordsAffected;

if(m_spObjectContext != NULL)
m_spObjectContext->SetComplete();
}
catch(_com_error &e)
{
hr = e.Error();

if(m_spObjectContext != NULL)
m_spObjectContext->SetAbort();

TRACE2(_T("\nError occured in %s::Update\nError Number : %d\n") , COMPONENT , hr);
TRACE3(_T("Source : %s\nDescription : %s\nErrorMessage : %s\n") ,
(LPCTSTR) e.Source() , (LPCTSTR) e.Description() , (LPCTSTR) e.ErrorMessage());

CString str = (LPCTSTR) e.Description();
if(str.GetLength()<1)
str = e.ErrorMessage();

if(str.GetLength()<1)
str = "Unknown";

AtlReportError(CLSID_ArrearageC , (LPCTSTR)str ,
IID_IArrearageC , hr);
}

return hr;
}


3.To add a data source programmatically

Call SQLConfigDataSource with the fOption set to either ODBC_ADD_DSN or ODBC_ADD_SYS_DSN.
To add a file data source

Call SQLDriverConnect with a SAVEFILE=file_name parameter in the connect string. If the connect is successful, the ODBC driver creates a file data source with the connection parameters in the location pointed to by the SAVEFILE parameter.
Examples
A. Create a data source using SQLConfigDataSource
#include <stdio.h>

#include <windows.h>

#include "sql.h"

#include <odbcinst.h>



int main()

{

RETCODE retcode;



UCHAR *szDriver = "SQL Server";

UCHAR *szAttributes =

"DSN=MyDSN\0DESCRIPTION=SQLConfigDSN Sample\0"

"SERVER=MySQL\0ADDRESS=MyServer\0NETWORK=dbmssocn\0"

"DATABASE=pubs\0";



retcode = SQLConfigDataSource(NULL,

ODBC_ADD_DSN,

szDriver,

szAttributes);



B. Create a file data source
Use the SAVEFILE keyword in SQLDriverConnect to create a file data source, and then use SQLDriverConnect to connect with the file data source. This example has been simplified by removing error handling.

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>



#define MAXBUFLEN 255



SQLHENV henv = SQL_NULL_HENV;

SQLHDBC hdbc1 = SQL_NULL_HDBC;



int main() {



RETCODE retcode;



// This format of the SAVEFILE keyword saves a successful

// connection as the file Myfiledsn.dsn in the ODBC default

// directory for file DSNs.

SQLCHAR szConnStrIn[MAXBUFLEN] =

"SAVEFILE=MyFileDSN;DRIVER={SQL Server};SERVER=MySQL;"

"NETWORK=dbmssocn;UID=sa;PWD=MyPassWord;";



SQLCHAR szConnStrOut[MAXBUFLEN];

SQLSMALLINT cbConnStrOut = 0;



// Allocate the ODBC Environment and save handle.

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);



// Let ODBC know this is an ODBC 3.0 application.

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);



// Allocate an ODBC connection handle and connect.

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

retcode = SQLDriverConnect(hdbc1, // Connection handle

NULL, // Window handle

szConnStrIn, // Input connect string

SQL_NTS, // Null-terminated string

szConnStrOut, // Addr of output buffer

MAXBUFLEN, // Size of output buffer

&cbConnStrOut, // Address of output length

SQL_DRIVER_NOPROMPT);



// Disconnect, set up a new connect string, and then test file DSN.

SQLDisconnect(hdbc1);

strcpy(szConnStrIn, "FILEDSN=MyFileDSN;UID=sa;PWD=MyPassWord;");

retcode = SQLDriverConnect(hdbc1, // Connection handle

NULL, // Window handle

szConnStrIn, // Input connect string

SQL_NTS, // Null-terminated string

szConnStrOut, // Addr of output buffer

MAXBUFLEN, // Size of output buffer

&cbConnStrOut, // Address of output length

SQL_DRIVER_NOPROMPT);



/* Clean up. */

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

return(0);

}

[xwchena]

up