locked
Delete Record Not Working RRS feed

  • Question

  • User-1081423463 posted

    Hi,

    I currently have two pages, one to show current drivers which lets me add records, delete etc without any problems. I then created a show current cars page based on the code from the current drivers. I have got the add records to work perfectly but the delete is not working and im not entirely sure. I get the error;

    No value given for one or more required parameters.

    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: No value given for one or more required parameters.

    Code:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"

    DataFile="~\CarDatabase.mdb"

    DeleteCommand="DELETE FROM [tblCars] WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))"

    InsertCommand="INSERT INTO [tblCars] ([Name], [Make], [Model], [EngineSize], [Color], [DateBrought], [SoldDate], [FirstRegistered], [VINNumber], [StartingMileage], [ClosingMileage], [Comments]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    SelectCommand="SELECT * FROM [tblCars]"

    UpdateCommand="UPDATE [tblCars] SET [Make] = ?, [Model] = ?, [EngineSize] = ?, [Color] = ?, [DateBrought] = ?, [SoldDate] = ?, [EngineSize] = ?, [FirstRegistered] = ?, [VINNumber] = ?, [StartingMileage] = ?, [ClosingMileage] = ?, [Comments] = ? WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))">

    <DeleteParameters>

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    </DeleteParameters>

    <InsertParameters>

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    </InsertParameters>

    <UpdateParameters>

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    </UpdateParameters>

    </asp:AccessDataSource>

    Thanks in advance,

    Chris

     

     




    Thursday, November 24, 2011 9:54 AM

Answers

  • User-1199946673 posted

    I managed to get it working by adding another "<asp:Parameter Name="Name" Type="String" />" - not sure if this is to do with the fact that the Name field is the primary key.

    It may work, but you don't seem to understand exactly what you're doing!

    DeleteCommand="DELETE FROM [tblCars] WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))"

    In this command, you're using 2 parameters. However, in you deleteparameter collection you specify 13 parameters. This doesn't make sence. Also, your WHERE clause suggests that if the Parameter Name IS NULL, all records in the tblCars need to be deleted. I doubt that this is what you intend to do? This WHERE clause syntax needs to be used in the SelectCommand, to select all records if the parameter is Null. However, this will only work when you set the CancelSelectOnNullParameter Property of the DataSource to False (Its default value is true). More info on Optional Parameters, read this

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    In this article you'll see that you can use Named Parameters in Access as well, it makes your Commands more readable, and you don't need to specify parameters multiple times if you want to use them more than once. Another remark is the plave where to put your database. You put your database in the root of the website, where it can be downloaded. To avoid this, put your database in the App_Data folder, where it is protected:

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

    When you understand all this, this is how your DataSource should look like:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
     DataFile="~\App_Data\CarDatabase.mdb"
     SelectCommand="SELECT * FROM [tblCars]"
     DeleteCommand="DELETE FROM [tblCars] WHERE [Name] = @Name"
     InsertCommand="INSERT INTO [tblCars] ([Name], [Make], [Model], [EngineSize], [Color], [DateBrought], [SoldDate], [FirstRegistered], [VINNumber], [StartingMileage], [ClosingMileage], [Comments]) VALUES (@Name, @Make, @Model, @EngineSize, @Color, @DateBrought, @SoldDate, @FirstRegistered, @VINNumber, @StartingMileage, @ClosingMileage, @Comments)"
     UpdateCommand="UPDATE [tblCars] SET [Make] = @Make, [Model] = @Model, [EngineSize] = @EngineSize, [Color] = @Color, [DateBrought] = @DateBrought, [SoldDate] = @SoldDate, [FirstRegistered] = @FirstRegistered, [VINNumber] = @VINNumber, [StartingMileage] = @StartingMileage, [ClosingMileage] = @ClosingMileage, [Comments] = @Comments WHERE [Name] = @Name">
     <DeleteParameters>
      <asp:Parameter Name="Name" Type="String" />
     </DeleteParameters>
     <InsertParameters>
      <asp:Parameter Name="Name" Type="String" />
      <asp:Parameter Name="Make" Type="String" />
      <asp:Parameter Name="Model" Type="String" />
      <asp:Parameter Name="EngineSize" Type="String" />
      <asp:Parameter Name="Color" Type="String" />
      <asp:Parameter Name="DateBrought" Type="String" />
      <asp:Parameter Name="SoldDate" Type="String" />
      <asp:Parameter Name="FirstRegistered" Type="String" />
      <asp:Parameter Name="VINNumber" Type="String" />
      <asp:Parameter Name="StartingMileage" Type="String" />
      <asp:Parameter Name="ClosingMileage" Type="String" />
      <asp:Parameter Name="Comments" Type="String" />
     </InsertParameters>
     <UpdateParameters>
      <asp:Parameter Name="Make" Type="String" />
      <asp:Parameter Name="Model" Type="String" />
      <asp:Parameter Name="EngineSize" Type="String" />
      <asp:Parameter Name="Color" Type="String" />
      <asp:Parameter Name="DateBrought" Type="String" />
      <asp:Parameter Name="SoldDate" Type="String" />
      <asp:Parameter Name="FirstRegistered" Type="String" />
      <asp:Parameter Name="VINNumber" Type="String" />
      <asp:Parameter Name="StartingMileage" Type="String" />
      <asp:Parameter Name="ClosingMileage" Type="String" />
      <asp:Parameter Name="Comments" Type="String" />
      <asp:Parameter Name="Name" Type="String" />
     </UpdateParameters>
    </asp:AccessDataSource>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 25, 2011 7:55 PM

All replies

  • User-1081423463 posted

    Pls delete, fixed now,.

    Thursday, November 24, 2011 10:56 AM
  • User-1199946673 posted

    Pls delete, fixed now,.

    Perhaps you can show the code that works also, because you marked your question as the answer, but I'm sure it will not work?

    Thursday, November 24, 2011 5:08 PM
  • User-1081423463 posted

    Hi, unmarked as you are right that was not the correct code but couldn't find a delete button. I managed to get it working by adding another "<asp:Parameter Name="Name" Type="String" />" - not sure if this is to do with the fact that the Name field is the primary key.

    It seems to Delete and Add new records without any errors now. I know the code is pretty messy, but at present I am happy it just works. Full code below;

    <%@ Page Title="" Language="VB" Theme="HPC_Theme" MasterPageFile="~/MasterPage.master"%>

    <asp:Content ID="Content1" runat="server"

    contentplaceholderid="ContentPlaceHolder_GV">

    <asp:FormView ID="frmCallLog" runat="server" DataKeyNames="Name"

    DataSourceID="AccessDataSource1" AllowPaging="True">

    <ItemTemplate>

    <table style="width: 570px; border-style: solid; border-width: 1px; border-color: Black">

    <tr>

    <td align="center" colspan="4"

    style="border: 1px solid #000000; font-weight: bold; background-color: #C0C0C0;">

    Car Information - Test

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold">

    Driver Name:

    </td>

    <td>

    <asp:TextBox ID="NameTextBox" runat="server" Enabled="false"

    Text='<%# Bind("Name") %>' Width="150px" />

    </td>

    <td>

    &nbsp;</td>

    <td>

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold">

    Make:

    </td>

    <td>

    <asp:TextBox ID="MakeTextBox" runat="server" Enabled="false"

    Text='<%# Bind("Make") %>' Width="150px" />

    </td>

    <td>

    &nbsp;</td>

    <td>

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Model:

    </td>

    <td>

    <asp:TextBox ID="ModelTextBox" runat="server" Enabled="false"

    Text='<%# Bind("Model") %>' Width="150px" />

    </td>

    <td colspan="2" style="vertical-align: top;">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Engine Size:

    </td>

    <td>

    <asp:TextBox ID="EngineSizeTextBox" runat="server" Enabled="false"

    Text='<%# Bind("EngineSize") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Colour:

    </td>

    <td>

    <asp:TextBox ID="ColorTextBox" runat="server" Enabled="false"

    Text='<%# Bind("Color") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Date Purchased:

    </td>

    <td>

    <asp:TextBox ID="DateBroughtTextBox" runat="server" Enabled="false"

    Text='<%# Bind("DateBrought") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Date Sold:

    </td>

    <td>

    <asp:TextBox ID="SoldDateTextBox" runat="server" Enabled="false"

    Text='<%# Bind("SoldDate") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    First Registered:

    </td>

    <td>

    <asp:TextBox ID="FirstRegisteredTextBox" runat="server" Enabled="false"

    Text='<%# Bind("FirstRegistered") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    VIN Number:

    </td>

    <td>

    <asp:TextBox ID="VINNumberTextBox" runat="server" Enabled="false"

    Text='<%# Bind("VINNumber") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Starting Mileage:

    </td>

    <td>

    <asp:TextBox ID="StartingMileageTextBox" runat="server" Enabled="false"

    Text='<%# Bind("StartingMileage") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Closing Mileage:

    </td>

    <td>

    <asp:TextBox ID="ClosingMileageTextBox" runat="server" Enabled="false"

    Text='<%# Bind("ClosingMileage") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Comments:

    </td>

    <td>

    <asp:TextBox ID="CommentsTextBox" runat="server" Enabled="false"

    Text='<%# Bind("Comments") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td>

    &nbsp;

    </td>

    <td>

    &nbsp;

    </td>

    </tr>

    </tr>

    <tr>

    <td>

    <asp:Button ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="Add New Record" />

    </td>

    <td>

    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete"

    OnClientClick="return confirm('Are you certain you want to delete?');"

    Text="Delete Record" />

    </td>

    </tr>

    </table>

    </ItemTemplate>

    <EmptyDataTemplate>

    <table style="width: 570px; border-style: solid; border-width: 1px; border-color: Black">

    <tr>

    <td align="center" colspan="4"

    style="border: 1px solid #000000; font-weight: bold; background-color: #C0C0C0;">

    Car Information - Test

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold">

    Driver Name:

    </td>

    <td>

    <asp:TextBox ID="NameTextBox" runat="server"

    Text='<%# Bind("Name") %>' Width="150px" />

    </td>

    <td>

    &nbsp;</td>

    <td>

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold">

    Make:

    </td>

    <td>

    <asp:TextBox ID="MakeTextBox" runat="server"

    Text='<%# Bind("Make") %>' Width="150px" />

    </td>

    <td>

    &nbsp;</td>

    <td>

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Model:

    </td>

    <td>

    <asp:TextBox ID="ModelTextBox" runat="server"

    Text='<%# Bind("Model") %>' Width="150px" />

    </td>

    <td colspan="2" style="vertical-align: top;">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Engine Size:

    </td>

    <td>

    <asp:TextBox ID="EngineSizeTextBox" runat="server"

    Text='<%# Bind("EngineSize") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Colour:

    </td>

    <td>

    <asp:TextBox ID="ColorTextBox" runat="server"

    Text='<%# Bind("Color") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Date Purchased:

    </td>

    <td>

    <asp:TextBox ID="DateBroughtTextBox" runat="server"

    Text='<%# Bind("DateBrought") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Date Sold:

    </td>

    <td>

    <asp:TextBox ID="SoldDateTextBox" runat="server"

    Text='<%# Bind("SoldDate") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    First Registered:

    </td>

    <td>

    <asp:TextBox ID="FirstRegisteredTextBox" runat="server"

    Text='<%# Bind("FirstRegistered") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    VIN Number:

    </td>

    <td>

    <asp:TextBox ID="VINNumberTextBox" runat="server"

    Text='<%# Bind("VINNumber") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Starting Mileage:

    </td>

    <td>

    <asp:TextBox ID="StartingMileageTextBox" runat="server"

    Text='<%# Bind("StartingMileage") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Closing Mileage:

    </td>

    <td>

    <asp:TextBox ID="ClosingMileageTextBox" runat="server"

    Text='<%# Bind("ClosingMileage") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top;">

    Comments:

    </td>

    <td>

    <asp:TextBox ID="CommentsTextBox" runat="server"

    Text='<%# Bind("Comments") %>' Width="150px" />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td>

    &nbsp;

    </td>

    <td>

    &nbsp;

    </td>

    </tr>

    </tr>

    <tr>

    <td>

    <asp:Button ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="Add New Record" />

    </td>

    <td>

    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete"

    OnClientClick="return confirm('Are you certain you want to delete?');"

    Text="Delete Record" />

    </td>

    </tr>

    </table>

    </EmptyDataTemplate>

    <PagerTemplate>

    <center> <table>

    <tr>

    <td><asp:Button ID="FirstButton" CommandName="Page" CommandArgument="First" Text="First Record" RunAt="server"/></td>

    <td><asp:Button ID="PrevButton" CommandName="Page" CommandArgument="Prev" Text="Previous Record" RunAt="server"/></td>

    <td><asp:Button ID="NextButton" CommandName="Page" CommandArgument="Next" Text="Next Record" RunAt="server"/></td>

    <td><asp:Button ID="LastButton" CommandName="Page" CommandArgument="Last" Text="Last Record" RunAt="server"/></td>

    </tr>

    </table></center>

    </PagerTemplate>

    <InsertItemTemplate>

    <table style="width: 570px; border-style: solid; border-width: 1px; border-color: Black">

    <tr>

    <td align="center" colspan="4"

    style="border: 1px solid #000000; font-weight: bold; background-color: #C0C0C0;">

    Car Information - Test

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; width: 254px;">

    Driver Name:

    </td>

    <td>

    <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />

    </td>

    <td>

    &nbsp;</td>

    <td>

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; width: 254px;">

    Make:

    </td>

    <td>

    <asp:TextBox ID="MakeTextBox" runat="server"

    Text='<%# Bind("Make") %>' />

    </td>

    <td>

    &nbsp;</td>

    <td>

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px; height: 19px;">

    Model:

    </td>

    <td style="height: 19px">

    <asp:TextBox ID="ModelTextBox" runat="server"

    Text='<%# Bind("Model") %>' />

    </td>

    <td colspan="2" style="vertical-align: top; height: 19px;">

    </td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Engine Size:

    </td>

    <td>

    <asp:TextBox ID="EngineSizeTextBox" runat="server"

    Text='<%# Bind("EngineSize") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Colour:

    </td>

    <td>

    <asp:TextBox ID="ColorTextBox" runat="server"

    Text='<%# Bind("Color") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Date Purchased:

    </td>

    <td>

    <asp:TextBox ID="DateBroughtTextBox" runat="server"

    Text='<%# Bind("DateBrought") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Date Sold:

    </td>

    <td>

    <asp:TextBox ID="SoldDateTextBox" runat="server"

    Text='<%# Bind("SoldDate") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    First Registered:

    </td>

    <td>

    <asp:TextBox ID="FirstRegisteredTextBox" runat="server"

    Text='<%# Bind("FirstRegistered") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    VIN Number:

    </td>

    <td>

    <asp:TextBox ID="VINNumberTextBox" runat="server"

    Text='<%# Bind("VINNumber") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Starting Mileage:

    </td>

    <td>

    <asp:TextBox ID="StartingMileageTextBox" runat="server"

    Text='<%# Bind("StartingMileage") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Closing Mileage:

    </td>

    <td>

    <asp:TextBox ID="ClosingMileageTextBox" runat="server"

    Text='<%# Bind("ClosingMileage") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="font-weight: bold; vertical-align: top; width: 254px;">

    Comments:

    </td>

    <td>

    <asp:TextBox ID="CommentsTextBox" runat="server"

    Text='<%# Bind("Comments") %>' />

    </td>

    <td style="vertical-align: top; text-align: left">

    &nbsp;</td>

    </tr>

    <tr align="left">

    <td style="width: 254px">

    &nbsp;

    </td>

    <td>

    &nbsp;

    </td>

    </tr>

    </tr>

    <tr>

    <td style="width: 254px">

    <asp:Button ID="InsertButton" runat="server" CommandName="Insert"

    Text="Insert Record" />

    </td>

    <td>

    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel"

    Text="Clear Entry " />

    </td>

    </tr>

    </table>

    </InsertItemTemplate>

    </asp:FormView>

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"

    DataFile="~\CarDatabase.mdb"

    DeleteCommand="DELETE FROM [tblCars] WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))"

    InsertCommand="INSERT INTO [tblCars] ([Name], [Make], [Model], [EngineSize], [Color], [DateBrought], [SoldDate], [FirstRegistered], [VINNumber], [StartingMileage], [ClosingMileage], [Comments]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    SelectCommand="SELECT * FROM [tblCars]"

    UpdateCommand="UPDATE [tblCars] SET [Make] = ?, [Model] = ?, [EngineSize] = ?, [Color] = ?, [DateBrought] = ?, [SoldDate] = ?, [FirstRegistered] = ?, [VINNumber] = ?, [StartingMileage] = ?, [ClosingMileage] = ?, [Comments] = ? WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))">

    <DeleteParameters>

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    </DeleteParameters>

    <InsertParameters>

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Name" Type="String" />

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    </InsertParameters>

    <UpdateParameters>

    <asp:Parameter Name="Make" Type="String" />

    <asp:Parameter Name="Model" Type="String" />

    <asp:Parameter Name="EngineSize" Type="String" />

    <asp:Parameter Name="Color" Type="String" />

    <asp:Parameter Name="DateBrought" Type="String" />

    <asp:Parameter Name="SoldDate" Type="String" />

    <asp:Parameter Name="FirstRegistered" Type="String" />

    <asp:Parameter Name="VINNumber" Type="String" />

    <asp:Parameter Name="StartingMileage" Type="String" />

    <asp:Parameter Name="ClosingMileage" Type="String" />

    <asp:Parameter Name="Comments" Type="String" />

    <asp:Parameter Name="Name" Type="String" />

    </UpdateParameters>

    </asp:AccessDataSource>

    </asp:Content>

    <asp:Content ID="Content2" runat="server"

    contentplaceholderid="ContentPlaceHolder1">

    </asp:Content>



    Friday, November 25, 2011 4:35 AM
  • User-1199946673 posted

    I managed to get it working by adding another "<asp:Parameter Name="Name" Type="String" />" - not sure if this is to do with the fact that the Name field is the primary key.

    It may work, but you don't seem to understand exactly what you're doing!

    DeleteCommand="DELETE FROM [tblCars] WHERE (([Name] = ?) OR ([Name] IS NULL AND ? IS NULL))"

    In this command, you're using 2 parameters. However, in you deleteparameter collection you specify 13 parameters. This doesn't make sence. Also, your WHERE clause suggests that if the Parameter Name IS NULL, all records in the tblCars need to be deleted. I doubt that this is what you intend to do? This WHERE clause syntax needs to be used in the SelectCommand, to select all records if the parameter is Null. However, this will only work when you set the CancelSelectOnNullParameter Property of the DataSource to False (Its default value is true). More info on Optional Parameters, read this

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    In this article you'll see that you can use Named Parameters in Access as well, it makes your Commands more readable, and you don't need to specify parameters multiple times if you want to use them more than once. Another remark is the plave where to put your database. You put your database in the root of the website, where it can be downloaded. To avoid this, put your database in the App_Data folder, where it is protected:

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

    When you understand all this, this is how your DataSource should look like:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
     DataFile="~\App_Data\CarDatabase.mdb"
     SelectCommand="SELECT * FROM [tblCars]"
     DeleteCommand="DELETE FROM [tblCars] WHERE [Name] = @Name"
     InsertCommand="INSERT INTO [tblCars] ([Name], [Make], [Model], [EngineSize], [Color], [DateBrought], [SoldDate], [FirstRegistered], [VINNumber], [StartingMileage], [ClosingMileage], [Comments]) VALUES (@Name, @Make, @Model, @EngineSize, @Color, @DateBrought, @SoldDate, @FirstRegistered, @VINNumber, @StartingMileage, @ClosingMileage, @Comments)"
     UpdateCommand="UPDATE [tblCars] SET [Make] = @Make, [Model] = @Model, [EngineSize] = @EngineSize, [Color] = @Color, [DateBrought] = @DateBrought, [SoldDate] = @SoldDate, [FirstRegistered] = @FirstRegistered, [VINNumber] = @VINNumber, [StartingMileage] = @StartingMileage, [ClosingMileage] = @ClosingMileage, [Comments] = @Comments WHERE [Name] = @Name">
     <DeleteParameters>
      <asp:Parameter Name="Name" Type="String" />
     </DeleteParameters>
     <InsertParameters>
      <asp:Parameter Name="Name" Type="String" />
      <asp:Parameter Name="Make" Type="String" />
      <asp:Parameter Name="Model" Type="String" />
      <asp:Parameter Name="EngineSize" Type="String" />
      <asp:Parameter Name="Color" Type="String" />
      <asp:Parameter Name="DateBrought" Type="String" />
      <asp:Parameter Name="SoldDate" Type="String" />
      <asp:Parameter Name="FirstRegistered" Type="String" />
      <asp:Parameter Name="VINNumber" Type="String" />
      <asp:Parameter Name="StartingMileage" Type="String" />
      <asp:Parameter Name="ClosingMileage" Type="String" />
      <asp:Parameter Name="Comments" Type="String" />
     </InsertParameters>
     <UpdateParameters>
      <asp:Parameter Name="Make" Type="String" />
      <asp:Parameter Name="Model" Type="String" />
      <asp:Parameter Name="EngineSize" Type="String" />
      <asp:Parameter Name="Color" Type="String" />
      <asp:Parameter Name="DateBrought" Type="String" />
      <asp:Parameter Name="SoldDate" Type="String" />
      <asp:Parameter Name="FirstRegistered" Type="String" />
      <asp:Parameter Name="VINNumber" Type="String" />
      <asp:Parameter Name="StartingMileage" Type="String" />
      <asp:Parameter Name="ClosingMileage" Type="String" />
      <asp:Parameter Name="Comments" Type="String" />
      <asp:Parameter Name="Name" Type="String" />
     </UpdateParameters>
    </asp:AccessDataSource>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 25, 2011 7:55 PM
  • User-1081423463 posted

    Thanks hans_v have read what you have posted and got the code to run as suggested. Everything seems to be working well. A Quick question though; on my Drivers table I have Driver Name set as the Primary Key, is there anyway to be able to update this through the updatecommand or as it is the Primary Key its locked down?

    Cheers

    Monday, November 28, 2011 9:57 AM
  • User-1199946673 posted

    on my Drivers table I have Driver Name set as the Primary Key, is there anyway to be able to update this through the updatecommand or as it is the Primary Key its locked down?

    Primary Keys cannot be changed. You better use an Autonumber field as the primary key....

    Monday, November 28, 2011 6:06 PM