|
You could use the FoxPro drivers for creating DBF-files, and then use ODBC to access it. 
--
The moment of terror is the beginning of life.
|
|
|
|
|
J.S.
Would you expand your reply with a link to some code examples, or please explain the steps to accomplish this?
Thanks, Don.
|
|
|
|
|
Hmm.. I wish I could. I have dealt with dBase/FoxPro before, but it was (fortunately) a long time ago.
Anyway, if you install MDAC (Microsoft Data Access Components), you get an ODBC-driver for FoxPro. You can use that driver, just like you would use Access. The SQL syntax is fairly similar, so simple CREATE's, SELECT's, etc should work.
I am not sure how ODBC works with .NET, but I would assume it works (haven't fiddled much with anything other than MSSQL in .NET)
--
The moment of terror is the beginning of life.
|
|
|
|
|
Thanks for you time/input, Jorgen.
I guess that I will have to trial-and-error as we engineers say!
Xiao!
|
|
|
|
|
|
Arjan,
Thank You for this information resource!
Don.
|
|
|
|
|
I have a table and everything works out okay if I log in as sa, naturally.
If I run:
SELECT TOP 1000 * FROM [site] WHERE [date]>='2000-10-01' And [date]<='2000-10-07 23:59:00'
select count(*) from site
select * from site
I get back everything I expect.
However if I log is as another user, the first SQL results in zero rows (as sa it was ~700), the second statement reports there are ~5000 in the table, and the third returns all ~5000 rows. Weird!
I have a feeling I am missing something totally obvious, but I can't figure it out, especially as the statment without the WHERE clause works fine.
Any Ideas?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
It was a date thing. At some point a particular user got set to "British English" rather than just "English"???? And it flipped the month and days around.
Now... (perhaps I should go off to the soapbox at this point) WHY THE $&*% is American localisation formats called "English" and British localisation formats called "British English". If someone said this date is in "English" format I'd expect the format to be the one used in England. Makes Sense? Doesn't it?! But oooohhh! noooooo! in SQL Server 2000 "English" format dates mean the format of dates used in the United States of America! (which is about 3000 miles from England).
Aaaarrrrrrggghhhh!
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
What country is Microsoft located in? US. Thus English is US English.
Actually, if I saw "date format: English" I'd immediately be suspicious because I know that different English-speaking countries use different date formats.
--Mike--
Ericahist [updated Oct 26] | CP SearchBar v2.0.2 | Homepage | RightClick-Encrypt | 1ClickPicGrabber
#include "witty-quote.h"
|
|
|
|
|
I colleague of mine is having similar problems to what you're describing.
<rant>What's amazing to me is why the hell localization is done on this level. Why not stick with ISO dates and let applications worry about localization and presentation. Same thing goes for decimal points. The DBMS vendors have gotten it all wrong if you ask me. A database is seldom accessed by end users with a console anyway. A DBA should be able to handle ISO-dates.</rant>
--
The moment of terror is the beginning of life.
|
|
|
|
|
I totally agree - The RDMS should not be handling presentation layer issues like date formats.
However some localisation issues may still be important - For example, collation order - It is much more efficient if SQL Server handles localised collation sequences. For example, take the values:
Lladró
London
is correct if you are dealing with the English language. But if you select Traditional Spanish as the locale in SQL Server 2000 you get the order:
London
Lladró
because in Traditional Spanish "LL" is considered a letter in its own right that is placed after "L" in the alphabet.
And if the database is localised to non-latin character sets how would the ORDER BY work without locale specific collation rules.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Ah yes, but the sort order of things falls outside the scope of abstract datatypes. A date is an abstract datatype, while order is just.. well order! 
A chosen few should sit down and rethink SQL...
--
The moment of terror is the beginning of life.
|
|
|
|
|
Its interesting what you say about it being a localisation issue, as thats an ISO format, so it should not matter. Access will identify it as ISO always yyyy-mm-dd, but when you have 01-10-2000 it will then use the local version.
Iteresting that SQL Server tries to read it as yyyy-mm-dd or yyyy-dd-mm depending on where it thinks it is.
"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.
Shameless Plug - Distributed Database Transactions in .NET using COM+
|
|
|
|
|
Yes, that's what I thought - All the code uses ISO date strings except on the UI (where normal british dd/mm/yyyy is accepted also). That's why it took so long to discover the fault. And because it only happened to one user I was sure it was some kind of strange permissions thing.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have 8 fields in my table:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 Null Null Null Null
19 98 01 01 Null Null Null Null
I need to set century2,century2,year2,month2,day2 to the next day
after century1,year1,month1,day1, so my date will look like this:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 20 01 12 02
19 98 12 31 19 99 01 01
How do I do this?
|
|
|
|
|
Why don't you use a datetime field or smalldatetime field? Then you can use the DATEADD() SQL function. to Add one day.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I can do this, but What should I use do get after to the 4 field again?
|
|
|
|
|
CREATE TABLE [dbo].[my_date_table] (
[date1] [smalldatetime],
[date2] [smalldatetime] NULL,
[day1] AS (datepart(day, date1),
[month1] AS (datepart(month, date1),
[year1] AS (datepart(year, date1)/100),
[century1] AS (datepart(year, date1)%100)
[day2] AS (datepart(day, date2),
[month2] AS (datepart(month, date2),
[year2] AS (datepart(year, date2)/100),
[century2] AS (datepart(year, date2)%100)) ON [PRIMARY]
The SQL Server documentation has a lot of good information about manipulating dates. You can find more information there.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a table in SQL Server 2000 as such:
categoryId
categoryParentId
categoryName
I want the categories in hierarchical structure thats why I have the parentId. All top level categories have categoryParentId equal to 0. All other subcategories have a categoryParentId that is equal to the categoryId of their parent category.
I have the following statement:
<br />
SELECT categoryId, categoryParentId, categoryName<br />
FROM categories<br />
WHERE categoryParentId=" & parentId & " OR categoryId=" & parentId<br />
The OR categoryId= will also return the parent category along with its children.
The problem that I am facing is:
I need to modify the statement in such way that it will also provide me with a TRUE or FALSE value for each category that will let me know if the category has children. I cannot change the table at all on the database. I need to do this from within the SQL statement.
Any ideas?
Thank you
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
I'm not more than slightly familiar with SQL Server yet, but isn't there a recordcount property you can read? Assuming that each query will return at least one result (the categoryID = parentID case), any value greater than one would indicate that the recordset contains child records.
"Your village called -
They're missing their idiot."
|
|
|
|
|
I made this and it works but I dont know if there is a better way to do it.
<br />
SELECT a.categoryId, a.categoryParentId, a.categoryName, MAX(ISNULL(b.categoryId,0)) AS categoryHasChildren<br />
FROM categories a LEFT JOIN categories b ON a.categoryId = b.categoryParentId<br />
WHERE a.categoryParentID=2 OR a.categoryID=2<br />
GROUP BY a.categoryId, a.categoryParentId, a.categoryName<br />
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
What about using EXISTS with a CASE statement? One of the nice things about EXISTS is that it only walks the index-- it doesn't have to retrieve a result set. In this case, it sounds like you are just wondering whether a child record exists or not.
SELECT
a.categoryId,
a.categoryParentId,
a.categoryName,
(
CASE
WHEN EXISTS(SELECT * FROM categories b WHERE b.categoryParentID=a.categoryID)
THEN 1
ELSE 0
END
) AS categoryHasChildren
FROM categories a
WHERE
a.categoryParentID=2
OR
a.categoryID=2
Especially if you are often-reading and seldom-updating the number of children, you may want to consider adding an extra column to each record containing the number of children. Then you could use triggers to update that column on the parent record when you add or remove a child.
Thank you.
Jeff Varszegi
|
|
|
|
|
This seems to be a good and fast solution.
Thanks for your feedback.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
You're welcome!
Jeff Varszegi
|
|
|
|
|
I might be wrong but my SQL statement does only one SELECT but yours does two SELECT.
I have no idea how SQL server handles that, but is there a posibility that my version is faster than yours?
I am not sure about this...
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
