SQL Server 2008 :: Retrieving Same Identity Column Value By Multiple Users
Mar 11, 2015
I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.
DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR
From my queries,I am providing a available department information.Each user needs to get unique available department information.But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.
View 6 Replies
ADVERTISEMENT
Apr 28, 2015
I have two tables having one row identifier column each of int datatype. Both these columns are part of the respective primary keys. Now as a part of my process, i'm inserting one small part of data from one table to another table. This was working fine but suddenly started getting error like
Violation of PRIMARY KEY constraint 'PK_TargetTable'. Cannot insert duplicate key in object 'DW.TargetTable'. The duplicate key value is (58544748).First I checked with DBCC CHECKIDENT with NORESEED and found that there is difference in the current identity value and current column value. I fixed it by running DBCC CHECKIDENT. But to my surprise again got the same issue. interesting thing is that the error comes after inserting 65466 records.
View 4 Replies
View Related
Jul 26, 2007
I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.
Waht is the way to achive this please?
Marc
View 2 Replies
View Related
Feb 25, 2015
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
View 7 Replies
View Related
Aug 2, 2015
I am extremely new to database design, and I ran into a problem that I know comes up often, however has many opinions...
Basically I have a table that is going to have 50+ columns. The natural key on this table is actually 8 columns wide, 4 of them being Varchar columns by default. (varchar(50)'s).
I have added an identity column, (1,1) to the table, however I put the clustered index on the 8 natural keys... My plan is to rebuild the clustered index once nightly when the system isn't in use (after 7 pm).
I know others would say it would be better to have the clustered key on the 1,1 column and then add indexes on the other 8 fields... However I don't quite understand why honestly...
Every single query against this table will use the 8 columns, and will NOT use the Identity column (1,1) because they are calls from other systems that do not know the Identity column....
Therefore if your database is set up for query speed, and every single query has to have a value for 8 columns to get a valid result, does it make sense to put a clustered index over the 8 columns?
If not why? Why is putting a clustered index on an identity column (that will literally never be used in a query) a better solution?
View 9 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Jul 29, 2011
We have a database where the nvarchar columns currently holding English only data. It keeps the training information. Currently the default collation is Latin Case insensitive accent insensitive.
Now we are planning to allow multiple language support. When we go for it, we will upload the data from different languages. Based on user preferences, he/she should be able to query the data (One language at a time).
I am worried on how the existing queries will work if I load all language data in the same database.
For example, some characters in English are used in Norwegian too. But they have different sort order. ALso LIKE conditions too may fail.
So far I thought of few solutions: Add the Collation information along with select , order by clauses. It means we need to add more procedures (one set per collation) The other option is to create new database for each language. Each will have its own collation. The dowside is we need more databases which may lead to more servers and more maintenance work.
View 9 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Mar 19, 2008
I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.
I've tried adding a RowUpdated handler to all tableadapters that look like this:
Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {
if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {
if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {
e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();
}
}
}
I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...
So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?
Any hints would be greatly appreciated! Thanks!
Cheers!
View 3 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
May 15, 2015
I have a query running and returning 3 columns, user name, e-mail and device name
SELECT DISTINCT v_R_User.Full_User_Name0 AS 'User full Name', v_R_User.Mail0 AS 'E-Mail', _RES_COLL_DEV00144.Name
FROM v_R_System INNER JOIN
v_R_User ON v_R_System.User_Name0 = v_R_User.User_Name0 INNER JOIN
_RES_COLL_DEV00144 ON v_R_User.User_Name0 = _RES_COLL_DEV00144.UserName INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
Where v_R_User.Mail0 <> ''
ORDER BY 'User Full Name'
From here I would like to generate an e-mail to each user (like mail merge) to each user in the table an include their machine name. I can do it with PS, but rather have it run directly from SQL. Is it possible?
View 9 Replies
View Related
Jul 29, 2007
Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?
TIA.
View 6 Replies
View Related
Jul 21, 2005
hy all..How can I achieve the above ?Moreover can I retrieve data from multiple databases which are lying on different DBMSs ( like retrieving from database A which is on SQL and from database B which is on Oracle ) ?Rgds.
View 2 Replies
View Related
Feb 13, 2015
How to find the list of logins/users who do not have any permission (except default Public) access.
View 4 Replies
View Related
Sep 1, 2015
We are approaching the "go live" date of a big application upgrade, so while the application is being upgraded i was asked to lock out all users and backup all databases and make sure the data isnt changed during a period of time
It turns out, as one of my databases is being replicated to another server, i cant put that database in read_only mode, or restricted_mode, without removing the replication.
Removing the replication means i have to set it up again and that means another 3 hours added to an already cram-packed weekend without sleep!
How I can lock out the users to make sure there are no changes to a database other then read_only/restricted_mode/single_user ?
I really don't want to touch that replication...
View 0 Replies
View Related
Nov 14, 2006
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
View 2 Replies
View Related
Mar 12, 2015
I am troubleshooting some slow queries and notice that the primary table involved has a strange setup.
This table stores detailed information about time cards. This important column is Week_ending(that is the CI)
It has one column( identity column) as the PK and...
It has another column is used as CI
Is there ever a benefit to this approach?
I would think combining these two rows into the CI would be best?
View 5 Replies
View Related
Dec 3, 2007
As part of a web site I allow users to store documents in SQL Server 2000 using an Image column on a table. After the file is saved the user can open the document through the browser. This works great for most file extensions such as ".XLS" or ".DOC" but fails when attempting to open ".MSG" files (saved outlook messages). XLS documents will open in Excel and DOC documents open in Word, but MSG documents open in Notepad and display what appears to be binary data. I'm trying to figure out if this is a SQL Server issue or some sort of ASP/ASP.NET webserver issue.
If I attempt to open an MSG file stored directly on the web server, the file opens correctly using the "Microsoft Outlook View Control", but when attempting to open the same file saved in SQL Server, it attempts to open in notepad.
Any ideas?
View 2 Replies
View Related
Oct 2, 2007
The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3 As SqlConnectionDim mySettings3 As New NameValueCollectionmySettings3 = AppSettingsDim strConn3 As StringstrConn3 = mySettings3("connString")objConn3 = New SqlConnection(strConn3)Dim strInsertPatient As StringDim cmdInsert As SqlCommandDim strddlSex As StringDim strddlPatientState As StringDim rowsAffected As Integer
strddlSex = ddlSex.SelectedItem.TextstrddlPatientState = ddlPatientState.SelectedItem.TextstrInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert = New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@UserID", "Joe For Now")cmdInsert.Parameters.Add("@Accession", Accession.Text)cmdInsert.Parameters.Add("@LastName", LastName.Text)cmdInsert.Parameters.Add("@MI", MI.Text)cmdInsert.Parameters.Add("@FirstName", FirstName.Text)cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)cmdInsert.Parameters.Add("@ddlSex", strddlSex)cmdInsert.Parameters.Add("@DOB", DOB.Text)cmdInsert.Parameters.Add("@Address1", Address1.Text)cmdInsert.Parameters.Add("@Address2", Address2.Text)cmdInsert.Parameters.Add("@City", City.Text)cmdInsert.Parameters.Add("@Suite", Suite.Text)cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)cmdInsert.Parameters.Add("@ZIP", zip.Text)cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()cmdInsert.ExecuteNonQuery()objConn3.Close()
View 1 Replies
View Related
Feb 29, 2008
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
View 3 Replies
View Related
Aug 22, 2005
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1). However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??
View 5 Replies
View Related
Jul 11, 2006
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View 3 Replies
View Related
Jan 16, 2008
Hi, i just need some help?? just some info?is it TRUE that SQL Server 2005 Express Edition (free) is capable ONLY of SINGLE user?i mean if i have a data driven page, if someone is using or editing the data in my page, does no one can access my data base simultaneously??i hope you could enlighten my mind, thanks
View 2 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
May 5, 2008
in visual studio web developer express edition 2008 i added a new item this was a sql server database which i called test2. I added a table called comments, and the fields name, email, comments, IPAddress, and Date and in my comments.aspx page i added a name textbox, a texbox for email, a textbox for comments and the submit button i double clicked on the submit button and added the following code
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim mydS As New SqlDataSource mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString1").ToString mydS.InsertCommandType = SqlDataSourceCommandType.Text mydS.InsertCommand = "INSERT INTO comments(Name,Email,Comments,IPAddress,Date) VALUES (@Name,@Email,@Comments,@IPAddress,@Date)" mydS.InsertParameters.Add("Name", TextBox1.Text) mydS.InsertParameters.Add("Email", TextBox2.Text) mydS.InsertParameters.Add("Comments", TextBox3.Text) mydS.InsertParameters.Add("Date", Date.Now) mydS.InsertParameters.Add("IPAddress", Request.Params("REMOTE_ADDR")) Dim rowsaffected As Int16 = 0 Try rowsaffected = mydS.Insert Catch ex As Exception Server.Transfer("errors.aspx") Finally mydS = Nothing End Try If rowsaffected <> 1 Then Server.Transfer("errors.aspx") Else Server.Transfer("confirm.aspx") End If End Sub Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged End Sub but when i run it it gives me a problem with this line mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString2).ToString and says something abt unidentified object and I want to know how do i set the connection string or alternatively where?????? and my table does not update with the corresponding values i enter on the webpage. i also changed the authentication mode to forms instead of windows as i was advised but i'm still having a problem
View 12 Replies
View Related
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Mar 26, 2010
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies
View Related
Aug 12, 2009
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies
View Related
Oct 21, 2015
I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @SQL as varchar (200)
[Code] .....
View 4 Replies
View Related
Sep 9, 2015
I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
View 7 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 Replies
View Related