Trouble using ADODB to call a stored proc

Dev Articles Community Forums Sponsor:

December 19th, 2011, 03:56 PM

confuseddotcom

Registered User

Join Date: Dec 2011

Posts: 2

Time spent in forums: 1 h 7 m 49 sec
Reputation Power: 0

Trouble using ADODB to call a stored proc

Hi,

I have wasted most of my afternoon trying to figure this out - apologies if it turns out to be trivial.

I am using the following code to call a stored proc:

c# Code:

Original - c# Code

private void butTestCode_Click(object sender, EventArgs e)
        {
            ADODB.Connection con;
            ADODB.Command com;
            ADODB.Recordset rst;
            ADODB.Parameter prm;

con = null;
            con = new ADODB.Connection();
            con.Provider = "SQLOLEDB.1";
            con.ConnectionString = "Description=MyDB;IntegratedSecurity=SSPI;Trusted_C  onnection=Yes;APP=MyApp;WSID=MyServer;DATABASE=MyD  B";
            con.Open(con.ConnectionString , null, null, 0);

com = null;
            com = new ADODB.Command();
            com.CommandText = "MyStoredProc";
            com.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
            com.NamedParameters = true;
            com.Prepared = false;
            com.Name = ("MyStoredProc" + System.DateTime.Now.ToString("yyyyMMddHHmmssffff"));

prm = null;

prm = com.CreateParameter("@LocationNameIn", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 80, Convert.ToString("London"));
            com.Parameters.Append(prm);
            prm.Value = Convert.ToString("London");
            prm = null;

prm = com.CreateParameter("@Time", DataTypeEnum.adDBTimeStamp, ParameterDirectionEnum.adParamInput, 0, Convert.ToDateTime("01/12/2011 00:20"));
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param3", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, 1000.0);
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param4", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, -999);
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param5", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, 245.9);
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param6", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, -999);
            com.Parameters.Append(prm);

prm = null;

prm = com.CreateParameter("@Param7", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 20, "South");
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param8", DataTypeEnum.adDouble , ParameterDirectionEnum.adParamInput, 0, 0);
            com.Parameters.Append(prm);
            prm = null;

prm = com.CreateParameter("@Param9", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 120, "BlahdiblahPC");
            com.Parameters.Append(prm);
            prm = null;

com.ActiveConnection = con;
            object oRA = new object();
            object oPRM = new object();
            rst = com.Execute(out oRA, ref oPRM, (int)ExecuteOptionEnum.adExecuteRecord);

}

private void butTestCode_Click(object sender, EventArgs e)
        {
            ADODB.Connection con;
            ADODB.Command com;
            ADODB.Recordset rst;
            ADODB.Parameter prm;
 
            con = null;
            con = new ADODB.Connection();
            con.Provider = "SQLOLEDB.1";
            con.ConnectionString = "Description=MyDB;IntegratedSecurity=SSPI;Trusted_C  onnection=Yes;APP=MyApp;WSID=MyServer;DATABASE=MyD  B";
            con.Open(con.ConnectionString , null, null, 0);
 
            com = null;
            com = new ADODB.Command();
            com.CommandText = "MyStoredProc";
            com.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
            com.NamedParameters = true;
            com.Prepared = false;
            com.Name = ("MyStoredProc" + System.DateTime.Now.ToString("yyyyMMddHHmmssffff"));
 
            prm = null;
 
            
            prm = com.CreateParameter("@LocationNameIn", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 80, Convert.ToString("London"));
            com.Parameters.Append(prm);
            prm.Value = Convert.ToString("London");
            prm = null;
 
             prm = com.CreateParameter("@Time", DataTypeEnum.adDBTimeStamp, ParameterDirectionEnum.adParamInput, 0, Convert.ToDateTime("01/12/2011 00:20"));
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param3", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, 1000.0);
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param4", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, -999);
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param5", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, 245.9);
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param6", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 0, -999);
            com.Parameters.Append(prm);
 
            prm = null;
 
            prm = com.CreateParameter("@Param7", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 20, "South");
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param8", DataTypeEnum.adDouble , ParameterDirectionEnum.adParamInput, 0, 0);
            com.Parameters.Append(prm);
            prm = null;
 
            prm = com.CreateParameter("@Param9", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 120, "BlahdiblahPC");
            com.Parameters.Append(prm);
            prm = null;
 
 
            com.ActiveConnection = con;
            object oRA = new object();
            object oPRM = new object();
            rst = com.Execute(out oRA, ref oPRM, (int)ExecuteOptionEnum.adExecuteRecord);
 
        }

When i do a stack trace on the database i find the following is being submitted:

Quote:

exec MyStoredProc @LocationNameIn='System.Object',@Time='2011-12-01 00:20:00',@Param3=1000,@Param4=-999,@Param5=245.90000000000001,@Param6=-999,@Param7='South',@Param8=0,@Param9='BlahdiblahP C'

I can't figure out why it is inserting 'System.Object' instead of 'London'. I will be very grateful for any help received.

December 20th, 2011, 03:09 AM

confuseddotcom

Registered User

Join Date: Dec 2011

Posts: 2

Time spent in forums: 1 h 7 m 49 sec
Reputation Power: 0

it turns out i was overriding the first parameter in the line before command.execute. I added the following in it's place:

Code:

object prm = com.Parameters[0].Value;

November 7th, 2014, 11:17 PM

kathi

Registered User

Join Date: Nov 2014

Posts: 1

Time spent in forums: 7 m 42 sec
Reputation Power: 0

I am creating a scheduled service application.
One of the applications returns a message box when completed.
I need to simulate a user clicking ok or pressing enter or space to get rid of the message box
can anyone help us??????????????????????????

____________

Trouble using ADODB to call a stored proc

Developer Shed Advertisers and Affiliates