Please help!! How to get parameter back in ADO.NET

  • Thread starter Thread starter nashak
  • Start date Start date
N

nashak

Hi,

We are not using Stored Procedures. I need to get back the value of the
primary key (ID) field of the table once I've inserted a new record.

I have a Insert statement as follows:
cmdInsertStudy.CommandText = "Insert into " & TableName & "(" & _
strCol & ") values (@p1, @p2, @p3, @p4,
@p5, @p6, @p7) "

i.e passing 7 parameters.

cmdInsertStudy.CommandType = CommandType.Text
daStudy.InsertCommand = cmdInsertStudy

'Created new parameter to get the value of the ID field that
'is generated by sql server

Dim myParam As SqlParameter =
daStudy.InsertCommand.Parameters.Add("@StudyID", SqlDbType.Int, 10,
"StudyID")
myParam.Direction = ParameterDirection.Output

daStudy.InsertCommand.Transaction = trname
daStudy.Update(dsEmptyStudy, TableName)

What do I do now?


Thanks
 
First, we need a bit more information. What type of DBMS engine are you
using? Jet/Access, SQL Server, Oracle, 3x5Card?
Note the OUTPUT parameters ONLY work with stored procedures.
If you aren't using a SP, you need to query the newly created identity
value. http://www.betav.com/msdn_magazine.htm has an article that might
help.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hello Bill,

I am using SQl server 2000. Since our dataset tables map one-to-one
with our datasource tables, we are using commandbuilder to generate a
'generic' dataclass. Hence we are trying to avoid using stord
procedures and thereby we are not hardcoding parameters etc. in our
code.

But it seems that we might have to do something to get the primary key
value back from the database after an insert. There is a way by using
RowUpdated however I do not know how to do that.

Thanks
 
Hello Amit,

Where do i store this value that will be returned? im my output
parameter? Will this work for Sql Server or is this method for
Access/Jet?


Thanks
 
Nashak,

To retreive the identity - WHILE you are using Dynamic Sql, try using
batched queries and query the Sql server database for @@IDENTITY or
@@SCOPE_IDENTITY.

BTW do you believe in sublimnal voices??

<sublimnal_voice>Bill Vaughn might spank you for using command(don't
use)builder.</sublimnal_voice>

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
This is a Sql Server specific thing.

Here is an example to help ya out :-P
http://msdn.microsoft.com/library/e...vingidentityorautonumbervalues.asp?frame=true

Now before you ask the question "But I'm using CommandBuilder", let me
answer that for you ...

If you MUST use command builder, retreive the command text for the generated
command, populate a dataadapter with a relevant command, then use the above
example and pretend the commandtext generated out of command builder is
something you wrote.

Lemme know if there are any other questions.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Here is an example to help ya out :-P
http://msdn.microsoft.com/library/e...vingidentityorautonumbervalues.asp?frame=true

Now before you ask the question "But I'm using CommandBuilder", let me
answer that for you ...

If you MUST use command builder, retreive the command text for the generated
command, populate a dataadapter with a relevant command, then use the above
example and pretend the commandtext generated out of command builder is
something you wrote.


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Hello Sahil,

I did go through the link earlier but thought the first example
pertained to Stored Procedure (which I'm not using) and the second
example was for Access (I'm using Sql Server). I'll try to modify the
second example and see if that works.

Thanks,


Abhay
P.S. Yes I'm aware of 'subliminal voices' but did not know till now
that Bill was creating them :-)
 
Back
Top