Databases in aspx web pages using C#

Hi,

As regards web development, can someone please post some code (in C#) to open a connection to a database and print out the contents of it (assuming a database of any name with any contents - the SQL involved is not the concern).

I am learning asp.net with the language used being C# and can only find examples doing the above using VB as the language on the Internet.

I think the code will probably have to go in the Page_Load method, but correct me if I am wrong.

Cheers in advance.
LVL 19
mrwad99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DiRNCommented:
Here is the code that I use to populate a menu on my Web site.  Each entry in the database will appear formatted the same way.  If you need more control over that, let me know and I'll post that code for you.

################################################

<%
string sMenuConn = "PROVIDER=Microsoft.Jet.OleDb.4.0;DATA SOURCE=" + Server.MapPath ("/databases/menu.mdb");
string MenuSQL = "SELECT * FROM Menu WHERE Disabled=False ORDER BY Menu_Item";
OleDbConnection MenuConn = new OleDbConnection (sMenuConn);
OleDbCommand MenuCmd = new OleDbCommand (MenuSQL, MenuConn);
MenuConn.Open ();

MenuRepeater.DataSource = MenuCmd.ExecuteReader (CommandBehavior.CloseConnection);
MenuRepeater.DataBind ();

MenuConn.Close ();
%>
<asp:repeater id="MenuRepeater" runat="server">
  <itemtemplate>
&nbsp; &nbsp; &nbsp;<a href="/<%# ((IDataRecord) Container.DataItem) ["Link"] %>"><%# ((IDataRecord) Container.DataItem) ["Menu_Item"] %></a><br>
  </itemtemplate>
</asp:repeater>

################################################

The code in the <% %> tags should go in the Page_Load method, while the <asp:repeater> tags should go where ever you need the code to appear.
0
mrwad99Author Commented:
DiRN for some reason I cannot get that code to work.  Can you alter it so that instead of outputting to a menu the data is output to a table.

Also why have you used OleDbConnection instead of SqlConnection ? All the examples I have use the latter which is why I am getting confused since I do not understand the parameters,

eg in my C# book I have:

SqlConnection conn = new SqlConnection("data source = localhost; userid =; password=;Initial Catalog=northwind");

 - Where is the database source specified ????

This then goes on to talk about DataSets and SqlAdapters which is even more confusing....

I am really getting
0
mrwad99Author Commented:
...quite down about all this since I know exactly what I want to do but just cannot find the resources to tell me how to do it !

I have however figured out how to add data to the database and was relieved when it actually updated the .mdb file, but this is still a long way from outputting it and deciding on which of the many methods (as outlined above) I am going to use !

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mrwad99Author Commented:
The exact error I got on the code you posted is:

Line 19:     MenuRepeater.DataSource = MenuCmd.ExecuteReader (CommandBehavior.CloseConnection);

System.Data.OleDb.OleDbException: No value given for one or more required parameters.
0
DiRNCommented:
I am not running MSSQLServer, which is why I use the OleDbConnection.  I use Windows' built in ODBC.  I've never actually done any work with Northwind, but I believe its samples use SQLServer.

The string

"PROVIDER=Microsoft.Jet.OleDb.4.0;DATA SOURCE=" + Server.MapPath ("/databases/menu.mdb")

specifies the data source in the Server.MapPath method.  So if, for example, your database was called "db.mdb" and it was stored in the same directory as your .aspx file, you would use Server.MapPath("db.mdb") to specify your data source.  Also, you should note that this is a DSN-less connection.

Here is some modified code:

<%
string sConn = "PROVIDER=Microsoft.Jet.OleDb.4.0;DATA SOURCE=" + Server.MapPath (<string containing path to database>);
string SQL = "<sql statement>";
OleDbConnection Conn = new OleDbConnection (sConn);
OleDbCommand Cmd = new OleDbCommand (SQL, Conn);
Conn.Open ();

Repeater.DataSource = Cmd.ExecuteReader (CommandBehavior.CloseConnection);
Repeater.DataBind ();

Conn.Close ();
%>

<table>
<asp:repeater id="Repeater" runat="server">
 <itemtemplate>
<tr>
<td><%# ((IDataRecord) Container.DataItem) ["Field1"] %></td>
<td><%# ((IDataRecord) Container.DataItem) ["Field2"] %></td>
</tr>
</itemtemplate>
</asp:repeater>
</table>

The output here has been setup for a table.  And try running your scripts with just the OleDbConnection, and not the SqlConnection.

I hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrwad99Author Commented:
Thank you DiRN,

I will look at that code and come back with any questions before awarding you with the points for your efforts.

Cheers again.
0
mrwad99Author Commented:
Worked like a treat DiRN; thank you profusely.

:-)
0
DiRNCommented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.