Inserting and updating values

  • Thread starter Thread starter Keon
  • Start date Start date
K

Keon

I am trying to figure out the following information when storing some info
to the database, I don't know how to do this and therefore I am stuck.

I am going to try and explain this to the best of my ability.

I have two tables in my db, my first table I am writing the following
information to it.

ex....
// table one
FieldID, fName, lName, Address, State, Zip;

// table two
ParentID, Data;

// FieldID is an auto generated int field
FieldID.Value = 0;
fName.Value = "Jim";
lName.Value = "Anderson";
Address.Value = "747 Quarry Rd";
State.Value = "GH";
Zip.Value = "83920";
cmd.ExecuteNonQuery();

This is where I run into problems. For my second table, I need to get the
same FieldID value for ParentID. How do I go about that? And I am really
unsure on how
to write to two different tables at the same time. Not at the same time,
but one after the
other. After I do the cmd.ExecuteNonQuery() on the first table, what do I
do for the second
table?

ParentID.Value = Table1.FieldID value (how do you do this?)

Can someone help me out with this?


Thanks
 
If your database is SQL Server, I know that you can use an output parameter
(presumably other database engines support the same feature).

Using the output parameter, you would run your first query to insert the
parent record and generate the new ID value "FieldID", returning it to your
app via the Output parameter.

Then you would make a second call to insert the child record with its
supplemental data "Data".

Alternatively, you could make just one call by passing the "Data" value
along with your first call, then in the sproc that is handling your parent
record insert, modify it to obtain the new primary key (see
SCOPE_IDENTITY() ) just created and then pass that plus your "Data" value to
the child table.


CREATE PROCEDURE dbo.usp_MySaveSProc

@FieldID INT,
@fName VARCHAR(15),
@lName VARCHAR(20),
@Address VARCHAR(100),
@State VARCHAR(2),
@Zip VARCHAR(8),
@Data VARCHAR(xxx) --Set as data type you need

AS

-- Temp var for storing new auto-generated key
DECLARE @NewFieldID INT

Insert INTO [table-one-name]
(fName, lName, Address, State, Zip)
VALUES
(@fName, @lName, @Address, @State, @Zip)

-- Get the primary key the first insert creates.
SELECT @NewFieldID = SCOPE_IDENTITY()

Insert into [table-two-name]
(ParentID, Data)
VALUES
(@NewFieldID, @Data)


Depending on your app's requirements, I would also suggest that you wrap the
whole lot in tests for duplicates (e.g. same firstname, surname and address)
and allow for result returns that allow your app to handle success or
failure conditions.

Alec
 
Thanks a lot Alec,

Let me see if I can figure out how to do this now. I am using SQLite, I
think there is
something called LastInsertID() or something like that. I cant seem to find
any examples
on it when using SQLite.net.

Thanks again for your help.

Keon
 
Back
Top