How to display SQL table row data in gridview colums header.?

Question

0.00/5 (No votes)

See more:

, +

i have display sql data row in grid view colums but Sql query is not proper working.

Scoresheet is my sql table name.

What I have tried:

SELECT P_SC, P_LIT, TOT_WORK_P, MAIN_CL_P FROM Scoresheet

C#

protected void Page_Load(object sender, EventArgs e)
   {
       if (!Page.IsPostBack)
           this.GetData();
   }

   private void GetData()
   {
       var table = new DataTable();
       string connectionString = ConfigurationManager.ConnectionStrings["MediaMantradb"].ConnectionString;

       using (var connection = new SqlConnection(connectionString))
       {
           using (var command = new SqlCommand("SELECT P_SC, P_LIT, TOT_WORK_P, MAIN_CL_P FROM Scoresheet", connection))
           {
               using (var a = new SqlDataAdapter(command))
               {
                   connection.Open();
                   a.Fill(table);
                   connection.Close();
               }
           }
       }
       GridView1.DataSource = table;
       GridView1.DataBind();
   }

Posted 29-Nov-16 2:13am

Member 12844966

Updated 7-Dec-16 0:47am

v2

Add a Solution

Comments

dbrenth 30-Nov-16 16:14pm

What do you get when you run the SQL in the native SQL Server Management studio? It is possible you may need to qualify your table name. Make sure you log into Studio using the same user as in your connection string.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Er Parul · Accepted Answer · 2016-12-06T17:29:00

Hi,

Please check your connection string , is it proper ? after that make sure your connection done successfully ? Check SELECT P_SC, P_LIT, TOT_WORK_P, MAIN_CL_P FROM Scoresheet this is proper without any typo mistake.

Use Dataset like below.

C#

string connetionString = null;
SqlConnection connection ;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
connection = new SqlConnection(connetionString);
connection.Open();
adapter.SelectCommand = new SqlCommand("Your SQL Statement Here", connection);
adapter.Fill(ds);
connection.Close();
if(ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
 	  GridView1.DataSource = ds.Tables[0];
 	  GridView1.DataBind();
}

Hrishikesh · Accepted Answer · 2016-12-07T00:47:00

Solution 2

If you want the rows to be displayed as columns, then in that case you can use pivot to get this issue resolved.
Check this link:
Using PIVOT and UNPIVOT[^]

Posted 7-Dec-16 0:47am

Hrishikesh