none
Help with parameterized query RRS feed

  • Question

  •  

    Check out this exception:

     

    Unhandled Exception: Touchsource.Exceptions.DataProcessorException: An error ocurred while reporting event log entry data.  ---> System.Data.SqlClient.SqlException: Procedure or Function 'spInsertEvent' expects parameter '@EventLog', which was not supplied.

     

    Now, look at my  'spInsertEvent' T-SQL stored procedure:

     

    INSERT INTO EventErrors (EventLog, EventID, EntryType, Source, Msg, TimeWritten, DirectoryID)

    VALUES (@EventLog, @EventID, @EntryType, @Source, @Message, @TimeWritten, @DirectoryID)

     

     

    And finally, my C# code that throws the above exception:

     

    Code Snippet

    SqlCommand cmd = new SqlCommand("spInsertEvent", _con);

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

     

    foreach (EventLogEntry entry in entries)

    {

    cmd.Parameters.Add(new SqlParameter("@PropertyNumber", _propertyNumber));

    cmd.Parameters.Add(new SqlParameter("@DirectoryName", _directoryName));

    cmd.Parameters.Add(new SqlParameter("@EventID", entry.InstanceId));

    cmd.Parameters.Add(new SqlParameter("@Source", entry.Source));

    cmd.Parameters.Add(new SqlParameter("@Message", entry.Message));

    cmd.Parameters.Add(new SqlParameter("@TimeWritten", entry.TimeWritten));

    cmd.Parameters.Add(new SqlParameter("@EntryType",entry.EntryType.ToString()));

    cmd.Parameters.Add(new SqlParameter("@EventLog", entry.UserName));

     

    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();

    }

     

     

    I have clearly added the parameter "@EventLog" to the parameter collection and it still throw this error. I've cleaned and rebuilt this solution SEVERAL times. Still, no luck.

     

    It's really starting to irritate me at this point so if anyone can see an error in my code above, please do let me know. Maybe I've been staring at it too long.

    Friday, May 16, 2008 9:48 PM

All replies

  • I would take another approach.

    Populate the Parameters collection in one phase and set the Value parameter(s) in the second phase when the Command is reused.

    Saturday, May 17, 2008 3:20 AM
    Moderator
  • Ok - good idea. I'll give it a shot.

    Saturday, May 17, 2008 7:19 PM
  • I would make certain to assign default values to the parameters in the stored procedure in case you should happen to pass in a Null value.
    Monday, May 19, 2008 12:57 PM
  • Could you post header of your stored procedure (CREATE PROCEDURE part) with declared parameters? Could you also try to run SQL Profiler to see what is actually passed to SQL Server from the application?

     

    Tuesday, May 20, 2008 10:00 AM
    Moderator