.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

primary key and foreign key function generated values

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server


how to generate invoice type of key ex:emp100 with unique identifier

i generated invoice type by creating function by concatenate string and unique identifier 

but it can't be reference to another table

 can i have any other types generating invoice type primary key and used as a reference type


View Complete Post

More Related Resource Links

How to get all the foreign key references for a primary key

Hi ,  I would like to know all the foriegn key references in the db for a table's primary key..How can i get that? Thanks sunil m datla

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

Update with unique random generated values.

Hi there, I have an issue with an update of a column with unique random generated values, using SQL 2005. What I want to achieve is to create unique username for a list of let's say 1000 rows. My approach, which may be wrong, is as follows: to create a view to retrieve a random generated number of 7 digits; to use a function that retrieves the number from the view and checks if it is unique in the table or not (if it is, the number is returned, if it is not, a new one will be generated and so on); to update the table using that random generated number. The problem is that it does not work. It does not generate unique numbers, not all the times (you have to test by running the code few times). I suspect it is something with the update, but not sure how to solve it. Here are the steps to reproduce the problem. Create the table: CREATE TABLE [dbo].[TB_Temp]( [ID] [int] IDENTITY(1,1) NOT NULL, [ADM_USERNAME] [nvarchar](7) COLLATE Latin1_General_CI_AS NULL, ) ON [PRIMARY] Add some test rows: DECLARE @i INT SET @i = 0 WHILE @i <= 1000 BEGIN INSERT INTO TB_TEMP (ADM_USERNAME) VALUES ('') SET @i = @i +1 END Create the view to random generate 7 digits numbers: CREATE VIEW vw_RandNumberForAdminUserName AS SELECT ROUND((9999999 - 1000000 - 1) * RAND() + 1000000, 0) AS RandNumber Create the function to retrieve the unique 7 digits number: CREATE FUNCTION [dbo].[fn

Cannot retrieve values of dynamically generated controls

Hi All, I am creating a user control which contains checkboxes and labels that needs to be created on fly. The dynamic generation of these controls depend on the values retreived from a SPlist. I have several panels that display items queried from the list on the basis of a selection criteria. On a button click event I need to capture all the selected values from the controls that were generated dynamically.  When I traverse each dynamic control that I added to the panel, I dont see their values. I tried using recursive retrieval of the control from the page as well but it just behaves crazy, it treats a radio button as a checkbox and returns a value that I am not looking for. I am not using the the page_load() or any other event, I use the button handler on my custom method. I was thinking for ViewState, ControlsState or SessionState. I assume ViewState may not work on this as the dynamically generated controls need to be in the page_load event. Please guide as this getting way more complicated than what I had ever thought of it to be.

Binding Bool values using SQLBindParameter() function

Hi, I have a bool variable in my C++ code, I am bounding it as shown below: SQLPOINTER cbNULL = SQL_NULL_DATA; m_nReturnValue = SQLBindParameter(m_pHstmt,ColumnSequence,SQL_PARAM_INPUT,SQL_C_BIT, SQL_BIT,1,0,&pBool,0,&cbNull); issue is when *pBool = 1 (true),the values of binded parameters works fine but in case of *pBool = 0(false), it binds the value as 1 again. I cannot catch its reason why it is binding 1 instead of 0. Can someone please help me for this how to bind the bool variable which has values as 0(false).? Cheers!!! Vatsa

Report Parameter Values - Can I use getdate() or now() or any other function?

Hi all,      I have a report that utilizes a calendar function but I need to make it a subscription report. Is it possible to use getdate() or now() on the Report Parameter Values? I have a text box that says Enter a Date and that is where I want to have the default value to be run with the subscription. Please let  me know if possible. Thank you.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

Entity Framework: No support for server-generated keys and server-generated values

HelloI tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".How can I add entities to my Sqlce- database?I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.What do I have to change to make successfully a SaveChanges()?Thanks for your help,Gerald

Foreign & Primary Keys


I have the following FK on Items table

ALTER TABLE [dbo].[items]  WITH NOCHECK ADD  CONSTRAINT [FK_items_i_tmpnam] FOREIGN KEY([i_tmpnm_id])

REFERENCES [dbo].[i_tmpnam] ([tmpnam_id])



Yet I'm able to delete all records from i_tmpnam and leave orphans in the FK field in the Items table. So, does NOCHECK disables the constraint completely?


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

encoding function parameters and return values when binaryMessageEncoding is used


Dear All,

I'm creating simple web service that will accept some parameters (such as customer name and file name) and return content of an xml file as a string and WS client will save it on his side. I'm using custom binding with binaryMessageEncoding and httpTransport. The question is if it is possible to have these input parameters as well as returned xml content binary encoded somehow?

Thanks in advance for your help!


foreign key to reference composite primary key


Hello all!

I am new to tsql so please be kind if this is bad idea.  I am using visual studio, thus why everything is split up.
I am writing software that allows the user to "turn on" customizable tables to use.  each table is the same.  so for brevity in the database i made a single table 'User'.

CREATE TABLE [dbo].[User]
 U_Table    int     NOT NULL,
 U_ID     varchar(50)   NOT NULL


ALTER TABLE [dbo].[User]

the primary key is made up of a table id (which user-customizable table) and the record id
now i have a transaction table that holds the id's used for each user customizable table

CREATE TABLE [dbo].[Transaction]
TRAN_ID      int     NOT NULL,
TRAN_User_01    varchar(50)   NULL,
 TRAN_User_02    varchar(50)   NULL,
 TRAN_User_03    varchar(50)   NULL,
 TRAN_User_04    varchar(50)   NULL

My question is, i want to add a foreign key relationshi

SQL Server Mgmt Studio - primary/foreign keys


I currently have a number of tables in my database, which include these 2 tables:

tblOne - Uid(pk), FirstName, LastName, Email

tblTwo - HCCID(pk), Uid, Category, Description, Company, Manager


In my application I have users from tblOne logging in and creating records which are inserted into tblTwo. Thankfully this is working ok, however, my problem is with the Uid in tblTwo (fk).

When a new record is created in tblTwo, the HCCID(pk) iterates, so i have a numeric identifier for each record. However is there any way i can also keep a record of the user by recording the Uid in tblTwo based on which user creates the record?

Seeing as though this is already a pk in tblOne and I also already have a pk in tblTwo, is this possible?

Any ideas will be greatly appreciated!

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?

How to display encrypted foreign key values?



In DD, how can I show encrypted foreign key values in a drop down list?

For example, there are order and product tables in NW database.

Let's say product names are encrypted in the product table.

In my product filter (auto-generated) in the "Orders/List.aspx", I would like to first decrypt product names and populate the drop down list.

Assumption is that symmetric encryption is used and we know how to decrypt strings.

Will decryption be something that I should do in "FilterUserControl"?

If you have done something similar, could you please share your experience?

Many thanks,

Problem in displaying values for foreign keys in Dynamic data asp.net web app


I have the following tables in my database which are as given under:


Table 2: CREATE TABLE [dbo].[PromotionCodes]( [PromotionCode] nchar NOT NULL, [CustomerPromotionDiscountID] [int] NOT NULL, [EmployeePromotionDiscountID] [int] NOT NULL, [IsActive] [bit] NOT NULL, CONSTRAINT [PK_PromotionCodes_1] PRIMARY KEY CLUSTERED ( [PromotionCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

There is a foreign key relationship between columns (1) PromotionID of table PromotionDiscountValues and CustomerPromotionDiscountID of table PromotionCodes and (2) PromotionID of table PromotionDiscountValues and EmployeePromotionDiscountID of table PromotionCodes

When i browse the PromotionCodes table in the dynamic data listing, the following columns get displayed:

  1. PromotionCode

  2. IsActive

  3. Prom

Changing default values for foreign key fields on ListDetails


Steve Naughton wrote a great article (Getting Default Values from List page Filters for Insert) that supports separate-page mode, where the List, Detail, Insert, and Update tasks are performed by using separate pages. How do I set the dropdownlist defaults in the DetailsView during insert using combined-page mode on the ListDetails page? I want to set the dropdownlist defaults in the DetailsView during insert to match the values selected in the page filters. Thanks!

Foreign Key Drop downlist available in edit mode even though part of primary key


I am using Dynamic Data 4 in VS2010 with EF4.

So I have noticed and read that DD using Entity Framework does not allow editing of primary keys in a scaffolded table...fair enough...I don't know if its a difference between developing in SQL Server vs Oracle but often in our database we use composite primary keys that consists of several columns in a table (as in not generated ids, or unique ids as the primary key).  
So I have a table that has a primary key that that consists of two text fields and a foreign key from another table.  I have a custom entity template that i use to insert new records into the table.  When I select new, I get the desired behavior of a dropdown for the foreign key field to pick to insert a new record.  That works great.  When I select edit on a row in the table, I still get that dropdown!  Of course if I select a different entry from the dropdown DD yells at me since the foreign key field is part

So I have noti

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend