Help Please -- ADODB.Command does not work correctly in .NET

[Ivan]

Hi all,
i have been trying this for 7 hrs now !!!

How do you call a adodb.command object in .net(c#)

code******
ADODB.Connection cn = new ADODB.ConnectionClass();
ADODB.Command cm = new ADODB.CommandClass();
ADODB.Parameter pa = new ADODB.ParameterClass();
ADODB.Recordset rs = new ADODB.RecordsetClass();
object rc;

cn.Provider="SQLOLEDB.1";
cn.Open(ConfigurationSettings.AppSettings["sqlServerConnection"].ToString(),"","",-1);

cm.ActiveConnection=cn;
cm.CommandType=ADODB.CommandTypeEnum.adCmdStoredProc;
cm.CommandText="sp_rptRegister";

pa=cm.CreateParameter("@Id",ADODB.DataTypeEnum.adInteger,ADODB.ParameterDirectionEnum.adParamInput,0,506);
cm.Parameters.Append(pa);

object par= cm.Parameters;

rs=cm.Execute(out rc,ref
par,(int)ADODB.CommandTypeEnum.adCmdStoredProc);

sXML=rs.Fields[0].Value.ToString();

code******

The last line gives an error that it is BOF AND EOF (when there are
records!)
(it give the field names but not the records, i suspect the parameter
value is not getting passed to the sp (and yes i have tried passing
the value pa[0].value=506 in a seperate statement))

I have tried using the CommandText="sp_rptRegister 506" and it works
but i need to use the para collection.

Please Please somebody help me.....i need a solution by tonite

Thanks in advance

Ivan

 

[oj]

Be sure to put 'set nocount on' as the first statement in your stored procedure.
This prevents 'DONE_IN_PROC' messages from returning as an empty recordset.

Also, you're working with .NET why not use ADO.NET, specifically
System.Data.SqlClient namespace.
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClient.asp?frame=true

--
-oj
http://www.rac4sql.net

Hi all,
i have been trying this for 7 hrs now !!!

How do you call a adodb.command object in .net(c#)

code******
ADODB.Connection cn = new ADODB.ConnectionClass();
ADODB.Command cm = new ADODB.CommandClass();
ADODB.Parameter pa = new ADODB.ParameterClass();
ADODB.Recordset rs = new ADODB.RecordsetClass();
object rc;

cn.Provider="SQLOLEDB.1";
cn.Open(ConfigurationSettings.AppSettings["sqlServerConnection"].ToString(),"","
",-1);

cm.ActiveConnection=cn;
cm.CommandType=ADODB.CommandTypeEnum.adCmdStoredProc;
cm.CommandText="sp_rptRegister";

pa=cm.CreateParameter("@Id",ADODB.DataTypeEnum.adInteger,ADODB.ParameterDirectio
nEnum.adParamInput,0,506);
cm.Parameters.Append(pa);

object par= cm.Parameters;

rs=cm.Execute(out rc,ref
par,(int)ADODB.CommandTypeEnum.adCmdStoredProc);

sXML=rs.Fields[0].Value.ToString();

code******

The last line gives an error that it is BOF AND EOF (when there are
records!)
(it give the field names but not the records, i suspect the parameter
value is not getting passed to the sp (and yes i have tried passing
the value pa[0].value=506 in a seperate statement))

I have tried using the CommandText="sp_rptRegister 506" and it works
but i need to use the para collection.

Please Please somebody help me.....i need a solution by tonite

Thanks in advance

Ivan

 

[Ivan Fernandes]

I have tried useing SET NOCOUNT ON at the begining and at the end on the
stored proc. but it still returns a empty recordset.

I need to use ADODB as i call an external vb object thats requires a
ADODB.recordset as a paramater.

I know it works very well with ADO.net(use it else where in the project)
but i cannot use it. The other option is to open a ADO.net recordset
save it to file and reopen it using ADODB but is that what programming
in microsoft's own domain has come to !

Ivan

 

[Val Mazur]

Hi Ivan,

I think OJ is right. SET NOCOUNT could help and he is also right that there
is no reason to use COM-based ADO in .NET applications. Use ADO.NET instead.