// We Are Expecting That Stored Procedures Return ID for Entity to which // NSERT/UPDATE/DELETE operation is being performed return (long)pCmdPtr->Parameters->Item["RETURN_VALUE"]->Value ; } catch(_com_error &e) { ATLTRACE((LPCSTR)e.Description()); } return 0; }
C++ & ADObelow is from MSDN,you can find a lot of technical article in MSDN if you search"stored procedure and VC" good luckSteps To Reproduce Behavior In the SQL Server 7.0 Query Analyzer select the test database Pubs. Create the following stored procedure. This stored procedure returns a recordset and an out parameter count.if exists (select * from sysobjects where id = object_id(N'[dbo].[GetJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop proc GetJobs go create proc GetJobs @id as int, @count as int [out] as begin Select @count = Count(*) from jobs where job_id >@id Select * from jobs where job_id >@id end go Use VC App Wizard to create a new console application and modify the code as follows:#include "stdafx.h" #include "stdio.h" #import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2") struct InitOle { InitOle() { ::CoInitialize(NULL); } ~InitOle() { ::CoUninitialize(); } } _init_InitOle_; int main(int argc, char* argv[]) { _variant_t varErr((long)0, VT_ERROR); _CommandPtr comm(__uuidof(Command)); _ConnectionPtr conn(__uuidof(Connection)); _bstr_t connstr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=(local)"; conn->Open(connstr, "", "", adConnectUnspecified); comm->ActiveConnection=conn; comm->CommandText="GetJobs"; comm->CommandType = adCmdStoredProc ; comm->Parameters->Refresh(); _variant_t recs; comm->Parameters->Item[_variant_t((short)1)]->Value= _variant_t((long)5); _RecordsetPtr rs = comm->Execute(&recs, &vtMissing,adCmdStoredProc); _variant_t recordcount= comm->Parameters->Item[_variant_t((short)2)]->Value; printf("recordcount = %li\n", (long)recordcount); return 0; }
Change the Datasource, User ID and password in the connection string above. The recordcount variant that the above code returns is of type VT_NULL rather than the number of records that the stored procedure returns.
如果使用.Net的话,你可以使用 服务器 窗口来自动生成连接字符串, 在视图(view)菜单里面,中文名字叫做"服务器资源管理器",在那里你可以建立一个数据库连接,然后直接把数据拖到form里面就可以, by the way "服务器资源管理器"非常强大,你可以把他当作sql server来使用。可以在里面做很多事情,比如建表,视图,存储过程等等。
strSQL="EXEC [SP] @CODE_IN = 'BATCHEXCHANGE' "
With qdserver
.Connect="odbc;driver={SQL Server};database=datqbase;server=servername;pwd=123 "
.ReturnsRecords=True
.SQL = strSQL
Set rsBatch=.OpenRecordset(dbOpenSnapshot,dbSQLPassThrough)
End With
2.在VC++通过该ODBC调用SQL SERVER的STORED PROCEDURE(szTypes )
#include "stdafx.h"
#include "DatabaseServer.h"
#include <stdarg.h>#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////CDatabaseServer::CDatabaseServer()
{
}CDatabaseServer::~CDatabaseServer()
{}bool CDatabaseServer::getConnectionString(char *szConnectionString)
{
char szServerName[MAX_COMPUTERNAME_LENGTH + 1];
DWORD dwSize=sizeof(szServerName) ;
if(!GetComputerName(szServerName,&dwSize))
return false ;
if(!szConnectionString)
return false ; char szUserName[] = "SA";
char szPassword[] = "";
char szDatabase[] = "IPLOMA";//ADD YOU DATEBASE NAME
sprintf(szConnectionString,"DSN=ODBCTEST;uid=%s;pwd=%s;",szServerName,szDatabase,szUserName,szPassword); //建立CONNECTION STRING return true;
}VARIANT CDatabaseServer::getExecStoredProcedure(char *szTypes,SAFEARRAY *pSPFields)
{
_variant_t vtResultRows;
try
{
_CommandPtr pCmdPtr;
_RecordsetPtr pRecordset;
HRESULT hr ; hr = pCmdPtr.CreateInstance(__uuidof(Command)); char szConnectionString[255];
getConnectionString(szConnectionString);
_variant_t vtConnectionString(szConnectionString);
pCmdPtr->put_ActiveConnection(vtConnectionString); pCmdPtr->CommandType = adCmdStoredProc; //CALL SQL SP
pCmdPtr->CommandText = szTypes ; //YOU SP NAME
hr = pCmdPtr->Parameters->Refresh(); long lBound,uBound ;
HRESULT hresult ;
// Getting Safe Array's Lower and Upper Bounds
hresult = SafeArrayGetLBound(pSPFields, 1, &lBound);
hresult = SafeArrayGetUBound(pSPFields, 1, &uBound); variant_t vtParamVal;
_variant_t Index;
Index.vt = VT_I2;
Index.iVal = 1 ;
for (long iElements=lBound;iElements<=uBound;iElements++)
{
hresult = SafeArrayGetElement(pSPFields, &iElements, &vtParamVal);
pCmdPtr->GetParameters()->GetItem(Index)->PutValue(vtParamVal) ;
Index.iVal++ ;
} //Execute current Stored Procedure
_variant_t vEffected ;
pRecordset = pCmdPtr->Execute(&vEffected,NULL,NULL);
if (pRecordset->BOF || pRecordset->EndOfFile)
throw ;
// Get result set in the form of array
vtResultRows = pRecordset->GetRows(-1);
return vtResultRows.Detach() ;
}
catch(_com_error &e)
{
ATLTRACE((LPCSTR)e.Description());
}
vtResultRows.vt = VT_EMPTY ;
return vtResultRows.Detach();
}long CDatabaseServer::setExecStoredProcedure(char *szTypes,SAFEARRAY *pSPFields)
{
_variant_t vtResultRows;
try
{
_CommandPtr pCmdPtr;
_RecordsetPtr pRecordset;
HRESULT hr ; hr = pCmdPtr.CreateInstance(__uuidof(Command)); char szConnectionString[255];
getConnectionString(szConnectionString);
_variant_t vtConnectionString(szConnectionString);
pCmdPtr->put_ActiveConnection(vtConnectionString); pCmdPtr->CommandType = adCmdStoredProc;
pCmdPtr->CommandText = szTypes ;
hr = pCmdPtr->Parameters->Refresh(); long lBound,uBound;
HRESULT hresult;
// Getting Safe Array's Lower and Upper Bounds
hresult = SafeArrayGetLBound(pSPFields, 1, &lBound);
hresult = SafeArrayGetUBound(pSPFields, 1, &uBound); variant_t vtParamVal;
_variant_t Index;
Index.vt = VT_I2;
Index.iVal = 1 ;
for (long iElements=lBound;iElements<=uBound;iElements++)
{
hresult = SafeArrayGetElement(pSPFields, &iElements, &vtParamVal);
pCmdPtr->GetParameters()->GetItem(Index)->PutValue(vtParamVal) ;
Index.iVal++ ;
} _variant_t vEffected ;
pCmdPtr->Execute(&vEffected,NULL,NULL);
// We Are Expecting That Stored Procedures Return ID for Entity to which
// NSERT/UPDATE/DELETE operation is being performed
return (long)pCmdPtr->Parameters->Item["RETURN_VALUE"]->Value ;
}
catch(_com_error &e)
{
ATLTRACE((LPCSTR)e.Description());
}
return 0;
}
good luckSteps To Reproduce Behavior
In the SQL Server 7.0 Query Analyzer select the test database Pubs.
Create the following stored procedure. This stored procedure returns a recordset and an out parameter count.if exists (select * from sysobjects where id = object_id(N'[dbo].[GetJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop proc GetJobs
go
create proc GetJobs @id as int, @count as int [out] as
begin
Select @count = Count(*) from jobs where job_id >@id
Select * from jobs where job_id >@id
end
go
Use VC App Wizard to create a new console application and modify the code as follows:#include "stdafx.h"
#include "stdio.h"
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2") struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_; int main(int argc, char* argv[])
{
_variant_t varErr((long)0, VT_ERROR);
_CommandPtr comm(__uuidof(Command));
_ConnectionPtr conn(__uuidof(Connection)); _bstr_t connstr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=(local)";
conn->Open(connstr, "", "", adConnectUnspecified);
comm->ActiveConnection=conn;
comm->CommandText="GetJobs";
comm->CommandType = adCmdStoredProc ;
comm->Parameters->Refresh();
_variant_t recs; comm->Parameters->Item[_variant_t((short)1)]->Value= _variant_t((long)5);
_RecordsetPtr rs = comm->Execute(&recs, &vtMissing,adCmdStoredProc); _variant_t recordcount= comm->Parameters->Item[_variant_t((short)2)]->Value; printf("recordcount = %li\n", (long)recordcount);
return 0;
}
Change the Datasource, User ID and password in the connection string above.
The recordcount variant that the above code returns is of type VT_NULL rather than the number of records that the stored procedure returns.
在视图(view)菜单里面,中文名字叫做"服务器资源管理器",在那里你可以建立一个数据库连接,然后直接把数据拖到form里面就可以,
by the way "服务器资源管理器"非常强大,你可以把他当作sql server来使用。可以在里面做很多事情,比如建表,视图,存储过程等等。
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
// 连接到本地计算机的eShops数据库
String strConn="Data Source=localhost;Integrated Security=SSPI;"+
"Initial Catalog=eShops";
SqlConnection cn=new SqlConnection(strConn);
// 打开连接
cn.Open();
// 显示连接状态
Message.Text=cn.State.ToString();
// 关闭连接
cn.Close();
}
</script>
<body>
<asp:Label id="Message" runat="server" />
</body>
</html>我改成了下面的
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
// 连接到本地计算机的pubs数据库
String strConn="Data Source=192.168.4.11/BETTER;UID=sa;PWD=rolima;"+
"Integrated Security=SSPI;"+"Initial Catalog=pubs";
SqlConnection cn=new SqlConnection(strConn);
// 打开连接
cn.Open();
// 显示连接状态
Message.Text=cn.State.ToString();
// 关闭连接
cn.Close();
}
</script>
<body>
<asp:Label id="Message" runat="server" />
</body>
</html>这样有错吗?
说执行到
cn.Open();就有错了,,
说找不到SQL服务或连接不到