CODEWITHSUNDEEP
c#sql-serverwpfstored-procedures
Theresa Ferguson
Theresa Ferguson

Reputation: 111

Getting an error calling my stored procedure from c#

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.

Upvotes: 0

Views: 94

Answers (2)

Jacob Lambert
Jacob Lambert

Reputation: 7679

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.

Upvotes: 1

Matt Spinks
Matt Spinks

Reputation: 6698

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

Upvotes: 0

Related Questions