SQL command parameter Must declare variable error

I have a datatable method that returns information, but I have a problem with my parameters it keeps emitting . Must declare @SUPartnerNo variable . i tried google but none of the solutions i found helped. and I don't want to inline my parameters directly into my query as this is not a good practice.

public DataTable SearchInvoiceHeader(string SUPartnerNo, string invoiceNo, string bdate, string edate)
{
    DataTable dt = new DataTable();

    try
    {
        StringBuilder mySelectQuery = new StringBuilder(9000);


        mySelectQuery.AppendLine(@"SELECT I.[InvoiceNo]
                                    ,I.[SUPartnerNo]
                                    ,I.[OrderNo]
                                    ,I.[RefNo]
                                    ,I.[DocType]
                                    ,I.[SAP_Type]
                                    ,I.[SUName]
                                    ,I.[InvoiceDate]
                                    ,I.[PaymentDate]
                                    ,I.[BaseLineDate]
                                    ,I.[DeliveryDate]
                                    ,I.[DeliveryNoteNo]
                                    ,I.[TotalAmount]
                                    ,I.[StartTime]
                                    ,p.ProcessType
                                    ,s.DocDate
                                    ,s.IDocNo
                                    ,s.TotalValue
                                FROM [dbo].[mainhead] I WITH (NOLOCK) 
                                LEFT JOIN [INV_Processed] p WITH (NOLOCK) 
                                ON p.InvoiceNo = I.InvoiceNo
                                AND p.PartnerNo = I.SUPartnerNo
                                LEFT JOIN dbo.INV_SAP s WITH (NOLOCK) 
                                ON s.InvoiceNo = I.InvoiceNo
                                AND s.SupplierNo = I.SUPartnerNo
                                WHERE
                                (I.SUPartnerNo =@SUPartnerNo) OR  (I.InvoiceNo = @InvoiceNo) 
                                and I.[StartTime] between  @bdate and @edate");
        SqlConnection myConnection;

        myConnection = new SqlConnection(ConnectionString);

        SqlCommand myCommand = new SqlCommand(mySelectQuery.ToString());

        myCommand.Parameters.AddWithValue("@SUPartnerNo", SUPartnerNo);
        myCommand.Parameters.AddWithValue("@InvoiceNo", invoiceNo);
        myCommand.Parameters.AddWithValue("@bdate", bdate);
        myCommand.Parameters.AddWithValue("@edate", edate);
        myCommand.Connection = myConnection;
        myConnection.Open();

        SqlDataAdapter mytable = new SqlDataAdapter(mySelectQuery.ToString(), myConnection);
        myCommand.ExecuteNonQuery();
        mytable.Fill(dt);


        myConnection.Close();

      

+3


source to share


1 answer


You are adding a parameter to the wrong command. The object myCommand

being created is not used by the method Fill

. Instead, add the parameter to SqlDataAdapter

:

mytable.SelectCommand.Parameters.AddWithValue(...)

      



You can omit everything related to myCommand

, including the call, into your code ExecuteNonQuery()

.

+6


source







All Articles