Join 150,407 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 950 people online right now. Registration is fast and FREE... Join Now!
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)
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 = } }
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.
I have made a new class named DataAccess like in the tutorial and here is my method to retrieve the connection string.
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;
/// <summary> /// Summary description for DataAccess /// </summary> public class DataAccess { public DataAccess() { // // TODO: Add constructor logic here // }
public static string GetConnectionString(string strConnection) { string strReturn;
Then on my store C# page I connect to the data base...it won't let me connect, gives me an error.
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;
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);
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 Mar, 2008 - 08:46 AM
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:
CODE
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:
csharp
//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.
csharp
//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.
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.
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;
public partial class _Default : System.Web.UI.Page {
//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 Mar, 2008 - 03:03 PM
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.
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 Mar, 2008 - 12:19 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:
csharp
//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
CODE
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*)
This post has been edited by Footsie: 23 Mar, 2008 - 05:47 AM
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:
CODE
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".
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;
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 Mar, 2008 - 09:57 PM
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.
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 Mar, 2008 - 10:03 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
csharp
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
| 