Inserting Record using ASP.net c# and Access Database

[giahmed]

Dear all,
I am really stuck with inserting record into access database using ASP.net and C#, as in university I am only allowed to use access. I am trying to create new room reservation for meetings its not a hotel room, I have two time slots AM and PM and date with Room number, all of these are in one access table called Rooms, I want to insert a new booking, it works when I insert a new record but i want to check weather room is free for that date and time to avoid duplicate booking. I am using accessdatasource and using query builder in ASP.net. Any ideas, I know ASP.net with Access is bit old now but I have to use that because university sever doesnt let us use SQL.

Thanks in advance.

[giahmed]

I am Accessdatasource and ity generated following code and I only ammended Insert into statement, which works fine for inserting records but its not checking for duplication of records, Is there anything we can do in Access or amend Insert Into statement,

<asp:AccessDataSource ID="AccessDataSource3" runat="server" 
                        DataFile="~/App_Data/ASPNetDB.mdb" 
                        SelectCommand="INSERT INTO Rooms([Room Name]) VALUES (@TextBox2)" 
                        InsertCommand="INSERT INTO [Rooms] ([Room Name], [Meeting Date], [Time]) VALUES (@TextBox2.Text,@SelectedDate,@TextBox4.Text)" 
                        
                        UpdateCommand="UPDATE [Rooms] SET [Room Name] = ?, [Meeting Date] = ?, [Time] = ? WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        ConflictDetection="CompareAllValues" 
                        DeleteCommand="DELETE FROM [Rooms] WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        OldValuesParameterFormatString="original_{0}">
                        <DeleteParameters>
                            <asp:Parameter Name="original_Room_ID" Type="Int32" />
                            <asp:Parameter Name="original_Room_Name" Type="String" />
                            <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
                            <asp:Parameter Name="original_Time" Type="String" />
                        </DeleteParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="Room_Name" Type="String" />
                            <asp:Parameter Name="Meeting_Date" Type="DateTime" />
                            <asp:Parameter Name="Time" Type="String" />
                            <asp:Parameter Name="original_Room_ID" Type="Int32" />
                            <asp:Parameter Name="original_Room_Name" Type="String" />
                            <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
                            <asp:Parameter Name="original_Time" Type="String" />
                        </UpdateParameters>
                        <InsertParameters>
                            <asp:ControlParameter ControlID="TextBox2" Name="Room_Name" PropertyName="Text" 
                                Type="String" />
                            <asp:ControlParameter ControlID="TextBox3" Name="Meeting_Date" 
                                PropertyName="Text" Type="DateTime" />
                            <asp:ControlParameter ControlID="TextBox4" Name="Time" PropertyName="Text" 
                                Type="String" />
                        </InsertParameters>
                    </asp:AccessDataSource>

[reach_yousuf]

Hi giahmed

Change the following line in your aspx page
Use this

SelectCommand="select * from Rooms where  ([Room Name]) = (@TextBox2)"

Instead of

SelectCommand="INSERT INTO Rooms([Room Name]) VALUES (@TextBox2)"

and change selectParam also...

Mark as solved if it helps you!!!

[giahmed]

I changed it but no difference, it inserted same record twice.

<asp:AccessDataSource ID="AccessDataSource3" runat="server" 
                        DataFile="~/App_Data/ASPNetDB.mdb" 
SelectCommand="SELECT * FROM Rooms([Room Name]) VALUES (@TextBox2)" 
InsertCommand="INSERT INTO [Rooms] ([Room Name], [Meeting Date], [Time]) VALUES (@TextBox2.Text,@SelectedDate,@TextBox4.Text)" 
                        
                        UpdateCommand="UPDATE [Rooms] SET [Room Name] = ?, [Meeting Date] = ?, [Time] = ? WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        ConflictDetection="CompareAllValues" 
                        DeleteCommand="DELETE FROM [Rooms] WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        OldValuesParameterFormatString="original_{0}">
                        <DeleteParameters>
   <asp:Parameter Name="original_Room_ID" Type="Int32" />
  <asp:Parameter Name="original_Room_Name" Type="String" />
  <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
   <asp:Parameter Name="original_Time" Type="String" />
                        </DeleteParameters>
                        <UpdateParameters>
            <asp:Parameter Name="Room_Name" Type="String" />
          <asp:Parameter Name="Meeting_Date" Type="DateTime" />
             <asp:Parameter Name="Time" Type="String" />
          <asp:Parameter Name="original_Room_ID" Type="Int32" />
        <asp:Parameter Name="original_Room_Name" Type="String" />
   <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
       <asp:Parameter Name="original_Time" Type="String" />
                        </UpdateParameters>
                        <InsertParameters>
 <asp:ControlParameter ControlID="TextBox2" Name="Room_Name" PropertyName="Text" 
                                Type="String" />
 <asp:ControlParameter ControlID="TextBox3" Name="Meeting_Date" 
                                PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TextBox4" Name="Time" PropertyName="Text" 
                                Type="String" />
                        </InsertParameters>
                    </asp:AccessDataSource>

[reach_yousuf]

I changed it but no difference, it inserted same record twice.

<asp:AccessDataSource ID="AccessDataSource3" runat="server" 
                        DataFile="~/App_Data/ASPNetDB.mdb" 
SelectCommand="SELECT * FROM Rooms([Room Name]) VALUES (@TextBox2)" 
InsertCommand="INSERT INTO [Rooms] ([Room Name], [Meeting Date], [Time]) VALUES (@TextBox2.Text,@SelectedDate,@TextBox4.Text)" 
                        
                        UpdateCommand="UPDATE [Rooms] SET [Room Name] = ?, [Meeting Date] = ?, [Time] = ? WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        ConflictDetection="CompareAllValues" 
                        DeleteCommand="DELETE FROM [Rooms] WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        OldValuesParameterFormatString="original_{0}">
                        <DeleteParameters>
   <asp:Parameter Name="original_Room_ID" Type="Int32" />
  <asp:Parameter Name="original_Room_Name" Type="String" />
  <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
   <asp:Parameter Name="original_Time" Type="String" />
                        </DeleteParameters>
                        <UpdateParameters>
            <asp:Parameter Name="Room_Name" Type="String" />
          <asp:Parameter Name="Meeting_Date" Type="DateTime" />
             <asp:Parameter Name="Time" Type="String" />
          <asp:Parameter Name="original_Room_ID" Type="Int32" />
        <asp:Parameter Name="original_Room_Name" Type="String" />
   <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
       <asp:Parameter Name="original_Time" Type="String" />
                        </UpdateParameters>
                        <InsertParameters>
 <asp:ControlParameter ControlID="TextBox2" Name="Room_Name" PropertyName="Text" 
                                Type="String" />
 <asp:ControlParameter ControlID="TextBox3" Name="Meeting_Date" 
                                PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TextBox4" Name="Time" PropertyName="Text" 
                                Type="String" />
                        </InsertParameters>
                    </asp:AccessDataSource>

in your page load event wrap up all your code under the ispostback statement

if not ispostback then
    'your code here
end if

if it doesnt solve your problem then, show all your code behind "aspx.vb" to know the cause

[giahmed]

I Dont think I can add this

if not ispostback then
    'your code here
end if

as it's in .aspx page not .aspx.cs page. Any thing else I can do or any other suggestions.

[giahmed]

This is the complete code behind aspx page. Quiet alot of code ia autogenerated by adding calender control and datasources.

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Roombooking.aspx.cs" Inherits="Default2" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 253px;
        }
        .style3
        {
            width: 253px;
            height: 27px;
        }
        .style4
        {
            height: 27px;
        }
        .style5
        {
            width: 248px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <h2>Room Booking</h2>
        <table class="style1">
        <tr>
            <td class="style2">
                Room Available</td>
            <td>
 <asp:DropDownList ID="DropDownList1" runat="server" Width="202px" 
 DataSourceID="AccessDataSource1" DataTextField="Room_Name" 
              DataValueField="Room_Name">
                </asp:DropDownList>
 <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
                    DataFile="~/App_Data/ASPNetDB.mdb" 
                    
                    SelectCommand="SELECT DISTINCT [Room Name] AS Room_Name FROM [Rooms] ORDER BY [Room Name]">
                </asp:AccessDataSource>
            </td>
        </tr>
        <tr>
            <td class="style2">
                Time</td>
            <td>
 <asp:Calendar ID="Calendar1" runat="server" BackColor="White" 
 BorderColor="Black" BorderStyle="Solid" CellSpacing="1" Font-Names="Verdana" 
  Font-Size="9pt" ForeColor="Black" Height="250px" NextPrevFormat="ShortMonth" 
                    Width="330px">
   <SelectedDayStyle BackColor="#333399" ForeColor="White" />
   <TodayDayStyle BackColor="#999999" ForeColor="White" />
                    <OtherMonthDayStyle ForeColor="#999999" />
                    <DayStyle BackColor="#CCCCCC" />
 <NextPrevStyle Font-Bold="True" Font-Size="8pt" ForeColor="White" />
 <DayHeaderStyle Font-Bold="True" Font-Size="8pt" ForeColor="#333333" 
                        Height="8pt" />
 <TitleStyle BackColor="#333399" BorderStyle="Solid" Font-Bold="True" 
                        Font-Size="12pt" ForeColor="White" Height="12pt" />
                </asp:Calendar>
            </td>
        </tr>
        <tr>
            <td class="style2">
                Date</td>
            <td>
 <asp:DropDownList ID="DropDownList3" runat="server" Height="19px" Width="53px">
                    <asp:ListItem>AM</asp:ListItem>
                    <asp:ListItem>PM</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
     <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
<asp:Button ID="b_button" runat="server" Text="Next" Width="71px" 
                    onclick="b_button_Click" />
            </td>
        </tr>
        <tr>
            <td class="style3">
                </td>
            <td class="style4">
                </td>
        </tr>
    </table>
    <asp:Panel ID="Panel1" runat="server">
        <table class="style1">
            <tr>
                <td class="style5">
                    Room Number</td>
                <td>
      <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style5">
                    Date</td>
                <td>
      <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style5">
                    Time</td>
                <td>
     <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style5">
                    &nbsp;</td>
                <td>
 <asp:Button ID="Button1" runat="server" Text="Button" Width="76px" 
                        onclick="Button1_Click" />
  <asp:AccessDataSource ID="AccessDataSource3" runat="server" 
  DataFile="~/App_Data/ASPNetDB.mdb" 
 SelectCommand="SELECT * FROM Rooms([Room Name]) = (@TextBox2)" 
 InsertCommand="INSERT INTO [Rooms] ([Room Name], [Meeting Date], [Time]) VALUES (@TextBox2.Text,@SelectedDate,@TextBox4.Text)" 
                        
                        UpdateCommand="UPDATE [Rooms] SET [Room Name] = ?, [Meeting Date] = ?, [Time] = ? WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        ConflictDetection="CompareAllValues" 
                        DeleteCommand="DELETE FROM [Rooms] WHERE [Room ID] = ? AND (([Room Name] = ?) OR ([Room Name] IS NULL AND ? IS NULL)) AND (([Meeting Date] = ?) OR ([Meeting Date] IS NULL AND ? IS NULL)) AND (([Time] = ?) OR ([Time] IS NULL AND ? IS NULL))" 
                        OldValuesParameterFormatString="original_{0}">
                        <DeleteParameters>
  <asp:Parameter Name="original_Room_ID" Type="Int32" />
  <asp:Parameter Name="original_Room_Name" Type="String" />
 <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
  <asp:Parameter Name="original_Time" Type="String" />
                        </DeleteParameters>
                        <UpdateParameters>
 <asp:Parameter Name="Room_Name" Type="String" />
 <asp:Parameter Name="Meeting_Date" Type="DateTime" />
 <asp:Parameter Name="Time" Type="String" />
 <asp:Parameter Name="original_Room_ID" Type="Int32" />
 <asp:Parameter Name="original_Room_Name" Type="String" />
 <asp:Parameter Name="original_Meeting_Date" Type="DateTime" />
 <asp:Parameter Name="original_Time" Type="String" />
                        </UpdateParameters>
                        <InsertParameters>
<asp:ControlParameter ControlID="TextBox2" Name="Room_Name" PropertyName="Text" 
                                Type="String" />
<asp:ControlParameter ControlID="TextBox3" Name="Meeting_Date" 
                                PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="TextBox4" Name="Time" PropertyName="Text" 
                                Type="String" />
                        </InsertParameters>
                    </asp:AccessDataSource>
                </td>
            </tr>
        </table>
    </asp:Panel>
</asp:Content>

And this is the ode behind aspx.cs file which is c# file

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       string dte;
       dte = Calendar1.SelectedDate.ToShortDateString();
       TextBox1.Text = dte;
         }
    protected void b_button_Click(object sender, EventArgs e)
    {
        TextBox2.Text = DropDownList1.SelectedValue.ToString();
        string dte;
        dte = Calendar1.SelectedDate.ToShortDateString();
        TextBox3.Text = dte;
        TextBox4.Text = DropDownList3.SelectedValue.ToString();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        AccessDataSource3.Insert();
        AccessDataSource3.DataBind();
    }
}

Please any help will be appriciated, it's holding me back to finishing off my work.
Thanks alot.