11 Replies - 16097 Views - Last Post: 23 March 2008 - 11:22 PM Rate Topic: ****- 1 Votes

#1 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Pulling data from a Database and displaying in label

Posted 19 March 2008 - 09:30 PM

I am unsure how I can display something from a database into a label. I have a database setup named: "grocerydbmdf.mdf". Within the database I have a table named "content" which holds three fields:

1. (Field) id
under id I have a record named "1" and "2"

2. (Field) content_item
under content_item I have a record named "title" and "content"

3. (Field) page_content
Under page_content I have "Welcome to Tom's Store" and "Tom's Store"


I want to display the content record "Welcome to Tom's Store" and the title record "Tom's Store" (from the database) within some labels. How do I got about doing that?

Something like?? (totally wrong, but just an idea, not sure how you do it)
lblTitle.DataBindings.Add("Text", content, "page_content", Title); 
lblContent.DataBindings.Add("Text", content, "page_content", Content);


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
	protected void Page_Load(object sender, EventArgs e)
	{
		
		lblTitle.Text = (not sure how to pull it from database)
			lblContent.Text =
	}
}




and my Store page code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Tom's Store</title>
	<link href="default.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<form id="form1" runat="server">
		<div id="header">
			<h1>
				<asp:Label ID="lblTitle" runat="server" Text=""></asp:Label></h1>
		</div>
		<div id="content">
			<div id="colTwo">
				<asp:Label ID="lblContent" runat="server" Text=""></asp:Label>
			</div>
			<div style="clear: both;">
				&nbsp;</div>
		</div>
		<br />
		<div align="center">
			<a href="login.aspx">Login to the Control Panel</a></div>
	</form>
</body>
</html>


This post has been edited by kkgaming: 20 March 2008 - 12:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Pulling data from a Database and displaying in label

#2 Footsie   User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Pulling data from a Database and displaying in label

Posted 20 March 2008 - 01:06 AM

There's a lot of stuff you need to add here. You need to establish a connection to the DB create a command object, or data-adapter etc...
Start by having a look at this tutorial by PsychoCoder, this is for a MS Sql DB.

If you have more issues getting your code to work post your new problem.
Was This Post Helpful? 0
  • +
  • -

#3 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 20 March 2008 - 08:19 AM

Thanks for the reply!

In web.config I have my connection string:

<configuration>
	<appSettings/>
	<connectionStrings>
<add name="gcConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\grocerydbmdf.mdf;Integrated Security=True;User Instance=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
	<system.web>
		<compilation debug="true"/>
		<authentication mode="Windows"/>
	
	  
		<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
			
		</customErrors>
		
	</system.web>
</configuration>



I have made a new class named DataAccess like in the tutorial and here is my method to retrieve the connection string.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
	public DataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}

	public static string GetConnectionString(string strConnection)
	{
		string strReturn;
	   
		if (!string.IsNullOrEmpty(strConnection))
		{
			strReturn = ConfigurationManager.ConnectionStrings[strConnection].ConnectionString;
		}
		else
		{
			strReturn = ConfigurationManager.ConnectionStrings["StrConnect"].ConnectionString;
		}
	   
		return strReturn;
	}


Then on my store C# page I connect to the data base...it won't let me connect, gives me an error.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		
		SqlConnection connection = null;
		SqlCommand command = null;
		SqlDataReader reader = null;
		try
		{
			connection = new SqlConnection("server=localhost;uid=sa;" + "pwd=;database=grocerydbmdf");
			command = new SqlCommand("SELECT * FROM page_content", connection);

			connection.Open();
			reader = command.ExecuteReader();

			while (reader.Read())
			{

				lblTitle.Text = reader.GetString(1);
				lblContent.Text = reader.GetString(2);
			}
		}
		finally
		{
			if (reader != null)
				reader.Close();
			if (connection != null)
				connection.Close();
		}
}
}



I am still unsure how to pull certain data out of my table named "content" and display that data within a label control. I don't think it should be that hard, but what do I need to do next?

This post has been edited by kkgaming: 20 March 2008 - 09:46 AM

Was This Post Helpful? 0
  • +
  • -

#4 Footsie   User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Pulling data from a Database and displaying in label

Posted 20 March 2008 - 01:50 PM

If you've set up a DataAccess class you don't need to set the connectionString again in your "Store" page as you've done here:
connection = new SqlConnection("server=localhost;uid=sa;" + "pwd=;database=grocerydbmdf");
The whole idea of a separate class is to simplify the code. Replace this with a call to your DataAccess class instead. And I don't think you should initialize your connection, command, and reader to null, you need to create an object instance:
//create new connection object, call dataAccess method 
//GetConnectionString
SqlConnection connection = new SqlConnection(DataAccess.GetConnectionString("YourConnectionName"));
    //create a SqlCommand Object
SqlCommand command = connection.CreateCommand();
//tell it that it is a text command, not a StoredProcedure
command.CommandType = CommandType.Text;
//enter the command to execute
command.CommandText = "SELECT * FROM page_content"; 



The SqlDataReader "pulls" info from your DB for you and stores it in your reader variable. In this eg above (SELECT *) pulls all info from that table.
//declare SqlDataReader object only - you will assign it when 
//you execute the reader
SqlDataReader reader;
try
{
connection.Open();
//set the reader to execute and close when finished
reader = command.ExecuteReader(CommandBehaviour.CloseConnection);

//read through the returned values
 while (reader.Read())
            {
//assuming the values you want to display are the first 2 returned
                lblTitle.Text = reader.GetString(0);
                lblContent.Text = reader.GetString(1);
            }
}
finally
{
//always close connection
connection.Close();
}




My code here is probably not perfect but I hope it helps you out a little.
:)
Was This Post Helpful? 0
  • +
  • -

#5 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 20 March 2008 - 02:55 PM

Thanks for all the help Footsie! You are right I wouldn't need to set the connection string twice. I changed the code a little because I was getting errors, but I am still getting errors.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
   

	protected void Page_Load(object sender, EventArgs e)
	{
		SqlConnection connection = new SqlConnection(DataAccess.GetConnectionString("StrConnect"));
		SqlCommand command;
		command = connection.CreateCommand();
		command.CommandType = CommandType.Text;
		command.CommandText = "SELECT * FROM page_content";

		//declare SqlDataReader object only - you will assign it when 
		//you execute the reader
		SqlDataReader reader;
		try
		{
			connection.Open();
			//set the reader to execute and close when finished
			reader = command.ExecuteReader(CommandBehaviour.CloseConnection);
   
			//read through the returned values
			while (reader.Read())
			{
				//assuming the values you want to display are the first 2 returned
				lblTitle.Text = reader.GetString(0);
				lblContent.Text = reader.GetString(1);
			}
		}
		finally
		{
			//always close connection
			connection.Close();
		}

	}

}


Anyway, I am still getting an error from my DataAccess Class when I compile it the code above.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 28: if (!string.IsNullOrEmpty(strConnection))
Line 29: {
Line 30: strReturn = ConfigurationManager.ConnectionStrings[strConnection].ConnectionString;
Line 31: }
Line 32: else

This post has been edited by kkgaming: 20 March 2008 - 04:03 PM

Was This Post Helpful? 0
  • +
  • -

#6 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 21 March 2008 - 01:17 AM

Well I decided to forgo the DataAccess class, I didn't really think making a separate class was necessary. Anyway, I am getting an error saying it cannot connect to the remote Server or whatever" How do you connect to a database you created??? Nothing seems to work, it keeps telling me that it cannot connect to the remote host or the database all ready exists... Here is my connection string in my web.config file.

<configuration>
	<appSettings/>
	<connectionStrings>
  <add name="gcConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\grocerydbmdf.mdf;Integrated Security=True;User Instance=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
	<system.web>


Here is what I got for the store CS code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
	

	protected void Page_Load(object sender, EventArgs e)
	{
		// create an open connection
	 
		SqlConnection connection = new SqlConnection("localhost");
		connection.Open;

		// create a SqlCommand object for this connection
		SqlCommand command = connection.CreateCommand();
		command = connection.CreateCommand();
		command.CommandText = "SELECT id AS [id], content_item AS [content_item], page_content AS [page_content], FROM content WHERE Tab = '1'";
		command.CommandType = CommandType.Text;
		// execute the command that returns a SqlDataReader
			SqlDataReader reader = command.ExecuteReader();

			// display the results
			while (reader.Read())
			{
				lblTitle.Text = reader.GetString(0);
				lblContent.Text = reader.GetString(1);  
				string output = reader.ToString();
				Console.WriteLine(output);
			}

			// close the connection
			reader.Close();
			connection.Close();
		}

	}



This post has been edited by kkgaming: 21 March 2008 - 01:19 AM

Was This Post Helpful? 0
  • +
  • -

#7 Footsie   User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 06:44 AM

Well, you can't use this line to access your connection string:
SqlConnection connection = new SqlConnection("localhost");
If your connection string is still in your Web.config (which seems to be the case) you'll have to do something like this:
//declare a  ConnectionStringSettings object to hold the string
ConnectionStringSettings setting;
  //ConfigurationManager provides access to the config file
        setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
//<---this is the name of your string, set in web.config
        string myConnectString = setting.ConnectionString;

        SqlConnection connection = new SqlConnection(myConnectString);


As to your actual connection string in the Web.config...are you sure it is correct? Check this site ConnectionStrings.com to check if it is correct, look under the .NET section of your DB. (your string should be fairly simple if your DB is on your local machine)

Btw what database are you using?

In your Store code you have 2 using System.Configuration;
statements and 2 assignments for your "command" object
 SqlCommand command = connection.CreateCommand();
//if you've got above you don't need below
		command = connection.CreateCommand();
You won't need a Console.Writeline (output) line unless you're doing a console app.

Other than that I'm not sure why you can't connect to your DB.
(out of my depth)

Maybe someone with some more experience than me can check out your code? (*cough* PsychoCoder *cough*) :D

This post has been edited by Footsie: 23 March 2008 - 06:47 AM

Was This Post Helpful? 0
  • +
  • -

#8 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 10:49 PM

Alright I got it to connect to the data base, but I am getting the following error:

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.

Source Error:


Line 38: while (reader.Read())
Line 39: {
Line 40: lblTitle.Text = reader.GetString(0);
Line 41: lblContent.Text = reader.GetString(1);
Line 42: string output = reader.ToString();


I ended up changing my select statement, because it wasn't right:

 SqlCommand command = new SqlCommand("Select id, content_item, page_content From content", connection);


Here is the updated code, but I am still getting there error, above, not all I need to do is figure out how to display those to records fromt he first post. "Tom's Store" and "Welcome to Tom's Store".

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page
{


	protected void Page_Load(object sender, EventArgs e)
	{
   // create an open connection
		//declare a  ConnectionStringSettings object to hold the string
		ConnectionStringSettings setting;
		//ConfigurationManager provides access to the config file
		setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
		//<---this is the name of your string, set in web.config
		string myConnectString = setting.ConnectionString;

		SqlConnection connection = new SqlConnection(myConnectString);
		connection.Open();

		// create a SqlCommand object for this connection

		SqlCommand command = new SqlCommand("Select id, content_item, page_content From content", connection);
		command.CommandType = CommandType.Text;
		// execute the command that returns a SqlDataReader
			SqlDataReader reader = command.ExecuteReader();

			// display the results
			while (reader.Read())
			{
				lblTitle.Text = reader.GetString(0);
				lblContent.Text = reader.GetString(1);
				string output = reader.ToString();
			 
			  
			   
			}

			// close the connection
			reader.Close();
			connection.Close();
		}

	}
	
	



This post has been edited by kkgaming: 23 March 2008 - 10:57 PM

Was This Post Helpful? 0
  • +
  • -

#9 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 10:55 PM

You need to use ToString() to display the value in a label. Implicit conversions arent allowed in C#


lblTitle.Text = reader.GetString(0).ToString();
lblContent.Text = reader.GetString(1).ToString(); 




As fas as you having issues with my data access class, I cannot understand that. I use that constantly in all ASP.Net web applications, plus all Windows Applications I create. Ill have to look closer at all your code to see if I can figure out the issue.
Was This Post Helpful? 0
  • +
  • -

#10 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 11:01 PM

Thanks PsychoCoder for the reply! (oh and I am not using any class, I decided I didn't need it)

When I added the .ToString() I get the following error:

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.

Source Error:


Line 38: while (reader.Read())
Line 39: {
Line 40: lblTitle.Text = reader.GetString(0).ToString();
Line 41: lblContent.Text = reader.GetString(1).ToString();
Line 42: string output = reader.ToString();

This post has been edited by kkgaming: 23 March 2008 - 11:03 PM

Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 11:08 PM

the getString method will always throw this exception as when using getString the value being fetched must already be in string format. I would try getInt32 then use the ToString() on the value returned, like


lblTitle.Text = reader.GetInt32(0).ToString();



Which leads to a question in itself; If this is a title (be it a book, article, etc) why is it in integer format? If the value being returned isnt an int value what is it. Since those methods do no conversions, and will always throw that exception if the value being fetched isnt in the proper format, I would take a look at all the SqlDataReader Methods
Was This Post Helpful? 0
  • +
  • -

#12 kkgaming   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Pulling data from a Database and displaying in label

Posted 23 March 2008 - 11:22 PM

Here is my table where I am pulling the information..

Posted Image

I want to pull out the title "Tom's Grocery Store" and "Welcome to Tom's Grocery store...blah blah"

These are strings, so I wouldn't need int32...but how would I display those records?


I pulled out the "Welcome to Tom's Grocery Store........." by doing:

lblContent.Text = reader.GetString(2).ToString();


But how would I pull out the title? "Tom's Grocery Store" under page_content?

This post has been edited by kkgaming: 24 March 2008 - 01:37 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1