Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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.
0
mrwad99
Asked:
mrwad99
  • 5
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now