.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Inserting rows via stored procedure and under certain conditions

Posted By:      Posted Date: August 21, 2010    Points: 0   Category :ASP.Net
 

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd


View Complete Post


More Related Resource Links

How do we redirecting rows using stored procedure

  
Hi I have one SQL Execute task and it runs one stored procedure inside it. That SP inserts data into one of table.Now when I inserts records into this tbale let's say because of primary key some records are failing so how can i redirect this rows in stored procedure  

Count how many rows are returned from a stored procedure

  
Hi,   I have written a stored procedure for my database which takes two varchar parameters and returns lots of rows of data. This data will be passed back to my .NET application with a reader for parsing.   Imagine this scenario of calling a stored procedure:   MyDatabase.dbo.sp_MyStoredProcedure 'String 1', 'String 2'   Let's say that this command returns 12,434 rows of data when executed in SQL Server Management Studio Express containing the data of 5 joined tables which contains 2 or 3 unions (depending on the input of 'String 2') and the data within each union is correctly sorted.   How do I get a row count from the execution of the above stored procedure command? I need to pass this back to my application for the progress bar to function correctly.   I don't want to replicate the function and modify it for counting rows because the stored procedure consists of approximately 150 lines of SQL code.   Thanks in advance.   Sean

inserting data after caliculation using stored procedure

  
hi my requirement is like i want to insert data to table after caliculationliketable name salaryhikecolumns salaryinput,20%hike, 25%hike and 30%hikesalaryinput column details  to be input by the user and hike is to be caliculated and inserted with 20%hike, 25%hike and 30%hike are columns of same table.plz help me.thank you very much.

Performance of a Stored procedure : Optimum number of rows to be handled in a batch

  

In a stored procedure is there anything called as optimum number of rows that need to be handled in a batch

as In, If there is a SP,

   that picks up data(via union operation) from various objects and puts them into a temp table after which the required data is moved to various tables,The above scenario is handled as a batch run where in 1 million rows are processed in a batch and the next 1 million rows in a next batch,so on and so forth.

   Is there any optimum number of rows that can be processed in the above case?

  When we increase rows processed per batch we have seen improvements in performance of the SP,but not sure when it will start deteriorating.

Thoughts?

Thanks,

Kishore.


help inserting an empty string and a default date thru a stored procedure

  

I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (

deals_SoldItems.[TransactionID],

deals_SoldItems.[EmptyStringField],

deals_SoldItems.[BaseDateField]

)

VALUES (

@TransactionID,

"",

"01/01/1900

)

 

this dont work

cheers

 


problem inserting using a stored procedure and identity column.

  

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Looping through Textbox Values and Inserting into SQL Server via Stored Procedure

  

Hello All,

I have a database table as follows:

USE [Diel_inventory]
GO
/****** Object:  Table [dbo].[QUOTEDETAILPARTS]    Script Date: 05/09/2010 17:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QUOTEDETAILPARTS](
	[QuoteDetailPartID] [int] IDENTITY(1,1) NOT NULL,
	[QuoteDetailID] [int] NOT NULL,
	[PartNumber] [float] NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [pkQuoteDetailPartID] PRIMARY KEY CLUSTERED 
(
	[QuoteDetailPartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS]  WITH CHECK ADD  CONSTRAINT [fkQuoteDetailID] FOREIGN KEY([QuoteDetailID])
REFERENCES [dbo].[QUOTEDETAIL] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS] CHECK CONSTRAINT [fkQuoteDetailID]


This table receives data from my web application via a stored procedure, snippet pasted below:

 

nsert into dbo.QuoteDetailParts
	(QuoteDetailID, PartNumber, Quantity)
	values (@QuoteDetailPartID, @PartNumber, @Quantity) 
	
	set @QuoteDetailPartID=scope_identity()

 

In my quote.aspx page, I have a wizard control that collects numerous data points.  In one of the wizard steps, I hav

Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

Getting multiple rows from oracle stored procedure and binding it to grid view

  

 

To  get this stuff you need to create a Ref Cursor to return recordset.

             create or replace PACKAGE Types AS

            TYPE cursor_type IS REF CURSOR;

            END Types;

Now create a stored procedure to return multiple rows .

           create or replace

           PROCEDURE getAllCity(p_recordset OUT types.cursor_type) AS

           BEGIN

           OPEN p_recordset FOR

    

Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Sort by gridview SortExpression parameter via Stored Procedure

  

I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
                            <

Stored procedure generator?

  

Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.


Create stored procedure from asp.net

  

Hi,

we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.

 

 


Call SQL Stored Procedure Asyncronously

  

Hello,

I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.


Best regards,

Imran


Unable to select any stored procedure while creating TableAdapters in wizard

  

I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend