Odbc stored procedure Parameters, can't find Syntac error?

Question

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 15-Mar-12 1:45am

man_in_marak

Updated 15-Mar-12 1:49am

sravani.v

v2

Add a Solution

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.

2 solutions

Solution 2

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.

Posted 15-Mar-12 2:30am

AmitGajjar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

man_in_marak · Accepted Answer · 2012-03-15T03:14:00

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();