[RESOLVED] Retrieving data from a database

[bulgarian388]

Hi guys, I am stuck trying to retrieve data from a database. I just figured out how to properly connect to a database with .net so I'm pretty knowledge-less. Anyway, the code sample bellow is accessing a database successfully, but I have no clue what so ever on how to put the data into where it is needed:


<%@ Page Language="C#" %>
<script runat="server">
public void Page_Load() {
Response.ContentType = "application/xml";
Response.Charset = "utf-8";

SqlDataSource myDataSource = new SqlDataSource("ConnectionString", "SelectStatement");

Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\"?>" + "\n");
Response.Write("<databaseResult>" + "\n");
Response.Write(" <addressResult value=\"" + [This is where I am trying to put the data] + "\" />" + "\n");
Response.Write("</databaseResult>");
}
</script>


Any help would be really, really appericated. :) :) :)

Thanks in advance.

[muxdaddy]

<%@ Page Language="C#" %>
<script runat="server">
public void Page_Load() {
Response.ContentType = "application/xml";
Response.Charset = "utf-8";

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
SqlCommand cmd = new SqlCommand("", con);
con.Open();
cmd.CommandText = "SELECT Address From Wherever";

Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\"?>" + "\n");
Response.Write("<databaseResult>" + "\n");
Response.Write(" <addressResult value=\"" + cmd.ExecuteScalar() + "\" />" + "\n");
Response.Write("</databaseResult>");
con.Close();
}
</script>

[bulgarian388]

Hi, thanks for replying, it works beutifully. I have some more questions though. How do I pull out more than one column from the DB, and how do I also make it into a loop? Here is my updated code:


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
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;

public partial class _xml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.ContentType = "application/xml";
Response.Charset = "utf-8";

SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString);
SqlCommand myCommand = new SqlCommand("SELECT [keyId], [myField] FROM [myTable]", myConnection);
myConnection.Open();
Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
Response.Write("<myElement>\n");
Response.Write(" <keyId>" + myCommand.ExecuteScalar() + "</keyId>\n");
Response.Write(" <myField>" + [Additional Field] + "</myField>\n");
Response.Write("</myElement>");
myConnection.Close();
}
}

[muxdaddy]

I hope there's no typos, I didn't use VS to key that in, did it by hand in the editor here.



using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
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;

public partial class _xml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.ContentType = "application/xml";
Response.Charset = "utf-8";

Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");

SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString);
SqlCommand myCommand = new SqlCommand("SELECT [keyId], [myField] FROM [myTable]", myConnection);
myConnection.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Response.Write("<myElement>\n");
Response.Write(" <keyId>" + Convert.ToString(dataReader["keyId"]) + "</keyId>\n");
Response.Write(" <myField>" + Convert.ToString(dataReader["myField"]) + "</myField>\n");
Response.Write("</myElement>");
}
dataReader.Close();
myConnection.Close();
}
}

[bulgarian388]

Sir, you are a god. You have no idea how much you have just helped me out. I sincerely thank you. :) :) :) :) :) :)

It works perfectly, now I can actually transition into .NET without worries. That was the only thing I was getting stuck on in .NET coming in from ASP classic.

Again, my sincerest gratitude.

[muxdaddy]

Once you get to know your way around asp.net, you'll laugh at how trivial most things are when compared with classic asp.