impossible to update database

[davikokar]

Hallo,
I trying some asp.net but I encoured a quite strange problem. I have datagrid with edit column. When I update the fields I will get an error. I cannot delete neither. The only thing that works is the displaying of the data, all other interactions will not work.
The strange thing is that this happens only when I run the page locally on my computer with the localhost. If I upload the same files in my webserver everything works (almost) perfectly.
So I wonder: probably I missed some general settings or somethings else that I don't know.

I am working under win Xp professional, using VB.NET and a Microsoft Access 2003 Database.

Do you know where this problem could come from?

thanks

 

[link9]

The error is most likely permissions. Give the ASPNET user account modify permissions on the folder where your access file is stored.

Short of that, please provide the details of the exception.

The answer to getting answered -- faq855-2992

 

[davikokar]

hi link9,

I catched the exception, is:

"Operation must use an updateable query. " when i try to update or insert new data.

"Could not delete from specified tables. " when i try to delete.

these sentences they don't really suggest me something... if I use the same code in the real server it will work. Maybe it's a matter of permissions, but how can I set them properly?

thanks

 

[checkai]

looks like you should post the code you're using to do the update and delete.

 

[davikokar]

hi checkai

here below is the code... as I wrote beofore I don't think the problem is in the code (I took from an example in a handbook)... and it actually works when online. It doesn't work when I try it locally on my machine. Maybe there is something wrong with my IIS...

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
   'declare connection
    Dim Conn As New OleDbConnection( _
       "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\inetpub\[URL unfurl="true"]wwwroot\new1\banking.mdb")[/URL]
   
   sub Page_Load(obj as Object, e as EventArgs) 
      if Not Page.IsPostBack then
         FillDataGrid()
      end if
   end sub
   
   sub Submit(obj as object, e as eventargs)
      'insert new data
      dim i, j as integer
      dim params(7) as string
      dim strText as string
      dim blnGo as boolean = true
       
      j = 0
      
      for i = 0 to AddPanel.Controls.Count - 1
         if AddPanel.controls(i).GetType Is GetType(TextBox) then
            strText = Ctype(AddPanel.Controls(i), TextBox).Text
            if strText <> "" then
               params(j) = strText
            else
               blnGo = false
               lblMessage.Text = lblMessage.Text & "You forgot to enter " & _
                  "a value for " & AddPanel.Controls(i).ID & "<p>"
               lblMessage.Style("ForeColor") = "Red"
            end if
            j = j + 1
         end if
      next
       
      if not blnGo then
         exit sub
      end if
       
      dim strSQL as string = "INSERT INTO tblUsers " & _
         "(FirstName, LastName, Address, City, State, " & _
          "Zip, Phone) VALUES (" & _
         "'" & params(0) & "'," & _
         "'" & params(1) & "'," & _
         "'" & params(2) & "'," & _
         "'" & params(3) & "'," & _
         "'" & params(4) & "'," & _
         "'" & params(5) & "'," & _
         "'" & params(6) & "')"
         
      ExecuteStatement(strSQL)
      
      FillDataGrid()
   end sub
    
   sub dgData_Edit(obj as object, e as DataGridCommandEventArgs)
      FillDataGrid(e.Item.ItemIndex)
   end sub
    
   sub dgData_Delete(obj as object, e as DataGridCommandEventArgs)
      dim strSQL as string = "DELETE FROM tblUsers " & _
         "WHERE UserID = " & e.Item.ItemIndex + 1
       
      ExecuteStatement(strSQL)
       
      FillDataGrid()
   end sub
    
   sub dgData_Update(obj as object, e as DataGridCommandEventArgs)
      if UpdateDataStore(e) then
         FillDataGrid(-1)
      end if
   end sub
   
   sub dgData_Cancel(obj as object, e as DataGridCommandEventArgs)
      FillDataGrid(-1)
   end sub
    
   sub dgData_PageIndexChanged(obj as Object, e as DataGridPageChangedEventArgs)
      dgData.DataBind()
   end sub
    
   function UpdateDataStore(e as DataGridCommandEventArgs) _
      as boolean
       
      dim i,j as integer
      dim params(7) as string
      dim strText as string
      dim blnGo as boolean = true
      
      j = 0
      
      for i = 1 to e.Item.Cells.Count - 3
         strText = Ctype(e.Item.Cells(i).Controls(0), TextBox).Text
         if strText <> "" then
            params(j) = strText
            j = j + 1
         else
            blnGo = false
            lblMessage.Text = lblMessage.Text & "You forgot to enter " & _
               "a value<p>"
         end if
      next
      
      if not blnGo then
         return false
         exit function
      end if
       
      dim strSQL as string = "UPDATE tblUsers SET " & _
         "FirstName = '" & params(0) & "'," & _
         "LastName = '" & params(1) & "'," & _
         "Address = '" & params(2) & "'," & _
         "City = '" & params(3) & "'," & _
         "State = '" & params(4) & "'," & _
         "Zip = '" & params(5) & "'," & _
         "Phone = '" & params(6) & "'" & _
         " WHERE UserID = " & Ctype(e.Item.Cells(0).Controls(1), Label).text
      
      ExecuteStatement(strSQL)
      return blnGo
   end function
    
   sub FillDataGrid(Optional EditIndex as integer=-1)
      'open connection
      dim objCmd as new OleDbCommand _
         ("select * from tblUsers", Conn)
      dim objReader as OleDbDataReader
      
      try
         objCmd.Connection.Open()
         objReader = objCmd.ExecuteReader()
      catch ex as Exception
            lblMessage.Text = ex.Message
            
      end try
      
      dgData.DataSource = objReader
      if not EditIndex.Equals(Nothing) then
         dgData.EditItemIndex = EditIndex
      end if
      
      dgData.DataBind()
       
      objReader.Close
      objCmd.Connection.Close()
       
   end sub
    
   function ExecuteStatement(strSQL) 
      dim objCmd as new OleDbCommand(strSQL, Conn)
      
      try
         objCmd.Connection.Open()
         objCmd.ExecuteNonQuery()
      catch ex as Exception
         lblMessage.Text = ex.Message
      end try
      
      objCmd.Connection.Close()
   end function
</script>

<html><body>
   <asp:Label id="lblMessage" runat="server"/>
      
   <form runat="server">
      <asp:DataGrid id="dgData" runat="server" 
         BorderColor="black" GridLines="Vertical" 
         cellpadding="4" cellspacing="0" width="100%"
         AutoGenerateColumns="False"
         OnDeleteCommand="dgData_Delete"
         OnEditCommand="dgData_Edit"
         OnCancelCommand="dgData_Cancel"
         OnUpdateCommand="dgData_Update"
         OnPageIndexChanged="dgData_PageIndexChanged" >

         <Columns>
            <asp:TemplateColumn HeaderText="ID">
              <ItemTemplate>
                 <asp:Label id="Name" runat="server" 
                    Text='<%# Container.DataItem("UserID") %>'/>
              </ItemTemplate>
            </asp:TemplateColumn>
            
            <asp:BoundColumn HeaderText="FirstName" DataField="FirstName" />
            <asp:BoundColumn HeaderText="LastName" DataField="LastName" />
            <asp:BoundColumn HeaderText="Address" DataField="Address" />
            <asp:BoundColumn HeaderText="City" DataField="City"/>
            <asp:BoundColumn HeaderText="State" DataField="State" />
            <asp:BoundColumn HeaderText="Zip" DataField="Zip" />
            <asp:BoundColumn HeaderText="Phone" DataField="Phone"/>
            
            <asp:EditCommandColumn
               EditText="Edit"
               CancelText="Cancel"
               UpdateText="Update"
               HeaderText="Edit"/>
            
            <asp:ButtonColumn HeaderText="" text="Delete"
               CommandName="delete" />
         </Columns>
      </asp:DataGrid><p>

      <asp:Panel id="AddPanel" runat="server">
         <table>
         <tr>
            <td width="100" valign="top">
               First and last name:
            </td>
            <td width="300" valign="top">
               <asp:TextBox id="tbFName" runat="server"/>
               <asp:TextBox id="tbLName" runat="server"/>
            </td>
         </tr>
         <tr>
            <td valign="top">
               Address: 
            </td>
            <td valign="top">
               <asp:TextBox id="tbAddress" 
                  runat="server" />              
            </td>
         </tr>
         <tr>
            <td valign="top">
               City, State, ZIP: 
            </td>
            <td valign="top">
               <asp:TextBox id="tbCity" 
                  runat="server" />,
               <asp:TextBox id="tbState" runat="server"
                  size=2 />&nbsp;
               <asp:TextBox id="tbZIP" runat="server"
                  size=5 />
            </td>
         </tr>
         <tr>
            <td valign="top">
               Phone:          
            </td>
            <td valign="top">
               <asp:TextBox id="tbPhone" runat="server"
                  size=11 /><p>
            </td>
         </tr>
         <tr>
            <td colspan="2" valign="top" align="right">
               <asp:Button id="btSubmit" runat="server" 
                  text="Add"
                  OnClick="Submit" />
            </td>
         </tr>
         </table>
      </asp:Panel>
   </form>
</body></html>

 

[link9]

Updateable query error w/ access is an NTFS permissions error.

some info on how to set permissions:

http://www.google.com/search?client...ons+on+a+folder+windows+xp&btnG=Google+Search

The answer to getting answered -- faq855-2992