Click here to Skip to main content
15,845,436 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hey Guys,

I need some help passing my params to a stored Procedure, First heres my code:

C#
string connetionString = null;
      OdbcConnection connection;
      OdbcDataAdapter adapter;
      OdbcCommand command = new OdbcCommand();
      OdbcParameter param;
      OdbcParameter param2;
      OdbcParameter param3;
      DataSet ds = new DataSet();

      int i = 0;

      connetionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
      connection = new OdbcConnection(connetionString);

      connection.Open();
      command.Connection = connection;
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "p_GetUnitsDetail_p (?, ?, ?)";


      param = new OdbcParameter("@OrgId", "CBS");
      param.Direction = ParameterDirection.Input;
      param.DbType = DbType.String;
      command.Parameters.Add(param);

      param2 = new OdbcParameter("@ProductId", "AGR");
      param2.Direction = ParameterDirection.Input;
      param2.DbType = DbType.String;
      command.Parameters.Add(param2);

      param3 = new OdbcParameter("@MPAcc", "55315628");
      param3.Direction = ParameterDirection.Input;
      param3.DbType = DbType.String;
      command.Parameters.Add(param3);


      adapter = new OdbcDataAdapter(command);
      adapter.Fill(ds);

      for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
      {
          Label1.Text = (ds.Tables[0].Rows[i][0].ToString());
      }

      connection.Close();


It works fine when i run the code with the command like this:

command.CommandText = "p_GetUnitsDetail_p (CBS, AGR, 55315628)";


But when i try to specify params i get the error:

Incorrect syntax near '@0001'

Please help
Posted
Updated 15-Mar-12 2:49am
v2
Comments
ZurdoDev 15-Mar-12 7:54am    
What does your CommandText look like when using parameters?
man_in_marak 15-Mar-12 8:14am    
command.CommandText = "p_GetUnitsDetail_p (?, ?, ?)";

Sorry first time using odbc
ZurdoDev 15-Mar-12 8:36am    
I don't know about using ?, but try "p_GetUnitsDetail_p (@OrgId, @ProductId, @MPAcc)". That way it inserts the parameters properly.

Hi,

you are getting error because of incorrect parameter passing method.

if you would like to pass parameter you need to specify like

C#
if (parameters != default(IList<sqlparameter>) && parameters.Count > 0) 
   {
      parameters.ToList().ForEach(p =>
      {
      sqlCommand.Parameters.Add(p);
      });
   }
</sqlparameter>


Although above example is with SqlParameter but OdbcParameter have similar method and property.


Hope this will help you,

Thanks
-Amit.
 
Share this answer
 
Hi,

Thanks Amit, I used a different example and its working!

Here's the code:

OdbcConnection cn;
OdbcCommand cmd;
OdbcParameter prm;
OdbcParameter prm2;
OdbcParameter prm3;
OdbcDataReader dr;
string connetionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;

//Change the connection string to use your SQL Server.
cn = new OdbcConnection(connetionString);

//Use ODBC call syntax.
cmd = new OdbcCommand("{call p_GetUnitsDetail_p (?,?,?)}", cn);

prm = cmd.Parameters.Add("@OrgId", OdbcType.Char, 5);
prm.Value = "CBS";
prm2 = cmd.Parameters.Add("@ProductId", OdbcType.Char, 5);
prm2.Value = "AGR";
prm2 = cmd.Parameters.Add("@MPAcc", OdbcType.Char, 20);
prm2.Value = "55315628";

cn.Open();

dr = cmd.ExecuteReader();

//List each product.
while (dr.Read())
Label1.Text = dr.GetString(0);

//Clean up.
dr.Close();
cn.Close();
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900