DataSource Controls :: Retrieve Scope_identity Value From Insert Statement In Codebehind
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.
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
I need to get the id of a record created so that I can store that ifnormation in another SQL table. A snipet of my c#code is as follows:
string insertSql = "INSERT INTO [Group] ([userid], [groupname]) VALUES (@userid, @groupname); SET @groupid = SCOPE_IDENTITY()"; using (SqlConnection myConnection = new SqlConnection(connectionString)) { myConnection.Open(); SqlCommand myCommand = new SqlCommand(insertSql, myConnection); myCommand.Parameters.AddWithValue("@userid", currentUserId); myCommand.Parameters.AddWithValue("@groupname", groupTextBox1.Text); SqlParameter groupidParameter = new SqlParameter("@groupid", SqlDbType.Int); groupidParameter.Direction = ParameterDirection.Output; myCommand.Parameters.Add(groupidParameter); myCommand.ExecuteNonQuery(); myCommand.ExecuteNonQuery(); gid = (int)groupidParameter.Value; myConnection.Close(); }
The problem is that I am not getting the @groupid from the SET @groupid = SCOPE_IDENTITY() that is appended to the INSERT statement. The groupid in the SQL table is the Key of type uniqueidentifier
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();
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 ?
I have a project that I was using Access for but now find I have to change to SQL Express. Well I have no big problem with that but I now find that the SQL Statement I have been using was for OLE and does not work in SQL/Express. I am not even sure anymore if I can dump the entire CSV file in at one time? The statement I was using is below.
[Code]....
Here is my problem. What is the INSERT statement to insert a complete flat (CSV) into SQL Express? OR what method can I use to preform this task?
I have SQL Details View insert statement, I need to have a Condition checked for the Insert Query. The condition is, I do have a table by name table1 with the No of Questions (for Eg: 10). The insert statement shud insert data to the table2 (Another table) only if the no of rows is less than or equal to 10 else it shud give me a message (It has exceeded the no of questions).
How would you handle an INSERT that gets some values from another table using SELECT, where you wanted some values to come from the source table record and other values to come from a different parameter source like a session value? Is this possible?
i'm developing windows application one of its forms supposed to fill a table called rooms with data throughout a collection of text boxes and comboboxes controls, i'm successfuly bound each control to the binding source and make sure that each data adapter contains the required data but when i start to excute the insert statement
it displays an error message when i'm trying to select a value from the combobox for any parameter that is"column currenc_id is constrained to be unique value 2 is already exists"can any body help me it is very important
How do I set my insert identity return value parameter in codebehind? I have working code now but i have all of my parameters set in codebehindexcept the following:
I dragged and dropped a sqldatasource and want to do an insert statement, taking the new values from a textbox. I did this already however when i do my insert statement eg. INSERT INTO Development_Programme(MHID) VALUES (@m) HOWEVER when to select the insert parameters the 'Next' button and the 'Finish' button is shaded off, I added a Select statement and the 'Next' was unshaded HOWEVER the screen to select the insert parameters is not appearing it goes straight to the Test Query.
I've got a TextBox to capture the value of "Name" in Table A.
Ive got a CheckBoxList that displays all the pre-determined values of Table B.
When the user selects an item from the CheckBoxList i want to populate Table C with the value of the selected item from the CheckBoxList and the cooresponding ID associated with the user name for that person's entry.
There is this error troubling me whenever i tried to do something to the ShopCart such as add to cart.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ShopCart_Shopper". The conflict occurred in database "DIYERA", table "dbo.Shopper", column 'ShopperID'. The statement has been terminated.
I checked the database and the foreign key, ShopperID, was set correctly.
I have a problem with my SqlDataSource, when the web form i loaded it works OK but when I execute an insert statement and then i execute the method DataBind(), the data in my grid view is not changed.
i have a form view that takes fields from two tables 'Contract' and 'volunteers', however because i used a query builder,i have to input an insert statement. How do i create an insert statement for two tables in one query?
how do i multiply the columns like this. for now static explanation would be ok but eventually i would like to make it dynamic so there would be a text box for users to enter the amout they like e.g 160g then the table would know to multiply the columns by 1.6.
I have a select statement and want to be able to insert some spaces in the returned data:
[Code]....
For the parts with ' - ', I want to remove the dash and simply put spaces now. But when I remove the dash and put 4 spaces, I only get one space. How do I put in those 4 spaces?
I've used a sql data source to execute an insert stored procedure into my sql database. This time though, I want to execute the sp from my code behind. BUT, I'm stuck on the parameters and how to pass in the ones for the insert, and return one parameter, which is the identity of the row just inserted. The error I'm getting is. I'm missing something, but I don't see what... I think it's with the input/output parameters... Procedure or function 'sp_CloneIT' expects parameter '@ClonedMatterID', which was not supplied. My stored proc
I have been searching on here for many hours and read many posts but for some reason I am being slow and cannot figure this out. I am writing a record and need to get the identity field back. I have tried many combinations based on what I have read but the below code is where I am sitting at now. it has appdatasource.ExecuteScalar() underlined and the error says 'ExecuteScalar' is not a member of 'System.Web.UI.WebControls.SQLDataSource'. I would be very grateful if someone could point out what I am missing to get this to work.
Protected Sub SubmitApp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitApp.Click
Dim appdatasource As New SqlDataSource appdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToString appdatasource.InsertCommandType = SqlDataSourceCommandType.Text appdatasource.InsertCommand = "INSERT INTO APPLICATION (FirstName,MiddleName,LastName,AcceptTerms,IPv4Address,DateInserted) VALUES (@FirstName,@MiddleName,@LastName,@IPv4Address,@DateInserted);select @@SCOPE_IDENTITY" appdatasource.InsertParameters.Add("FirstName", fname.Text) appdatasource.InsertParameters.Add("MiddleName", minit.Text) appdatasource.InsertParameters.Add("LastName", lname.Text) appdatasource.InsertParameters.Add("DateInserted", DateTime.Now()) appdatasource.InsertParameters.Add("IPv4Address", Request.UserHostAddress.ToString) Dim appid As Integer = appdatasource.ExecuteScalar() End Sub
have a very important issue,i have three Stored Procedures Sp1,Sp2 and Sp3 .the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:INSERT EXEC statement cannot be nested I tried to change the place of execute Sp2 and it display me another error:Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
