Add a record using T-SQL and Connection.Execute
How can i insert the identity in a VB-variable
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn = CurrentProject.Connection
objConn.Open
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
SELECT @@IDENTITY as 'NewID'"
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script. This is my SP
AS Insert Into Tbl_ad (ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location) VALUES (@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity return GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats") Dim subcatid = Request.form("subcatrad") Dim adtitle = Request.Form("txttitle") Dim AdDescription = Request.form("txtdescription") Dim uid = getUID(Context.User.Identity.Name) Dim areaid = Request.form("ddarea") SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.
Hello, I'm using C# to access sql server. When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use? Thanks.
I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.
FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.
hello, I'm new to store procedure in slq 2000. I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier", now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful. So how to create this store procedure ? much thanks
I have a stored proc that will insert a new row into a table with the values of the parameters you pass in. I need it to return the value of the ID that's generated by an Identity column once the row has been written and that value has been generated. If I just do a SELECT Max(), I could accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output @LabelType int , @Logo int , @Field01 char(30)
AS
INSERT INTO tbLabel (LabelType , Logo , Field01)
VALUES (@LabelType , @Logo , @PrintCC , @Field01)
How do I grab the new LabelID (the column is int, Identity) and return it from the stored proc. Any help would be greatly appreciated...
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work. Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
' Sequence ID for request magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"
' Subject for new Wac Ticket magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"
' First Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50)) magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName
' Last Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75)) magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName
DSMagic = new DataSet() magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString End If
how do i return the identity from stored procedure to asp.net code behind page? CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name) DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber code behind: public void _Insert( string _Name, { DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId); db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null); db.ExecuteNonQuery(dbCommand); int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber"); whats wrong with to the above code?
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer 'that is the variable for the new id.Dim intNewID As Integer Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity" Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL 'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-( dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim) dbCommand.Connection = dbConnection TrydbConnection.Open()dbCommand.ExecuteNonQuery() 'here i want to return the new ID!Return intNewID Catch ex As Exception Throw New System.Exception("Error: " & ex.Message.ToString()) Finally dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose() End Try End Function
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
i have found loads of topics on this but have yet to find one that gives the answer i need.I want to display the last insert id into a label/textbox after the INSERT functionhow do i do this?
I got two tables one table has the fields ie table1 orderid ofd orderdate customername where order id is autonumber the other table2 orderid ofd product id productname the problem here is thatif customer purchases 3 product at a time all the 3 products get the same ofd number ........and any 2 customers can have the same ofd number................ now i have to pull the order ID value from table 1 to table 2............ can somebody help with this i am the front end is asp.net amd the database is done on SQL server management studio
Hi, I was looking through this thread about @@Identity: http://forums.asp.net/p/1039145/1443971.aspx#1443971 I'm still unsure how to ue it. I have an Orders table, a Products table and a Products_Orders table. When I add an order to the Orders table I want the PK OrderID in this table to also update the FK OrderID in my Products_Orders table. I'm using SQL EXP05 and I'm in C#.
I know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?
I've got a problem reading the @@identity in vb.net I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found. (--> means with rsLastIdent)
comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @@IDENTITY AS Ident;"