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
LVL 7
MHenryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Oliver AmayaConnect With a Mentor EntrepeneurCommented:
what you could do is used an SqlDataSource instead, that way you can use the connection string in the web.config file.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myCS %>" ProviderName="<%$ ConnectionStrings:myCS.ProviderName %>" SelectCommand="SSELECT [BlackListID], [Tel_vc], [Comments_vc] FROM [BlackList_T]"></asp:SqlDataSource>

Open in new window

0
 
Mortaza DoulatyCommented:
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)
0
 
MHenryAuthor Commented:
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Now
 
Mortaza DoulatyCommented:
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

0
 
Mortaza DoulatyCommented:
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

0
 
MHenryAuthor Commented:
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
0
 
Oliver AmayaEntrepeneurCommented:
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.
0
 
MHenryAuthor Commented:
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
0
 
MHenryAuthor Commented:
Perfect. That works!

Thanks joex911

Best,
MH
0
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.

All Courses

From novice to tech pro — start learning today.