I am SELECTING two columns:
SELECT ([FirstName] + [LastName]) AS [FullName], UserID WHERE CustomerID = @CustomerID
I would just like to know if there is a way to add a space between the two columns in the select statement.
This SqlDataSource Binds to a DropDownList so TextValue = "FullName" and DataValue = "UserId"
but of course, the way it is now the fullname is all one word.
I creat a Family Website and i use Dataset to get Family members from sql databace.
In my databace i have MemberID, Name, MiddleName and Last Name.
I want also return the Member FullName and i use this Query to return the members from database table:
SELECT MemberID, ParentID, Name, MiddleName, LastName, Name + MiddleName + LastName AS FullName
when we have Name="Jon" MiddleName="" Lastname="Smith" it return JohnSmith
my only problem is how can i get space between the Name MiddleName And Last name like "Jon Smith"
I am trying to replace all null value with space for all columns in a table.
(note: just we have to pass parameter table name "XYZ" result will come aotumaticly, we dont need to bother about column name)
i have done it through C# application. but love to do it through Sqlserver only like using storeprocedure, function.
my C# code is like that
[code]....
I set the CellPadding and CellSpacing properties to 0 and BorderStyle to 'None' on my gridview.
I set up a hover event on the row so that the entire row is highlighted on mouse over. When this happens, I can still see a gap between the two columns.Am I missing a property setting here?
I know this might be a trivial question but I can't find how to do it...
I want to increase the space between the columns, there barely is any space, the columns are just very near each other...
I have two columns first name and last name , that have duplicates, how can i remove the duplicates and only leave the distinct members intact?
View 6 Repliesi have a query like this and its running correctly
SELECT CAST(REPLACE(LTRIM(RTRIM(SUBSTRING(P.Telephone, 2, LEN(P.Telephone)))), ' ', '') AS BIGINT)
FROM P INNER JOIN PCommunication ON P.ID = PCommunication.ID
WHERE P.Telephone IS NOT NULL AND CAST(REPLACE(LTRIM(RTRIM(SUBSTRING(P.Telephone, 2, LEN(P.Telephone)))), ' ', '') AS BIGINT) > 5000000000
AND PCommunication.TypesID = '7667d187-5fe1-4aaf-9e31-79761561da58'
i am trying to use it with dynamic query like this but its not working
@query = 'SELECT CAST(REPLACE(LTRIM(RTRIM(SUBSTRING(P.Telephone, 2, LEN(P.Telephone)))), ''' ''', '''''') AS BIGINT)
I have a query which is returning records like below which returns a list of tasks with a corresponding task type. there is a task table (wh_task) and a task type table (wh_task_type). What i want to achieve is a count of all the tickets per ticket type. So at the end of the query i will have a total for Administration / Maintenance tasks (1) and a total for Routine Maintenance Tasks (3). ** and all the other types *
There are many different types of task type so I dont want to have to hard code it. Can count all the occurencies of the different ticket types.
Task Name
Task Type
Date Completed
SBS Server C drive running out of space
Administration / Maintenance
16/07/2007 16:03:34
Patching Server(s).
Routine Maintenance
08/01/2009 06:51:56
Patching Server(s).
Routine Maintenance
11/02/2009 10:06:06
Patching Server(s).
Routine Maintenance
04/05/2009 06:53:24
I can also get wh_task_type_id.
Here is my SQL:
[Code]....
i want to add leading space in dropdown list after binding from DB.
for that i used leftpad method but not working.
code are as below
[Code]....
I've been trying to insert new values while I create a new user with a createuserwizard, but I've been getting this error over and over again:
"insufficient result space to convert uniqueidentifier value to char"
The code that does it is the following:
[Code]....
But when I debug, the error is thrown before reaching the previous code, so I suppose the error is in the SqlDataSource, look:
<asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
InsertCommand="INSERT INTO [User_Personal] ([UserId], [Gender], [ToGender], [StateName], [CityName],
[code]...
I have a datasource, which includes many columns, idealy, I need use a gridview to show:
1) first 3 columns: template fields, these fields depends on values in some columns of datasource. I use template fields, hard coded. works fine.
2) the other columns. This is I do not know how to do it. In the data source, there are about 10-20 columns data, each time, the # of columns of the data varies. idealy, I need show each of them as a seperated column in gridview. The entire data source may have 30 columns, but some of them are used in 1), and I only want show these 10-20 columns in gridview. Some columns in data source, I may not use them at all. is there a way to do this? or have to seperate them as a detail view style UI?
I wanna join all the field as a new col.
but how can i ignore the empty field because i always join the field and separate by space like the following.
select (field_1+' '+field_2+' '+field_3) as new_col from TABLEA
but if the field is empty, then there is the double space
how can i ensure all the space is one space only.
i'm writing a stored proc and if a field i return is longer then 200 characters, i want to truncate, find the last space, remove any characters after that space, then add some full stopseg:
INSERT INTO @rec(articleid, abstract)
SELECT a.id,
CASE
[code]...
How to replace a character in a String to space or white-space?
View 3 RepliesI'm trying to add these 4 co,umns to the table as bit type woth a defaut of FALSE. What am I doing wrong here? I get a syntax error near (
create PROCEDURE [dbo].[AddADIFields]
AS
ALTER TABLE dbo.tblTESTS ADD (AI1 Bit 0,AI2 Bit 0,AI3 Bit 0,AI4 Bit 0)
I want to create a constraint for 3 columns (A B and C) where if columns B or C are not null, then A cannot be null. And vice versa (i.e., if A is not null, then either B cannot be null, or C cannot be null).I am not very well versed in SQL and would like to know what is the best way to achieve this (e.,g Check constraint, stored procedure, function, trigger)? I'm leaning towards trigger but I'm not 100% sure if that's right nor of how to implement it.
View 3 RepliesI want to know that how many tables we can have in database and what is the maximum no. of columns.I also want to know the maximum size for each datatype.
View 3 RepliesI need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.
View 4 RepliesI have a DataSet that I bind to a GridView control. I want to only show certain columns from the DataSet. Should I do that with my SQL query (that I use to populate my DataSet) or somehow with a DataView?
View 7 Repliesis there a SQL statment i could write that would sum each column and allow me to then access each sum individually
i.e
Dim objCmd As New OleDb.OleDbCommand("SELECT SUM(expression) FROM orderForm WHERE OrderDate=@OrderDate", objConn)
is there anything i can replace expression with such that all the columns in orderForm could be summed and returned as one record
or is there a way to replace expression with something that would allow me to just keep changing the paramater so i could continuously reexectute the same command w/o having to create a new command for each column
this is my sample script
[Code]....
after execute this script which returns a column name "XML_F52E2B61-18A1...." can I change this column name?like this
select EMPNO AS EmployeeName -- we can use AS to change column name
from EMPLOYEE
I am returning a dataset from a method and trying to set one of the columns as Primary Key.
CategoryList.Tables[0].PrimaryKey = new DataColumn[] { CategoryList.Tables[0].Columns["CategoryNodeId"] };
Interestingly it gives me this error at run-time, These columns don't currently have unique values.
Now I perfectly know why this error should error, but there are no duplicate keys in this data column. I broke the code in between and bound the dataset to gridview to check if this column contains any duplicates and this is not the case. Here is the result set underneath. Have a look at the first column 'CategoryNodeId'.
CategoryNodeId
CategoryId
Name
ParentCategoryNodeId [code]...
unable to find a work around for now.
If I have a linq statement like:
[Code]....
There may be a lot of data in the Blogs table. What if all I wanted was say BlogID, BlogTitle and BlogAuthor and nothing else, is there a way to do this in an effort to make the sql more efficent?
Along the same lines in my DataContext (.dbml) file I added some associations between some of my tables. This makes coding nice when I do a linq statement that will join those tables automatically for me but my concern is does it do that everytime, thus making all my linq quieries inefficient?
I use MySQL database and SqlDataSource controls. I generate update and insert queries in my code behind.how I can get a list of columns from the SqlDataSource control?
View 2 RepliesI have a situation where a query returns ~20 columns, but at any given execution only 5 of the 20 will have non-NULL values. The remaining 15 will contain null for every row in those columns.
I need to figure out which of the 5 columns have values. I am using a SqlCommand. How would I best approach figuring out these 5 columns?