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


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

How can I get the cumulative sum of a field in a table

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
For Eg.   I have a table like gias given below: Name                  Amount ------------------------------ aaa                       10 bbb                      20   and I want an output like one given below on running SQL query or stored procedure Name                  Amount                  cumulative amount --------------------------------------------------------------------- aaa                       10                              10 bbb                      20                              30   can anyone plz help me on this


View Complete Post


More Related Resource Links

importing table with Access "memo" field to SQL

  
I imported an access database using the DTS wizard, into SQL 2005. There is a field in one of the Access tables that is a memo field, filled with a bunch of text including linebreaks. After importing, the field is indicated as the nvarchar(MAX) datatype when I check in SQL Enterprise Manager. The text in that field is displayed without line breaks, both in EM and when viewed on a web page. I changed the datatype of that column to "text" and now can see line breaks when I hover over a value in that column while viewing the table data in Visual Studio 2010's Server Explorer.But when I display the data in a ListView, there are no linebreaks. When I view my TableAdapter in my dataset, there are no linebreaks either.I must be missing something here? Thanks. 

xml as text will not save in table field in some cases

  
I have a stored procedure that saves 4 fields into a data table named xml.  One of the fields is called xml, and is just a text field.  Usually, my sp properly stores the data, but occassionally the xml portion of my record is not stored.  It's on a remote server, so I'm having trouble hooking up a debugger to it, but I may have identified some charactoristics of the data in the cases where the data is not saving into the field.  I think it has a lot of "......."s mixed in with the xml.  I'm wondering if SQL could be identifying this as some kind of security threat.  Any thoughts would be super appreciated. 

Update duplicate field in same table

  
  hi I have a table with some other duplicate field as below   IssueID Title A1 Test1 A1 Test2 A2 Test3 A2 Test4 A3 Test5 A4 Test6   i want update all duplicate field based on IssueID and the final result will show as below   IssueID Title A1 Test1 A1 Test1 A2 Test3 A2 Test3 A3 Test5 A4 Test6 may someone help me?  

Field Population Count over whole table

  

Hi

I have a huge table and would like to know of a way to count field population across the whole table, this can be stored in a separate table to refer back to, i am using sql 2008. A visual display could be:

TableName FieldName Blank Populated MinValue MaxValue DateCreated

Person       Address1    1500  5000        <blank>    9 any street   30-sep-2010

Person      First Name    6000  500          <blank>     Zac               30-sep-2010

has anyone done something like this before, would like something quite reusable and generic?

any help greatly appreciated.


Insert ID field into SQL table programatically from Stored Procedure

  

Hi,

I have a table in SQL where I insert data (CatCodes) using C# code but I want to insert ID code (DealerID) with it as well. Following is my Stored Procedure and C# code:

PROCEDURE [dbo].[InfoInsert]
	-- Add the parameters for the stored procedure here
	@UserId uniqueidentifier,
	@Title char(32),
	@FirstName char(32),
	@LastName char(32),
	@CompanyName char(32),
	@Email char(32),
	@DealerID int OUTPUT
	
	
	
	
AS
BEGIN
SET NOCOUNT ON

	INSERT INTO [tblDealers] ([UserId], [Title], [FirstName], [LastName], [CompanyName], Email) 
        VALUES (@UserId, @Title, @FirstName, @LastName, @CompanyName, @Email)
        
        select @DealerID = SCOPE_IDENTITY()
        
       
       
END


 

C#:

protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
        {
            

            
         
            
            //Insert membership and personal data
            TextBox UserNameTextBox = (TextBox)CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName");
            SqlDataSource DataSource = (SqlDataSource)CreateUserWizardStep1.ContentTemplateContainer.FindControl("InsertExtraInfo");
           
            

            

            Membership

retrive details records as a field in master table

  

I have 2 tables:

Student table with Id, Name fields

Phone table with Id,StudentId,PhoneNumber

Now, I want to show student list with all it's phone numbers in 1 row.

this query: "select student.*, phone.* from student inner join phone on student.id=phone.studentid" shows me the list of students with duplicate per each phone number.

I want something like this:

1 paul       66665566,7766665

2 ben        5566556,8877667

3 sam       4444334,3454332,7656765


how to get Notepad all Item in sql server database table field

  

Hi..

I have one asp.net application using Sql server as back end.. in sql server database i have one table which consist two fields.. ItemName and Rate

I have one notepad file which consist around 700 ItemName with Rate.. 

So how to get this ItemName and Rate in my Table...






How to add a field data onto an email table?

  

Hi VB newbie here. How can I call my TextBox ID = "txtFromAddress" data to a table that I am trying to send? Right now it is just showing the text '& txtFromAddress' on the table on the email that I send. Thanks in advance.

 

<table><tr><td><b><font color = 336699>Contact Information</b></font></td><td></td></tr><tr><td>Contact Name</td><td> & txtFromAddress </td></tr></table>

Contact Information
Contact Name & txtFromAddress

Drag and drop field from db into HTML table cell possible?

  

Hi

I'm used to Dreamweaver where this is possible. I find it hard to believe that I would need to use a 1 column gridView control.

Is there a way to drag one field from the db Explorer window onto my page and NOT get a grid view?

Or alternatively, I used to be able to put something like this in ASP classic (years ago!) <%= Eval("User") %>

Any help would be SO appreciated.

Thanks



I am updating a currency field of an access table through detailsview, cannot input decimals.

  

I changed a text field to a template field assigning the selected text and the selected value is being assigned to another currency field also templated. I get aa error

[FormatException: Input string was not in a correct format.]

Could you please give me a few tips on this one. Thanks
 


Adding a new field to a table via script in sql server management studio

  
I know how you can script to and create a new table but how about adding a new field to this table? For example: how would i add a new varchar(50) field named mynewfield?

USE [example]
GO
/****** Object: Table [dbo].[Password]  Script Date: 10/26/2010 10:17:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Password](
	[user_id] [int] NOT NULL,
	[password] [varchar](30) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response1] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response2] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response3] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
 CONSTRAINT [Password_PK] PRIMARY KEY CLUSTERED 
(
	[user_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Password] WITH CHECK ADD CONSTRAINT [User_Password_FK1] FOREIGN KEY([user_id])
REFERENCES [dbo].[User] ([id])
GO
ALTER TABLE [dbo].[Password] CHECK CONSTRAINT [User_Password_FK1]


Dataset has one field, populate a table 2 columns wide n rows deep

  

Hello,

A dataset returns a single field of child names, instead of presenting this as a single column table, I would like it to be multi column.

Dataset returns:

Child1
Child2
Child3
Child4
Child5

I would like to display it as:

Child1 Child2 
Child3 Child4
Child5

 


Reformatting variable character date field in custom table

  

I have a custom table with a variable character field storing a date. It is currently in the format YYYY/DD/MM plus a time stamp. I want it in the format MM/DD/YYYY without a time stamp.

I've tried a few CONVERT and UPDATE statements, with no change. The syntax is okay, and in some cases the statement has an effect (such as cutting off all but one character of the time stamp), but does not reformat the date. I've also tried resetting the fields as DATE fields or SMALLDATETIME fields in the custom table, and I receive errors after doing that.


Additional " mark at the end of field (CSV file to SQL table) - URGENT

  

Hi All,

 

I have a csv file which I am loading into a sql table and for some reason it is leaving a " mark at the end of one of the fields.  I cannot seem to get rid of it and need some urgent help as the data is needed immediately!

 

When I look at the source CSV file in notepad it looks like this:

92|"ITEMS FOR DELETION"|36|"ORGANIC BEEF SET"|6001056858861|"B/SNACK DGSTV CARAME"

42|"BISCUITS"|2|"BRANDED SAVOURY"|5410041001204|"TUC SALT"

42|"BISCUITS"|99|"ELIMINATED"|6009171986766|"Cherry + Orange Bisc"

42|"BISCUITS"|99|"ELIMINATED"|6009171986773|"Nut + Fruit Biscuits"

92|"ITEMS FOR DELETION"|36|"ORGANIC BEEF SET"|6009171987909|"Choc Biscuit Creams"

 

So you can see that Ints are not qualified and text is qualified with double quotations, all fields are pipe seperated.

My CSV connection in SSIS is set as follows:

Format = Delimited

Text qualifier = "

Header row delimiter {CF}{LF}

Header rows to skip = 0

No column names in first data row

Row delimiter {LF}

Column delimiter {|}

 

Once the data is in SQL I get the following:

Reformatting variable character date field in custom table

  

I have a custom table with a variable character field storing a date. It is currently in the format YYYY/DD/MM plus a time stamp. I want it in the format MM/DD/YYYY without a time stamp.

I've tried a few CONVERT and UPDATE statements, with no change. The syntax is okay, and in some cases the statement has an effect (such as cutting off all but one character of the time stamp), but does not reformat the date. I've also tried resetting the fields as DATE fields or SMALLDATETIME fields in the custom table, and I receive errors after doing that.


How to filter data in repeating section using field value in repeating table?

  

Hi!

My form is web-browseable. I have a repeating table and repeating section in one of my form views. 

I want to filter data in my repeating section by field value in selected row in repeating table. How is it possible? How to fire event on row change in reapeating table? How to get value from field in selected row?

Actualy, it is the same data in these sections, but its a list with some fields in the reapeating table and all fields(details)  in reapiting section. I would like to filter data in repeating section by field DPNumber in repeating table.

 


How to check if a certain XML field data already exists in a table?

  

Hi,

Let's say I have a table like this:

ID int

Data XML

...

When I insert, I want:

if(not exists(select 1 from MyTable where Data = @MyXMLString))

  insert into MyTable(Data) values(@MyXMLString)

The problem is, Data = @MyXMLString does not work because Data is XML, @MyXMLString is varchar. So, how should I do this?

Thanks.


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