DataSource Controls :: Multi Insert Query Using Scope_identity Not Working

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


Similar Messages:

DataSource Controls :: Pass In As An Insert Parameter For SQLds2 The Scope_identity Of The Insert Of SQLds1

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

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.

View 1 Replies

DataSource Controls :: SCOPE_IDENTITY() - Insert A New Record And Get The Value From The New OrderID Column

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

DataSource Controls :: Gridview Insert Operation With Multi Value Listbox?

Dec 15, 2010

I am inserting mortgage loan scenarios in via GridView table. Let's say $100K loan with FICO score 700 in state of MN.

Sometimes I want to apply the same scenario to multiple states rather than Inserting one row at a time. I figured a Listbox with SelectionMode="Multiple" option would be to way to go.

Here is some code snippets:

[Code]....

View 7 Replies

DataSource Controls :: Update Query Not Working When Using Query Builder To Configure Table Adapter

Jan 15, 2010

[code]...

This query works perfectly on the query analyser.

But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.

What could I be getting wrong in this case.

NB:Existing GalleryID has been supplied.

View 1 Replies

DataSource Controls :: Insert Query Statement?

Aug 16, 2010

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).

View 3 Replies

DataSource Controls :: Using IF Statements In LINQ Insert Query?

Jun 7, 2010

I tried to find a LINQ section on this forum, but couldn't, so I'll post this here since is deals with LINQ, C#, MVC, SQL Server, and well...just about everything.

Here's the details....My Application is an ASP .Net MVC 1.0 app, using C# 3.5, SQL Server 2005 Std, and LINQ to Entity.

In my View, I have numerous Html.CheckBox controls, they get passed to my Controller as a string collection via a JQuery Ajax call. The values of each checkbox are seperated by a comma(,). I then Split the string, to get the individual values. The overall purpose to to allow users to build a custom report with only the data elements they have checked the boxes for. Here is my controller:

[Code]....

The part that I am having a problem with is that I only want to Insert the data elements into my DB table for the ones that the user has selected the checkbox for. So, for example, I'd like to do something like this:

[Code]....

I don't know if there is a LINQ switch statement in which I can access outside variables and the columns of the table, or any thing like that.

View 1 Replies

DataSource Controls :: How To Create An Insert Statement For Two Tables In One Query

Apr 1, 2010

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?

View 4 Replies

DataSource Controls :: SQL Insert Not Working?

Feb 27, 2010

I have the following code:

[Code]....

but when I run it inserts '@test' (no quotes) into the database. What am I doing wrong? It's probably really simple but I've been looking at it for ages! :(

View 2 Replies

DataSource Controls :: Data Not Get Inserted Even Insert Query Execute Successfully?

Mar 31, 2010

i made one window base application in VS2008 (VB.net). i made one simple code where i can insert data.and after inserting i can see that data on grid.when i am inserting data insert query runs perfect.after that for debugging purpose i fire select query and get result in dataset and its show data too.but when go
into database my inserted data not showing there.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con_str As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True"
Dim con As SqlConnection = New SqlConnection(con_str)
Dim cmd, cmd2 As SqlCommand
Dim adp As New SqlDataAdapter.....

View 2 Replies

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

DataSource Controls :: Use Of Scope_identity(), @@identity And Ident_current?

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

DataSource Controls :: SqlDataSource Query Not Working?

Feb 18, 2011

I have a strange problem...I am programatically adding an SQL SELECT query to my SqlDataSource that my GridView is bound to. If I use the follwing statement it works fine:

[Code]....

But if I use the following one, the gridview fails to load. The only way that this is different is it uses RTRIM and CAST on one of the fields:

[Code]....

I don't understand why this can be. If I try the statement exactly how it is in the query builder and run it it returns the rows no problem.

View 2 Replies

ADO.NET :: Using SCOPE_IDENTITY() To Retrieve The Id Of Last SQL Insert

Sep 27, 2010

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

View 3 Replies

C# - Getting SCOPE_IDENTITY From SQL Server On Insert

Feb 7, 2011

I guess it is too late and I'm too tired to see what I'm doing wrong. Here is what I'm trying:

int imageId = imageDal.AddImage(new SqlParameter[]
{
new SqlParameter("@IMAGE_ID",
SqlDbType.Int, Int32.MaxValue, ParameterDirection.Output,
true, 0, 0,"IMAGE_ID", DataRowVersion.Current,DBNull.Value),
new SqlParameter("@IMAGE",
SqlDbType.Image, 11, ParameterDirection.Input,
true, 0, 0,"IMAGE", DataRowVersion.Current,image)
});
public int AddImage(SqlParameter[] spParams)
{
SqlHelper.ExecuteNonQuery(BaseDAL.ConnectionStringImages, INSERT_IMAGE_SQL, spParams);
return Convert.ToInt32(spParams[0].Value);
}
Stored Procedure:
[dbo].[sp_insert_image]
-- Add the parameters for the stored procedure here
@IMAGE_ID int OUT,
@IMAGE image
AS
BEGIN
INSERT INTO images
(IMAGE)
VALUES
(@IMAGE)
SELECT @IMAGE_ID = SCOPE_IDENTITY();
END
GO

I get DBNull as spParams[0].Value. I've tried setting value of @IMAGE_ID to a constant in my stored procedure yet it didn't change anything so the problem isn't with my stored procedure (that is what I think). When I execute the procedure from sql management studio, I see the inserted_id returning..

View 2 Replies

DataSource Controls :: FormView InsertItem Return Scope_Identity?

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

DataSource Controls :: Insert In Stored Procedure Not Working?

May 18, 2010

i am using modalpopupextender and a listviewWhen the user click on an item in the listview it call a modalpopupextender.In that modalpopupextender there are 2 dropwn fill with datasource.When i click the ok button it call a stored procedure with 3 parameter.Nothing happen if i pass the dropdown SelectedItem.Value to the parameter but if i use a static variable the insert will work i don't know what i am missing.

View 3 Replies

DataSource Controls :: Cannot Get The Linq To SQl Query Return Type Working(C#)?

Apr 14, 2010

I am trying to fill a gridview with the data from Product table selecting few columns . I am using 3 - tier architecture and in DAL getproduct(userid) I am writing the query but cannot figure out exactly how to get that working .. here is piece of method I wrote

public List<Project> GetProjectList(int ownerId)

View 11 Replies

DataSource Controls :: Update Query Via LINQ Not Working As Expected?

Jan 6, 2010

I have a form that gets populated by LINQ and I want to be able to update the database using that same form. The form is populated by grabbing a querystring (for example, "edit=10") and searching the primary key for that record. So far so good. I have a button attached to the function below which is supposed to use LINQ to update the database with data from the form. I get no errors, but for some reason, it is only updating one field (Last_Updated) instead of all of them.

[Code]....

View 7 Replies

DataSource Controls :: Select Query Not Working / Getting An Invalid Column Name Error?

Jul 9, 2010

i have the following select staement which deosnt work, can anyone advise me whats wrong with it:

string _name = ddl_Name.SelectedValue.ToString();
string cmd =
"SELECT ID, Telephone FROM User_List WHERE Name = '' + _name;

i keep getting an invalid column name error? and i know the syntax next to _name isnt correct. how are the single quotes meant to go?

View 3 Replies

SCOPE_IDENTITY() Returns An Empty String After A Confirmed Insert

Oct 13, 2010

This is completely frustrating and probably embarrassing as well. A while back I posted a similar question and resolved the issue. [URL]...scope_identity The program has been working ever since, until yesterday when as it turns out SCOPE_IDENTITY() started returning an empty string even though a record is being inserted. I am not sure what has changed since the fateful day.

[Code]...

I am hoping that there is something I am just not seeing here. A lot of the information I have seen thus far indicates that there are problems with SCOPE_IDENTITY() but most of those are related to inserting multiple records. As you can see this should be fairly straight forward. The thing that really has got my goat is that it used to work and now it doesn't. If someone could take a look at the above code and comment on any anomalies it would be great. Also I have heard suggestions for switching to ADO.NET and it is beginning to look a lot more attractive every day. But I have a ways to go before making the switch so it is SqlDataSources for now

View 37 Replies

Forms Data Controls :: Query Working In Query Builder But Not In Webpage?

Jul 13, 2010

I'm encountering a strange problem. Here is the scenario. I have built a query that accepts a parameter (WHERE LIKE clause).

I've tested this within the query builder and it returns exactly the number I would expect.

I then go back to my webpage and add the following controls:

Dropdown

Gridview

the user to make a selection from the dropdown. Postback on the dropdown is enabled so the page reloads and the Gridview displays the filtered results (via an objectdatasource). I've set the parameter to the dropdown control.

The trouble is when I run the page I make the selection in the dropdown no results are displayed. Even though the text displayed in the dropdown control is exactly the same as I inputted into the query builder when testing it.

View 3 Replies

DataSource Controls :: Multi-sql Table Example / Getting Error?

Feb 4, 2010

I am a newbie on the subject. Seems I am missing something.

I have viewed and tried the samples in this site and tried to modify the moviesDB example with adding 2 more tables to my database. Tried to use other tables with different pages but didnt work. When I try to go to the page i created it redirects me to the index.aspx again and again. When I try to enter the direct adress to the explorer then I get an error. Is there any multi-paged and multi-tabled(sql i mean) example around? for the ppl who need to start from the begining....

View 2 Replies

DataSource Controls :: Multi-Threading With OleDbDataAdapter?

Jun 22, 2010

I have a .NET 4.0 console application that does a lot of reads from SQL Server 2008 using the OleDbDataAdapter object.I tried to improve performance by spreading the processing logic across four threads using Visual Studio 2010's Task Parallel Library. Sadly the multi-threaded version is three times slower than the original. Using VS2010's performance tools, I found thousands of thread contentions caused by the method OleDbDataAdapter.Fill() which populates a DataSet.This is puzzling as there are no static classes or variables involved that would result in the OleDbDataAdapter being shared by my threads. Also, four simultaneous connections just can't cause a hold up at the database level, right? The default connection pool size should be much larger than this.

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved