|
Interesting approach, though I don't understand all of it. The attribute bit stumps me - what's that got to do with data and tables? I've only used Access, Reflex, FoxPro and Paradox in the past, so I tend to think of database structures in those terms. I'm going to have enough to do just keeping up with variations on equipment types. For instance, transformers can have Load Tap Changers (LTCs) which effectively act as regulators to change voltage in response to load changes. If one is so equipped I'll need to keep track of a separate object, along with its own tests and reading history, but never let it get disconneted from its parent transformer. Other transformers are associated with regulators as separate devices, and these can be separated from their parent transformers and installed in other locations. They also use different methods of oil protection - some are sealed, some have pressure tanks of nitrogen to maintain positive pressure, which has to be monitored. Others have oil reservoirs, and the levels have to be recorded in both the transformer and reservoir.
I think for now I need to keep it simple, if somewhat inefficient, just to avoid driving myself nuts. Fortunately there aren't a lot of tests that are done, and this industry hasn't changed in 50 years. They still think solid state relays are risky, new-fangled toys. It might be another 50 years before I have to add a new test, and I really won't give a damn by then.
One of my primary goals will be to incorporate trend reporting in the test results, once I get the inventory tracking functions in working order. A DB is only as useful as the money it saves you, and it's a lot cheaper to anticipate a transformer failure before it happens. Gas testing in particular is remarkably valuable, though the level of dissolved gasses is less important than the rate of change. This information must be easy to manipulate in the future in order to easily generate views with meaningful content, and while the tests haven't changed, the interpretation of the results has been steadily evolving.
I'll look into using attributes, though. It sounds extremely flexible, even if the supporting code has to be more complex. Thanks!
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: . Fortunately there aren't a lot of tests that are done, and this industry hasn't changed in 50 years.
In which case by all means create a table for each test type. While not technically elegant it WORKS WELL, the main criteria for any system. I live in a world where the test can change from month to month and they want to restructure the universe every 6 months so I need the flexibility to change rapidly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Although I logon with administrator permisson but I can't see SQL Server Agent is displayed.I use Win7
Please help me!
|
|
|
|
|
SSMSE does not support scheduling administrative tasks by using SQL Server Agent.
The SQL Agent isn't available in the Express version[^].
I are Troll
|
|
|
|
|
Hy,
I've got a really unpleasant task.
To create a personnel tracking app using MS Access as the "DB provider".
Here's my problem:
I have a table named Person
Person
=========
ID - AutoNumber
Name - Text(40)
Adress - Text(50)
.
.
.
I have some "stored procs"/querys.
PARAMETERS inName Text ( 255 ), inAdress Text ( 255 );
INSERT INTO Person( Name, Adress )
VALUES(inName, inAdress);
PARAMETERS inName Text ( 255 ), inAdress Text ( 255 );
SELECT Person.ID
FROM Person
WHERE (Person.Name= [inName]) AND (Person.Adress= [inAdress]);
And here is a mini version of one of the calsses:
public class Person : IComparable<Person>
{
public int ID { get; set; }
public string Name { get; set; }
public string Adress { get; set; }
public int CompareTo(Person other)
{
int equality = this.Name.CompareTo(other.Name);
if (equality < 0) return -1;
else if (equality == 0)
{
return this.Adress.CompareTo(other.Adress);
}
else
{
return 1;
}
}
}
int id = 0;
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "EXEC procAddPerson";
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
cmd.CommandText = "EXECUTE procGetPersonID";
id = Convert.ToInt32(cmd.ExecuteScalar());
}
}
catch
{
id = -1;
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
con = null;
}
return id;
And here is my question:
I know that Access/JET does not support out params but is there a better/faster way of getting the ID or any auto generated numbers?
Thanks in advance.
===========
EDIT:
===========
heres the working code(A lot fatser then searching for a match and return/select the ID):
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "EXEC procAddPerson";
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
cmd.CommandText = "Select @@Identity";
id = Convert.ToInt32(cmd.ExecuteScalar());
}
modified on Friday, January 22, 2010 12:10 PM
|
|
|
|
|
Assuming there is an @@IDENTITY equivalent in Access (if not kludge one) then change the last line of your insert proc to
select @@IDENTITY as PersonID
Change you command from executenonquery to executescalar and put the returned value int a variable, oh you already do that but on a nonquery?
Instead of relying on the RETURN as in SQL Server you need to explicitly return a result set (PersonID)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks.
I do that (assign to the result variable the value of executeNonquery) to see if it succeeded.
Thanks again.
|
|
|
|
|
check below code
Dim cnDatabase As ADODB.Connection
Dim rsNewAutoIncrement As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strPathToMDB As String
strPathToMDB = "C:\NewJet4.MDB"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set cnDatabase = New ADODB.Connection
cnDatabase.Open strConn
strSQL = "CREATE TABLE AutoIncrementTest " & _
"(ID int identity, Description varchar(40), " & _
"CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "INSERT INTO AutoIncrementTest " & _
"(Description) VALUES ('AutoIncrement Test')"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "SELECT @@Identity"
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _
adLockReadOnly, adCmdText
MsgBox "New Auto-increment value is: " & rsNewAutoIncrement(0).Value
rsNewAutoIncrement.Close
Set rsNewAutoIncrement = Nothing
strSQL = "DROP TABLE AutoIncrementTest"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
cnDatabase.Close
Set cnDatabase = Nothing
or go to followin link
http://support.microsoft.com/kb/232144/EN-US/[^]
for more info will provide info that you want
|
|
|
|
|
Thanks it works
|
|
|
|
|
hi,
i have to insert record in table ,and it has Composite Primary Keys
col 1=PRJID and Col2=RSRID and both columns has foreign keys on two tables.
The problem was when i try to insert a record in table through query analyzer ,i got error msg as time out expire and my lan connection also working fine.
|
|
|
|
|
If a reindexing is going on in that table or the table is being locked by another update/insert that could happen I guess... or maybe a snipper [you are blocking someone else so DBA killed your connection or something like that]
|
|
|
|
|
hi, i got a database table that have 4 column
id,subject,contents,date
the date column is varchar datatype and its the data is DateTime.Now.ToString()
How do i select 7 days record from the table..
Select * from announcement WHERE ????
thanks
|
|
|
|
|
if your date and datetime information were stored as such (and not as a string), then you could simply use SQL stuff such as "ORDER BY" and "TOP N" since databases that support date types also know how to sort them chronologically.
If you insist on storing datetime information as strings, you will:
- be subject to problems due to different regional settings (which day/month/year is 02/03/04 in each country on Earth?)
- need conversion functions everywhere in your SQL statements.
You might want to read this little article[^].
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. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
As Luc has said you need to change your data type to datetime, leaving it as varchar is a disaster. Think about this, to get the top 7 records you could do the following
SELECT TOP 2<br />
Dt<br />
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt<br />
FROM Period ) X<br />
ORDER BY Dt desc
That will have to convert EVERY datestring in your table, creating an index is a waste of time and all the regional problem Luc mentioned will be visited upon you.
FIX YOUR DATA NOW!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: to get the top 7 records you could do the following
SELECT TOP 2
Dt
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt
FROM Period ) X
ORDER BY Dt desc
Call me picky, but won't this give the top 2 records? Or am I missing something here?
|
|
|
|
|
I only had 3 records in the test table I wrote this against . If the OP can't work out the difference then I probably can't help him.
And yep I call you picky
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: And yep I call you picky
Every forum needs at least one anal-retentive pedant to keep things in line. I do my best.
|
|
|
|
|
David I doubt you are the only one - AR is a required trait for some aspects of development so you are welcome.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What do you mean by "select 7 days record from the table"? Do you mean that you want all the records from the table for the last 7 days? Or do you mean that you want the top 7 records from the table irrespective of date? Or do you mean that you want all the records for the most recent 7 days which are actually in the database (which chronologically may range over more than 7 days if for example you have no entries at the weekend)?
Each one of these will require a different query. None of which are particularly straightforward given the way in which the dates are stored in the table.
|
|
|
|
|
Hi
I am planning to implement a custom version control system.
Is there anyway to represent physical directory structure in database?
Or should I represent them using 'tree' data structure?
If using tree data structure, how can I save the contents of tree to an external file??
thanks
fadi
|
|
|
|
|
which version of SQL are you using?
If SQL Server 2008, you may want to look at the FileStream Datatype[^]
I guess you can create a self referencing table [ heirarchical ] and either use the datatype above or file path and have some code to export it out. I dont know exactly what you want to do but it seems dangerous
|
|
|
|
|
Along with the filestream storage you can use the hierarchicyid you define your tree structure but you are going to have a coordination problem between the physical structure and the hierarchy in the table.
The filestream will not allow you to move the files via explorer so that would be one benefit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hallo,
Did anyone solve the problem with a Boolean Column in a datatable for .net / c#?
I am new in Oracle development and want to convert my database to oracle. But there is no Boolean datatype with SQL and I want to display a gatagridview column as a checkbox. With Postgres or MSSQL there were no problems with that. But with Oracle I search a elegant solution.
Any ideas?
Thank you.
camau
|
|
|
|
|
Boolean datatype is not defined by ANSI.
So either you'll have to use a number or a char, for example: flag char(1) check (flag in ( 'Y', 'N' ))
|
|
|
|