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

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

SQL statement to get data like outlook

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

 I have been struggling to write an sql statement that will give me data like outlook format., especially in term of grouping like





Last week

two weeks ago

Last Month


Is there anyone that can help me on this please? I appreciated

View Complete Post

More Related Resource Links

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Outlook Data, Sharepoint

I've created a custom list in sharepoint 2007.  I used the people picker option by selecting (Person or Group) for the type of information in this column.  In the additional column settings there is a show field drop down that contains various information you can display for the user.  Problem 1 : I select a person from the people picker on the form page, the result would display Last Name, First Name even though I selected Work Email in the show field drop down while creating the column.  However it displays properly on the custom list after saving the form.  Is there a way to change the display on the edit page? Problem 2:  I would like to auto populate fields.  Is there a way I could auto populate the list or edit form based off an employee ID such as department local etc.  All this information is in outlook but it doesn't seem to be accessable through sharepoint OTB. I created the list through sharepoint OTB on a team site and I'm using SharePoint Designer to create work flows. Thanks for the help!

ex {"String or binary data would be truncated. The statement has been terminated."}

Hello all,Please I have a Contact form which a user can use to send his or her message to the admin of a website. When all is done, he or she must click on submit button which in turn runs the code below.However, when this done the is an error ex    {"String or binary data would be truncated. The statement has been terminated."}. I would be grateful if someone could help me out to prevent the error.Thank you. Protected Sub txtSendMessage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtSendMessage.Click Dim hdDataSource As New SqlDataSource() hdDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("hotelpauldbConnectionString1").ToString() hdDataSource.InsertCommandType = SqlDataSourceCommandType.Text hdDataSource.InsertCommand = "INSERT INTO contact (emailaddress, name, phone, reason, message) VALUES (@emailaddress, @name, @phone, @reason, @message)" hdDataSource.InsertParameters.Add("emailaddress", txtEmail.Text) hdDataSource.InsertParameters.Add("name", txtName.Text) hdDataSource.InsertParameters.Add("phone", txtPhone.Text) hdDataSource.InsertParameters.Add("reason", cmbReason.SelectedItem.Text) hdDataSource.InsertParameters.Add("message", txtMessage.Text) Dim

statement with SQL_C_WCHAR returns 22001 string data, right truncation error

I am trying to execute the following code  value = L"Test13Digit++"; SQLPrepare(stmt, L"delete Entries where [type] = ?", SQL_NTS); SQLBindParameter(stmt, paramNumber, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, 100, 0, (SQLPOINTER) value.c_str(), 0, &strLen); SQLExecute(stmt); the table entries is as follows: Entries ([type] nvarchar(100), [key] nvarchar(100), [version] bigint, [data] varbinary(8000), CONSTRAINT PK_Entries PRIMARY KEY([type], [key]))   I keep getting the 22001 - string data, right truncation error. I have tried all different versions of WCHAR types but does not seem to help.

INSERT statement and Getting Data From User Into My Database



I have spent 2 days reading and trying to figure this out and everything i have read and every book I have brought only tells how to read info from a database and how to edit info that is in a database and they all display the data in the database!

I have a page created (getinfo.aspx)

On that page I have a correctly configured SQLDatasource (SQLSource1) (it is set to allow insert, edit and delete).

I have 1 fileupload control

I have 2 text boxes

I have 2 drop down list boxes

The 2 drop down list boxes are "binded" to to seperate tables (via 2 other correctly configured SQLDatasource controls 0 but with out permission to insert, edit or delete) and they correctly provide a drop down list for the user to pick from the 51 states and the 200+ Countries. Those work wonderfully).

I also have a Button Control

The table (myFriends) that SQLSource1 is "connected to" has the following columns:
Column Name  Type   Length Nullable
   userID  uniqueidentifier 16 false
   userName  sql_variant  80

Error Message: String or binary data would be truncated. The statement has been terminated




I have the following problem.

When I edit a field in the SQL Server Management Studio Express then I get the following error:

Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated.

The statement has been terminated


When I use the SQL update commando, it works fine.

Fore debugging we need to change the enabled field several times.

Is there a solution fore this?



The Table Layout

USE [Project]


/****** Object: Table [dbo].[Proces] Script Date: 06/02/2008 10:01:20 ******/

SQL Statement to Query Boolean Data Example


Hello All,


Looking for a sample sql statement to query a db table that boolean data.


Here is the scenario...

Records are lodging businesses with fields for each of the amenities.  Example of fields below.








After db is complete users will need to be able to query the db by checking checkboxes of the amenities they desire, click search, and have only those businesess appear with relavent amenities.

What might the a sameple sql statement look like for this?  FYI... at this time unknown how many amenities fields there might be and may need to add new amenities at any time.

Select * From TableName Where.....




Can I display Data from a SQL Select Statement to a Label


            sql_select = "SELECT auid FROM tbl_auAccount WHERE username='" & IUN & "'"

            'Add Error Trapping Later
            con = Server.CreateObject("ADODB.Connection")

            'Execute the SQL Command

            'Close the connection
            con = Nothing

Now this works and I was able to put the value into a dropdown list box, but I need the value returned placed into a label such as:

Delete or Truncate statement for data purging



I am writting a stored procedure to purge data from table every month. There is no constraint on table column except primary key.

I am using 'delete' statement as it records an entry in the transaction log for each deleted row. But it's a slow process compared to 'Truncate' statement.

Can you please suggest what should I choose (Delete or truncate) as per best practice.








Selecting data from multiple tables whilst using a union statement.



any help on this would be great.

I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different  database that contains information regarding the group of the data.

I so far have the following:

SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx

I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.

Thanks again,


Binding WPF Controls to an Entity Data Model

In this interview, programming writer, McLean Schofield, demonstrates how to bind WPF controls to an entity data model, using Visual Studio 2010 Beta 1. You can also learn more in the topic: Walkthrough: Binding WPF Controls to an Entity Data Model.

Surrogate vs Natural Primary Keys - Data Modeling Mistake 2 of 10

In case you're new to the series I've compiled a list of ten data modeling mistakes that I see over and over that I'm tackling one by one. I'll be speaking about these topics at the upcoming IASA conference in October, so I'm hoping to generate some discussion to at least confirm I have well founded arguments.

The last post in this series Referential Integrity was probably less controversial than this one. After all, who can argue against enforcing referential integrity? But as obvious as surrogate keys may be to some, there is a good deal of diversity of opinion as evidenced by the fact that people continue to not use them.

Editing the Data in a GridView

In addition to deleting a GridView's underlying data, another common need is to allow end users to edit the data displayed in a GridView. With ASP.NET 1.x's DataGrid control, editing the data is certainly possible, but requires creating three event handlers and writing a dozen or so lines of code. With the GridView and ASP.NET 2.0, it is possible to create an editable GridView without writing a single line of code! All of the necessary functionality is encapsulated within the GridView.

The GridView allows editing on a row-by-row basis. An editable GridView contains an additional column with an Edit button in each row. When the end user clicks on an Edit button that row becomes editable, causing the Edit button to change to Update and Cancel buttons and the other columns to become TextBoxes. The end user can then update one or more column values and click Update to save their changes.

Using ASP.NET 3.5's ListView and DataPager Controls: Displaying Data with the ListView

Prior to ASP.NET 3.5, developers who needed to display a set of records could choose between the GridView, DataList, and Repeater controls. The GridView provides rich data features, but has a very boxy layout; the DataList and Repeater allow for a more flexible layout, but lack the "point and click" data features found in the GridView, DetailsView, and FormView controls. The ListView bridges the gap between layout flexibility and built-in data features.

This article, the first in a series of articles on the ListView and DataPager controls, looks at the ListView's many available templates and illustrates how to display data. Read on to learn more!

Dynamic Data Templates in GridView ASP.NET 3.5

At a high level, Dynamic Data Templates can be seen as a system to very quickly build up a User Interface linked with the relevant data model, immediately enabling you to perform CRUD operations (Create, Read, Update, Delete) on the database using the specified data source. They provide a powerful method for building up these kinds of applications, and I'm going to focus on an overview of what Dynamic Data Templates actually are, and a few ways of customizing them to suit industry needs.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Displaying Data using ASP.NET 2.0 Repeater and C#

This tutorial will show you how to display data using the .NET Repeater Control, ASP.NET 2.0 and C#.NET

The Repeater control is a powerful tool and is easy to use.

First, you will need to import the System.Data.SqlClient namespace.
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