How to return a string value from datatable in c#?

Question

I am getting unable to store datatable value in string . How to return a string value of a data table? It gives an error while returning scalar value.

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code-- error in return statement

txtSalespersonName.Text = dl.GetStringValue("select top 1 [ContactPerson]  from tbl_Companies where Company='" +companyname + "' order by id desc");
public string GetStringValue(string query)
{
    DataTable dt = new DataTable();

    try
    {

        string constr = ConfigurationManager.ConnectionStrings["KernelCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }

        }

    }
    catch (Exception ex)
    {

      //  Response.Write(ex.Message);

    }
    return dt.Rows[1].ToString();
}

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code-- error in return statement — krishna mohan
– krishna mohan, Commented Feb 25, 2016 at 11:23
shouldn't it be dt.Rows[0] because it is the first element ? — MrFabio
– MrFabio, Commented Feb 25, 2016 at 11:26
ya i changed that . im getting value like this - "System.Data.DataRow" — krishna mohan
– krishna mohan, Commented Feb 25, 2016 at 11:27
Correct, then you need to access the column to get the data dt.Rows[0][0].ToString() or dt.Rows[0]["Name"].ToString() — MrFabio
– MrFabio, Commented Feb 25, 2016 at 11:31

Rahul Singh · Accepted Answer · 2016-02-25 11:28:08Z

1

To answer your question, error is occurring because DataTable is zero indexed based and your query is returning just 1 value thus it is out of range. dt.Rows[0][0].ToString() will give you correct output.

But Since you are returning a single value from the query, I would suggest you to use ExecuteScalar instead like this:-

string ContactPerson = String.Empty;
string constr = ConfigurationManager.ConnectionStrings["KernelCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand(query,con))
    {
        ContactPerson = cmd.ExecuteScalar().ToString();
    }
}
return ContactPerson;

Also, use parametrized query to avoid SQL Injection attack.

answered Feb 25, 2016 at 11:28

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Soner Gönül · Accepted Answer · 2016-02-25 11:41:14Z

1

dt.Rows[1] returns the second DataRow in your table which is not exist since your query returns only one row. You can use it as;

return dt.Rows[0][0].ToString();

But don't use SqlDataAdapter if you only get one row. Use ExecuteScalar which is exactly what this for.

using (var con = new SqlConnection(constr))
using (var cmd = con.CreateCommand())
{
     cmd.CommandText = query;
     con.Open();
     return cmd.ExecuteScalar().ToString();               
}

By the way, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

edited Feb 25, 2016 at 11:41

answered Feb 25, 2016 at 11:29

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Collectives™ on Stack Overflow

How to return a string value from datatable in c#?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related