I am trying to get the Id of the visitor that will be added and put it straight in the related table. I have the visId and VisId as foreign key. I've only shown a little of the ASPX and ASPX.cs. The following is entering the information into the History table however I need the VisId to be with associated booking. I also cannot get the logged in user to insert into Staff ID for some reason. Any help would be great as its driving me insane. My structure is as below:
TABLE WITH PARKING SPACE INFORMATION- CLCAMPUS:
CREATE TABLE [dbo].[clCampus] ( [SpaceID] INT IDENTITY (1, 1) NOT NULL, [ParkName] NVARCHAR (50) NULL, [Campus] NVARCHAR (50) NULL, [BayNo] INT NULL, [VisId] INT NULL, [StaffId] NVARCHAR (128) NULL, CONSTRAINT [PK_clCampus] PRIMARY KEY CLUSTERED ([SpaceID] ASC)
TABLE WITH VISITOR INFORMATION - VisId:
CREATE TABLE [dbo].[Vis] ( [visId] INT IDENTITY (1, 1) NOT NULL, [fName] NVARCHAR (20) NULL, [sName] NVARCHAR (20) NULL, [vCompany] NVARCHAR (50) NULL, [vEmail] NVARCHAR (50) NULL, [regNo] NVARCHAR (8) NULL, [carMake] NVARCHAR (50) NULL, [carModel] NVARCHAR (50) NULL, [carColour] NVARCHAR (50) NULL, PRIMARY KEY CLUSTERED ([visId] ASC) );
TABLE I WANT TO BOOKING ie clCampus and VisId - History:
CREATE TABLE [dbo].[History] ( [BookID] INT IDENTITY (1, 1) NOT NULL, [Disabled] BIT NULL, [BookingID] INT NULL, [ParkName] NVARCHAR (50) NULL, [Campus] NVARCHAR (50) NULL, [BayNo] INT NULL, [VisId] INT NULL, [StaffId] NVARCHAR (128) NULL, [bkDate] NVARCHAR (10) NULL, CONSTRAINT [FK_History_ToTable_1] FOREIGN KEY ([VisId]) REFERENCES [dbo].[Vis] ([visId]), CONSTRAINT [FK_History_ToTable_2] FOREIGN KEY ([StaffId]) REFERENCES [dbo].[AspNetUsers] ([Id]), CONSTRAINT [FK_clCampus_ToTable_1] FOREIGN KEY ([StaffId]) REFERENCES [dbo].[AspNetUsers] ([Id]), CONSTRAINT [FK_History_ToTable] FOREIGN KEY ([BookingID]) REFERENCES [dbo].[clCampus] ([SpaceID]) );
ASPX:
<div class="row"> < asp:Label ID="Label1" runat="server" Text="First Name:" Width="125px" Font-Bold="True"></asp:Label><b></b> <asp:TextBox ID="Vis1" runat="server"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Vis1" ErrorMessage="You must enter a first-name to proceed!" Display="Dynamic" Text="*" ForeColor="Red"></asp:RequiredFieldValidator> </div> <p></p> <div class="row"> <asp:Label ID="Label2" runat="server" Text="Surname:" Width="125px" Font-Bold="True"></asp:Label><b></b> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox1" Display="Dynamic" ErrorMessage="You must enter a surname to proceed!" ForeColor="Red">*</asp:RequiredFieldValidator> </div> <div class="row"> <asp:Label ID="Label4" runat="server" Text="Company:" Width="125px" Font-Bold="True"></asp:Label><b></b> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox2" Display="Dynamic" ErrorMessage="You must enter a company to proceed!" ForeColor="Red">*</asp:RequiredFieldValidator> </div> </asp:PlaceHolder> <p></p>
ASPX.CS:
int CreateBooking() { using (SqlConnection myConnection = new SqlConnection(DefaultConnection)) { int status; //Add visitor to database SqlCommand MyCommand = new SqlCommand ("INSERT INTO Vis (fName, sName, vCompany, vEmail, regNo, carMake, carColour) VALUES (@fName, @sName, @vCompany, @vEmail, @regNo, @carMake, @carColour)", myConnection); MyCommand.Parameters.AddWithValue("@fName", Vis1.Text); MyCommand.Parameters.AddWithValue("@sName", TextBox1.Text); MyCommand.Parameters.AddWithValue("@vCompany", TextBox2.Text); MyCommand.Parameters.AddWithValue("@vEmail", emailTB.Text); MyCommand.Parameters.AddWithValue("@regNo", mailTB.Text); MyCommand.Parameters.AddWithValue("@carMake", lsMake.SelectedItem.Value); MyCommand.Parameters.AddWithValue("@carColour", lsColour.SelectedItem.Value); myConnection.Open(); status = MyCommand.ExecuteNonQuery(); //Get visitor identity*********PROBLEM AREA********** SqlCommand MyCommand2 = new SqlCommand ("SELECT FROM Vis (visId) WHERE ('fName = @fName') AND ('sName = @sName') AND ('vCompany = @vCompany')", myConnection); MyCommand.Parameters.ToString("@fName", Vis1.Text); MyCommand.Parameters.AddWithValue("@sName", TextBox1.Text); MyCommand.Parameters.AddWithValue("@vCompany", TextBox2.Text); // Create INSERT statement to insert to History SqlCommand MyCommand1 = new SqlCommand ("INSERT INTO History(Campus, ParkName, BayNo, Disabled, bkDate) VALUES (@Campus, @ParkName, @BayNo, @Disabled, @bkDate)", myConnection); MyCommand1.Parameters.AddWithValue("@Campus", mytexthid1.Text); MyCommand1.Parameters.AddWithValue("@Disabled", rbDisabled.SelectedItem.Value); MyCommand1.Parameters.AddWithValue("@bkDate", DateBox.Text); MyCommand1.Parameters.AddWithValue("@BayNo", mytexthid.Text); MyCommand1.Parameters.AddWithValue("@ParkName", mytexthid2.Text); status = MyCommand1.ExecuteNonQuery(); return status; } }