|
<b>2. using the DataFormatString property of bound column in gridview. (examples: DataFormatString="{0:MM-dd-yyyy}", DataFormatString="{0:MMM dd, yyyy}", DataFormatString="{0:ddd MM, yyyy}", etc )</b>
That worked, thanks
|
|
|
|
|
Hi,
you can set the formatting specification for each individual column of a DataGridView. Here is a C# example:
dgv.Columns[1].DefaultCellStyle.Format="dd MMM yyyy";
which works fine as long as the field bound to that column is a DateTime (and not a string!)
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Hi guys,
i'm kinda new in using MSSQL. I'm using MSSQL Server 2005. I've created the primary key on a field that auto generates. However, i need to disallow the same name inputed again. I think i might have to write a t-sql statement on the table. But i really don't know how to go about it.
Pls help. How can i achieve this?He who goes for revenge must first dig two graves.
|
|
|
|
|
Creamboy wrote: However, i need to disallow the same name inputed again.
That would be a Unique Constraint, somewhat similar to this example;
ALTER TABLE [MyTable]
ADD CONSTRAINT [UniqueConstraintName]
UNIQUE (ColumnValue) I are Troll 
|
|
|
|
|
Hello everyone,
I have a MS SQL Server 2005 Express Edition and I want to restrict Login access to the database to only one specific domain account. How can I do this any ideas? because when I went to try and create a windows login and specified the domain account that I want it worked ok, but when then I tried to Login into the database again with a different domain account to test it still let me in. Is there some particular setting that I don't know about that anyone can help me with?
Thank you very much.
|
|
|
|
|
How to retrieve data from two different database with single query
|
|
|
|
|
Put the database in teh from clause:
select * from [database].[owner].[tablename]
select * from nortwind.dbo.customers
You can ommit the owner if you want:
select * from northwind..customers Wout Louwers
|
|
|
|
|
select t1.*,t2.* from database1name.dbo.tablename as t1
join database2name.dbo.tablename as t2
on t1.columnname=t2.columnname
where condition (if any)
|
|
|
|
|
| The responses you have assume that the databases are on the same server, in which case database.owner.tablename will work. If they are on different servers you have a problem, you need to set up linked servers, something I hate doing as the server is then hard coded into the query. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day All
i have a Simple Query
select id,NodeID,Description,refParent from #nodes2
order by id ,refParent,description
this shows the following data
ID NODEID DESCRIPTION REFPARENT
======================================================================
0 149 Compulsory NULL
1 155 One of 0
2 156 GunningM 0
3 157 JonesJ 1
4 158 One of 0
5 159 D1127 4
if you can look in this table the Records ID 5 and 5 are fine but this record but
2 156 GunningM 0
is supposed to be under its parent which is the one with ID 0, so i want to sort it so that it can have the following results
ID NODEID DESCRIPTION REFPARENT
======================================================================
0 149 Compulsory NULL
2 156 GunningM 0
1 155 One of 0
3 157 JonesJ 1
4 158 One of 0
5 159 D1127 4
Thank youVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Your question makes no sense whatsoever.
Why should record ID 2 come above record id 1? There is nothing there that could possibly identify the fact that it should do.
And besides, your order by gives prescendence to the ID column:
order by id ,refParent,description
So even if there was something (such as an ordinal column) it would be ignored.
If there is a fake order that children must come in, then you will need to add a column for this and order by that column.
|
|
|
|
|
The record
2 156 GunningM 0
is a Child of the Parent 0 and it has no Children. So there are other records that have REFPARENT of 0 but they have children. So if the record has no child it must come close to its parent.Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
| Your ordering is wrong, you are ordering by id first so your results are in the correct order. To get the results you want, you will need to change the "order by" settings; probably to refParent first, then by id descending. txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|
| Can you please explain the criteria that cause the records with ID = 2 and ID = 1 to sort that way? Is it because there is no reference to that ID? Thanks. Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
let me Explain again. Here is my SQL Query that Produces that table till the end.
The First part that i run is this
Create table [#Nodes]
(id int IDENTITY(0,1),
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
INSERT INTO #Nodes
(NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr = 10
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr =10
and it Produces the table #Nodes with the Following Records
ID NODEID PARENT DESCRIPTION REFPARENT
=========================================================================
0 149 NULL Compulsory NULL
1 155 149 One of NULL
2 156 149 GunningM NULL
3 157 155 JonesJ NULL
4 158 149 One of NULL
5 159 158 D1127 NULL
and in the next statement i create another temp table
select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.Description
and the temp table #nodes2 will look like this
ID NODEID PARENT DESCRIPTION REFPARENT
============================================================================
0 149 NULL Compulsory NULL
2 156 149 GunningM 0
1 155 149 One of 0
4 158 149 One of 0
3 157 155 JonesJ 1
5 159 158 D1127 4
and i made a mistake here by posting a query without a parent Field, to modify that it looks like this
select id as [id2],NodeID,parent,Description,refParent
from #nodes2
order by nodeid,parent, refParent Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
| That way you are ordering is not correct. Change the order by clause as per your preferences. "No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
|
|
|
|
|
If this had been Oracle, it would have been a walk in the park:
SELECT *
FROM nodes2
CONNECT BY PRIOR Id = RefParent
START WITH RefParent IS NULL
ORDER SIBLINGS BY Id
If you have SQLServer 2008, this query might work: (Havent tested)
WITH records AS
(
SELECT
Id,
RefParent,
CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Id)) AS thePath
FROM nodes2
WHERE RefParent IS NULL
UNION ALL
SELECT
n.Id,
n.RefParent,
r.thePath + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY n.Id)) AS thePath
FROM records r
JOIN nodes2 n ON n.RefParent = r.Id
)
SELECT *
FROM records
ORDER BY
thePath
There are times when I really hate Oracle, but when you're working with trees it really is SO superior to SqlServerMy postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int
with recompile
AS
set nocount on
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]'))
drop table [#Nodes]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]'))
drop table [#Nodes_FINAL]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]'))
drop table [#SemiFinal]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]'))
drop table [#Surt1]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Children]'))
drop table [#Children]
Create table [#Nodes]
(id int IDENTITY(0,1),
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
Create table [#Nodes_FINAL]
(id int IDENTITY(0,1),
id2 int ,
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
INSERT INTO #Nodes
(NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr = @curr
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr = @curr
select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.Description
WITH CTENodes AS
(
SELECT
ID,
NODEID,
PARENT,
DESCRIPTION,
REFPARENT,
CAST(ROW_NUMBER() OVER(ORDER BY id ) AS VARCHAR(MAX)) NodePath
FROM #nodes2
WHERE REFPARENT is null
UNION ALL
SELECT
c.ID,
c.NODEID,
c.PARENT,
c.DESCRIPTION,
c.REFPARENT,
NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath
FROM CTENodes AS P
JOIN #nodes2 AS C
ON C.REFPARENT = P.id
)
SELECT ID,NODEID, PARENT, DESCRIPTION, REFPARENT
into #SemiFinal
FROM CTENodes
ORDER BY NodePath,ID,REFPARENT
--These are all Records that inherit from the Root
select distinct s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Surt1 from #SemiFinal s1
inner join #SemiFinal s2
on s1.id = s2.id
where s1.RefParent = 0
and s1.id != s1.Refparent
and s1.Parent in (select Nodeid from #semifinal where Parent is null and ID = 0)
--Find Records that are Children
select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Children from #SemiFinal s1
where Refparent <> 0
--Get the Record that does not have Children
--select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent from #Surt1 s1
--where s1.id not in (select refparent from #Children)
--delete those that have Children from the #surf1 table
delete #Surt1
from #Surt1 s1
where s1.id in (select refparent from #Children)
--Then Union the Results the way you want them
--First Union the Root
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is null
--Follow with the Children
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #Surt1
--Follow with the Normal
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID, nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is not null
and ID not in (select ID from #Surt1)
select distinct ID, nodeid , parent, [description] from #Nodes_FINAL
order by nP.id , nodeid
and it did the Trick
ThanksVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
How to select duplicate names in SQL?
|
|
|
|
|
SELECT *
FROM Table
HAVING Count(name) > 1
To get a better answer you'll need to specify your question better.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Can we use HAVING without GROUPING
|
|
|
|
|
No,
Sorry about that.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Here's My issue:
I have 4 Tables
Author BookAuthor
--------- --------------
ID BookID
AuthorFirst AuthorID
AuthorLast
Book BookNumber
--------- --------------
ID ID
Title BookID
Price SurrogateNumber
Retired
Keywords
author.id linked to bookauthor.authorid
book.id linked to bookauthor.bookid
book.id linked to booknumber.bookid
I don't understand how to get all my records that have instances in BookNumber. Basically what is happening is I have 94 rows so far in booknumber (which is what i want), but since booknumber contains duplicate bookid's i only get a totaly of 86 rows back when i do the following:
var options = new DataLoadOptions();
options.LoadWith<Book>(c => c.BookAuthors);
options.LoadWith<BookAuthor>(c => c.Author);
options.LoadWith<Book>(c => c.BookNumbers);
db.LoadOptions = options;
books = db.books.tolist();
bookbindingsource.datasource = books;
and whenever I look in the database 86 books are in books (so that is right) because i have duplicates that were added to booknumber to make it the total of 94.
So my question is how do i get all 94 books instead of just the 86?
I tried this but its obviously wrong because I got like 116 results 
var RightJoin = from b in db.Books
join ba in db.BookAuthors on b.ID equals ba.BookID
join a in db.Authors on ba.AuthorID equals a.ID
join bn in db.BookNumbers on b.ID equals bn.BookID into bookandBook
from bn in bookandBook.DefaultIfEmpty()
select b;
bookBindingSource.DataSource = RightJoin.ToList();
I just want to be able to show every single book I have entered in the database where it has a unique surrogate number. In the current case only 94 books...
thanks for your help. 
|
|
|
|
|
|
I'm setting my sql server express for LAN. I see that there are so many things to setup. Isn't there an easy way to do this? Maybe from the installation?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
