Not getting count for datatable object

Question

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 ?

Seems like this needs a debug, try to debug the `dtmkeylength` instance and see if there is actually any data retrieved, I suspect that the select statement didn't return any rows — sameh.q, Mar 29 '16 at 05:16
By calling `new DataTable("select count(lease_no) from XXACL_PROSPECTIVE_DATA_SAVE where mkey = " + Request.QueryString["userid"].ToString() + "");` you just create `DataTable` with name `select count(lease_no) from XXACL_PROSPECTIVE_DATA_SAVE where mkey = + Request.QueryString["userid"].ToString();` — SᴇM, Mar 29 '16 at 05:17
@SeM: so what shd I do to get the record in datatable object ? — Nad, Mar 29 '16 at 05:17
read [this](http://www.dotnetperls.com/sqlcommandbuilder) article — SᴇM, Mar 29 '16 at 05:18

Pedro G. Dias · Accepted Answer · 2016-03-29T05:28:52.113

2

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();
        }

edited Mar 29 '16 at 05:28

answered Mar 29 '16 at 05:16

Pedro G. Dias

3,162
1
18
30

I think `dtmkeylength.Rows.Count` should contain something, regardless of the column alias you suggested, right? – sameh.q Mar 29 '16 at 05:17
Row count should be 1, since he's selecting a count without grouping, so yeah, that's right. I guess he could also just address column 0 instead of using the column alias. Not as verbose, but still does the trick – Pedro G. Dias Mar 29 '16 at 05:18
Yes, you are correct, it should contain 1 row with value of 0 at least – sameh.q Mar 29 '16 at 05:20
@PedroG.Dias: can you update your answer totally. As I am confused how to write it ? – Nad Mar 29 '16 at 05:22
Try this: HidMKey.Value = dtmkeylength.Rows[0][0].ToString(); – Pedro G. Dias Mar 29 '16 at 05:23
sorry, didn't got u. it is not related to getting record into the datatable. Kindly update your answer.Please – Nad Mar 29 '16 at 05:25
Well then it must be your query, check the userId against the table to see if it exists. Try executing the query in your favourite SQL editor. – Pedro G. Dias Mar 29 '16 at 05:48

score 1 · Answer 2 · answered Mar 29 '16 at 05:18

1

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)

answered Mar 29 '16 at 05:18

Alexei - check Codidact

22,016
16
145
164

how to pass mkey as a parameter ? as it takes when the user is logged – Nad Mar 29 '16 at 05:22
You can have an extra `if` condition to treat unlogged user case. What is your code supposed to do when the user is not logged in? – Alexei - check Codidact Mar 29 '16 at 05:29
it will redirect to an error page, if the user is not logged in. So can u update the code if user is unlogged... – Nad Mar 29 '16 at 05:34

vpv · Answer 3 · 2016-03-29T05:56:33.117

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.

thanks, but it would be great if you let me know in my question only. I have also created and it is working thier — Nad, Mar 29 '16 at 05:44
The way you are placing your select command in DataTable constructor, is not write because even though you are placing a command string, the constructor is detecting it as TABLE NAME (see DataTable's constructor overload). As a result, your datatable was never populated and as expected, it does not going inside IF condition — vpv, Mar 29 '16 at 05:50

Eminem · Answer 4 · 2016-03-29T05:29:45.097

0

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

try

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

if (number > 0)
{
...
}

edited Mar 29 '16 at 05:29

answered Mar 29 '16 at 05:26

Eminem

7,206
15
53
95

tried like this .`DataTable dtmkeylength = new DataTable("select count(lease_no) as result from XXACL_PROSPECTIVE_DATA_SAVE where mkey =" + Request.QueryString["userid"].ToString() + ""); int number = Convert.ToInt32(dtmkeylength.Rows[0]["result"]);` getting error as **There is no row at position 0.** – Nad Mar 29 '16 at 05:40

score 0 · Answer 5 · edited May 23 '17 at 11:45

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

Not getting count for datatable object

5 Answers5