Call in stored procedure error with SQL server in C#

Question

Possible Duplicate:
Stored procedure return into DataSet in C# .Net

How can I call a stored procedure from my project using C#? I need to get the output of that stored procedure into a data table but I'm getting an error.

Code:

private string connectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=blablalbla;";
    private DataTable dtProductPrijsActueel = new DataTable();
    public DataTable productPrijsActueel()
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand comm = conn.CreateCommand();
        comm.CommandType = CommandType.StoredProcedure;
        comm.CommandText = "procSelectPrijsActueel";

        dtProductPrijsActueel.Load(comm.ExecuteReader());
        conn.Close();
        conn.Dispose();

        return dtProductPrijsActueel;
    }

Error:

ERROR : KEYWORD NOT SUPPORTED : 'PROVIDER'

The usual way, you just set the `SqlCommand` object's `CommandType` property to `StoredProcedure`. — user17753, Nov 28 '12 at 20:56
There are so much documentation around, you could try searching first, and then come back with specific code / questions when you have a problem. — Andrew, Nov 28 '12 at 20:57

score 1 · Answer 1 · answered Nov 28 '12 at 21:01

This is really something that should've just been googled, but the general code you're looking for looks something like this:

  string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];
  SqlConnection connection = new SqlConnection(connect);
  string spName = "TheSpName";

  SqlCommand spCmd = new SqlCommand(spName, connection);
  spCmd.CommandType = CommandType.StoredProcedure;

  spCmd.Parameters.Add("@SomeParam", SqlDbType.String).Value = "Some Parameter";

  connection.Open();

  var dataTableReader = spCmd.ExecuteReader();

Call in stored procedure error with SQL server in C#

1 Answers1