Getting an error calling my stored procedure from c#

Question

I have a stored procedure which works in SQL:

ALTER PROCEDURE [dbo].[sp_fetch_journal_search]
    -- Add the parameters for the stored procedure here
    @sJournalText nvarchar(250) OUT,
    @dJournalDate date OUT,
    @sJournalTime nvarchar(250) OUT,
    @sJournalImageName nvarchar(250) OUT,
    @sJournalWebsite nvarchar (250) OUT,
    @iJournalID int OUT,
    @iJournalEntryID int OUT,
    @sSearch nvarchar(250)
AS
BEGIN           
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select  @dJournalDate = journal_date, 
            @sJournalTime = journal_time, 
            @sJournalImageName = journal_text, 
            @sJournalImageName= image_name, 
            @sJournalWebsite = website,
            @sJournalText = journal_text,
            @iJournalEntryID = journal_entry_id,
            @iJournalID = journal_id 
    from journalview
    where journal_date like '%' + @sSearch + '%'
    or journal_time like '%' + @sSearch + '%'
    or journal_text like '%' + @sSearch + '%'
    or image_name like '%' + @sSearch + '%'
    or website like '%' + @sSearch + '%'
    group by journal_date, journal_time, journal_text, image_name, website, journal_id,journal_entry_id

When I call it from c# I get an error on the ExecuteReader command which says int is incompatible with date. I can't see what is wrong:

SqlCommand cmdFetchJournal = new SqlCommand();
cmdFetchJournal.Connection = ConnectData.connection;
cmdFetchJournal.CommandText = "sp_fetch_journal_search";
cmdFetchJournal.CommandType = CommandType.StoredProcedure;
cmdFetchJournal.Parameters.AddWithValue("sSearch", (sSearch != null ? sSearch : ""));
cmdFetchJournal.Parameters["sSearch"].Direction = ParameterDirection.Input;
cmdFetchJournal.Parameters.AddWithValue("sJournalText", SqlDbType.VarChar);
cmdFetchJournal.Parameters["sJournalText"].Direction = ParameterDirection.Output;
cmdFetchJournal.Parameters.AddWithValue("dJournalDate", SqlDbType.Date);
cmdFetchJournal.Parameters["dJournalDate"].Direction = ParameterDirection.Output;
cmdFetchJournal.Parameters.AddWithValue("sJournalTime", SqlDbType.VarChar);
cmdFetchJournal.Parameters["sJournalTime"].Direction = ParameterDirection.Output;
cmdFetchJournal.Parameters.AddWithValue("sJournalWebsite", SqlDbType.VarChar);
cmdFetchJournal.Parameters["sJournalWebsite"].Direction = ParameterDirection.Output;
cmdFetchJournal.Parameters.AddWithValue("sJournalImageName", SqlDbType.VarChar);
cmdFetchJournal.Parameters["sJournalImageName"].Direction = ParameterDirection.Output;
cmdFetchJournal.Parameters.AddWithValue("iJournalID", SqlDbType.Int);
cmdFetchJournal.Parameters["iJournalID"].Direction = ParameterDirection.Output
cmdFetchJournal.Parameters.AddWithValue("iJournalEntryID", SqlDbType.Int);
cmdFetchJournal.Parameters["iJournalEntryID"].Direction = ParameterDirection.Output;
SqlDataReader drFetchJournal = cmdFetchJournal.ExecuteReader();

All help much appreciated.

The field underneath is a Date type too. The line where I get the error is the ExecuteReader command above. — Theresa Ferguson, Feb 23 '17 at 14:47
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 23 '17 at 14:48
SQL has various `Date` types, are you trying to squash a `DateTime` into a `Date`? — Adam Houldsworth, Feb 23 '17 at 14:49
Something worth a read as well... http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ though you are calling a SP so not really relevant--but good to know for future use. — S3S, Feb 23 '17 at 15:07

Jacob Lambert · Accepted Answer · 2017-02-23T14:52:46.507

1

I just noticed that these are output, so you need to use the Add method instead of AddWithValue. I'm going to leave the remainder of this answer as reference.

The reason that you are having this issue is that you are not supplying the value for your Parameters.AddWithValue() call. So when you call

Parameters.AddWithValue("dJournalDate", SqlDbType.VarChar);

You are setting the value to SqlDbType.VarChar which is probably 1. If you change it to be

Parameters.AddWithValue("dJournalDate", yourDate);

You should be fine. This goes for all your other Parameters.AddWithValue() calls also.

edited Feb 23 '17 at 14:52

answered Feb 23 '17 at 14:45

Jacob Lambert

7,449
8
27
47

Your welcome. You are also going to have problems getting the data with the way you currently have it. Take a look at [this](http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp) question for the way to execute stored procedures `ExecuteNonQuery` and access output parameter values. – Jacob Lambert Feb 23 '17 at 14:55

score 0 · Answer 2 · answered Feb 23 '17 at 14:43

Your problem is this line:

cmdFetchJournal.Parameters.AddWithValue("dJournalDate", SqlDbType.VarChar);

dJournalDate is a date, but you are trying to pass in a VarChar. Change it to this:

cmdFetchJournal.Parameters.AddWithValue("dJournalDate", SqlDbType.Date);

Getting an error calling my stored procedure from c#

2 Answers2