OleDB Stored Procedure returns Data Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to Exceute a Stored Procedure with OleDB in C#
I've looked all over the net and can't seem to find how to do it and have
had minimal sucess: here is the sniplet of code i'm using:
#region Create TieWindow DataSet
DataSet dsTieWindow = new DataSet();
DataTable tieWindowTable = new DataTable();
tieWindowTable.TableName = "TieWindow";
tieWindowTable.Columns.Add("TieIndex");
tieWindowTable.Columns.Add("ReferenceEntity");
tieWindowTable.Columns.Add("ContainerName");
tieWindowTable.Columns.Add("TieName");
tieWindowTable.Columns.Add("StartTime");
//...... keep adding columns, etc
#endregion


OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;

//OleDB Parameter Objects to be used for all database modification
#region Create OleDb Parameters
OleDbParameter oMWin = new OleDbParameter("MWin", OleDbType.Double, 8,
"MWin");

OleDbParameter oMWout = new OleDbParameter("MWout", OleDbType.Double, 8,
"MWout");

OleDbParameter oStopTime = new OleDbParameter("StopTime", OleDbType.Date, 8,
"StopTime");
OleDbParameter oStartTime = new OleDbParameter("StartTime", OleDbType.Date,
8, "StartTime");
OleDbParameter oInterfaceType = new OleDbParameter("InterfaceType",
OleDbType.Integer, 4, "InterfaceType");
OleDbParameter oImportIntoSched = new OleDbParameter("ImportIntoSched ",
OleDbType.Integer, 4, "ImportIntoSched");

///....paramter, paramater, etc
#endregion

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "beta_operational.dbo.WS_tie_get_tie_window '" +
lowerBoundDate + "', '" + upperBoundDate + "','" + "PNM" + "', 0";


cmd.Parameters.Clear();
cmd.Parameters.Add(oTieIndex);
cmd.Parameters.Add(oReferenceEntity);
cmd.Parameters.Add(oContainerName);
cmd.Parameters.Add(oTieName);
cmd.Parameters.Add(oStartTime);
cmd.Parameters.Add(oStopTime);
//....paramter, parameter, etc


dsTieWindow.Tables.Add("TieWindow");
adapter.SelectCommand = cmd;
adapter.Fill(dsTieWindow);

===============================================

The Error I get is this:

OleDb Connection Error:System.Data.OleDb.OleDbException: Syntax error or
access violation at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at....

What am i doing wrong? Thanks in advance.
 
Nevermind I was able to solve it by tinkering arround long enough.
I had my table and columns pre-defined in the dataset which I took out so
just the DataSet object was created.

Further, the Command Text should only include the Stored Procedure Name and
no parameters.

Lastly, the parameters should be delclared using @Variable, then the value
set, and then the Direction set to ParameterDirection.Input

then after that it worked....so i've answer my own question haha
....hope this helps someone out.
 
1) cmd.CommandText should be set to the name of the stored procedure.
In this case
cmd.CommandText = "beta_operational.dbo.WS_tie_get_tie_window";
Click to expand...
is sufficient

2) As and when you create the command parameters objects (oMWin,
oStopTime, etc.), assign each the value you would like to send to the
stored procedure in that parameter. This is how you send parameters,
not in the command text

3) Add the parameter objects to the command's parameters collection in
the order they are defined by the stored procedure. (I'm not sure if
OleDb binds SQL Server parameters by name. I think it binds by
position)

Finally, you seem to be defining a DataTable (TieWindow) in the dataset
in anticipation that the data returned by the stored procedure will
fill this table. You don't have to do that. OleDbDataAdapter
automatically adds a DataTable for each result set returned by the
stored procedure. (they're named "Table", "Table1", "Table2", etc.)

Vamsi.
 
Back
Top