Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Get Identity immediately and enter it to different table with other information

c# mssql visualstudio sql identity csharp aspx aspx.cs insert scope_identity

This topic has been archived. This means that you cannot reply to this topic.
6 replies to this topic

#1 innocence

innocence

    CC Lurker

  • New Member
  • Pip
  • 4 posts

Posted 31 May 2014 - 08:42 AM

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;
}
}

 



#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 31 May 2014 - 09:26 AM

Welcome aboard Innocence,

 

Which Database engine are we talking about?

If it is, as I think it is, SQL Server, then you should check the @@Identity variable, you can ask after the sql sentence (separating lines with ; ) and check it on the message-prints of the database (not the recordset) or even return it as a row. 

 

you should check, because I am talking memory here, the T-SQL var name could be slightly different to @@IDENTITY, but it's something like that, and it will give you exactly what you want : The identity assigned to your inserted row.



#3 innocence

innocence

    CC Lurker

  • New Member
  • Pip
  • 4 posts

Posted 31 May 2014 - 10:02 AM

Thank you @BlackRabbit, Your memory serves you well. :)

I wanted to use SCOPE_IDENTITY and have now done the following to the ASPX :

 

 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); SET @visID = SCOPE_IDENTITY()", 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);
                MyCommand.Parameters.AddWithValue("@visID", 0);
                MyCommand.Parameters["@visID"].Direction = ParameterDirection.InputOutput;  


                myConnection.Open();
                status = MyCommand.ExecuteNonQuery();


               int visID = (int)MyCommand.Parameters["@visID"].Value;
               


                // 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;
            }
        }

No I don't know how to insert the visId parameter into my History table at VisId


Edited by innocence, 31 May 2014 - 10:03 AM.


#4 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 31 May 2014 - 10:25 AM

You already have the ID into an int...

just use it in a new insert my friend, or is anything else the problem?

 

As I see, the history table already has the visID field, you just need to add it to the insert you already have



#5 innocence

innocence

    CC Lurker

  • New Member
  • Pip
  • 4 posts

Posted 31 May 2014 - 10:34 AM

When I get the ID how do I enter it into my History table?

 

// Create INSERT statement to insert to History SqlCommand MyCommand1 = new SqlCommand ("INSERT INTO History(Campus, ParkName, BayNo, Disabled, bkDate, VisId) VALUES (@Campus, @ParkName, @BayNo, @Disabled, @bkDate, @VisId)", myConnection); MyCommand1.Parameters.AddWithValue("@Campus", mytexthid1.Text); MyCommand1.Parameters.AddWithValue("@Disabled", rbDisabled.SelectedItem.Value); MyCommand1.Parameters.AddWithValue("@bkDate", DateBox.Text); MyCommand1.Parameters.AddWithValue("@ParkName", mytexthid.Text); MyCommand1.Parameters.Add("@VisId", //WHAT DO I PUT HERE );



#6 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 31 May 2014 - 10:37 AM

the value you saved here: int visID .



#7 innocence

innocence

    CC Lurker

  • New Member
  • Pip
  • 4 posts

Posted 31 May 2014 - 10:53 AM

  

I tried it like this but I don't appear to be working. Any Ideas!

        SqlCommand MyCommand = new SqlCommand
                    ("INSERT INTO Vis (fName, sName, vCompany, vEmail, regNo, carMake, carColour) VALUES (@fName, @sName, @vCompany, @vEmail, @regNo, @carMake, @carColour); SET @visID = SCOPE_IDENTITY()", 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);
                MyCommand.Parameters.AddWithValue("@visId", 0);
                MyCommand.Parameters["@visID"].Direction = ParameterDirection.InputOutput;  


                myConnection.Open();
                status = MyCommand.ExecuteNonQuery();


               int visID = (int)MyCommand.Parameters["@visId"].Value;
               


                // Create INSERT statement to insert to History
                SqlCommand MyCommand1 = new SqlCommand
                ("INSERT INTO History(Campus, ParkName, BayNo, Disabled, bkDate, VisId) VALUES (@Campus, @ParkName, @BayNo, @Disabled, @bkDate, @VisId)", 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);
                MyCommand1.Parameters.Add("@VisId", visID);
                              
                status = MyCommand1.ExecuteNonQuery();
                
                return status;
            }
        }

:biggrin: Happy days Its working there was a simple capitalization error on an ID. Thank you so much.






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download