Sychronize Key between Database and Dataset on Insert

5 Mar 2006 10:28 PM

Aeden Jameson

Hi,

I'm sure this has been asked many times before, but I haven't had a
lot luck finding an answer; not googling the question right. So I
apologize before hand. I have a TableAdapter whose insert command uses
a store procedure. When I call TableAdapter.Update() passing in the
table the database gets updated, but the primary key in of the
DataTable doesn't match the one in the database. I've read some
responses about selecting the "Refresh Dataset" option under Advanced
Options in the Dataset Designer, but I only see that option when using
a SQL statement and not a stored procedure. I prefer to ues the stored
procedures. Is there an option I can configure on the TableAdapter
that I missed? What's the simplient way to handle this?

Cheers,
Aeden

6 Mar 2006 6:04 AM

Cor Ligthert [MVP]

Aeden,

What is the magic about that stored procedure.

In fact is is only an SQL commandstring that is stored on the server and not
in your program and which can therefore have because of that a slight
performance advantage.

So build your program, copy the commandstring to your SP's and change the
commandtype.

I hope this helps,

Cor

Show quote

"Aeden Jameson" <aeden.jame***@gmail.com> schreef in bericht
news:1141597731.758060.90270@v46g2000cwv.googlegroups.com...
> Hi,
>
> I'm sure this has been asked many times before, but I haven't had a
> lot luck finding an answer; not googling the question right. So I
> apologize before hand. I have a TableAdapter whose insert command uses
> a store procedure. When I call TableAdapter.Update() passing in the
> table the database gets updated, but the primary key in of the
> DataTable doesn't match the one in the database. I've read some
> responses about selecting the "Refresh Dataset" option under Advanced
> Options in the Dataset Designer, but I only see that option when using
> a SQL statement and not a stored procedure. I prefer to ues the stored
> procedures. Is there an option I can configure on the TableAdapter
> that I missed? What's the simplient way to handle this?
>
> Cheers,
> Aeden
>

6 Mar 2006 8:51 AM

Brian Smith

I'd ignore that rather silly reply. Assuming your 'problem' is caused by
having an IDENTITY column as your primary key, all you need do is add a
SELECT statement after the INSERT inside the stored procedure, after
capturing the new IDENTITY in a variable
e.g.
    INSERT MyTable ....

    SELECT @key = SCOPE_IDENTITY()

    SELECT * FROM MyTable
    WHERE key = @key

brian smith

Cor Ligthert [MVP] wrote:
Show quote

> Aeden,
>
> What is the magic about that stored procedure.
>
> In fact is is only an SQL commandstring that is stored on the server and not
> in your program and which can therefore have because of that a slight
> performance advantage.
>
> So build your program, copy the commandstring to your SP's and change the
> commandtype.
>
> I hope this helps,
>
> Cor
>
>
>
>
>
>
>
>
> "Aeden Jameson" <aeden.jame***@gmail.com> schreef in bericht
> news:1141597731.758060.90270@v46g2000cwv.googlegroups.com...
>> Hi,
>>
>> I'm sure this has been asked many times before, but I haven't had a
>> lot luck finding an answer; not googling the question right. So I
>> apologize before hand. I have a TableAdapter whose insert command uses
>> a store procedure. When I call TableAdapter.Update() passing in the
>> table the database gets updated, but the primary key in of the
>> DataTable doesn't match the one in the database. I've read some
>> responses about selecting the "Refresh Dataset" option under Advanced
>> Options in the Dataset Designer, but I only see that option when using
>> a SQL statement and not a stored procedure. I prefer to ues the stored
>> procedures. Is there an option I can configure on the TableAdapter
>> that I missed? What's the simplient way to handle this?
>>
>> Cheers,
>> Aeden
>>
>
>

6 Mar 2006 9:07 AM

Cor Ligthert [MVP]

Brian,

> I'd ignore that rather silly reply.

Are you chosen to judge.

In my opinion it is better to learn how to fish than giving a fish.

If you disagree that, feel free, however keep your condemning replies for
your own.

Cor

6 Mar 2006 6:19 PM

Aeden Jameson

Thanks Brian. I ended up doing that. It's just nagging that I have to
have that extra statement at the bottom of every SP that I need a
primary key from.

I also figured I might abandon the TableAdapter.Update method. Create a
method UpdateWithKey that takes a, say DataTable, calls
TableAdapter.Insert, which returns the key, and set the appropriate
column.

I also tried defining an output parameter, but that makes the parameter
signature on methods like xxxxTable.AddxxxxRow awkward.

I also read about detecting the insert in the RowChanged event, which
seems reasonable. What are the downsides to this?

Cheers,
Aeden