1

I have been using a Stored Procedure created by our DB guy, who happens to be out of town for the next week. The SP used to work, but before he left, the DB guy edited the SP, causing my code to throw a server error: "Column name or number of supplied values does not match table definition". He claimed before he left the SP should mainly be the same, so I'm at a loss for why it's no longer matching.

Here is the c# code:

    System.Data.SqlClient.SqlCommand objCmd = new 
    System.Data.SqlClient.SqlCommand("dci.webDonorStatistics", objConn);
    objCmd.CommandTimeout = 950;
    objCmd.CommandType = System.Data.CommandType.StoredProcedure;
    objCmd.Parameters.Add(new 
    System.Data.SqlClient.SqlParameter("@FiscalYear", 2018));
    GridView1.DataSource = objCmd.ExecuteReader();
    GridView1.DataBind();

Here is the declaration of the SP:

   ALTER PROCEDURE [dci].[webDonorStatistics] @FiscalYear INT
   AS
   BEGIN
    --DECLARE @FiscalYear INT = 2018

   DECLARE
   @StartDate DATE = CONVERT(DATE, '01-OCT-' + CONVERT(CHAR(4), @FiscalYear - 1))
   , @EndDate DATE = DATEADD(DAY, -1, GETDATE())

   IF @EndDate >= CONVERT(DATE, '30-SEP-' + CONVERT(CHAR(4), @FiscalYear))
    SELECT @EndDate = CONVERT(DATE, '30-SEP-' + CONVERT(CHAR(4), @FiscalYear))
    ELSE
     SELECT @EndDate = DATEADD(DAY, -1, CONVERT(DATE, '01-' + DATENAME(MONTH, GETDATE()) + '-' + CONVERT(CHAR(4), YEAR(GETDATE()))))  -- End of previous month

    IF DATEDIFF(DAY, @StartDate, @EndDate) < 0
    SELECT @EndDate = GETDATE()

    BEGIN TRY
     DROP TABLE #webDonorStatistics
    END TRY
    BEGIN CATCH
    END CATCH

Any help would be greatly appreciated. Thanks in advance.

5
  • 3
    What happens if you try to run the sproc from SQL Server Management studio? Commented Aug 3, 2018 at 18:49
  • I've only ever seen that error message when a table structure was changed but some query still referenced an old column name, or tried to INSERT without the correct number of columns specified. Are you sure this is where the exception is thrown? Commented Aug 3, 2018 at 18:56
  • The really odd part is that, as far as I can see, there's no table reference outside of the try block. Are you sure you're talking to the right server & database? I would roll the sproc back to the previous version (surely, happily saved in source control) to fix your error, and compare the two versions. Commented Aug 3, 2018 at 19:01
  • There must be more to this procedure than what's shown. You have an unmatched BEGIN at the top of the code, your local variables aren't being used for anything, and your calling code is clearly expecting a result set. Commented Aug 3, 2018 at 19:25
  • The error was also occurring on Management studio, so it seemed to be a bug in the stored procedure. It's been corrected by our DB guy now. Thanks for all the help! Commented Aug 10, 2018 at 15:35

2 Answers 2

3

First step is to isolate your error. Is your error thrown from your application code or database code? If you follow 3Dave's suggestion, what do you get? Assuming you are pointed to the correct database server. Try running:

EXEC [dci].[webDonorStatistics] 2018

If the above call does not return any error, I would check the application code.

Sign up to request clarification or add additional context in comments.

1 Comment

Error was occurring in Management studio as well, so it was a bug in the stored procedure that our DB guy has now fixed. Thanks for the help!
0

Your Error indicates that the data which you are trying to insert into the table is not valid, You would have to right-click on the stored procedure and click on execute stored procedure where you can insert the parameter for the fiscal year, The execution should fail. Then create a copy of same stored procedure with a different name and change the data type for the fiscal year and see if that would fix the issue and give you the result when you execute the stored procedure. Also on the Stored Proc could which you have I dont see any insert commands if there are any try checking the data type of what you are trying to insert and the data type of what is present inside the table schema if that doesn't match then you can change accordingly .

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.