CODEWITHSUNDEEP
c#sql-server
shakeel ahmad
shakeel ahmad

Reputation: 149

Must declare the scalar variable "@s"

CmdString = "SELECT s.TotallBill - s.Advance - SUM(iph.Amount) AS RemainingBalance FROM SalesInvoice s INNER JOIN InstallmentPaymentHistory iph ON iph.SalesInvoiceId = s.SalesInvoiceID WHERE  [s.SalesInvoiceID] = @s.SalesInvoiceID";

try
{
    SqlCommand cmd = new SqlCommand(CmdString, con);
    cmd.Parameters.AddWithValue("@SalesInvoiceID", txtSIN2.Text);

    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
    {
        DataTable dt = new DataTable("SalesInvoice");
        sda.Fill(dt);

        foreach (DataRow dr in dt.Rows)
        {
            txtBalance2.Text = (dr["RemainingBalance"].ToString());
        }
    }
}
catch (Exception exp)
{
    MessageBox.Show(exp.ToString());
}

I get the error

Must declare the scalar variable "@s"

In a textbox I am taking the value of s.SalesInvoiceID. How can I fix the error? Please answer me the correct query

Upvotes: 0

Views: 378

Answers (1)

Mary
Mary

Reputation: 15081

Not too sure what the intent is but your Select statement is missing a Group by or Over clause to go with the Sum. Also, your parameter name in your Select must match the name you use in the Parameters.AddWithValue. Maybe something like this.

"SELECT 
s.SalesInvoideID
s.TotallBill, 
s.Advance, 
sum(iph.Amount) as RemainingBalance 
from SalesInvoice s 
inner join InstallmentPaymentHistory iph 
on 
s.SalesInvoiceID =iph.SalesInvoiceId
where 
[s.SalesInvoiceID]=@SalesInvoiceID"
Group By s.SalesInvoiceID;"

The join looked backwards to me. I am not the best at Transact SQL but I think Inner is really a Left join or just Join and s.SalesInvoiceID belongs on the left.

Upvotes: 1

Related Questions