accessdatasouce1.update using VB code in Asp.net

Dear Mr

I have problem , I add Button in Edit templete in form view1 and I put command accessdatasouce1.update . I find it is not working

I found No update happend and also No error I get it , I expect the command should be run from Accessdatasouce updatecommand specified in Aspx

please need your help , what is the missing ?

See the real code

ASPX code
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
 DataFile="~/App_Data/DB.accdb"
 DeleteCommand="DELETE FROM [cargo] WHERE [código] = ?"
 InsertCommand="INSERT INTO [cargo] ([código], [cargo]) VALUES (?, ?)"
 SelectCommand="SELECT * FROM [cargo]"
 UpdateCommand="UPDATE [cargo] SET [cargo] = ? WHERE [código] = ?">
 <DeleteParameters>
 <asp:Parameter Name="código" Type="Int32" />
 </DeleteParameters>
 <InsertParameters>
 <asp:Parameter Name="código" Type="Int32" />
 <asp:Parameter Name="cargo" Type="String" />
 </InsertParameters>
 <UpdateParameters>
 <asp:Parameter Name="cargo" Type="String" />
 <asp:Parameter Name="código" Type="Int32" />
 </UpdateParameters>
 </asp:AccessDataSource>
 <asp:FormView ID="FormView1" runat="server" DataKeyNames="código"
 DataSourceID="AccessDataSource1">
 <EditItemTemplate>
 código:
 <asp:Label ID="códigoLabel1" runat="server" Text='<%# Eval("código") %>' />
 
 cargo:
 <asp:TextBox ID="cargoTextBox" runat="server" Text='<%# Bind("cargo") %>' />
 
 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
 CommandName="Update" Text="Update" />
  <asp:LinkButton ID="UpdateCancelButton" runat="server"
 CausesValidation="False" CommandName="Cancel" Text="Cancel" />
  <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
 Text="Button" />
 </EditItemTemplate>
 <InsertItemTemplate>
 cargo:
 <asp:TextBox ID="cargoTextBox" runat="server" Text='<%# Bind("cargo") %>' />
 
 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
 CommandName="Insert" Text="Insert" />
  <asp:LinkButton ID="InsertCancelButton" runat="server"
 CausesValidation="False" CommandName="Cancel" Text="Cancel" />
 </InsertItemTemplate>
 <ItemTemplate>
 código:
 <asp:Label ID="códigoLabel" runat="server" Text='<%# Eval("código") %>' />
 
 cargo:
 <asp:Label ID="cargoLabel" runat="server" Text='<%# Bind("cargo") %>' />
 
 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False"
 CommandName="Edit" Text="Edit" />
  <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False"
 CommandName="Delete" Text="Delete" />
  <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False"
 CommandName="New" Text="New" />
 </ItemTemplate>
 </asp:FormView>

VB :

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        AccessDataSource1.Update()
        FormView1.ChangeMode(FormViewMode.ReadOnly)

End Sub

Re: accessdatasouce1.update using VB code in Asp.net

Need your reply

Re: accessdatasouce1.update using VB code in Asp.net

Your code looks good, but there's only one thing wrong. The AccessDatasource control uses the Jet Oledb 4.0 driver, which means that it won't work with Access 2007 and higher (*.accdb) files. In order to make it work, convert your database to an Access 2003 file (*.mdb), or use a SqlDataSource instead

Re: accessdatasouce1.update using VB code in Asp.net

I change from ACCDB to MDB and I get Exactlly the same result

<asp:AccessDataSource ID="AccessDataSource2" runat="server"
 DataFile="~/App_Data/Form_ContinuousSimulated.mdb"
 DeleteCommand="DELETE FROM [T_Trains] WHERE [TrainID] = ?"
 InsertCommand="INSERT INTO [T_Trains] ([TrainID], [TrainCode], [ColorValue]) VALUES (?, ?, ?)"
 SelectCommand="SELECT * FROM [T_Trains]"
 UpdateCommand="UPDATE [T_Trains] SET [TrainCode] = ?, [ColorValue] = ? WHERE [TrainID] = ?">
 <DeleteParameters>
 <asp:Parameter Name="TrainID" Type="Int32" />
 </DeleteParameters>
 <InsertParameters>
 <asp:Parameter Name="TrainID" Type="Int32" />
 <asp:Parameter Name="TrainCode" Type="String" />
 <asp:Parameter Name="ColorValue" Type="Int32" />
 </InsertParameters>
 <UpdateParameters>
 <asp:Parameter Name="TrainCode" Type="String" />
 <asp:Parameter Name="ColorValue" Type="Int32" />
 <asp:Parameter Name="TrainID" Type="Int32" />
 </UpdateParameters>
 </asp:AccessDataSource>
 
 <asp:FormView ID="FormView2" runat="server" DataKeyNames="TrainID"
 DataSourceID="AccessDataSource2">
 <EditItemTemplate>
 TrainID:
 <asp:Label ID="TrainIDLabel1" runat="server" Text='<%# Eval("TrainID") %>' />
 
 TrainCode:
 <asp:TextBox ID="TrainCodeTextBox" runat="server"
 Text='<%# Bind("TrainCode") %>' />
 
 ColorValue:
 <asp:TextBox ID="ColorValueTextBox" runat="server"
 Text='<%# Bind("ColorValue") %>' />
 
 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
 CommandName="Update" Text="Update" />
  <asp:LinkButton ID="UpdateCancelButton" runat="server"
 CausesValidation="False" CommandName="Cancel" Text="Cancel" />
 <asp:Button ID="Button2" runat="server" onclick="Button2_Click1"
 Text="Button" />
 </EditItemTemplate>
 <InsertItemTemplate>
 TrainCode:
 <asp:TextBox ID="TrainCodeTextBox" runat="server"
 Text='<%# Bind("TrainCode") %>' />
 
 ColorValue:
 <asp:TextBox ID="ColorValueTextBox" runat="server"
 Text='<%# Bind("ColorValue") %>' />
 
 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
 CommandName="Insert" Text="Insert" />
  <asp:LinkButton ID="InsertCancelButton" runat="server"
 CausesValidation="False" CommandName="Cancel" Text="Cancel" />
 </InsertItemTemplate>
 <ItemTemplate>
 TrainID:
 <asp:Label ID="TrainIDLabel" runat="server" Text='<%# Eval("TrainID") %>' />
 
 TrainCode:
 <asp:Label ID="TrainCodeLabel" runat="server" Text='<%# Bind("TrainCode") %>' />
 
 ColorValue:
 <asp:Label ID="ColorValueLabel" runat="server"
 Text='<%# Bind("ColorValue") %>' />
 
 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False"
 CommandName="Edit" Text="Edit" />
  <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False"
 CommandName="Delete" Text="Delete" />
  <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False"
 CommandName="New" Text="New" />
 </ItemTemplate>
 </asp:FormView>

Protected Sub Button2_Click1(ByVal sender As Object, ByVal e As System.EventArgs)

        AccessDataSource2.Update()
        FormView2.ChangeMode(FormViewMode.ReadOnly)
    End Sub

How can I update using VB by using accessdatasource . updateparamenter.add ..

Re: accessdatasouce1.update using VB code in Asp.net

Hello ashrafedes

Since you've defined the Update statement as well as UpdateParameters. And you've put "Update" button inside the EditTemplate, You don't need to manually call AccessDataSource1.Update().

Of courseWell, if you want to manually do update, plz have a try like this:

AccessDataSource2.UpdateParameters["TrainCode"].DefaultValue = (FormView2.FindControl("TrainCodeTextBox") as TextBox).Text;
AccessDataSource2.UpdateParameters["ColorValue"].DefaultValue = (FormView2.FindControl("ColorValueTextBox") as TextBox).Text;
AccessDataSource2.UpdateParameters["TrainID"].DefaultValue = (FormView2.FindControl("TrainID") as Label).Text;
AccessDataSource2.Update();
FormView2.ChangeMode(FormViewMode.ReadOnly);

Re: accessdatasouce1.update using VB code in Asp.net

Decker Dong - MSFT

Since you've defined the Update statement as well as UpdateParameters. And you've put "Update" button inside the EditTemplate, You don't need to manually call AccessDataSource1.Update().

No he doesn't have to (however, this update is coded inside antother button), but this is NOT the reason the update fails. It is because Access 2007 files (.accdb) do not work with an AccessDataSource!

Re: accessdatasouce1.update using VB code in Asp.net

I already have Access Database .accdb , for Website , I can not change this Database , so what is the solution ?

Re: accessdatasouce1.update using VB code in Asp.net

I already gave you the 2 options in my first reply:

hans_v

In order to make it work, convert your database to an Access 2003 file (*.mdb), or use a SqlDataSource instead

But by the way, why can't you convert your Access 2007 (.accdb) file into a Access 2003 (.mdb) file? And are you sure the ACE 12.0 oledb driver is installed on you webserver as well?

Re: accessdatasouce1.update using VB code in Asp.net

By the way, I noticed something:

ashrafedes
;<asp:LinkButton ID="UpdateCancelButton" runat="server"
 CausesValidation="False" CommandName="Cancel" Text="Cancel" />
  <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
 Text="Button" />

In the onclik event of your cancel button you're calling the button_click event, in which you ccall the Update method of the datasource, so you're trying to update in the cancel button as well. I don't think this is what you want?

Re: accessdatasouce1.update using VB code in Asp.net

Look About This code

Protected Sub Button2_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connectionString As String = "provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + Page.Server.MapPath("App_Data\Form_ContinuousSimulated.mdb")

Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)

Dim cmd As System.Data.OleDb.OleDbCommand = conn.CreateCommand()

conn.Open()

cmd = New System.Data.OleDb.OleDbCommand("UPDATE [T_Trains] SET [TrainCode]=TrainCodex,[ColorValue]=ColorValuex WHERE [TrainID]=1", conn)

        cmd.Parameters.AddWithValue("TrainCodex", DirectCast(FormView2.FindControl("TrainCodeTextBox"), TextBox).Text)
        cmd.Parameters.AddWithValue("ColorValuex", CInt(DirectCast(FormView2.FindControl("ColorValueTextBox"), TextBox).Text))
        cmd.Parameters.AddWithValue("TrainIDx", DirectCast(FormView2.FindControl("TrainIDLabel1"), Label).Text)
        cmd.ExecuteNonQuery()

FormView2.ChangeMode(FormViewMode.[ReadOnly])

conn.Close()
End Sub

This make update manually by Connection string Defined right ?

Re: accessdatasouce1.update using VB code in Asp.net

ashrafedes

This make update manually by Connection string Defined right ?

Yes.

Some remarks.

You converted your database into an mdb, but you also using the new ACE 12.0 driver. This works, however, this dirver is not installed by defualt on a WIndows Serevr. So when using mdb files, you better use the Jet.Oledb.4.0 driver. And you can also use the DataDirectorty Directive for files located in your App_Data folder:

Dim connectionString As String = "provider=Microsoft.Jet.OleDb.4.0;data source=|DataDirectory|Form_ContinuousSimulated.mdb")

http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

And you're not using an Error handling, so when an error occurs, your connection doesn't close properly. When you're using another syntax, the connection will alwasy be closed and disposed properly, which is very important when using Access files:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access