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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Deleting items from a list based on a stored procedure

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :SharePoint
          foreach (SPListItem listItem in collListItems)
            var result = db.sp_MOSSCloseCase(Convert.ToInt32(listItem["Case Number"]));
            if (result == listItem["Case Number"])


Obviously I can't delete items in a collection within a Foreach loop. Problem is that the stored procedure is to return the case number in the DB only if there is a completed date(case being closed). So i thought if (result != null) then move the result to a List or something. Then go through the list and delete items where results = item["Case Number"] but result is never null... nor does it ever == listItem["Case Number"]


For the life of me, I cannot figure out how to get the result values into a list and then delete them from the SPList.

View Complete Post

More Related Resource Links

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

Pass list to stored procedure



I'm looking to pass a list into a stored procedure, stored as List<myClass>.  I've passed a table in before but I'm not sure how to pass a list - anyone help?

Stored procedure, with null items?


I build a stored procedure it should do something like this.
But the code is not ok.
I have some if statemants, i like to check if the var is filled. If its empty some checks het should not do.
But also the 'and'  is not ok. If i don't like the first 3 statemants, then the 4th  isnt running because you get: select * from artikelen where and (breedte >= ..........

So what is the best way to do a SP like this?
Is it possible to add an empty float to the stored procedure ?

USE [artinfo]
/****** Object:  StoredProcedure [dbo].[GetFilesByRole]    Script Date: 07/16/2008 16:43:36 ******/
ALTER PROCEDURE [dbo].[SearchArtikelen]
 @kleur nvarchar(50),
 @fabrikant nvarchar(50),
 @lengtevan float,
 @lengtetot float,
 @breedtevan float,
 @breedtetot float 

declare @str as nvarchar(5000)

set @str = 'SELECT *
FROM dbo.artikelen

if (@lengtevan is not null){
 set @str += '(Lengte >= ' + @lengtevan + ') '
if (@lengtetot is not null){
 set @str += 'and (Lengte <= ' + @lengtetot + ') '
if (@breedtevan is not null){

Stored procedure to get list of files and downloaded

Hi all,I have the below table which gets updated whenever the files get downloaded.The details of list of filenames downloaded will be saved in this table.i need to get the files which are downloaded and put it into a temp table on regular basis.From that Temp TAble i will use the Filenames and split each file into two.The DownloadID will be unique in this table.I need to write a Stored procedure for all this process.How will be the stored proc?i need to call this proc in a job.can you help me in writing the proc..
DownloadID	ConfigurationId		FileName	          LogDate	     FilePublishedDateTime	 
1	          2301		Nobel_2010_10_01.tsv	 2010-10-01	2010-10-04 02:19:53.000	
2	          2301		Nobel_2010_09_08.tsv	 2010-09-08	2010-09-09 21:49:00.000	
3	          2301		Nobel_2010_09_08.tsv	 2010-09-08	2010-09-09 21:49:00.000	
4	          2701		System_2010_10_01.tsv	 2010-10-01	2010-10-03 23:57:41.000 
5	          2401	     Boot_2010_10_01.tsv	 2010-10-01	2010-10-04 00:46:19.000 
Thanks in advance!!

Need to order stored procedure output based on custom order


*Read the whole question before you decide to send this to the chart forum

So here is what I am trying to do:

I have a chart in VB Express (vb.net) that obtains its data from a stored procedure in my SQL Express database. The chart is a stacked column chart and shows events on the X axis. What I need is these events to be displayed in a very specific order in my X Axis.

The problem:

I have been told that I cannot custom order this in the application, but rather I need to change my stored procedure output to get the events in the order I want. It was suggested to me that I should make a new table in my database that has the names of the events in 1 column and the order I want them in, in the other. Then use that to order my output in my stored procedure.

I do not care how I accomplish what I need to accomplish so long as I do indeed accomplish it. Here is some database structure to help you understand my database:

Table: Buyer

BuyerID (PK), ProjectID (FK), Status, Type

There are 12 different statuses, though my stored procedure doesn't know that. These 12 statuses need to be shown in a very specific order. My current stored procedure is as follows:

select Status as Status, Type as Type, count(BuyerID) as Value from Buyer WHERE projectID = @ProjectID AND Status IS NOT NULL AND Type IS NOT NULL GROUP BY Status,Ty

stored procedure based custom conflict resolver truncates data


I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values.  The values saved to the test table are correct but some of the values saved as the conflict winter are truncated.


Example a char(3) filed is updated at the subscriber as '111' and updated at the publisher as '222', in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as  '11 ', if I use the publisher value the conflict resolver updates the field as '22 '.  Now the same records is saved to the test table correctly as either '111' or '222' depending on the logic I used.  So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated.  Has anybody run into this before and what steps can I take to avoid this.


Thank you,

using a stored procedure for a listview, I am trying to only return items in a column of a table tha


I am trying to create a stored procedure that when fed a session parameter, it will only find items that  start with a particular character.  For example, if I have a table named table1, and a column name col1, then if I have 10 items in the column and 5 of them start with the letter c, I want to use a stored procedure to onlly return the items that start with a c.

I am sure it is somethin like:


FROM table1 

WHERE col1 = "c??????????"

Is there any function that copy the items from one list and insert into another list based on functi


Hi to all,

             I am using visaulwebpart  that gets the item from one list to other based on condition.But i want to know is there any function i.e helps copies the item of other list and insert into my current list.If it is existed then it is helpful to me a lot ,Because this makes me to learn new concept on sharepoint.



Mohammad Siddiqali

dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.


I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
	 public partial class TheReportPage : System.Web.UI.Page  
	     protected void Page_Load(object sender, EventArgs e)


Web part to retrieve List Items based on configurable property


I want to create a web part that will do the following

1) Have a custom choice property based on teh Choice List Column in the list.

2) Return the records based on the custom choice selected by the user.


Is this possible?


Creating Sites/Lists/List Items under Current User Context in SharePoint By Using SPUserToken

Usually we do this by performing the action under RunWithElevatedPrivileges method and updating the listitem using SPListItem.SystemUpdate() method (see here). but this approach has its own flaws like (RunWithElevatedPrivileges will run under system account, we cannot use SystemUpdate for SPSite,SPWeb,SPList, since it runs under system we will "CreatedBy" by as SystemAccount).

Get pixel format, raw format, property id list and property items image in VB.net - C#.net.

n this simple article you will learn that how we can get pixel format, raw format, property id list and property items image in VB and C#.net.

Get Pixel Format:

Image consists of pixels to get the format of the pixel we have a property named pixel format. This property returns an enumeration of pixel format. This enumeration tells the pixel format.

To demonstrate make a window application. Drag one button and a picture box on form.

Now write the following code on Button click event:

Send Email from SQL Server Express Using a CLR Stored Procedure

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.

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.

Inserting rows via stored procedure and under certain conditions


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

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

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

Creating A Stored Procedure Which Searches Team Names



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">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
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