DataSource Controls :: Get The Inserting ID Out Of The Record Using Scope_identity
Jun 9, 2010
Im trying to get the ID out of the record that i'm inserting using scope_identity. I've add the following line of code to the Insert statements in the sqldatasource - SET @insID = Scope_Identity() I've add the following line to the Insert parameter list
asp:Parameter
Name="insID"
Type="Int32"
Direction="Output"
I now want to insert a number records into the database using the following:
Dim insID As Object = e.Command.Parameters("@insID").Value ' gets out latest ID
Dim ordernumber As String = "CX" & insID.ToString & DateTime.Now.DayOfYear.ToString
sqlOrders.InsertParameters("orderNumber").DefaultValue = ordernumber
sqlOrders.InsertParameters("orderDate").DefaultValue = DateTime.Now
sqlOrders.InsertParameters("customerID").DefaultValue = userID
sqlOrders.InsertParameters("productID").DefaultValue = productID
sqlOrders.InsertParameters("totalPrice").DefaultValue = "0.00"
sqlOrders.InsertParameters("status").DefaultValue = "Paid"
I currently have this code in the SQLDataSource_Inserting sub but the first line only works once the record is inserted. How can i get the ID out as im inserting the parameters?
View 3 Replies
Similar Messages:
Jun 27, 2010
I have an orders table with a column names orderID. What I want to do is insert a new record and get the value from the new orderID column. The orderID is my Primary key. The Insert SQL I have now is -
INSERT INTO orders
(creditAutorizarionNumber, OrderName, OrderPersonPhone, OrderPersonEmail)
VALUES (@creditAutorizarionNumber,@OrderName,@OrderPersonPhone,@OrderPersonEmail)
How do I write the query to get the newly entered orderID ?
View 6 Replies
Dec 20, 2010
I am creating an album using the following stored procedure, which returns the primary key for the newly created record:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fpa_sp_albums_update_insert]
@album_id int,
@album_name nvarchar (50),
@album_descr nvarchar(250),
@album_img_cover_id_FK int,
@album_creation_date date
AS
If @album_id > 0
UPDATE [fpa_albums]
SET
album_name=@album_name,
album_descr= @album_descr,
album_img_cover_id_FK = @album_img_cover_id_FK,
album_creation_date = @album_creation_date
Where ((album_id = @album_id))
Else
INSERT INTO [fpa_albums] (
album_name,
album_descr,
album_img_cover_id_FK,
album_creation_date)
VALUES (
@album_name,
@album_descr,
@album_img_cover_id_FK,
@album_creation_date)
Return SCOPE_IDENTITY();
I execute the above stored procedure using the SQLHELPER. The VB.NET code for the page is as follows:
[Code]....
However, the createdAlbumID always shows -1 instead of the id for the newly created album.
View 1 Replies
Jan 14, 2010
I have the logged-in user's Guid stored in a session variable. My understanding is that when the Guid is passed to the session variable it becomes a string. When I use the detailsview control I have to convert the session variable back to a guid and I've been doing it like this and it seems to work fine:
e.values("UserId") = session("LoggedInUserId")
Dim UserId as Guid = New Guid()
When I try to use the Insert() command after the following:
SqlDataSource3.InsertParameters("UserId").DefaultValue = Session("LoggedInUserId")
I get an "InvalidCastException" error with the comment "Conversion from type 'Guid' to type 'String' is not valid." I'm assuming the session variable needs to be converted to a type Guid since that's what I've had to do with the Detailsview control. I've tried a few different ways of converting the session variable back to a Guid so the insert command will work, but no matter which method of conversion I try I get errors such as "value of type system.guid cannot be converted to 'string'. I'm confused because I thought I was supposed to convert it from a string to a Guid. The "UserId" parameter is a non-primary key uniqueidentifier in the database.
View 2 Replies
May 23, 2010
I have an entity model, in which I have a table per type inheritance. Is it possible to perform an insert using ado.net dataservice and asp.net ajax library? I was trying the following client code :
dataContext.insertEntity(childData, "Parent");
I was getting this response from server : Error processing request stream. Type information must be specified for types that take part in inheritance.
View 1 Replies
Apr 3, 2010
I am in a serious trouble right now, i wanted to know that how to write a query in LINQ to SQL to retrieve identity column's current value before inserting any value.
I need this cos i m building a windows app n i need to display this value before inserting a record. I have tried DataContext.ExecuteCommand('DBCC CHECKIDENT (Tablename)') but it returns the number of rows modified.
View 2 Replies
Jan 29, 2010
My insert is adding a record to the database in the call to the stored procedure in SQL Server, but a -1 is returned - Shouldn't it return 0 if the insert/update was successful?
Result = myCommand.ExecuteNonQuery();
View 3 Replies
Apr 27, 2010
I've got a sp as below:
The functionality I want to achieve is that when isHomePageFeatured is checked then this record is set to 1 for that column and for all the other records in the table it is set to 0.
This is fine for update as I can use the property Id to compare against, but for the newly inserted record it doesn't yet have a propertyId so I wondered if using newID() to affect all other records would have the same affect.
[Code].....
View 3 Replies
Aug 31, 2010
I need to get the identity (scope_identity) value of the newly inserted record. I don't see a way to do this.
I'm using
[Code]....
View 2 Replies
Jan 19, 2010
I'm trying to insert data into two tables that are related within a store procedure. After the first insert I want to get the id of the inserted row to use as a parameter during the insertion of data in the other table. I'm sure this is a common task, and I have looked into things like scope_identity(), @@identity and ident_current. However I'm not sure exactly how to use the id as a parameter.My attempt now looks like:
DECLARE @RETURN_VALUE
INSERT INTO Table1(test_name) VALUES ('asdf')
@RETURN_VALUE = SCOPE_IDENTITY()
INSERT INTO Table2(test_name) VALUES(@RETURN_VALUE)
View 4 Replies
Sep 24, 2010
I've looked all over for this answer and had to resort to several resources. I wanted to post this here because it was so hard for me to find a simple example. If it exist other places then please excuse my post and put references to it on a reply.
So, How do I return the Primary Key Identity when I click the control with the 'Insert' command on a FormView? (With very least minimal code) Help me if there's a quicker way.
<InsertItem>
<SqlDatasource ID="sqlSource">
<InsertCommand = "INSERT INTO ... ; SELECT @ID = Scope_Identity()">
<InsertParameters>
<asp:Parameter Name="ID" Direction="Output" Type="Int32" />
</InsertParameters>
</SqlDatasource>
</InsertItem>
Protected Sub sqlSource_Inserted()
Dim cmd as System.Data.Common.DbCommand = e.command
Dim strID as String
sqlSource.InsertParameters("ID").DefaultValue = cmd.Parameters("@ID").Value.ToString()
strID = sqlSource.InsertParameters("ID").DefaultValue.ToString()
lblResults.Text = strID
End Sub
View 1 Replies
Jun 7, 2010
Iam inserting ,updating in one DBfunction .i want to retrieve identityvalue when in insert.for that am adding select SCOPE_IDENTITY() in insert statement.how i can i get this value??
public int funAddEdit_rtblIncidentTypesDB(clsCM_Inc_IncidentTypesData objData, int actiontype)
{
string qry = "";
if (actiontype == 1)
qry = "INSERT INTO _rtblIncidentType (cDescription,iEscGroupID,bAllowOverride,bRequireContract,iIncidentTypeGroupID,iWorkflowID,bAllowOverrideIncidentType,bPOIncidentType,cDefaultOutline) VALUES ('" + objData.cDescription + "'," + objData.iEscGroupID
+ ",'" + objData.bAllowOverride + "','" + objData.bRequireContract + "'," + objData.iIncidentTypeGroupID + "," + objData.iWorkflowID + ",'" + objData.bAllowOverrideIncidentType + "','" + objData.bPOIncidentType + "','" + objData.cDefaultOutline + "'); select
SCOPE_IDENTITY() ";
else if (actiontype == 2)
qry = "UPDATE _rtblIncidentType SET cDescription='" + objData.cDescription + "' WHERE idIncidentType=" + objData.idIncidentType;
int result = SqlHelper.ExecuteNonQuery(clsHelper.ConnectionString.ToString(), CommandType.Text, qry);
return result;
}
if i exceute the above query individually,it returns iddentity value,,bt unable to retireve the value in codebehind.
View 1 Replies
Jul 10, 2010
I'm trying to create multi insert query in single stored procedure along with scope_identity variable. while execute the aspx page getting error "Procedure 'sp_seller_ins' expects parameter'@new1', which was not supplied." I have created stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_seller_ins]
@txt_n_properity VARCHAR(50),
@txt_t_properity VARCHAR(50),
@txt_prop_for VARCHAR(50),
@txt_flat_no VARCHAR(50),
@txt_loc_country VARCHAR(50),
@txt_loc_state VARCHAR(50),
@txt_loc_district VARCHAR(50),
@txt_loc_town VARCHAR(50),
@txt_loc_area VARCHAR(50),
@txt_loc_locality VARCHAR(50)
AS
BEGIN
declare @new1 int
declare @new2 int
INSERT INTO dbo.sel_pro_details( nature_property, type_property, property_for, flat_no) VALUES ( @txt_n_properity, @txt_t_properity, @txt_prop_for, @txt_flat_no);
set @new1 = scope_identity()
INSERT INTO dbo.sel_loc_details( country, state, district, town, area, locality, pro_details_id) VALUES (@txt_loc_country, @txt_loc_state, @txt_loc_district, @txt_loc_town, @txt_loc_area, @txt_loc_locality, @new1);
set @new2 = scope_identity()
end
code behind aspx page
sqlconn mySql = new sqlconn();
mySql.CreateConn();
mySql.Command = mySql.Connection.CreateCommand();
mySql.Command.CommandType = System.Data.CommandType.StoredProcedure;
mySql.Command.CommandText = "sp_seller_ins";
mySql.Command.Parameters.Add("@txt_n_properity", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_n_properity"].Value = txt_n_properity.Text;
mySql.Command.Parameters.Add("@txt_t_properity", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_t_properity"].Value = txt_t_properity.Text;
mySql.Command.Parameters.Add("@txt_prop_for", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_prop_for"].Value = txt_prop_for.Text;
mySql.Command.Parameters.Add("@txt_flat_no", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_flat_no"].Value = txt_flat_no.Text;
mySql.Command.Parameters.Add("@txt_loc_country", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_country"].Value = txt_loc_country.Text;
mySql.Command.Parameters.Add("@txt_loc_state", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_state"].Value = txt_loc_state.Text;
mySql.Command.Parameters.Add("@txt_loc_district", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_district"].Value = txt_loc_district.Text;
mySql.Command.Parameters.Add("@txt_loc_town", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_town"].Value =txt_loc_town.Text;
mySql.Command.Parameters.Add("@txt_loc_area", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_area"].Value =txt_loc_area.Text;
mySql.Command.Parameters.Add("@txt_loc_locality", SqlDbType.VarChar);
mySql.Command.Parameters["@txt_loc_locality"].Value =txt_loc_locality.Text;
mySql.Command.ExecuteNonQuery();
mySql.Command.Dispose();
mySql.Connection.Close();
mySql.CloseConn();
View 1 Replies
Oct 11, 2010
When page load it is invisible. I have a button1 onclick make it visible and people can input data the press insert. I set it invisible right in Formview Insert eventhandler code.
When people click that button1 again to add 2nd record the form appear again as desired. However, the data from 1st record is still there.
What code I can make in eventhandler to make sure when button1 is clicked the Formview does not contain any data from last entry ?
The formview clear the data from 1st record if I did not set visible/invisible.
View 2 Replies
May 4, 2014
I have a GridView1 With Paging allowed, PageSize = 10. And a Nested GridView2. The INSERT function on nested GridView2 works well until the number of record is 10.
On inserting the 11th record, the GridView1 is recorded normally in page 2 of 2. The Nested GridView2 does not fired properly. it appears empty on the 11th record (page 2 of 2), but instead add its record on the last recorded ID on page 1 of 2.
When I remove the paging, everything works well.
How to resolve the paging issue with Nested GridView on "INSERT" record?
View 1 Replies
Nov 7, 2010
I have a formview and inside it there are two textboxes taking integer as input.I put a condition that if both textboxes are empty then user should be notified that "Please put value in any one of the textbox".It is working very fine.From database side DBA checked that if user input any integer value in any one of the two textboxes and if it is already found in database then there is no insertion.The problem is that everything is working fine and i checked in the database that the record is also not inerting due to duplicate check but user is getting message that "Record inserted successfully."How to stop this message and how to notify user that you have entered duplicate value and transaction should also not been done. Means all inputs should be there rather than clearing textboxes that could be done when "Record Inserted."
[Code]....
View 1 Replies
Aug 25, 2010
On inserting record, I want to check that for example if a user enters a telephone number for any record is already in the database then it should validate that the number already exists in table and you cannot enter a duplicate value.
Then if user will provide some other telephone number then he allows to insert record.
For insertion of record I used ObjectdataSource.
User will enter telephone number in a simple textbox.
I may want to inform that I am working in asp.net2.0 and also not allowed to use any third party control or ajaxtoolkit.
View 11 Replies
Jun 24, 2010
I am counting from a table for that i have written code as below
protected void get_Id_Afterpoint()
{
int counter = 0;
string strSql = "select count(*) as ID from tblEnergy where ID=?";
OdbcCommand com = new OdbcCommand(strSql, con);
com.Parameters.AddWithValue("ID", DropDownList1.SelectedValue);
OdbcDataAdapter oda = new OdbcDataAdapter(com);
DataTable dt = new DataTable();
oda.Fill(dt);
if (dt.Rows.Count == 0)
{
lblID2.Text = "1";
}
else
{
counter = dt.Rows.Count;
counter = counter + 1;
lblID2.Text = Convert.ToString(counter);
}
}
there is no record related to DropDownList1.SelectedValue. but as i am counting if(dt.rows.count) and i put break point on the bolded part it shows 1 record. how it can be possible?
View 5 Replies
Jun 22, 2010
I am having trouble changing page numbers on my gridview after deleting a record if the deletion of the record causes there to be no more records on that page. Currently I have a form below the gridview that adds a record to the datasource of the gridview.
[Code]....
Code behind stuff:
[Code]....
Now with this code above, if I insert a new record using the form below the gridview, it will reload and go to the new page if the insertion causes the record to appear on a new page, however when I delete that record, thus causing nothing to be on that page, the gridview visually just dissappears. I've got the gridview bound by a linqdatasource control, and on the page_load function I only databind if the page is not a postback.
View 2 Replies
Jan 12, 2010
I am trying to pass in as an insert parameter for SQLds2 the scope_identity of the insert of SQLds1.
Exception Details: System.InvalidOperationException: Error executing 'InsertCommand' in SqlDataSource 'SqlDataSource1'. Ensure the command accepts the following parameters: @Name1, @NewId
[Code]....
View 4 Replies
Aug 9, 2010
This is the first time I have been working on databases and I am trying to insert data using an object datasource but no data is being inserted and no exception is thrown. I'm not sure where I am going to wrong. I am putting user data into session variables and inserting the session variables into the dataacess class layer and have a try and exception but nothing being thrown. Any help would be really appreciated because I don't know even where to start looking with this or how I can get the error to be thrown. Please see some of my code below to give an idea of what I am doing
Object Access Class
public void InsertCarPolicyHolder(PolicyHolderClass ph, CarInsuranceOptions options)
{
[code]...
View 2 Replies
Oct 10, 2010
[Code]....
[Code]....
[Code]....
[Code]....
[Code]....
View 2 Replies
Aug 14, 2010
Students can subscribe for any project via a webform. Table 'project ' contains all the proposed project and table 'student' contain the name of the student and the project-id.Now, suppose there is a limit of 4 students for project 'A' and there are already 3 subscribed students for that project. One more student can choose that project. The code-behind checks whether the limit is not reached (by counting the amount students for that project in table 'student') before inserting that student in the table 'student'.
My problem is that when two students fills the webform for the same project and click on the 'save-button' exactly at the same time, the code has no time to check the limit and both students are inserted into table 'student'. Is there a way to lock the table or something in order to preventing this?
[Code]....
View 5 Replies
Aug 4, 2010
I am using ASP.NET 2.0, C#, SQL Server 2005. I have a gridview which has 10 records. I want to only insert few records(for example: 3 records) to the database. How to insert only part of the whole data to the database.
View 3 Replies
Dec 26, 2010
i got the following error when i tried to inser a recrord from my MVC web application , although the insert will work fine when i tried to inser the record manually from the databse explorer, and i checked all the FK relationship they sound valid:-
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Articles_Users". The conflict occurred in database "84A669DA0E532BEFAFBE7BE38367071F_IONALNETWORKWEBAPPLICATIONPROFESSIONALNETWORKWEBAPPLICATIONAPP_DATAPROFESSIONALNETWORKDB.MDF", table
"dbo.Users", column 'User_ID'.The statement has been terminated.
View 12 Replies
