|
Hi
Please help me out here with some hierarchies. It’s blowing my mind.
Here’s the table…
CREATE TABLE [dbo].[Tree] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Lineage] [varchar] (50) NULL ,
[Name] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
Here’s some data…
SET IDENTITY_INSERT Tree ON
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (1, NULL, '.0001.', 'Root')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (2, 1, '.0001.0002.', 'I')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (3, 1, '.0001.0003.', 'H')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (4, 1, '.0001.0004.', 'G')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (5, 2, '.0001.0002.0005.', 'F')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (6, 2, '.0001.0002.0006.', 'E')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (7, 2, '.0001.0002.0007.', 'D')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (8, 4, '.0001.0004.0008.', 'C')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (9, 4, '.0001.0004.0009.', 'B')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (10, 3, '.0001.0003.0010.', 'A')
SET IDENTITY_INSERT Tree OFF
You have the above tree data. You can ignore the lineage if you don’t want to work with it.
There problem is as follows…
Please sort the tree nodes according to the hierarchy, BUT the nodes must also be in ALPHABETICAL order.
So “SELECT * FROM Tree ORDER BY Lineage” will look like so…
Root
I
F
E
D
H
A
G
C
B
But I need it to be sorted in that hierarchy but also alphabetically like so
Root
G
B
C
H
A
I
D
E
F
Can someone figure this out for me please?
Regards
Dirk
|
|
|
|
|
Sounds like homework 
Try:
SELECT Name FROM Tree ORDER BY (Lineage + Name) DESC
|
|
|
|
|
Just a comment. Think a little more about your 'lineage' field. It seems like you are trying to re-create a character representation for data that already exists through the 'ID' and 'ParentID' fields. I think if you create a view that self joins the table based on the ParentID column you might be farther ahead.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
Hoping someone can help me. I am trying to implement a custom built paging control. I need to retrieve the total number of records as well as only select the number of records I require from the stored procedure and extract these two variables using a sqlDataAdapter.
My sql query is as follows ... would prefer not to use "tomato sauce" but this is the only solution I can think of - however i'm still not getting results .....
And how do I extract these two variables into my sqldataAdapter ...?????
Any advices would be very welcome .. thank you.
My stored procedure ...
alter PROCEDURE [dbo].[prc_tbUser_GetAllPaged1]
--create variable for NumRec
@From int,
@To int
AS
DECLARE @SQL varchar
DECLARE @SQLCount varchar
DECLARE @sSQL varchar
DECLARE @NumRec varchar
set @NumRec = @To - @From
set @SQLCount = 'Declare @TotalRecords int, @NumRec int '
set @SQLCount = @SQLCount + 'SELECT @TotalRecords = count(UserId) from tbUser'
set @SQL = 'SELECT a.Name, a.UserID, a.Login, b.Name as OrganisationName, c.Description as Status
FROM tbUser a
INNER JOIN tbOrganisation b
ON a.OrganisationID = b.OrganisationID
INNER JOIN tbStatus c
ON a.StatusID = c.StatusID'
set @sSQL = @SQLCount + ' SELECT top ' + @NumRec + ' @TotalRecords as NumRecords ' + @SQL
print @sSQL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--prc_tbUser_GetAllPaged1 20,40
and this is the sqlDataAdapter ...
connection = new SqlConnection( Configuration.ConnectionString );
command = new SqlCommand( "prc_tbUser_GetAllPaged1", connection );
command.CommandType = CommandType.StoredProcedure;
//From value
command.Parameters.Add( "@From", SqlDbType.Int ).Value = iFrom;
//To value
command.Parameters.Add( "@To", SqlDbType.Int ).Value = iTo;
//need to add another column to read total number of records return
command.Parameters.Add("@TotalRecords", SqlDbType.Int);
// Open the connection
connection.Open();
//create the data adapter
SqlDataAdapter oDA = new SqlDataAdapter(command);
//Gets the parameters set by the user when executing an SQL SELECT statement.
oDA.GetFillParameters();
//execute the reader
oDA.Fill(oDS,iFrom,iTo - iFrom, "tbUser");
return oDS
|
|
|
|
|
Hi everybody
I have created an installer using NSIS,but I am realy having trouble finding the code to attach the database within the installer.I dont know if its maybe my RegKey or what,but the database does not want to attach.If anyone knows anything about NSIS,or maybe a diff RegKey that I can try,or even some coding to help me,please do.
Thanks so much.
Kind Regards
Mattie20
Hello.Thanks for your help
|
|
|
|
|
Hi,
Can anybody tell me how I can get nested transactions going with ADO.Net and the SqlClient classes?
Any articles or sample code would be helpfull.
thanks
Wouter
|
|
|
|
|
hi,
What is the SQL querry for not having negative values in the table?
thanks,jijo
Jijo kuruvila
software developer
trivandrum
|
|
|
|
|
john kuruvila wrote:
What is the SQL querry for not having negative values in the table?
When you create the table you set a constraint like this:
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
thanx....
Jijo kuruvila
software developer
trivandrum
|
|
|
|
|
i have an sql table.it has one column totalitems.it should not be negative values.how can i accomplish this task?
thanks in advance
|
|
|
|
|
When you create the table you set a constraint like this:
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
thank you,it worked well.When a user purchases some things the totalitems in ptable is decremented according to it.when totalitems in ptable becomes 0, user cannot update.this was the problem.yes, i solved it using consraints.can i make the validation at the client side ?
|
|
|
|
|
|
the userinterface has a textbox and a button.the user must enter the number of items he wishes to buy and then enter the submit button.the stock table in server side contains the TotalItems of a particular product.if TotalItems is zero,the user should not be allowed.I have added the check constraint in serverside "check(TotalItems>=0)" as you said and done it successfully.so the question is can it be done at client side?
|
|
|
|
|
|
I've posted a message here yesterday, but it seems that i was mis-understood.
I'm using vb6 ADO's Recordset.
i want to use the recordset's Filter property.
but the target column is numeric (the ID column of the table, int).
i want to filter the table, so i'll stay with the rows that begins with a certain number, just as a varchar column i'll use "LIKE" filter.
when trying to do it, i get an error "Filter can not be opened."
any ideas?
thanks
|
|
|
|
|
you could put a textbox on your form, and in the code, use the textboxes text property in your filter. create a varible and set the val(textbox) = to your varible and search that way
ex.
dim SQLSearch as integer
sqlSearch = Val(Text1.Text)
"SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
the more you type in the textbox, the closer your search will be
please note the use of the % in the SQL Query
you could even put your search into the textboxes textchange ()
if your using ado
ex. ado.recordsource = "SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
ado.refresh
so as you type, the the search gets smaller
Hope this helps
Help is great only if you ask correctly 
|
|
|
|
|
i built a database in excel sheet. everything seems to be ok except deleting records. i defined everything and opened database.
INSERT INTO works properly but when i use
Sql_string = "DELETE FROM test_base WHERE ID='"+ID_act+"'";
database.ExecuteSQL(sSql);
i get an error - "database error: could not delete from specified tables"
i have no clue why... anyone can provide some working code snippet?
|
|
|
|
|
Is the ID a string ? If it's an integer, try removing the quotes.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
it's a string. just converted integer with some zeros glued in front of it to make all IDs 6-digit long - to make it sensibly sortable :]
|
|
|
|
|
WHERE ID='"+ID_act+"'
I tohught it was '" & ID_act & "' for a string. At least thats what i use and works OK in Access & SQL Server.
Jon
|
|
|
|
|
'&' operator concerns VB (or VBA in access) as far as i remember...
my code was from C++.
|
|
|
|
|
Dear Gurus,
Err Num: -2147467259:
Err Desc: Operation must use an updatable query.
Err Source: Microsoft JET Database Engine
The majority of the users run into this crazy error when trying to insert, update or delete a record. I, my boss and the IT admin have no problems at all with either inserting, updating, or deleting records. The three of us have full access to the location where the MS Access DB resides. The error only happens to the rest of the users at my work place. We added read/write access to the troublesome users but did not fix the problem.
IIS Version: 5.0
App: ASP
Script: VBScript
DB: MS Access
I appreciate any help from you gurus,
Khang
=========================================================================
Here is the code snippets:
Global.asp:
----------
Sub Session_OnStart
' Database Parameters
Session( "Database_Location" ) = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & server.mappath("\CMPSupport\database\support.mdb") & ";"
End Sub
Categories.asp:
--------------
' Open Database Connection
set myConn = Server.CreateObject( "ADODB.Connection" )
MyConn.Open Session( "Database_Location" )
...
If Category <> "" Then
' Insert new Category to DB
SQL = "INSERT into Download_SubCategories (Category) values ('" & Category & "');"
Set RS = MyConn.Execute(SQL)
|
|
|
|
|
If I'm not mistaken, you have to give read/write permission to the folder where the Access .mdb file resides (from your post it seems you only give access to the .mdb).
This is because Access needs to create a temporary file when it is being opened.
Tell me if it fixed your problem.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you so much for replying that quick, Edbert. I will let you know when I can get the users carrying out the test.
Happy holidays,
Khang
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
