I want to return the value of Identity Key which in this case is an AutoNumber field in my table and place the value of that field of the newly added record into a textbox. Here's what I got so far, my insert works, but no return as of yet:
Protected Sub Button2_Click(B yVal sender As Object, ByVal e As System.EventArg s) Handles Button2.Click
Dim sql As New SqlCommand
'Dim myconn As Data.SqlClient. SqlConnection
Dim mycmd As Data.SqlClient. SqlCommand
Dim ResultReader As SqlDataReader
Dim result As String
Dim mySqlConnection As SqlConnection = New SqlConnection(C onfigurationMan ager.Connection Strings("MAS_XC MConnectionStri ng").Connection String)
mySqlConnection .Open()
result = ""
ResultReader = Nothing
mycmd = New SqlCommand("SEL ECT * FROM CA_IT_STORYBOOK ", mySqlConnection )
mycmd.CommandTe xt = "INSERT INTO CA_IT_STORYBOOK (SalesOrderNumb er, InvoiceNumber, StoryBook) VALUES (012,334,'NewTe st3');Select @OutVar = SCOPE_IDENTITY( )"
mycmd.ExecuteNo nQuery()
' mycmd.ExecuteSc alar()
If ResultReader.Ha sRows Then
result = result & vbCrLf & ResultReader.It em(0).ToString( )
TextBox1.Text = result
End If
mySqlConnection .Close()
End Sub
6 1740
I think it's because "Select @OutVar = SCOPE_IDENTITY( )" is just an assignment statement. If you really need that statement there, select @OutVar again to get its value. If not, you can select SCOPE_IDENTITY( ) directly instead.
You can do it like this: - mycmd.CommandText = "INSERT INTO CA_IT_STORYBOOK(SalesOrderNumber, InvoiceNumber, StoryBook) VALUES (012,334,'NewTest3');Select @@IDENTITY"
Visit this to know more about it.
Also, is your TextBox1 a multiline control? You are using a carriage return (vbCrLf), try to take that away if your textbox is not a multiline.
The main difference between @@SCOPE_IDENTIT Y and @@IDENTITY is
@@SCOPE_IDENTIT Y - Gets identity value for the last executed SQL statement with in the scope. Its a local variable
@@IDENTITY - Its a global variable. For the last executed statement if you have any trigger/function then after that stmt executed, it returns that stmts executed identity value.
Based on your requirement you use those varibles
The main difference between @@SCOPE_IDENTIT Y and @@IDENTITY is
@@SCOPE_IDENTIT Y - Gets identity value for the last executed SQL statement with in the scope. Its a local variable
@@IDENTITY - Its a global variable. For the last executed statement if you have any trigger/function then after that stmt executed, it returns that stmts executed identity value.
Based on your requirement you use those varibles
Thanks, how can I actually show the value of the Identity Id in a textbox?
ck9663 2,878
Recognized Expert Specialist
This would depend on your front-end tool that you used.
-- CK
This would depend on your front-end tool that you used.
-- CK
I just have a regular asp page with a button and a textbox. When I hit the button I want my record is being inserted into the table, but then I want to retrieve the Identity Id and show it in the textbox.
ck9663 2,878
Recognized Expert Specialist
First, make sure your stored proc/function is already returning what you want. On your front-end side, create a recordset object. Execute your module. It will return the value as an object of the recordset (column). You can store that value as value property of your textbox object inside your form.
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Scrappy |
last post by:
I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:
SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::',...
|
by: Lauren Quantrell |
last post by:
I'm using an Access2K front end on a SQL Server2K backend.
I use Scope_Identity() in a lot of stored procedures to pass the
newwly inserted record's unique ID to the next select statement in the
same stored procedure.
What I'm wondering is how I can pass the Scope_Identity back to the
calling application.
I'm hoping someone can show me the SP code and the aceess code needed
to accomplish the following:
I insert a new record in a table...
|
by: Larry |
last post by:
I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.
What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact...
|
by: Hardy Wang |
last post by:
Hi all,
I have the following codes, but SCOPE_IDENTITY() just returns NULL to
me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works
fine!! Since I have a trigger on the table, I have to use SCOPE_IDENTITY().
Any ideas?
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
//Create the dataadapter
|
by: Mr Newbie |
last post by:
I was using this fine
sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE
(SectionID=SCOPE_IDENTITY());"
UNTIL. . . .
I added a parameter, now it fails, it doesent matter if I use the parameter
or not, when I add a param, it falls over, Any Ideas ?
| |
by: Mr Newbie |
last post by:
The following code is comprises a function to create a section of a
documemt.
Each section is stored in a table. This successfully inserts a record in the
Sections table, but the
scope_identity() returns DbNull. If I remove the parameter 'pSectionName'
and replace it with dummy value, it works fine. Alternatively, if I use
@@IDENTITY with or withour the parameter, that works fine too.
It seems there is a problem with using...
|
by: David Lozzi |
last post by:
Howdy,
ASP.Net 2.0 using VB on SQL 2005
This is a two fold issue.
I have a DetailsView control which users can insert or edit items. Editing
works great. Insert works great however I need to display the form once the
user has entered the information and clicked Add.
|
by: Mike |
last post by:
Sql Server, Scope_Identity(), Ado.NET:
Which is better? Using an output parameter to return Scope_Identity through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the
procedure or ad hoc SQL and using ExecuteScalar()?
Thanks.
|
by: MrMancunian |
last post by:
Ok, I'm stuck on a design problem and I need some feedback how to go around it.
CASE:
Pathologists can request stains on certain tissues. It's possible to request more than one stain a time. The program where they make these requests (which is third-party software) creates an XML-file for one stain. So, if two stains are requested on one piece of tissue, it will create two XML-files. The program I'm writing needs to parse those XML-files,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |