Where clause not picking value, why?

Question

I am filtering records on basis of registrationNo like it should pick only record for '1002' but it doesn't, it picks all records, why ?

string connStr = ConfigurationManager.ConnectionStrings["ETT"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection sqlCon = new SqlConnection(connStr);
        SqlCommand sqlCom = new SqlCommand("Vehicleledger", sqlCon);
        sqlCom.Parameters.Add("@RegistrationNo", SqlDbType.VarChar);
        sqlCom.Parameters["@RegistrationNo"].Value = "1002";
        SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCom);
        DataSet ds = new DataSet();
        sqlDA.Fill(ds);

        try
        {
            sqlCon.Open();
            gvLiveTransaction.DataSource = ds;
            gvLiveTransaction.DataBind();
            sqlCom.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
            Response.Write("Error:" + exc.Message);
        }
        finally
        {
            sqlCon.Close();
        }
    }
}

Source Code:

<form id="form1" runat="server">
        <div>
         <asp:GridView ID="gvLiveTransaction" runat="server" DataKeyNames="RNo" 
             AutoGeneratedColumns="false" CellPadding="4" ForeColor="#333333" GridLines="None">
             <AlternatingRowStyle BackColor="White" />
             <EditRowStyle BackColor="#2461BF" />
             <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
             <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
             <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
             <RowStyle BackColor="#EFF3FB" />
             <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
             <SortedAscendingCellStyle BackColor="#F5F7FB" />
             <SortedAscendingHeaderStyle BackColor="#6D95E1" />
             <SortedDescendingCellStyle BackColor="#E9EBEF" />
             <SortedDescendingHeaderStyle BackColor="#4870BE" />
         </asp:GridView>
        </div>
        </form>

i am calling this store procedure, it works fine in sql server when i pass same value but not in asp.net , why ?

ALTER procedure [dbo].[Vehicleledger]
@RegistrationNo varchar(20)
as

begin

SELECT  dbo.Vehicle.RNo AS RegistrationNo, dbo.TransactionTable.DateOfTransaction, dbo.TransactionTable.RNo, dbo.EngineCapacity.EngineCapcity, 
               dbo.MainCategories.Name, dbo.Categories.Description, dbo.TransactionTable.NoOfMonth, dbo.TransactionTable.Amount
FROM  dbo.Categories INNER JOIN
               dbo.MainCategories ON dbo.Categories.MainCategory_ID = dbo.MainCategories.CatID INNER JOIN
               dbo.TransactionTable ON dbo.Categories.ID = dbo.TransactionTable.CategoryID INNER JOIN
               dbo.EngineCapacity ON dbo.TransactionTable.EngineCapacityID = dbo.EngineCapacity.ID INNER JOIN
               dbo.Vehicle ON dbo.TransactionTable.RNo = dbo.Vehicle.RNo
where dbo.Vehicle.RNo=@RegistrationNo
end

Do you get the correct results just running the stored procedure itself? — Ryan Gates
varchar is for non-unicode while c# string literals are unicode. have you ruled this out? — RadioSpace

Jian Huang Jian Huang · Accepted Answer · 2014-03-12T19:29:57

I usually add parameters like this. Could you try this in your code?

...
SqlParameter parameter = new SqlParameter("@RegistrationNo", SqlDbType.VarChar);
parameter.IsNullable = false;    
parameter.Size = 20;
parameter.Value = "1002";
sqlCom.Parameters.Add(parameter);
...

Where clause not picking value, why?

1 Answers