0

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">
                &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>

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();
    }
}
2
Contributors
2
Replies
7
Views
7 Years
Discussion Span
Last Post by giahmed
-1

hi giahmed,
you can verify a thread named "connection problem" submitted by me.I have attached a file along with it, which executes 2 sql queries.
please reply, if you get any progress.

0

Hy ajijacobm,

Its a really good and simple example you have posted, But as I am not using SQL so its not really helpfull and also I am trying to Insert records in two different tables, One is Authors which has Author ID and Author Name, and Second one is Books Table and has Book ID, Title, Published Year, ISBN and Author ID. I am using Access Database with Dataset and have query in Dataset as,

INSERT INTO Books
             (AuthorID, Title, PublishedYear, ISBN, Genre)
VALUES (?, ?, ?, ?, ?)
 SELECT scope_identity();

and in c# file I have

protected void Button1_Click(object sender, EventArgs e)
    {
        AuthorTableAdapter obj = new AuthorTableAdapter();

        String id;
        id = obj.InsertQuery(txtFirstName.Text).ToString();
        String aid;
        bookTableAdapter aobj = new bookTableAdapter();
        aid = aobj.InsertQuery(Convert.ToInt32(Request.QueryString["AuthorId"]),txttitle.Text,Convert.ToInt32(txtisbn.Text),txtgenre.Text,Convert.ToInt32(txtpublyer.Text)).ToString();
             
      }

This inserts NULL value or zero rather then selecting a recently enterd authord ID.

Anything you can help with??
Thanks for before and in advance.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.