Stored Procedures not found

[Stropher]

I just want to call a stored procedure while working with Visual c#
2005 Express Edition and SQL Server
Here is my PressButton method and the subsequent method:

//pick up the date from the user
DateTime = this.dateTimeDatum.Value;

string str = dt.ToShortDateString();

//execute the stored procedure - it gets DateTime as parameter
string commandText = "Exec DBName..ProcessProcedure_Step1 '" +
DateTime.Parse(str) + "'";

string result = dal.ProcessProcedure(commandText);
MessageBox.Show(result);


My ProcessProcedure
int returnedRows = 0;
string result = "";
SqlCommand command = new SqlCommand();
command.Connection = this.connection;//Connection already instantiated
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = CommandText;
command.CommandTimeout = 5000;
try
{
connection.Open();
returnedRows = command.ExecuteNonQuery();
result = "Execution successful...";
connection.Close();
}
catch (SqlException ex)
{
result = " SQLException: " + ex.Message;
}

Immediately it commes to command.ExecuteNonQuery(), it jumps into the
catch exception area with the message - Stored Procedure not found...

Is there any tricks I am missing?

Thanks in advance

 

[Ian Frawley]

SqlCommand command = new
SqlCommand("DBName..ProcessProcedure_Step1",this.connection);
try
{
command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();
returnedRows = command.ExecuteNonQuery();
result = "Execution successful...";
}
catch (SqlException ex)
{
result = " SQLException: " + ex.Message;
}
finally
{
if(command .Connection.State == ConnectionState.Open)
command .Connection.Close();
}

 

[Ian Frawley]

sorry no need to ref the db either in the command text

SqlCommand("ProcessProcedure_Step1",this.connection);
try
{
command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();
returnedRows = command.ExecuteNonQuery();
result = "Execution successful...";
}
catch (SqlException ex)
{
result = " SQLException: " + ex.Message;
}
finally
{
if(command .Connection.State == ConnectionState.Open)
command .Connection.Close();
}

 

[Gav]

Include the date as a parameter:

command.Parameters.Add("@Parametername", DateTime.Parse(str));

 

[Ian Frawley]

heh heh heh missed his parameter, I'll get me coat.

in message

 

[Stropher]

Thanks for your answers, but where do I have to place this?

Does the fact of the program not locating my stored procedure has to do
with the code or...?

The contributions do not seem to bring me further...

 

[Ian Frawley]

"Stropher" wrote in message

Well because you are passing an active connection to the DB that contains
the stored procedure you only have to set the command text to be the actual
name of the stored procedure.

What you was doing was setting the command text to "EXEC server.db.sp
@param" which aint needed

 

[Stropher]

Or sorry Gav and Ian, ...

with this line
command.Parameters.AddWithValue("@date", DateTime.Parse(commandText));
and that of Ian Frawley
it was able to run through....

thanks and best regards

 

[Stropher]

fine, good to know (at first it did not appear logical to me b/c I was
asking myself how it knows that it has to execute the statement).
Thanks very much...

one other question is, can I call more than one stored procedures
within a particular connection?

e.g.
...
command.Connection.Open();
returnedRows = command.ExecuteNonQuery(); //stored procedure for
drops a table
command.CommandText = CommandText2; //it gets another statement or
command text
noOfRows = command.ExecuteNonQuery();//create another table
........
Is it from programming technique okay so?

 

[Ian Frawley]

Yes you can, your connection just allows you to interact with the database
much in the same way as an ftp connection allows you too issue ftp commands
to a remote source. You can call as many or as little stored procedures as
you like. But remember don't keep your connection open un-necessarily.

Regards

Ian

 

[Stropher]

Much thanks...

Best regards,

Stropher