How to read from SQL2000 and show the Data?

[jesus4u]

Can anybody show me in ASP.NET how to read from a SQL 2000 DB and present the data in the html?

Thanks

[fmardani]

Several ways you can do this.

First of all make sure you have:
imports system.data.sqlclient

create a function to get the dbconnectionstring
private readonly property connectionString()
Get
connectionstring="server=(local);user id=sa;password=;initial catalog=dbname"
End property

private function ConnectToDB() as sqldbreader
dim oCon as new sqlconnection(connectionstring)
dim oCmd as new sqlcommand(strSQL,oCon)
ocmd.executereader
textbox1.text=ocmd.item????
end sub

I think this will get u started;-)

[pvb]

Yikes...

Can anybody show me in ASP.NET how to read from a SQL 2000 DB and present the data in the html?

asked from someone with this on their signature:

MCSD as of 12/12/2002

...and so the value of the MCSD drops again...

[DevGrp]

Originally posted by pvb
Yikes...
asked from someone with this on their signature:...and so the value of the MCSD drops again...

lol

[jesus4u]

Originally posted by pvb
Yikes...
asked from someone with this on their signature:...and so the value of the MCSD drops again...

That is the most ridiculous comment I have ever read on this forum!

The whole .NET architecture is totally different than old ADO code.

So please,......keep quiet.

Obviously some people have what it takes to get certified.

[Cander]

add a datagrid control to your asp.net page. Here is sample code to fill it. Just change the connection string to a SQL one like you would with regular ADO

	Dim myDS As New DataSet
	Dim myConn As OleDbConnection
	Dim myCommand As OleDbDataAdapter
	Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & System.Web.HttpContext.Current.Server.MapPath("test.mdb")
	Dim strSQL As String
	
	Private Sub Page_Load(ByVal sender As Object, e As EventArgs)
		' Get Catagory listing from db
		strSQL = "SELECT c.ID, c.Catagory, c.PostCount, c.LastPostDate, c.Description, u.Username, u.ID AS UserID FROM Catagories AS c INNER JOIN Users AS u ON c.LastPostBy = u.ID"


		' Populate the Dataset from the SQL statement
		' Start Connection to ADO source
		myConn = New OleDbConnection (strConnString)
		' Run the Sql statement and get the Data into the DataAdapter
		myCommand = New OleDbDataAdapter(strSQL, myConn)
		' Fill the DataSet and name the table
		myCommand.Fill(myDS, "Catagory")
		myConn.Close()
		
		' Now set the DataGrid's source to the Dataset and bind it
		rptCatagory.DataSource = myDS.Tables("Catagory").DefaultView
		rptCatagory.DataBind()
	End Sub

the rptCatagory is the name of the Datagrid control

The datagrid is a web for control that automatically creates all the html table code for you.

[jesus4u]

Thanks Cander. I appreciate it. That does look pretty simple.

Another thought...What other ways are there? Do we still do the OLD inline asp type of code?

[Cander]

Originally posted by jesus4u
Do we still do the OLD inline asp type of code?

You can, but it is so much better to do ti this way, but there is something called the ASP .NET Repeater control which is actually what I used for the code I gave, but it is bounded to it the same way as a Datagrid. The repeater gives you more manual control of building the table. Here is a sample of using the repeater, still using the same binding code as above, this is the ASP page code.

<asp:repeater id="rptCatagory" runat="server">
		<ItemTemplate>
				<tr style = "font-family:Verdana, Arial;font-size:8pt">
					<td width="60%" class="threadcolumn">
						<table border="0" width="100%" style = "font-family:Verdana, Arial;font-size:8pt">
						<tr>
							<td width="100%" class="threadcolumn">
								<b>
									<a href="gotoforum.aspx?id=<%# Container.DataItem("Id") %>"><%# Container.DataItem("Catagory") %></a>
								</b>
							</td>
						</tr>
						<tr>
							<td width="100%" class="threadcolumn">
								<%# Container.DataItem("Description") %>
							</td>
						</tr>
						</table>
					</td>
					<td width="20%" class="threadcolumn" align="center">
						<b>
							<%# Container.DataItem("PostCount") %>
						</b>
					</td>
					<td width="20%" class="threadcolumn" align="center">
						<b>
							<%# Container.DataItem("LastPostDate") %><br/>
							<a href="userprofile.aspx?id=<%# Container.DataItem("UserID") %>"><%# Container.DataItem("Username") %></a>
						</b>
					</td>					
				</tr>
		</ItemTemplate>
	</asp:Repeater>

what that will do is loop through each item is in the dataset binded to it and the <%# Container.DataItem("LastPostDate") %> parts is the inline code specifying the field name to fill from.

Does that help?

[jesus4u]

Yes! Appreciate it!

By the way..are you developing in C# or VB?

I am doing the C# thing.

[Cander]

Both. they are virtually exactly the same when it comes to calling objects. Just depends on what I feel like using when I write the code.

[pvb]

Well unfortunately for me, I do have my MCSD, and the fact that you have one makes me upset that I wasted my time and money, apparently anyone can get certified. Here's my contribution to the original question:

http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://samples.gotdotnet.com/quickst...ataaccess.aspx
http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://www.csharp-corner.com/Database.asp
http://www.datagridgirl.com/articles.aspx

[jesus4u]

Originally posted by pvb
Well unfortunately for me, I do have my MCSD, and the fact that you have one makes me upset that I wasted my time and money, apparently anyone can get certified. Here's my contribution to the original question:

http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://samples.gotdotnet.com/quickst...ataaccess.aspx
http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://www.csharp-corner.com/Database.asp
http://www.datagridgirl.com/articles.aspx

Based on your ignorant and ARROGANT attitude I certainly will not follow any information you share with me.

[pvb]

lol, proving my point loud and clear.

[plenderj]

Originally posted by pvb
lol, proving my point loud and clear.

As an MCSD no doubt you are well aware of the enormous differences between ADO and ADO.Net ?

[pvb]

he he he, this is good stuff. So after receiving a "private" message from jesus4u, I have to laugh. So here's my beef, and now I see another note just got posted about knowing the differences between ADO and ADO.NET.

Jeez. I never mentioned anything about difficulty of the .NET framework, "enormous" differences between ADO and ADO.NET, or knowing everything just because you're an MCSD.

First of all, a comment on this: http://techgnome.blogspot.com/,

Sometimes the arrogance and ignorance of people astound me. Take this thread out at VBForums for instance. PVB seems to think that just because you have MCSD status that you must know everything there is to know about programming. What does he think this is, "The Highlander"? And that by achieving MCSD, that you then have all the knowledge there is to know? If that's the case, I don't want it... I wouldn't want to know everything... there would be nothing more to learn.... where would the fun in that be?
It's people like that that just make me want to...... grrr....

Ok, if there's any notion of arrogance jesus4u, it's in your signature.

MCSD as of 12/12/2002

What is that? a sign that you're better than people without one? Seriously, why do you put that in your sig?

My point with the MCSD is not that having one means you know everything, it means that you're intelligent enough to go look up the answer to a simple question like:

How to read from SQL2000 and show the Data?

or put in a tiny bit of effort to find out about asp.net to answer this question:

Do we still do the OLD inline asp type of code?

It's all in the documentation, all you have to do is read a little. And whatever you don't find in the docs is gonna be somewhere in one of the million newsgroups or websites dedicated to .NET.

So just out of curiousity, how much effort did you put in before posting these questions? Did you read any documentation? did you go to msdn online? I searched msdn on your question and came up with plenty of online docs, did you search there? did you try google? lol, so i put in your original question in google and what did i find? hmm, the exact same question from you on another forum: http://www.dotnetforums.net/t70435.html. <sarcasm>That's so weird that you didn't get much of a response over there...</sarcasm>

- The Highlander

[Cander]

What is that? a sign that you're better than people without one? Seriously, why do you put that in your sig?

Pride I would assume, nort arrogance. Nothing wrong with pride.

[pvb]

I guess that's one way to look at it...

[J Staniforth]

Perhaps you could help me?
Using a repeater I have tried putting other db control eg datagrid. But from code-behind class cannot seem to access the datagrid to set its bindings dynamically although its defined in the class:-

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents Repeater1 As System.Web.UI.WebControls.Repeater

It even gives code completion but when run says "Object reference not set to an instance of an object." when code tries to set datasource (which happens to be same as repeater's).

[Cander]

did you inherit the Page object in you class?


Inherits Page

[J Staniforth]

Yeah -Inherits System.Web.UI.Page.
The reason I thought I would ask you guys is that maybe this is only accessible via HTML or something and I'm barking up the wrong tree.
It successfully sets the datasource etc of the Repeater in the same event.

[Cander]

wait. Are you trying to put a Datagrid inside a repeater?

[J Staniforth]

Yes - let me know how daft this is on a scale 1 - 10.

[Cander]

30.

I dont think you can repeat a Datagrid control, or any other asp.net webform control. Only html stuff.

Logically thinking: I dont think it could process the repeater, place a Datagrid, then process the Datagrid!

[pvb]

You can totally use a DataGrid in a repeater. That's one way you can get a master/detail report on a web page ( I think i saw the original concept on dotnetjunkies, but here's my little test page ) :

<%@ Page language="c#" 
    Codebehind="MasterDetail.aspx.cs" 
    AutoEventWireup="false" 
    Inherits="CSharp.DataGridMasterDetail.MasterDetail" %>
<html>
    <body>
        <form runat="server">
            <asp:Repeater
                EnableViewState="False"
                id="CustomerOrderRepeater"
                runat="server">
                
                
                <HeaderTemplate>
                    <table width="100%">
                </HeaderTemplate>
                
                <ItemTemplate>
                        <tr>
                            <td><%# DataBinder.Eval(Container.DataItem, "CompanyName") %></td>
                        </tr>
                        <tr>
                            <td>
                                <asp:DataGrid
                                    id="OrderGrid"
                                    runat="server"
                                    AutoGenerateColumns="True"
                                    DataSource='<%# GetDataSource( Container.DataItem ) %>'/>
                            </td>
                        </tr>
                </ItemTemplate>
                
                <FooterTemplate>
                    </table>
                </FooterTemplate>
                
            </asp:Repeater>
        </form>
    </body>
</html>

and the code behind:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace CSharp.DataGridMasterDetail
{
    public class MasterDetail : System.Web.UI.Page
    {
        protected DataSet ds = null;
        protected Repeater CustomerOrderRepeater;

        private void bindGrid()
        {
            string connString = 
                "user id=sa;password=sa;database=northwind;server=(local);";
            using (SqlConnection cn = new SqlConnection(connString))
            {
                string cmdText = "Select * From Customers;Select * From Orders";
                ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmdText, cn);
                da.Fill(ds);
                ds.Tables[0].TableName = "Customers";
                ds.Tables[1].TableName = "Orders";
                CustomerOrderRepeater.DataSource = ds.Tables["Customers"];
                CustomerOrderRepeater.DataBind();
            }
        }
        protected DataView GetDataSource(object dataItem)
        {
            string customerId = (String)DataBinder.Eval(dataItem,"CustomerId");
            DataView dv = ds.Tables["Orders"].DefaultView;
            dv.RowFilter = "CustomerId = '" + customerId + "'";
            return dv;
        }
        override protected void OnLoad(EventArgs e)
        {
            if (!Page.IsPostBack)
                bindGrid();
        }
        override protected void OnInit(EventArgs e)
        {
            base.OnInit(e);
        }
    }
} 

[Cander]

AHHH cool. Well color me wrong this time!