none
Updating Access database using ASP.NET 2.0 RRS feed

  • Question

  • To Whom It Might Help:
    I have created an Access Data Source to Select and Update data (see the code below). In my personal computer both commands work perfectly but when I upload to the server only the Select command works but the Update command is not an updateable query to the Server. Any one with ideas of what is happening?
    Thanks,
    Hugo

     

    CODE:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/FirstCommunionSignUp.mdb"

    SelectCommand="SELECT * FROM [SignUp] WHERE (([FirstName] = ?) AND ([LastName] = ?) AND ([Password] = ?))"

    UpdateCommand="UPDATE [SignUp] SET [EmailAddress] = ?, [LiturgyTime] = ?, [Flexibility] = ?, [TimeofSignUp] = Now() WHERE (([FirstName] = ?) AND ([LastName] = ?))">

    <SelectParameters>

    <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text"

    Type="String" />

    <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text"

    Type="String" />

    <asp:ControlParameter ControlID="txtPassword" Name="Password" PropertyName="Text"

    Type="String" />

    </SelectParameters>

    <UpdateParameters>

    <asp:ControlParameter ControlID="DetailsView1" Name="EmailAddress" PropertyName="SelectedValue" />

    <asp:ControlParameter ControlID="DetailsView1" Name="LiturgyTime" PropertyName="SelectedValue" />

    <asp:ControlParameter ControlID="DetailsView1" Name="Flexibility" PropertyName="SelectedValue" />

    <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text" />

    <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text" />

    </UpdateParameters>

    </asp:AccessDataSource>

    Thursday, December 6, 2007 5:14 AM

Answers

  • Sounds like a permissions issue. Assuming your Access database is located on the web server, you would need to provide full permissions to the folder where the database is located for the account under which the web application is executing.

     

    The account for which you would need to provide the permissions will depend upon a number of variables, such as which authentication mechanism the web app is configured for (Anonymous? Basic? Integrated NT), and whether impersonation for the application is enabled.

     

    Thursday, December 6, 2007 1:37 PM
  • Did you mean the database is located on the web server?

     

    Your ASP.NET can be configured for different types of authentication using IIS. The more common ones are Anonymous, BASIC and Integrated NT. Which one(s) are you using?

     

    There is a setting in the web.config file to enable impersonation. If you haven't made any changes then it should be disabled.

     

    With respect to your other error I would make certain to specify the data types for each of your parameter objects. If I had to guess I would say that the date parameter is probably causing the problem.

     

    Code Block

    OLEDBCommand.Parameters.Add("", Data.OleDb.OleDbType.Date)

    OLEDBCommand.Parameters.Add("", Data.OleDb.OleDbType.VarWChar, 50)

     

    Monday, December 10, 2007 2:30 PM

All replies

  • Sounds like a permissions issue. Assuming your Access database is located on the web server, you would need to provide full permissions to the folder where the database is located for the account under which the web application is executing.

     

    The account for which you would need to provide the permissions will depend upon a number of variables, such as which authentication mechanism the web app is configured for (Anonymous? Basic? Integrated NT), and whether impersonation for the application is enabled.

     

    Thursday, December 6, 2007 1:37 PM
  • Dear Paul or Whom It May Help:
    Thank you so much for your suggestion. The IT guy try to give full permission so the ASP Page could update the Access database but it did not work. Please let me know if you have any idea about what to do.
    Thanks,
    Hugo

     

    The following is the coding I used to update:

     

    Dim updateCommand As New OleDbCommand ("UPDATE [SignUp] SET [EmailAddress] = ?, [LiturgyTime] = ?, [Flexibility] = ?, [TimeofSignUp] = ? WHERE (([FirstName]=?) AND ([LastName]=?))", timeConnection)

    Dim EmailTextBox As TextBox = CType(DetailsView1.FindControl("TextBox1"), TextBox)

    Dim LiturgyDropDownList As DropDownList = CType(DetailsView1.FindControl("LiturgyTimeOptions"), DropDownList)

    Dim FlexibilityDropDownList As DropDownList = CType(DetailsView1.FindControl("FlexibilityOptions"), DropDownList)

    Dim mm As New MailMessage("firstcommunion@faithhope.org", ToAddress)

    Dim updateparamCollection As OleDbParameterCollection = updateCommand.Parameters

     

    updateparamCollection.Add(New OleDbParameter("EmailAddress", EmailTextBox.Text))

    updateparamCollection.Add(New OleDbParameter("LiturgyTime", LiturgyDropDownList.SelectedValue))

    updateparamCollection.Add(New OleDbParameter("Flexibility", FlexibilityDropDownList.SelectedValue))

    updateparamCollection.Add(New OleDbParameter("TimeofSignUp", DateTime.Now()))

    updateparamCollection.Add(New OleDbParameter("FirstName", txtFirstName.Text))

    updateparamCollection.Add(New OleDbParameter("LastName", txtLastName.Text))

    updateCommand.ExecuteNonQuery()

     

    The ASP Page select without a problem but it does not update. The following is the error message from server:

    Operation must use an updateable query.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

    Source Error:

    Line 68:             updateparamCollection.Add(New OleDbParameter("LastName", txtLastName.Text))
    Line 69: 
    Line 70:             updateCommand.ExecuteNonQuery()
    Line 71: 
    Line 72: 

    Source File: c:\inetpub\wwwroot\FormationEducation\Sacraments\Test.aspx.vb    Line: 70

    Stack Trace:

    [OleDbException (0x80004005): Operation must use an updateable query.]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106
       System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +108
       FormationEducation_Sacraments_FCForm.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\FormationEducation\Sacraments\Test.aspx.vb:70
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
    

    Monday, December 10, 2007 4:33 AM
  • Is the database located on the web server? What type of authentication are you using for the web app? Is the web app enabled for impersonation?

     

    Monday, December 10, 2007 1:34 PM
  • Hi Paul!

    The database is located in the database and I am using the authentication for Internet (Site Administration Tool). 
    What do you mean with Impersonation?
    The IT guy changed some settings in the database. Now he is receiving another error message but stops at the same line 70 (updateCommand.ExecuteNonQuery()). The error is type mismatch. Maybe it could be the parameters values. All of them are coming from controls that provide strings except one that is Data/Time Type. Should I use the CType to convert each parameter? Example... CType(TextBox1, String), or ... CType(DataTime.Now(), Data). Please see the coding in the previous code. 

    Thanks
    Monday, December 10, 2007 2:04 PM
  • Did you mean the database is located on the web server?

     

    Your ASP.NET can be configured for different types of authentication using IIS. The more common ones are Anonymous, BASIC and Integrated NT. Which one(s) are you using?

     

    There is a setting in the web.config file to enable impersonation. If you haven't made any changes then it should be disabled.

     

    With respect to your other error I would make certain to specify the data types for each of your parameter objects. If I had to guess I would say that the date parameter is probably causing the problem.

     

    Code Block

    OLEDBCommand.Parameters.Add("", Data.OleDb.OleDbType.Date)

    OLEDBCommand.Parameters.Add("", Data.OleDb.OleDbType.VarWChar, 50)

     

    Monday, December 10, 2007 2:30 PM
  • Dear Paul:
    Thank you so much for your suggestion.
    I know that the problem to the UPDATE query is the Date and Time parameter. I tested without this parameter and the database was updated.
    I saw an article about creating your own Date and Time Parameter by Scott Mitchell and that is what I think I need to resolve the problem. Now I have this question: How do I create the skmParameters Visual Basic Control Project of Scott's example (see article below)? What file should I used to create this project? Web User Control?
    And another thing: How can I reference this Parameter in the following syntax line to fill a Parameter Collection:

    updateparamCollection.Add(New OleDbParameter("TimeofSignUp", TodayParameter?))

    I would really appreciate if you help me with this.
    Maybe you have another suggestion!!!

    Thank you,
    Hugo

     

    Creating a Parameter Control to Return the Current Date and Time
    In certain scenarios we want to have the current date and time inserted into a particular date/time value. For example, imagine that we had an Employees database table that, among other columns, had a HireDate column. When adding a new employee through the website, we might want to have the HireDate value be today's date and time. Or we may want to have a data source control that returns all employees hired today. Unfortunately, there's no built-in Parameter control that returns the current date and time, meaning we have to have this parameter value set programmatically.

    Let's examine how to create our own TodayParameter control that returns the current date (and, optionally, the time). This parameter was inspired by Eilon Lipton's Custom Parameters for Data Sources blog entry. The code we'll be examining is in VB; Eilon's blog entry provides similar functionality, but is written in C#.

    In the download you'll find a Visual Studio 2005 Solution that includes two projects: skmParameters, a Visual Basic Control Library project, and TestWebsite, which is a file system-based website for testing skmParameters. The TodayParameter.vb file in skmParameters includes the code for the TodayParameter control. In the simplest form, we could create the TodayParameter control so that it blindly returns the current date and time:

    Imports System.ComponentModel
    Imports System.Web.UI.WebControls

    Public Class TodayParameter
        Inherits Parameter

        Protected Overrides Function Evaluate(ByVal context As System.Web.HttpContext, ByVal control As System.Web.UI.Control) As Object
           Return DateTime.Now
        End Function
    End Class

    That's all there is to it! Of course without the Clone() method, the TodayParameter control will need to be modified through the declarative syntax (as there will be no design-time support), but it meets the minimum requirements. To use this parameter in an ASP.NET page, you'd first need to add a reference to the project (or drop the DLL from the project into the website's /bin folder), and then "register" the control (either at the page level or in Web.config). At the page level, this can be accomplished by using the <% @Register %> directive like so:

    <%@ Register Assembly="skmParameters" Namespace="skmParameters" TagPrefix="skm" %>

    Then, in one of the SqlDataSource or ObjectDataSources parameters collections, the TodayParameter control can be used. In the download at the end of this article you'll find a demo page (TodayParameter.aspx) that provides a DetailsView that allows new employees to be added to the Employees table. When adding a new employee, the HireDate value is determined via the TodayParameter control (resulting in the current date/time). The syntax for the SqlDataSource is as follows:

    <aspTongue TiedqlDataSource ID="EmployeesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
       InsertCommand="INSERT INTO [Employees] ([Name], [Salary], [HireDate]) VALUES (@Name, @Salary, @HireDate)"
       SelectCommand="SELECT [EmployeeID], [Name], [Salary], [HireDate] FROM [Employees]">
       <InsertParameters>
          <aspStick out tonguearameter Name="Name" Type="String" />
          <aspStick out tonguearameter Name="Salary" Type="Decimal" />
          <skm:TodayParameter Name="HireDate" />
       </InsertParameters>
    </aspTongue TiedqlDataSource>

    Tuesday, December 11, 2007 5:51 AM
  • This control isn't really necessary for what you're trying to do and it's just adding baggage to your application. Plus, it appears to be designed to work with SQL Server. Access doesn't support "named" parameters, which are used in the above example.

     

    It would be much easier just to use DateTime.Now directly.

    Tuesday, December 11, 2007 1:51 PM
  • Thank you so much for your prompt reply.
    I have already tried the DateTime.Now. The following is the line of code that is giving me problem and I have tried to convert to Date and Time as well:

    updateparamCollection.Add(New OleDbParameter("TimeofSignUp", DateTime.Now()))

    Do you have any suggestion on how to code this line so I can load the Date and Time to the parameter collection and update the database? For the rest of the parameters, I am using CType("NameofParameter", String) because they are all string.

    This is my first ASP.NET Page dealing with database. Thank you for the patience!!!

    Hugo

    Tuesday, December 11, 2007 3:03 PM