Getting unknown data from SQL Server using SqlDataReader

Question

I use this code for getting data from SQL Server:

    string rootname = "[" + nameroot + "]";
    string retVal = "";

    string constring = "Data Source =(Local);Initial Catalog=Tajari;Integrated Security=True;";

    SqlConnection conobj = new SqlConnection(constring);

    string commandtext = "select distinct " + rootname + " from TBLJobsRootName where MenuId between 1 and " + countroot + "";
    SqlCommand comobj = new SqlCommand(commandtext, conobj);

    conobj.Open();

    SqlDataReader dr = comobj.ExecuteReader();

    while (dr.Read())
    {
        //retVal = dr[nameroot].ToString();
    }

    conobj.Close();

Now I need this data inserted into a variable or textbox.

The problem is that my values are unknown

Sudhakar Tillapudi · Accepted Answer · 2014-03-29 11:32:23Z

3

Solution 1 :

You can use Index value.

Try This:

while (dr.Read())
{
    retVal = dr[0].ToString();
}

Solution 2 :

You can also use alias name.

string commandtext = "select distinct " + rootname + " as myrootname from   
               TBLJobsRootName   where MenuId between 1 and " + countroot + "";
SqlCommand comobj = new SqlCommand(commandtext, conobj);
conobj.Open();
SqlDataReader dr = comobj.ExecuteReader();
while (dr.Read())
{
    retVal = dr["myrootname"].ToString();
}

Suggestion: Your Query is open to SQL Injection Attacks i would suggest you to use Parameterised Queries to avoid them.

Solution 3: Using Parameterised Queries

if you want to add all rootname values you need to add all values to List.

List<string> list = new List<string>();
string commandtext = "select distinct "+rootname+" as myrootname from   
               TBLJobsRootName   where MenuId between 1 and @countroot";
SqlCommand comobj = new SqlCommand(commandtext, conobj);

comobj.Parameters.AddWithValue("@countroot",countroot);
conobj.Open();
SqlDataReader dr = comobj.ExecuteReader();
while (dr.Read())
{
    lis.Add(dr["myrootname"].ToString());
}

//You can retunrn list

edited Mar 29, 2014 at 11:32

answered Mar 29, 2014 at 10:22

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

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

2 Comments

Sudhakar Tillapudi Over a year ago

@M.Tajari: You need to add items to list, check my edited answer in solution3

Steve Over a year ago

@SudhakarTillapudi you cannot use a parameter to express a column name. QUOTENAME is the only possibility here

Collectives™ on Stack Overflow

Getting unknown data from SQL Server using SqlDataReader

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related