asked on

Displaying a database table in my default.aspx

I'm trying to learn how to make connections with my online SQL Server database, and for starters I want to get the code right to display the table "fieldreps2" in my database "DJRTest1."

Before a recent computer crash, a friend helped me set up the original default.aspx in Visual Web Developer, but since I'm starting over I want to do it in Dreamweaver. I have established a connection to my database in Dreamweaver, so that's not an issue.

Please take a look at my current working file: http://dijitalrealm.com/Default_beta1b.aspx. Below is the code as I have it in Dreamweaver. Could you please insert the appropriate code so that my table "fieldreps2" will display on the page? For now just assume a password of "1234"

Thanks!
John

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JohnsFirstWebApp._Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
    <style>
    #container{width:880px; height:680px; margin: auto; background:white}
    #header{width:880px; height:130px; background:url(http://dijitalrealm.com/images/DJRHeaderDots.png)}
    #searchBox{width:140px; height:20px; background: white; border: 1px solid #333; float:right; margin:20px 20px; padding-left:10px}
    .formHolder{width:400px; clear:both; margin-bottom:10px}
    .label{width:110px; text-align:right; padding: 0 10px; float:left;}
    body {margin-top:0px; background:url(images/DjRbg.png) repeat-x #221642; font-family:"Gill Sans MT"; font-size:15px}
    #footer{margin:auto; width:880px; height:32px; background:url(http://dijitalrealm.com/images/DjRFooterBG880.png)  no-repeat}
    .contentBox1{width:400px; height:auto; padding:20px 0 0 0; margin:0 auto; margin-top: 10px; color:#221642; text-align:center; font-family:Gill Sans MT; font-size:16px; font-weight:bold; font-style:italic}
    .contentBox2{width:400px; height:auto; border:1px solid blue; padding:20px; margin:20px; color:green; font-family:Gill Sans MT; font-size:16px; font-weight:bold;}
    .contentBox3{width:auto; height:auto; padding:20px; margin:20px; font-family:Verdana; font-size:11px; margin: auto}
    </style>
    <link rel="Shortcut Icon" href="http://dijitalrealm.com/images/DJRFavicon.ico" type="image/x-icon" />
 
</head>
 
<body>
 
<div id="container">
<div id="header">
<div id="searchBox"></div>
</div> 
<div class="contentBox3">
 <div align="center">    
  <form id="form1" runat="server">
 
<!--HERE IS WHERE I WOULD LIKE TO INSERT MY TABLE "fieldreps2" IN DATABASE "DJRTest1."-->
 
<div align="center">
    <asp:Calendar ID="Calendar1" runat="server" BackColor="White" BorderColor="White" 
           BorderWidth="1px" Font-Names="Verdana" Font-Size="9pt" ForeColor="Black" 
           Height="190px" NextPrevFormat="FullMonth" Width="350px">
        <SelectedDayStyle BackColor="#333399" ForeColor="White" />
        <TodayDayStyle BackColor="#CCCCCC" />
        <OtherMonthDayStyle ForeColor="#999999" />
        <NextPrevStyle Font-Bold="True" Font-Size="8pt" ForeColor="#333333" 
            VerticalAlign="Bottom" />
        <DayHeaderStyle Font-Bold="True" Font-Size="8pt" />
        <TitleStyle BackColor="White" BorderColor="Black" BorderWidth="4px" 
            Font-Bold="True" Font-Size="12pt" ForeColor="#333399" />
       </asp:Calendar></div>
       <br />
     </form>
   </div> 
     <div class="contentBox1">We're working on it ...</div>
</div>
    
</div>
<div id="footer"></div>
    </body>
</html>

Open in new window

SOLUTION

GiftsonDJohn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Hi, thanks for the very promising post. I got a new error, the jist of which is that "the 'DataSource' property cannot be set declaratively". Please take a look at my updated code and let me know where I'm going wrong.

Thanks,
John

Here's the error message:

Server Error in '/' Application. Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: The 'DataSource' property cannot be set declaratively.

Source Error: Line 32: SelectCommand="SELECT * from fieldreps2">
Line 33: </asp:SqlDataSource>
Line 34: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" DataSource="SqlDataSource1">
Line 35: </asp:GridView>Line 36:

Source File: /Default_beta1b.aspx Line: 34
Version Information: Microsoft .NET Framework Version:2.0.50727.1434; ASP.NET Version:2.0.50727.1434

REVISED CODE:
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JohnsFirstWebApp._Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
    <style>
    #container{width:880px; height:680px; margin: auto; background:white}
    #header{width:880px; height:130px; background:url(http://dijitalrealm.com/images/DJRHeaderDots.png)}
    #searchBox{width:140px; height:20px; background: white; border: 1px solid #333; float:right; margin:20px 20px; padding-left:10px}
    .formHolder{width:400px; clear:both; margin-bottom:10px}
    .label{width:110px; text-align:right; padding: 0 10px; float:left;}
    body {margin-top:0px; background:url(images/DjRbg.png) repeat-x #221642; font-family:"Gill Sans MT"; font-size:15px}
    #footer{margin:auto; width:880px; height:32px; background:url(http://dijitalrealm.com/images/DjRFooterBG880.png)  no-repeat}
    .contentBox1{width:400px; height:auto; padding:20px 0 0 0; margin:0 auto; margin-top: 10px; color:#221642; text-align:center; font-family:Gill Sans MT; font-size:16px; font-weight:bold; font-style:italic}
    .contentBox2{width:400px; height:auto; border:1px solid blue; padding:20px; margin:20px; color:green; font-family:Gill Sans MT; font-size:16px; font-weight:bold;}
    .contentBox3{width:auto; height:auto; padding:20px; margin:20px; font-family:Verdana; font-size:11px; margin: auto}
    </style>
    <link rel="Shortcut Icon" href="http://dijitalrealm.com/images/DJRFavicon.ico" type="image/x-icon" />
 
</head>
 
<body>
 
<div id="container">
<div id="header">
<div id="searchBox"></div>
</div>
 
 
<div class="contentBox3">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings: DJRTest1ConnectionString %>"
        SelectCommand="SELECT * from fieldreps2">       
    </asp:SqlDataSource>    
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" DataSource="SqlDataSource1">        
    </asp:GridView>
</div>
 
<div class="contentBox3">
 <div align="center">    
  <form id="form1" runat="server">
 
<!--HERE IS WHERE I WOULD LIKE TO INSERT MY TABLE "fieldreps2" IN DATABASE "DJRTest1."-->
 
<div align="center">
    <asp:Calendar ID="Calendar1" runat="server" BackColor="White" BorderColor="White" 
           BorderWidth="1px" Font-Names="Verdana" Font-Size="9pt" ForeColor="Black" 
           Height="190px" NextPrevFormat="FullMonth" Width="350px">
        <SelectedDayStyle BackColor="#333399" ForeColor="White" />
        <TodayDayStyle BackColor="#CCCCCC" />
        <OtherMonthDayStyle ForeColor="#999999" />
        <NextPrevStyle Font-Bold="True" Font-Size="8pt" ForeColor="#333333" 
            VerticalAlign="Bottom" />
        <DayHeaderStyle Font-Bold="True" Font-Size="8pt" />
        <TitleStyle BackColor="White" BorderColor="Black" BorderWidth="4px" 
            Font-Bold="True" Font-Size="12pt" ForeColor="#333399" />
       </asp:Calendar></div>
       <br />
     </form>
   </div> 
     <div class="contentBox1">We're working on it ...</div>
</div>
    
</div>
<div id="footer"></div>
    </body>
</html>

Open in new window

SOLUTION

GiftsonDJohn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

I get the same error.

a) If "The 'DataSource' property cannot be set declaratively," then how can it be set?

b) Under Stack Trace, I get this error:

[InvalidOperationException: The connection name 'DJRTest1ConnectionString' was not found in the applications configuration or the connection string is empty.]

c) Here is the connection string that enables Dreamweaver to connect successfully with my database:

"Data Source=DJRTest1.db.3717092.hostedresource.com;
Initial Catalog=DJRTest1;
User ID=DJRTest1;
Password='1234'"

Thanks,
John

GiftsonDJohn

Hi,

The DJRTest1ConnectionString has to be configured. Open the design view. From the Smart Tag (a small arrow appears while the control is selected) of the GridView, select "choose datasource". From the dropdown select <new data source>. Choose Database and select the server and table name. Your sqlconnection will be automatically initialized and columns will be added into the GridView. run the page and you will be able to see the table in the page.

John Carney

ASKER

Thank you for sticking with me on this. When I left-click on the grid view control, I get nothing. When i right-click, I get these options in a drop down menu: Cut, Copy, Paste, Paste Special, Templates, and Elements.

I'm in the Design window of Dreamwaever CS3. Where on the page does the Smart Tag arrow appear?

GiftsonDJohn

The smart tag will appear in Visual Studio only. I haven't noticed Dreamweaver in the tag.

We have to do sql connection string setting manually. In web.config add the follwing under <system.web>. if <connectionStrings> is already there just copy the <add> tag inside the <connectionStrings>

<connectionStrings>
<add name="DJRTest1ConnectionString" connectionString="Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog= DJRTest1;User ID=DJRTest1;
Password=1234"
providerName="System.Data.SqlClient" />
</connectionStrings>

Now run the page. You should see the expected result. Make sure that the select command in sqldatasource is correct.

John Carney

ASKER

As you can see in the screenshot, Dreamweaver tells me to download the file from my server and store it locally, but I can't find the file on my server. Where do I get one?

Thanks,
John

ConnectionString.png

John Carney

ASKER

Well it turns out that I have the old web.config file created by my friend in Visual Web Studio. Two in fact. One called web.config, and another called WebConfig.aspx. I altered them both to have your new code, uploaded them to my testing server,and now I get this error:

500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.

I guess that's progress? :-) (BTW, I assume that the correct file is web.config?)

Also when I opened the old file, it had the line <system.web> below the <connectionStrings> section, so I moved it above, as indicated by your last post.

John

John Carney

ASKER

Please forgive me again for not being to get this yet, but I've attached the entire code for my aspx file and my web.config (I've reduced the aspx file to the table only).

I'm hoping you will be able to fix both files and re-post the corrected codes in their entirety so that even a dummy like me can get it to work!

I don't know if this any bearing on anything but I just noticed that when I go to my site www.dijitalrealm.com, where I have an index.html, I get the same 500 server error. (???)

Thanks, John

John Carney

ASKER

I've been reading a little on the Microsoft site about web.config files. Do I need to have a Machine.config file first, as their article seems to suggest?

Thanks,
John

GiftsonDJohn

Please post the web.config file contents. Let me correct it for you.

John Carney

ASKER

Here ya go. I removed this file from the server and I still get the 500 - Internal server error when I run the aspx file. I'm very confused and frustrated at this point, and if you can get this working for me, I will be amazed and deeply grateful. :-)

John

<?xml version="1.0"?>
 
 
 
<configuration>
	<configSections>
		<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
			<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
				<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
				<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
					<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere"/>
					<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
					<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
					<section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
				</sectionGroup>
			</sectionGroup>
		</sectionGroup>
   
	</configSections>
	<appSettings/>
    
    <system.web>
	<connectionStrings>
		<!--<add name="aardvark2ConnectionString" connectionString="Data Source=JOHNHOME\SQLEXPRESS;Initial Catalog=aardvark2;Integrated Security=True" providerName="System.Data.SqlClient"/>-->
        <add name="DJRTest1ConnectionString" connectionString="Data Source=DJRTest1.db.3717092.hostedresource.com;Initial Catalog= DJRTest1;User ID=DJRTest1;
Password=Password1"
     providerName="System.Data.SqlClient" />
	</connectionStrings>
	
		<!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
		<compilation debug="true">
			<assemblies>
				<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
			</assemblies>
		</compilation>
		<!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
		<authentication mode="Windows"/>
		<!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, DJRTest1ConnectionString
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.-->
 
 
        <customErrors mode="Off" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        
		<pages>
			<controls>
				<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			</controls>
		</pages>
		<httpHandlers>
			<remove verb="*" path="*.asmx"/>
			<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
		</httpHandlers>
		<httpModules>
			<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
		</httpModules>
	</system.web>
	<system.codedom>
		<compilers>
			<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
				<providerOption name="CompilerVersion" value="v3.5"/>
				<providerOption name="WarnAsError" value="false"/>
			</compiler>
		</compilers>
	</system.codedom>
	<!-- 
        The system.webServer section is required for running ASP.NET AJAX under Internet
        Information Services 7.0.  It is not necessary for previous version of IIS.
    -->
	<system.webServer>
		<validation validateIntegratedModeConfiguration="false"/>
		<modules>
			<remove name="ScriptModule"/>
			<add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
		</modules>
		<handlers>
			<remove name="WebServiceHandlerFactory-Integrated"/>
			<remove name="ScriptHandlerFactory"/>
			<remove name="ScriptHandlerFactoryAppServices"/>
			<remove name="ScriptResource"/>
			<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
		</handlers>
	</system.webServer>
	<runtime>
		<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
			<dependentAssembly>
				<assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
				<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
			</dependentAssembly>
			<dependentAssembly>
				<assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
				<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
			</dependentAssembly>
		</assemblyBinding>
	</runtime>
 
	<appSettings>
		<add key="MM_CONNECTION_HANDLER_MySQLdatabase" value="sqlserver.htm" />
		<add key="MM_CONNECTION_STRING_MySQLdatabase" value="Data Source=DJRTest1.db.3717092.hostedresource.com; Initial Catalog=DJRTest1; User ID=DJRTest1; Password='Password1'" />
		<add key="MM_CONNECTION_DATABASETYPE_MySQLdatabase" value="SQLServer" />
		<add key="MM_CONNECTION_SCHEMA_MySQLdatabase" value="" />
		<add key="MM_CONNECTION_CATALOG_MySQLdatabase" value="" />
	</appSettings>
</configuration>

Open in new window

ASKER CERTIFIED SOLUTION

GiftsonDJohn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Some progress. Now I can get my index.html page to display, but not my Default.aspx. I still get an error. First I got the runtime error I've attached below. What confuses me is that customErrors mode is already set to "Off" and still the "details of the application error" aren't shown.
Then I deleted the commented out refernce to the old database (aarvark1), and now I get the original parser error: "The 'DataSource' property cannot be set declaratively."
What exactly does that mean? And is there some way other than "declaratively" that the Data Source property CAN be set?
OR is there STILL something wrong with the aspx file?
OR, do I need the Machine.config file refrred to on the Microsoft site?
OR, are there any other things that could be messing this up? Do you have an environment in which you can test my aspx file?
Please forgive me for not being able to make your seemingly correct solutions work.
Thanks,
John

Server Error in '/' Application.
--------------------------------------------------------------------------------
 
Runtime Error 
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. 
 
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
 
 
<!-- Web.Config Configuration File -->
 
<configuration>
    <system.web>
        <customErrors mode="Off"/>
    </system.web>
</configuration>

Open in new window

John Carney

ASKER

Well for some reason it's working this morning, thank God. Sorry you had to go through all this. Curiously enough, while the aspx file is now working, all of a sudden I can't connect to my database in Dreamweaver as I could the whole time the aspx wasn't working. So that will be my next question. If you're not already completely worn out, please take a look at it.
Thanks!
John