none
C# - StoredProcedure - SqlDbType.Bit RRS feed

  • Question

  • Hello,
    I need to call a StoredProcedure. The connection all good
    The transfer value is of data type bit.

    Problem is that deleting the records does not work and I don't know why.

    not deleted, why

    I suspect I'm passing the value bit wrong. How do I do it right?
    Do I see that right?
    @ProductID is the variable from the stored procedure. (I make it BOLD)
    	
    ProductID is the table name.

    Right?

    CREATE PROCEDURE DeletePRODUCT_ID (@ProductID as nvarchar(20),
    						   @Result as bit)
    
    AS
    BEGIN
    
    -- Execute following SQL code 
    
    If @result = 1	
        Delete 
        FROM 
            [MAC].[dbo].[TABLEPRODUCTION]
    	Where 
    		ProductID = @ProductID
    
    If @result = 0
    	UPDATE 
    		[MAC].[dbo].[TABLEPRODUCTION]
    	SET
    		Reserved = 0
    	WHERE
    		ProductID = @ProductID
    
    END;
    using (SqlCommand cmd = new SqlCommand(StoredProcedureDeleteMac, SkovDB))
    {
    	cmd.CommandType = CommandType.StoredProcedure;
    	SqlParameter RetVal = cmd.Parameters.Add("@return_value", SqlDbType.Int);
    	RetVal.Direction = ParameterDirection.ReturnValue;
    
    	SqlParameter IdInMacAdress = cmd.Parameters.Add("@ProductID", SqlDbType.Char, 50);
    	IdInMacAdress.Direction = ParameterDirection.Input;
    	IdInMacAdress.Value = txtMacAddress.Text;
    
    	SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit, 50);
    	IdInResult.Direction = ParameterDirection.Input;
    	IdInResult.Value = 1; // true; // Convert.ToBoolean(txtState.Text);
    
    	txtReturnValue.Text = "";
    	int counter = 0;
    
    	cmd.ExecuteNonQuery();
    	var result = RetVal.Value;

    Thanks in advance for your help.

    Greetings  Markus


    Friday, March 13, 2020 4:39 PM

Answers

  • The parameter looks good, except that it shouldn't have a length of 50. Just declare it without specifying the length:

    SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit);

    However, this should not be reason enough for it to fail.

    Can you run the SQL Profiler against SQL Server? Capture the statement that the program is sending, and try to run it from SSMS (if there is no obvious problem with it). This should help us debug the issue.

    Be careful with the productId. You are passing a char but the SP declares it as nvarchar. There could be a problem in the implicit conversion (filling blanks to 50).

    Also keep in mind that the procedure does not have any Return statement, so your "RetVal" parameter will always return DBNull.



    Friday, March 13, 2020 4:49 PM
    Moderator
  • I see two problems in this code which I'm going to correct:

    SqlParameter IdInMacAdress = cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20);
    	IdInMacAdress.Direction = ParameterDirection.Input;
    	IdInMacAdress.Value = txtMacAddress.Text; // Why the parameter called Product Id but you're passing Mac Address ??
    
    	SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit);
    	IdInResult.Direction = ParameterDirection.Input;
    	IdInResult.Value = true; // Bit is true/false

    Also, did you check how the column ProductId is defined in your table? The parameter definition in both C# and stored procedure should match what the column type and size in the table is.

    Also, the stored procedure name doesn't match the purpose of the procedure. I would probably remove the second parameter, create 2 separate procedures (one for delete, one for update) and call it depending on your choice from C#. The code will be cleaner and also execute better.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 13, 2020 5:03 PM
    Moderator
  • Bit in SQL Server corresponds to Boolean in C#. That's why it's unclear as why we're using text column and why such strange code in the stored procedure too.

    See this link for SQL vs C# types:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, March 15, 2020 1:27 AM
    Moderator

All replies

  • The parameter looks good, except that it shouldn't have a length of 50. Just declare it without specifying the length:

    SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit);

    However, this should not be reason enough for it to fail.

    Can you run the SQL Profiler against SQL Server? Capture the statement that the program is sending, and try to run it from SSMS (if there is no obvious problem with it). This should help us debug the issue.

    Be careful with the productId. You are passing a char but the SP declares it as nvarchar. There could be a problem in the implicit conversion (filling blanks to 50).

    Also keep in mind that the procedure does not have any Return statement, so your "RetVal" parameter will always return DBNull.



    Friday, March 13, 2020 4:49 PM
    Moderator
  • I see two problems in this code which I'm going to correct:

    SqlParameter IdInMacAdress = cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20);
    	IdInMacAdress.Direction = ParameterDirection.Input;
    	IdInMacAdress.Value = txtMacAddress.Text; // Why the parameter called Product Id but you're passing Mac Address ??
    
    	SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit);
    	IdInResult.Direction = ParameterDirection.Input;
    	IdInResult.Value = true; // Bit is true/false

    Also, did you check how the column ProductId is defined in your table? The parameter definition in both C# and stored procedure should match what the column type and size in the table is.

    Also, the stored procedure name doesn't match the purpose of the procedure. I would probably remove the second parameter, create 2 separate procedures (one for delete, one for update) and call it depending on your choice from C#. The code will be cleaner and also execute better.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 13, 2020 5:03 PM
    Moderator
  • Thanks for the quick response.

    SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit, 50);
    IdInResult.Direction = ParameterDirection.Input;
    IdInResult.Value = txtState.Text;

     I use WPF and C#. The state should be a Bit, but txtState.Text is a string.

    How I can convert it?

    Greetings Markus

    Friday, March 13, 2020 5:18 PM
  • First of all, as we suggested, remove size from the declaration of the Bit parameter.

    Secondly, what are the values you receive in your text field and why this field is a text if it's supposed to be true/false state?

    If you're receiving, say, 1/0 as a text, then just do it using 

    if (txtState.text=="1") {

       myParam.Value = true;

    }

    else {

       myParam.Value = false;

    }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, March 13, 2020 5:34 PM
    Moderator
  • First of all, as we suggested, remove size from the declaration of the Bit parameter.

    Secondly, what are the values you receive in your text field and why this field is a text if it's supposed to be true/false state?

    Hello Naomi,

    SqlParameter IdInResult = cmd.Parameters.Add("@Result", SqlDbType.Bit);
    IdInResult.Direction = ParameterDirection.Input;
    IdInResult.Value = true; //txtState.Text;

    You mean this way, right.  Is true also right for data type bit?

    OK, is my user interface for test  I take a textfield. 

    I have received the procedure from the customer and am now checking its function.

    f (txtState.text=="1") {
       myParam.Value = true;
    }

    You take boolean here, I need bit, I can't change that. Is the customer's specification.

    I can test it at the customer on Tuesday and let you know. For the user interface it might be better to use a checkbox or a combo box.

    Greetings Markus




    Saturday, March 14, 2020 11:43 AM
  • Bit in SQL Server corresponds to Boolean in C#. That's why it's unclear as why we're using text column and why such strange code in the stored procedure too.

    See this link for SQL vs C# types:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, March 15, 2020 1:27 AM
    Moderator
  • Works well, thanks.
    Tuesday, March 17, 2020 9:21 AM