CODEWITHSUNDEEP
c#asp.netdatatable
HEEN
HEEN

Reputation: 4721

Not getting count for datatable object

I want to check if there exist any record in the query.

So what I tried is 

 DataTable dtmkeylength = new DataTable("select count(lease_no) from XXACL_PROSPECTIVE_DATA_SAVE where mkey = " + Request.QueryString["userid"].ToString() + "");

        if (dtmkeylength.Rows.Count > 0)
        {
            HidMode.Value = "M";
            HidMKey.Value = dtmkeylength.Rows[0]["Mkey"].ToString();
        }

The below datatable object has count of 2 records into the database, but still it is not going inside IF condition.

WHY ?

Upvotes: 1

Views: 607

Answers (5)

vpv
vpv

Reputation: 938

Try this: 

static void Main(string[] args)
        {
            string param = "VINET";//your param here
            string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            using(SqlConnection  conn = new SqlConnection(conStr))
            {
                conn.Open();
                //modify your command on below line
                SqlCommand cmd = new SqlCommand("select count(OrderId) from Orders where CustomerID='" + param + "'");
                cmd.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                if(ds.Tables[0].Rows.Count > 0)
                {
                    //do other staff
                }
            }
        }

*Please follow commented lines

Edit
Below is a debug screen shot of your way of populating data table. As you can see, dt is detecting the command as table name and NOT as command.
enter image description here

Upvotes: 1

sameh.q
sameh.q

Reputation: 1709

There is no constructor for DataTable that takes a select statement, thats why it is empty, nothing selected yet!

The available constructor overloads available are the below three:

System.Data.DataTable dtmkeylength = new System.Data.DataTable();

Or

System.Data.DataTable dtmkeylength = new System.Data.DataTable("TableName);

Or

System.Data.DataTable dtmkeylength = new System.Data.DataTable("TableName", "tableNameSpace");

Check this and this example of how to use a DataTable

Upvotes: 0

Eminem
Eminem

Reputation: 7484

It WILL always return rows even if the row count is zero.

try

int number = dtmkeylength.Rows[0].Field<int>(0);

if (number > 0)
{
...
}

Upvotes: 0

Pedro G. Dias
Pedro G. Dias

Reputation: 3222

in your query add the "as NNN" thing: 

...select count(lease_no) as result...

so you can reference it by name.

Then, when you query, you can type: 

dtmkeylength.Rows[0]["result"]

I hope that fixes it for you :)

EDIT

var userId = Request.QueryString["UserId"];

if(string.IsNullOrEmpty(userId)){
  throw new Exception("No UserID = no fun!");
}

DataTable dtmkeylength = new DataTable("select count(lease_no) from XXACL_PROSPECTIVE_DATA_SAVE where mkey =" + Request.QueryString["userid"].ToString() + "");

        if (dtmkeylength.Rows.Count > 0)
        {
            HidMode.Value = "M";
            HidMKey.Value = dtmkeylength.Rows[0][0].ToString();
        }

Upvotes: 2

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I think a better approach is to use ExecuteScalar, since you are using only the count.

using (SqlConnection conn = new SqlConnection(connString))
{
    String sql = "select count(lease_no) from XXACL_PROSPECTIVE_DATA_SAVE where mkey = @mkey";
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.Parameters.Add("@mkey", (int) Request.QueryString["userid"]);

    try
    {
        conn.Open();
        int rowCount = (int) cmd.ExecuteScalar();

        if (rowCount  > 0)
        {
            HidMode.Value = "M";
            HidMKey.Value = dtmkeylength.Rows[0]["Mkey"].ToString();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Also note the parameterized query - @mkey is provided as a parameter and string concatenation is not used (this may lead to Sql Injection)

Upvotes: 1

Related Questions