I am working in Visual Studio 2017 in C# WinForms- The project is based on a conversion from VB6 to C#, but now I have encountered a error where it says I have not supplied a parameter. But as far as I can see it seems I have.

The error that I am experiencing is: Procedure or function 'uspExportGetMailinfoTest' expects parameter '@CUSTOMER', which was not supplied.

I do not know if it is because of my C# code or that I am missing something from SQL. I wonder if anyone could describe what I am doing wrong. I am also always open for any suggestions to simplify my code.

C#

SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
        public bool getMailInfo(string sCustomer, ref string sMailTo, ref string sSubject, ref string sMsg)
        {
            try
            {
                SqlDataAdapter commSql = null;

                DataSet ds = new DataSet();

                db.Open();

                string sSql = "uspExportGetMailinfoTest";
                
                SqlCommand cmd = new SqlCommand("uspExportGetMailinfoTest", db);
                
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@CUSTOMER", sCustomer);

                cmd.ExecuteNonQuery();
              
                commSql = new SqlDataAdapter(sSql, db);
                commSql.Fill(ds, "list");

                if (ds.Tables["list"].Rows.Count > 0)
                {
                    cmd.Parameters.AddWithValue("mailto", sMailTo);

                    cmd.Parameters.AddWithValue("subject", sSubject);

                    cmd.ExecuteNonQuery();
                    db.Close();
                }
                else
                {

                    sMsg = "getMailInfo returns blank";
                    db.Close();
                }

                return true;


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }

This stored procedure is recovering a "Customer key" from another store proc. Depending on which key it gets, it then decides which customer will get the mail. SP

USE [Flextracker]
GO
/****** Object:  StoredProcedure [dbo].[uspExportGetMailinfoTest]    Script Date: 2021-11-09 10:34:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspExportGetMailinfoTest] 
@CUSTOMER as nvarchar(50)

AS

if @CUSTOMER in ('200600','200600-SEK','200600-USD','200602','200603','200604','200606') --2021-01-19 added 200606 --added ,'200603','200604' 20017-04-28
BEGIN
        select '\\XYXYX\MansTest' as mailto,'DELIVERY TO XYXYX' as subject                  
END

CodePudding user response：

If you want to use a data adapter with parameters, you need to pass it a SqlCommand object with the parameters in it.

There are a huge bunch of other issues with your code:

• All Sql objects need to be in using blocks
• Do not cache the connection object, create when you need, dispose with using. Note that if you have a using block there is no need to call Close()
• You are calling ExecuteNonQuery (multiple times), which doesn't return results
• A single row result will not come back in parameters, it's a resultset
• You could read that row from your DataSet, but it's actually much easier to just use a DataReader
• Don't use AddWithValue, specify parameter types and lengths explicitly
• Don't block with a MessageBox while the connection is open.

public bool getMailInfo(string sCustomer, ref string sMailTo, ref string sSubject, ref string sMsg)
{
    try
    {
        using (SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand("uspExportGetMailinfoTest", db) {CommandType = CommandType.StoredProcedure})
        {
            cmd.Parameters.Add("@CUSTOMER", SqlDbType.NVarChar, 50).Value = sCustomer;
            db.Open();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    sMailTo = (string)reader["mailto"];
                    sSubject = (string)reader["subject"];
                }
                else
                {
                    sMsg = "getMailInfo returns blank";
                }

                return true;
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        throw;
    }
}

For a multiple rows, you would use while (reader.Read())

For a single row, single column result, you can remove the reader and use cmd.ExecuteScalar()

CodePudding user response：

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value. Use instead:

    cmd.Parameters.Add("@CUSTOMER", SqlDbType.NVarChar, 50);
    cmd.Parameters["@CUSTOMER"].Value = sCustomer;