HELP ....difficulty in insert of 2 tables data with id

Hello frdz,

I have two tables in sqlserver 2005.

I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#

Table-1 CUSTOMER
Fields:
customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)
Table-2 CARD
Fields:
cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not null
 INSERT INTO CUSTOMER (customername ,address,city,emailid)
 VALUES (@.customername,@.address,@.city,@.emailid)
 SELECT @.customerid = SCOPE_IDENTITY()
/* HELP HERE NOT ABLE TO GET DATA OF CARD */
 SELECT @.cardid = cardid from CARD where customerid =@.cardid

Pls tell me how to insert the data ...

There is only one cardid for only one customerid both should be unique no duplication...

One customer can have only one and one card... 

If I understand your design correctly, you'll have to insert the CARD record first and get the new identity fieldcardid into a variable. Then you insert into CUSTOMER, using the variable to populate CUSTOMERS.cardid.

This is because cardid is a foreign key in the CUSTOMER table - so it has to be generated first. Just switch your order of inserts and you should be fine. And I advise that you put both insert statements within a transaction (BEGIN TRAN and COMMIT) - if one insert fails, then the other one shouldn't be left in the table.

If you need more on stored procedures, here's a link to a free chapter ondeveloping stored procedures for sql server.

|||

Your database to me seems odd, I would think that each customer could have many cards, while each card could only have 1 customer, but you seem to have it reversed. In any case, this is how you would insert:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

and optionally:

SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

|||

Thanxs everyone for the replies...

Motley...thanxs ... ya i want that 1 customer can have only 1 card.
and optionally:
SELECT @.cardid,SCOPE_IDENTITY() to return both the generated cardid and customerid

I have created stored procedur for card and customer.

I don't understand what is the above line and how it worksSELECT @.cardid,SCOPE_IDENTITY() ??

This should be written in which stored procedure ? I mean card or customer ?

Should a single stored procedure be created for this ?

Thanxs......waiting for reply

|||

That would return a resultset with 1 row and 2 columns. The first column would be the @.cardid variable that we set earlier in the code, and the second column would be SCOPE_IDENTITY(), which is a function that returns the value of the identity column for the row that was just inserted.

When I said optionally, I meant you could put at the end of the other code like this:

DECLARE @.cardid int

INSERT INTO Card(cardtype,carddetails) VALUES (@.cardtype,@.carddetails)

SET @.cardid=SCOPE_IDENTITY()

INSERT INTO Customer(cardid,customername,address,city,emailid) VALUES (@.cardid,@.customername,@.address,@.city,@.emailid)

SELECT @.cardid,SCOPE_IDENTITY()

|||

thanxs very much....