|
IMHO the self referencing table structure you have chosen is well suited for a "Help Contents" tree. As for orphaned cycles, you could write a trigger to delete all descentants, or just write a function that returns a table of all descendants:
Given the following:
TableName=SelfRefTable
Fields=PKey [int] Name [varchar (50)] PKeyOfParent [int]
PKey Name PKeyOfParent
------------------------------------------------------------
0 Root NULL
1 Child1 0
2 Child2 0
3 A GrandChild 1
4 Another GrandChild 2
5 A Great GrandChild 3
6 A Great Great GrandChild 5
7 One More GrandChild 3
You can delete Node 3 and all of its descendants with a function call:
delete from SelfRefTable where PKey in(select PID from GetDescendants(3))
This will delete PKey 3,5,6,7 in the above data.
CREATE FUNCTION GetDescendants (@nID int)
RETURNS @tDescReturn table (PID int)
AS
begin
DECLARE @RowsAdded int
declare @tDesc TABLE (PKey int,PKeyOfParent int, processed tinyint default 0)
INSERT @tDesc
SELECT PKey,PKeyOfParent,0
FROM SelfRefTable
WHERE PKey = @nID
SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
BEGIN
UPDATE @tDesc
SET processed = 1
WHERE processed = 0
INSERT @tDesc
SELECT e.PKey, e.PKeyOfParent,0
FROM SelfRefTable e, @tDesc r
WHERE e.PKeyOfParent =r.PKey and r.processed = 1
UPDATE @tDesc
SET processed = 2
WHERE processed = 1
SET @RowsAdded = @@rowcount
END
INSERT @tDescReturn
SELECT PKey
FROM @tDesc
return
end
Also, if you want all ancestors:
CREATE FUNCTION GetAncestors (@nID int)
RETURNS @tAscReturn table (PID int)
AS
begin
DECLARE @RowsAdded int
declare @tAsc TABLE (PKey int,PKeyOfParent int, processed tinyint default 0)
INSERT @tAsc
SELECT PKey,PKeyOfParent,0
FROM SelfRefTable
WHERE PKey = @nID
SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
BEGIN
UPDATE @tAsc
SET processed = 1
WHERE processed = 0
INSERT @tAsc
SELECT e.PKey, e.PKeyOfParent,0
FROM SelfRefTable e, @tAsc r
WHERE e.PKey = r.PKeyOfParent and r.processed = 1
UPDATE @tAsc
SET processed = 2
WHERE processed = 1
SET @RowsAdded = @@rowcount
END
INSERT @tAscReturn
SELECT PKey
FROM @tAsc
return
end
Transact-SQL Reference:
CREATE FUNCTION[^]
see: Multi-statement table-valued function
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I have a project that involves a MS Access database. I need to take a listbox that is populated with a SELECT DISTINCT list of records from a database. I need to take the results from the listbox selections and assign it to the IN CLAUSE from a SQL statement as in:
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 WHERE FIELD1 IN ('101001', '101002', '010003', '101004', '101005') ORDER BY FIELD2
If the list box control's name is lstExcludeViaControls then I suspect that the code to parse the selected items from the control should be:
' Declare variables and assign values
Dim count as Integer
Dom count_compare as Integer
count = 0
count_compare = 0
' Count the number of selected items from the list box
For Each listItem in lstExcludeViaControls.SelectedItems
count = count + 1
Next
' Concatenate the value to a variable and add a comma if the
number of items selected from the list box is less than the
total number of items selected, and if the number of items
is equal to the total number of items selected from the list box,
then do not add a comma.
For Each listItem in lstExcludeViaControls.SelectedItems
If count_compare < count Then
listItem = listItem + "'" + lstExcludeViaControls.Selected + "', "
else
listItem = listItem + "'" + lstExcludeViaControls.Selected + "'"
End If
count_compare = count_compare + 1
Next
Please provide some additional insights regarding how to approach this issue. How do I create a variable that is based upon the assembly a string of fields selected from the list box box? This variable would then need to be passed to the CRITERIA portion of a query. The string variable in the IN CLAUSE requires parameters to be provided to the SQL statement as:
WHERE FIELD1 NOT IN ('101001', '101002', '101003', '101004', '101005')
|
|
|
|
|
All,
I have the following table:
CREATE TABLE X
(
ID tinyint NOT NULL,
PARENT_ID tinyint NOT NULL,
CONSTRAINT X1 PRIMARY KEY (ID)
)
go
How can I write a select on it which order rows such that, a row with a particular ID appears BEFORE
a row which has the same value for PARENT_ID?
cheers,
Neil
|
|
|
|
|
Does this give you what you're after?
select *,case when parent_id is not null
then parent_id - .5
else id end as OrderByField
from x
order by OrderByField
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
Can anyone recommed a tutorial and websites where I can learn more about stored procedures?
thanks.
|
|
|
|
|
I have a CSV file which opens successfully in Excel. However, when I run the COPY TABLE statement on Ingres I get keep getting an unexpected end of file error message on line 281. I have checked this line in the file and there is nothing obviously wrong with it. When I delete that line I get the same error message but for a line with a lower number (eg. 104). Does anyone know the cause of this or how to fix it?
Thanks in advance 
|
|
|
|
|
Hi, I have a program that saves a variety of data to an access database. I was wondering, is this the best way of saving data or is it best to save as an XML file? This data will get large but will take a while for that to happen. If I give this program out to people with the database, will they be able to use the program straight away or do they need to have MS Access installed?
Cheers.
|
|
|
|
|
Friends, i've found a question with few choices on a newsgrop. I dont know the answer of this question. Please tell me the answer of this question and also tell me the reason.
You are writing an application that is initially designed to use an Oracle database. You know that there is a good chance that your company will be changing their corporate standard database platform to Microsoft SQL Server in the near future. You are concerned that your application needs to function with a minimum of changes if the database platform ever changes from Oracle to SQL Server.
Given the scenario above, how do you declare your DataReader objects to minimize the impact of a change to the database platform in the future?
1) OracleDataReader myReader = myCommand.ExecuteReader();
2) OleDbDataReader myReader = myCommand.ExecuteReader();
3) SQLDataReader myReader = myCommand.ExecuteReader();
4) OdbcDataReader myReader = myCommand.ExecuteReader();
5) IDataReader myReader = myCommand.ExecuteReader();
Imtiaz
|
|
|
|
|
#5 - It is the interface that all others DataReaders are required to implement.
However, the question does not guide you in the correct direction. You should create a DAL (Data Abstraction Layer) that can be swapped out when you change database vendor. The DAL is your application's interface to the database and only the DAL needs to care what the back end database is. Using just the interfaces supplied in System.Data isn't going to do much for you because you still need to construct the Connection, Command and DataAdapter objects which are database specific.
|
|
|
|
|
You may create a simplified and database-agnostic solution using an object-relational mapping product like NJDX.
Even developing a DAL layer can get quite complicated if you have to use SQL/ADO.NET. This report shows how NJDX OR-Mapper reduced the complexity and size (70%) of the DAL code for the famous .NET Pet Shop project.
|
|
|
|
|
I would reccomend that you use the Data Access Application Block (DAAB) which is free from MS. This provides the level of abstraction you need to make changing your DB backend painless.
if (ToErr == Human.Nature)
{
Forgive = Divine;
}
|
|
|
|
|
I just tried a small select, and mistakenly left the quotes off a varchar field. Instead of complaining that the column is varchar not numeric, SQL attempts to convert each value of the varchar column to an int, failing when it encounters the first non-numeric value in the column. Why doesn't it complain earlier, and why does it choose the least efficient conversion, if any converison at all?
|
|
|
|
|
Brady Kelly wrote: Why doesn't it complain earlier, and why does it choose the least efficient conversion, if any converison at all?
To make sure you won't forget next time...
|
|
|
|
|
Hello,
I have a query that select data from 2 tables which I want to print out in crystal report.
My query
<br />
ALTER PROCEDURE [dbo].[printJobSheet]<br />
@incidentID int<br />
<br />
AS<br />
BEGIN<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
-- Insert statements for procedure here<br />
SELECT incident.incidentID, incident.company, incident.subject, incident.contact, incident.phoneNo, incident.email, incidentTask.TaskID, incidentTask.Details<br />
FROM incident INNER JOIN incidentTask on incident.incidentID = incidentTask.incidentID<br />
WHERE incident.incidentID = @incidentID<br />
END<br />
The repeating field are in the incidentTask table (TaskID, Details) These will be in the detailed section of the report. The other fields from the incident table will be in the header.
The way I am fill my dataset which is a typed dataset is as follows:
<br />
Try<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.CommandText = "printJobSheet"<br />
<br />
Dim pIncidentID As New SqlParameter()<br />
pIncidentID.ParameterName = "@IncidentID"<br />
pIncidentID.DbType = DbType.Int16<br />
pIncidentID.Direction = ParameterDirection.Input<br />
pIncidentID.Value = IDNumber<br />
cmd.Parameters.Add(pIncidentID)<br />
<br />
DS_JobSheet2 = New DataSet<br />
<br />
cnn.Open()<br />
cmd.Connection = cnn<br />
<br />
da.SelectCommand = cmd<br />
da.Fill(DS_JobSheet2)<br />
<br />
dt = DS_JobSheet2.Tables(0)<br />
<br />
report.Load(Application.StartupPath & "/rptJobSheet2.rpt")<br />
report.SetDataSource(dt)<br />
<br />
Me.CrystalReportViewer1.ReportSource = report<br />
<br />
Catch ex As Exception<br />
MessageBox.Show(ex.Message)<br />
End Try<br />
If I do a SELECT * FROM Incident
or
SELECT * FROM IncidentTask
It works, but not when I join the tables together. I have created the xsd typed datasets and have the in my report.
Can any confirm my query, is it correct.
Thanks in advance,
Steve
|
|
|
|
|
What are the purpose of SQL Server Jobs, please can nay one let me know abt it in detail.thankx in advance.
|
|
|
|
|
SQL Server 2005 Books Online
Implementing Jobs [^]
A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command-line applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. Jobs can run repetitive tasks or those that can be scheduled, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration.
You can run a job manually, or you can configure it to run according to a schedule or in response to alerts.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
hello,
1. im trying to use analysis service
2. i hit the server connect button
3. i get error message : "connection cannot be made to redirector", check sql server browser
4. i checked, and it was running
5. what to do? 
thx
|
|
|
|
|
Hi all,
I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this..
Dim dataRS As New ADODB.Recordset
Dim cnnSQL As New ADODB.Connection
Set cnnSQL = New ADODB.Connection
cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;"
cnnSQL.Properties("Integrated Security").Value = "SSPI"
cnnSQL.Open
dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
The code fails though, on the last line with message
"Invalid object name 'dbo_Table_1'"
(yes, I verified that the table actually does exist in the DB)
I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions?
Thanks in advance,
|
|
|
|
|
psasidisrcum wrote: "SELECT dbo_Table_1.* FROM dbo_Table_1
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1? You also have a period after the first dbp_Table_1 which may be causing fits.
Jerry
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
Jerry Hammond wrote: I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1?
He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Jerry Hammond wrote: You also have a period after the first dbp_Table_1 which may be causing fits.
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is:
[server].[database].[owner].[table].[column]
|
|
|
|
|
Colin Angus Mackay wrote: He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Cool. That is good to know. He does say, "yes, I verified that the table actually does exist in the DB)".
Colin Angus Mackay wrote: The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is:
[server].[database].[owner].[table].[column]
Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
Jerry Hammond wrote: Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
No, it goes the other way around. The column is the mandatory bit, so it will parse as if the colum is the last thing and work back from there.
Valid sequences, when expecting a column name, are therefore:
[server].[database].[owner].[table].[column]
[database].[owner].[table].[column]
[owner].[table].[column]
[table].[column]
[column]
Valid sequences, when expecting a table name, are:
[server].[database].[owner].[table]
[database].[owner].[table]
[owner].[table]
[table]
(Note the owner name, in both cases, can be omitted when it is the dbo, so you'd get the two dots with nothing inbetween. e.g. mydatabase..mytable)
|
|
|
|
|
Cool beans, Colin. Thanks for clearing that up for me.
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
psasidisrcum wrote: Dim cnnSQL As New ADODB.Connection
Set cnnSQL = New ADODB.Connection
Are you sure the root cause isn’t the double initialize of cnnSQL?
Dim cnnSQL As ADODB.Connection
Set cnnSQL = New ADODB.Connection
psasidisrcum wrote: dataRS.Open "SELECT * FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
S Douglas wrote: Are you sure the root cause isn’t the double initialize of cnnSQL?
While the double initialisation is redundant it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
