Solved

How to modify an INSERT command within Visual Studio

Posted on 2010-08-31
13
293 Views
Last Modified: 2012-08-13
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
Comment
Question by:Jeenyusx10
13 Comments
 
LVL 1

Expert Comment

by:rajmraji
ID: 33570490
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
 
LVL 2

Expert Comment

by:tahula2
ID: 33570510
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
 

Author Comment

by:Jeenyusx10
ID: 33570566
@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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33573563
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
 

Author Comment

by:Jeenyusx10
ID: 33573768
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33576913
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Jeenyusx10
ID: 33578314
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
 

Author Comment

by:Jeenyusx10
ID: 33578343
I added it to the end and it still only returns 1 for return value
0
 
LVL 17

Accepted Solution

by:
Zhaolai earned 500 total points
ID: 33578374
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
 

Author Comment

by:Jeenyusx10
ID: 33578486
Still returns 1...
0
 
LVL 17

Assisted Solution

by:Zhaolai
Zhaolai earned 500 total points
ID: 33578628
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
 

Author Comment

by:Jeenyusx10
ID: 33578947
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33579292
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now