asked on

Change gridview datasource to connection sting in web.config. ASP.Net 2.0, VS2005, VB.Net, Access Database

I guess this is so basic no one shows how to do it...

The host I use wants me to use a direct path to the database. When I drag a gridview onto the page, it asks for a datasource. I enter the information and everything works fine.

However, I've read that you shouldn't have the physical path in the aspx file, it should be in the webconfig file. So, I need to know how to convert this to the webconfig file:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="C:\myDirectory\database\events.mdb"
DeleteCommand="DELETE FROM [events] WHERE [eventID] = ?" InsertCommand="INSERT INTO [events] ([eventID], [eventName], [eventDate], [eventTime], [eventAddress], [eventCity], [eventState], [eventPhone], [eventDescription]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
SelectCommand="SELECT * FROM [events] ORDER BY [eventID] DESC" UpdateCommand="UPDATE [events] SET [eventName] = ?, [eventDate] = ?, [eventTime] = ?, [eventAddress] = ?, [eventCity] = ?, [eventState] = ?, [eventPhone] = ?, [eventDescription] = ? WHERE [eventID] = ?">
<DeleteParameters>

Best,
MH

Mortaza Doulaty

In webconfig file add a section named ConnectionString before web.config section:
<connectionStrings>
<add name="mycs" connectionString="Data Source=38.157.64.125;Initial Catalog=dbname;User ID=sa; Password=123" />
</connectionStrings>

Then in your page html source, bind the connection string property of your proper control to this cs using:
ConnectionString=<%$ ConnectionStrings:mycs %>
(You may use data source controls)

MHenry

ASKER

Sorry, that's where you lose me. How do I add that to the code above? I know how to put it in the webconfig. What I don't know is how or where to put the other part.

Can you show me an example using the code above?

Thanks,
MH

Mortaza Doulaty

Here is a sample file:

<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration>
    <appSettings/>
  <connectionStrings>
    <add name="myCS" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\Mortaza\My Projects\Visual Basic Working Projects\Bell Telephone\Main\Database\Mrtdb.mdb'"/>
  </connectionStrings>
    <system.web>
        <!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
 
            Visual Basic options:
            Set strict="true" to disallow all data type conversions 
            where data loss can occur. 
            Set explicit="true" to force declaration of all variables.
        -->
        <compilation debug="false" strict="false" explicit="true" />
        <pages>
            <namespaces>
                <clear />
                <add namespace="System" />
                <add namespace="System.Collections" />
                <add namespace="System.Collections.Specialized" />
                <add namespace="System.Configuration" />
                <add namespace="System.Text" />
                <add namespace="System.Text.RegularExpressions" />
                <add namespace="System.Web" />
                <add namespace="System.Web.Caching" />
                <add namespace="System.Web.SessionState" />
                <add namespace="System.Web.Security" />
                <add namespace="System.Web.Profile" />
                <add namespace="System.Web.UI" />
                <add namespace="System.Web.UI.WebControls" />
                <add namespace="System.Web.UI.WebControls.WebParts" />
                <add namespace="System.Web.UI.HtmlControls" />
            </namespaces>
        </pages>
        <!--
            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, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
 
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
    </system.web>
</configuration>

Open in new window

Mortaza Doulaty

Sorry for misunderstanding, your aspx file should look like this:
(Assumed that your db file is copied to your web sites root directory)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="BlackListID"
            DataSourceID="AccessDataSource1">
            <Columns>
                <asp:BoundField DataField="BlackListID" HeaderText="BlackListID" InsertVisible="False"
                    ReadOnly="True" SortExpression="BlackListID" />
                <asp:BoundField DataField="Tel_vc" HeaderText="Tel_vc" SortExpression="Tel_vc" />
                <asp:BoundField DataField="Comments_vc" HeaderText="Comments_vc" SortExpression="Comments_vc" />
            </Columns>
        </asp:GridView>
    
    </div>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Mrtdb.mdb"
            SelectCommand="SELECT [BlackListID], [Tel_vc], [Comments_vc] FROM [BlackList_T]">
        </asp:AccessDataSource>
    </form>
</body>
</html>

Open in new window

MHenry

ASKER

Mortaza_doulaty,

In my case, the above wont work. The database is above root level so I can't use the ~/database.mdb.

What I need is to use the connection string from the webconfig in the .aspx file. In the above, you show that the connection string is name="myCS"

Is there a way to use the myCS in the gridview code?

Thanks,
MH

Oliver Amaya

Hi, you cannot use a connection string with an AccessDataSource. What you need to do is place the file in the app_data folder so that it can be found using a relative path.

MHenry

ASKER

joex911,

Not that I don't believe you, but I'll leave this open a while because I have absolutely no way of knowing. I guess I just do it the way the host suggests and let them worry about the security...

Best,
MH

ASKER CERTIFIED SOLUTION

Oliver Amaya

membership

This solution is only available to members.

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

Start Free Trial

MHenry

ASKER

Perfect. That works!

Thanks joex911

Best,
MH