CODEWITHSUNDEEP
c#sqlt-sqldatatable
dovla110010101
dovla110010101

Reputation: 431

Datatable always return second value

I found some strange behavior for which I haven't found a solution yet. when I debug SQL code, it returns correct value, but somehow in C# code, datatable returns wrong result.

Due to fact that this is billing application and it has sensitive information, I cannot publish whole code, only method which contains problematic code and SQL stored procedure which returns result.

SQL:

ALTER PROCEDURE [dbo].[conf_SetRationCurrency] 
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @Date AS VARCHAR(10),
      @Today AS VARCHAR(10)

SELECT @Date = CONVERT(VARCHAR(10),[date], 112) FROM RatioCurrency
SELECT @Today = CONVERT(VARCHAR(10),GETDATE(),112)

BEGIN TRAN
BEGIN TRY
   IF @Date <> @Today
   BEGIN
      -- INSERT
      INSERT INTO dbo.RatioCurrency 
      SELECT rateType, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS SMALLDATETIME) AS [date],fromCurrency,toCurrency,value 
      FROM dbo.RatioCurrency where [date] = 
      (
      SELECT TOP 1 CAST(CONVERT(VARCHAR, [date], 112) AS SMALLDATETIME) AS [date] 
      FROM dbo.RatioCurrency 
      ORDER BY ID DESC
      )       
      SELECT 'Yes' AS 'INSERTED' -- return flag for setting output text in code
   END
   ELSE
   BEGIN
      SELECT 'No' AS 'NOT' -- return flag for setting output text in code 
   END
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0
      ROLLBACK TRAN
      EXEC dbo.intf_ShowError
      RETURN
END CATCH
COMMIT
END

C#: 

     DialogResult dialog = MessageBox.Show("Da li želite kopirati valutu zadnjeg unosa za današnji dan...?","Upit",MessageBoxButtons.YesNo, MessageBoxIcon.Question);
        if (dialog == DialogResult.Yes)
        {
            DataTable dt = Program.ExecProc("conf_SetRationCurrency");

            if (dt.Rows.Count > 0 && dt.Columns[0].ColumnName == "ErrorNumber")
            {
                Program.ShowDataInfoError(dt.Rows[0]); //print error message
            }
            else if (dt.Rows.Count > 0 && dt.Columns[0].ColumnName == "NOT") //part which should only give info that value is already in the database, no need to insert new one. 
            {
                SplashForm sf = new SplashForm();
                sf.informationText.Text = "Valuta već postoji.";
                sf.Show();
                GetCurrency();
                this.Refresh();
            }
            else // this part actually gives info that new values are inserted
            {
                SplashForm sf = new SplashForm();
                sf.informationText.Text = "Valuta je upisana.";
                sf.Show();
                GetCurrency();
                this.Refresh();
            }
        }
        else
        {
            return;
        }

Even though SQL is returning correct colum name value, datatable is returning column name - "NOT" (a second one) and I mean always... This cannot be true since I have if/else statement and program should avoid second statemement. How the hell is datatable object holding wrong column name? is this a bug or since I'm using alias for columns so it uses last one...? Does anyone have an answer?

Upvotes: 0

Views: 369

Answers (2)

Tian van Heerden
Tian van Heerden

Reputation: 639

ADO.NET is caching the DataTable structure (column names & types) returned from the stored procedure. It is not a good idea to change the table structure based on business logic - rather vary the data than the structure. I suggest you change the sp so that it always returns the same column name, e.g. "Status" but the value changes depending on your business logic.

--SQL logic
if (errorCondition)
  SELECT 'ERROR' as Status, 'Error message' as Message
if @Date <> @Today
   SELECT 'Yes' AS Status, '' AS Message
else
   SELECT 'No' AS Status, '' as Message

Then your C# logic becomes simpler in that:

  • there is always one row of data returned with a fixed number of columns and with fixed names
  • you can use the values in the single row returned for your business logic

Upvotes: 1

evilive
evilive

Reputation: 996

Once had the same problem, though not in C#:

The cause was that by accessing the query-result by php the pointer was allready moved, so the first result was always skipped.

Sounds like the same kind of Problem, might be the rowcount.

(sorry can't just comment)

Upvotes: 1

Related Questions