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.
This is my .aspx page
<%@ 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">
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
</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">
</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>this is my .cs page
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();
}
}