Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

How to modify an INSERT command within Visual Studio

I would like to retrieve an auto-Increment PK while while inserting a new record. I have tried manipulating the standard tabladapter.insert() command, I have tried adding a new stored procedure as well as a new insert command.. I just learned the Identity_Scope () is used for this purpose..

However, I do not know where to place this bit within existing commands or how to create a new command that incorporates this...

i will not ask any extra questions within this, but I am no expert at this and am learning by doing.

0
Jeenyusx10
Asked:
Jeenyusx10
2 Solutions
 
rajmrajiCommented:
if you create a stored procedure, your last statement shoudl be
select scope_identity(), which will result in the inserted identity being returned as a single row, single column table
0
 
tahula2Commented:
If you have not already created I think need to create a sequence and insert the nextval.  Information on sequences is linked to below:

http://download.oracle.com/docs/cd/A97630_01/server.920/a96540/statements_615a.htm#2067095
0
 
Jeenyusx10Author Commented:
@rajmraji:

Ok I tried that in the query builder for a new SP..

It says Unable to parse query text.. Are you familiar with Visual Studio?

@tahula2:

The PK I am trying to receive is Auto-increment.. So as the insert happens, the PK is made automatically, and I need to retrieve this value.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ZhaolaiCommented:
From the question you closed below:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26441989.html

In your project, double click on your dataset's Designer.vb file, find the adapter's Insert Commmand, like this:

Me._adapter.InsertCommand.CommandText = "INSERT INTO `LookupTable` (`rectype`, `KeyValue`, `Datavalue`) VALUES (?, ?, ?)"

Add the following code:
; SELECT SCOPE_IDENTITY()

to the end of the command, like this:

Me._adapter.InsertCommand.CommandText = "INSERT INTO `LookupTable` (`rectype`, `KeyValue`, `Datavalue`) VALUES (?, ?, ?); SELECT SCOPE_IDENTITY()"

Then when you insert the record, do this:

                Dim ID as Integer =CInt(AJobTableAdapter.Insert(Supp_NumLabel2.Text.ToString, _
                                        Start_DateDateTimePicker.Value.ToString, _
                                       Property_AddressTextBox.Text.ToString, _
                                       Property_CityTextBox.Text.ToString, _
                                       Property_StateTextBox.Text.ToString, _
                                       Property_ZipCodeTextBox.Text.ToString, _
                                       Property_DescTextBox.Text.ToString, _
                                       Reo_NumberTextBox.Text.ToString, _
                                       Loan_Orig_NumTextBox.Text.ToString, _
                                       Work_ScopeTextBox.Text.ToString, _
                                       NotesTextBox.Text.ToString))



0
 
Jeenyusx10Author Commented:
It looks fantastic. Ill have to test in the morning. No need to go in depth, but is this possible by using the visual query builder?
0
 
ZhaolaiCommented:
Sure, you can, as long as you see the Insert Into command, add the Select SCOPE_IDENTITY() to the end of the command prefixed with a semi-colon ";".
0
 
Jeenyusx10Author Commented:
Here's what my code looks like...
 And I have no idea why It looks so bulky.. Should I literally place SELECT SCOPE_IDENTITY() at the end?

Me._adapter.InsertCommand.CommandText = "INSERT INTO [Job] ([Supp_Num], [Start_Date], [Property_Address], [Property_City]," & _
                " [Property_State], [Property_ZipCode], [Property_Desc], [Reo_Number], [Loan_Orig" & _
                "_Num], [Work_Scope], [Notes]) VALUES (@Supp_Num, @Start_Date, @Property_Address," & _
                " @Property_City, @Property_State, @Property_ZipCode, @Property_Desc, @Reo_Number" & _
                ", @Loan_Orig_Num, @Work_Scope, @Notes);" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "SELECT Property_Num, Supp_Num, Start_Da" & _
                "te, Property_Address, Property_City, Property_State, Property_ZipCode, Property_" & _
                "Desc, Reo_Number, Loan_Orig_Num, Work_Scope, Notes FROM Job WHERE (Property_Num " & _
                "= SCOPE_IDENTITY())"

Open in new window

0
 
Jeenyusx10Author Commented:
I added it to the end and it still only returns 1 for return value
0
 
ZhaolaiCommented:
Try this:

Me._adapter.InsertCommand.CommandText = "INSERT INTO [Job] ([Supp_Num], [Start_Date], [Property_Address], [Property_City]," & _
                " [Property_State], [Property_ZipCode], [Property_Desc], [Reo_Number], [Loan_Orig" & _
                "_Num], [Work_Scope], [Notes]) VALUES (@Supp_Num, @Start_Date, @Property_Address," & _
                " @Property_City, @Property_State, @Property_ZipCode, @Property_Desc, @Reo_Number" & _
                ", @Loan_Orig_Num, @Work_Scope, @Notes);" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "SELECT SCOPE_IDENTITY()"

0
 
Jeenyusx10Author Commented:
Still returns 1...
0
 
ZhaolaiCommented:
Take a look at the following link:
http://forums.asp.net/t/990365.aspx

What you need to do is in your tableAdapter's Designer.vb file's Insert event:

            Try
                Dim returnValue As Integer = Me.Adapter.InsertCommand.ExecuteNonQuery
                Return returnValue

Change ExecuteNonQuery to ExecuteScalar
0
 
Jeenyusx10Author Commented:
It works! Finally. I was actually reading a page just like the one you sent me.. but it wasn't specific enough to tell me where to make the change.. and then I had to reload the dataset because I screwed something up and it produced over 100 errors and each End tag for every function was sqiggly lined..

Anyway, it is returning the appropriate values... Thank you, so..so...so much

Seems like everyone hits and runs these days looking for quick points.. So I really appreciate you stickin through with me
0
 
ZhaolaiCommented:
Glad I helped. I too learned something in the process, even though I have not had much experience in SQL for about 3 years. :)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now