|
Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here?? 
|
|
|
|
|
Rafael Carmo wrote: Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??
AFAIK, a DBF file stores all data as ASCII text only - maybe this is the issue here...
|
|
|
|
|
Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well... 
|
|
|
|
|
Snaider wrote: Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...
It's been a while since I have seen a DBF file... I think the Date type in a DBF file is a 10-char storage. Maybe you could try '03/13/2008' (depending upon your system locale)
|
|
|
|
|
Try this
Insert Into Test2 values ('09/22/2007');
and check the data in database.
Do good and have good.
|
|
|
|
|
It didn't worked...but I've just find out how to do it:
Insert Into Results.dbf Values (CtoD([06/18/2005]))
Only by this way it worked.
Thx for everybody's help 
|
|
|
|
|
Dang. I'm stuck doing DBF stuff again!
But to answer your question, you can use the "CTOD" function as you've seen. Or you can use {} as the field delimiters for an actual date field. So:
Insert into Results({09/22/2007})
|
|
|
|
|
hi! I have a problem with sql server. I want to find a record from a database, with like 30 tables in it, and lots of records. I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it, any help will be welcomed, so I can resolve this issue. Thanks ahead
|
|
|
|
|
laziale wrote: I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it,
Well, if dbTest is the name of the database then it won't work. You must specify the name of the table. If you don't know your database well enough to know where things are then you should be asking a colleague for some sort of ER diagram and data dictionary (documentation) to show you where things are.
|
|
|
|
|
no, I know the name of the database, the name is dbo_Customers, but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
|
|
|
|
|
laziale wrote: but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying
You write 30 queries. Or you find out where the data is held.
As I said, you need to ask for a ER diagram and data dictionary to find out how the database is structured and what it all means. Randomly fishing around inside tables for some value is really not the way to go.
|
|
|
|
|
You have to give table name in select statement and if you want to search sometext from multiple tables, you should use multiple querires for multiple tables.
Do good and have good.
|
|
|
|
|
can you give me some example please. Thanks
|
|
|
|
|
select * from tablename where columnname = 'sometext';
Please tell what is "Values" in your query?
It must be the column name in your table on which you want to find the sometext.
Do good and have good.
|
|
|
|
|
the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside. Thx for helping
|
|
|
|
|
laziale wrote: the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside.
You say "we don't know" which implies that no one knows where this data is in your database. I therefore have to advise you to start creating documentation for your database. It is totally absurd to be running a database and not know what sort of information is in it and where it is.
All I can say is that the value hints that it might be the name of a road or street. Do you have any tables that hold that kind of information?
|
|
|
|
|
Hi Friends..
Can any body help me on....Database Migration.First time I am going to do this.
what approach i should follow.or any best approach you have implemented.
Oracle to SQL Server Migration
Dasrimk
|
|
|
|
|
Heres the scenario...
An ASP Page calls a c# component registered using regasm /tlb seehash.dll
I know the registration of the dll has gone fine because for testing purposes the dll just returned a simple string 'IamSeeHash' which got displayed on the asp page. Then I went back to my seehash.dll, uncommented the following line
Database db = DatabaseFactory.CreateDatabase();
and recompiled the dll and re-registered it again.
now I get the error 'Object reference not set to an instance of an object.'
I figure this could be because the config file cant be located by the asp process, so I created a copy of the config files in the SystemRoot%\System32 directory as 'dllhost.exe.config' and also 'asp.dll.config'
another copy named as 'index.asp.config' in the asp pages root directory. still I get the same error
I even went back to my seehash.dll and modified the line to add the specific database name
Database db = DatabaseFactory.CreateDatabase("MyDb");
then it says cant locate the database in the configuration file
What is going wrong? Please help. 
Thanks.
<b>+Heres my config file +</b>
<configuration>
<configSections>
<section
name="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data" />
</configSections>
<connectionStrings>
<add
name="MyDb"
providerName="System.Data.SqlClient"
connectionString=
"server=(local)\SQLEXPRESS;
database=MyDb;
UID=sa;PWD=usa;" />
</connectionStrings>
<dataConfiguration defaultDatabase="MyDb"/>
</configuration>
<b>+Heres my folder structure +</b>
For the asp page
C:\Inetpub\wwwroot\simpleAspSite
For the dlls (all referenced dlls of the datablock are in this directory)
c:\mydlls\seehash.dll
I am continually amazed by how little code is required to use atomic clocks in satellites 11,000 miles above my head.
|
|
|
|
|
|
Hi,
I have a table called tblUserDetail which contains the following attributes:
- ID
- ManagerID (which will refer to ID)
- etc...
Each user can have only 1 manager, but each user can have 0 or more staff.
Now, I have the following structure
A G
---|--- ---|---
| | | | |
B C D H I
|
E
|
F
Therefore, if I choose person "A" (for example), it will allow me to add person "G" as person "A" staff.
If I choose person "F" it will also allow me to add person "G" as person "F" staff.
Now, I can handle 3 level of hierarchy, but I don't know how to do recursive in SQL statement.
Here is my SQL Statement:
eg.
thisUserID = [current selected user]
thisUserManagerID = [the selected manager id]
strSQL = "SELECT UserID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE " +
"UserID<>" + thisUserID.ToString() + " AND ManagerID<>" + thisUserID.ToString() + " AND " +
"UserID<>" + thisUserManagerID.ToString() + " AND ManagerID=-1 AND " +
"(NOT EXISTS(" +
"SELECT * FROM tblUserDetail u2 WHERE u2.UserID=" + thisUserManagerID.ToString() + " AND " +
"NOT EXISTS(" +
"SELECT * FROM tblUserDetail u3 WHERE u1.UserID<>u2.ManagerID" +
")" +
")" +
")";
Currently, if I choose person "F" it will allow me to add "A" and "G" as person "F" staff because it doesn't handle more than 3 level of hierarchy.
Now, this works perfectly for 3 level of hierarchy, but I need more than 3 level. Is there a way to do recursive statement within SQL statement?
Cheers 
|
|
|
|
|
SQL Server 2005 provides common table expressions (CTE) for recursion. You can view more here[^]
Regards,
Mehroz
|
|
|
|
|
Hi Syed,
I am having a trouble to bind between fields. Here is a simple example
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.UserID<>u1.ManagerID
)
SELECT * FROM NewUserDetail
And I got this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u1.ManagerID" could not be bound.
I need to bind value to recursively go through all the users so the user will not be able to select its manager or manager of its manager.
Any idea on how to tackle this problem?
Cheers
|
|
|
|
|
Seems you didnt get the cte syntax right. The second query inside the cte syntax generally joins with the cte itself, not the first query.
Lets have a cte that lists your user and all its subordinates
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
|
The problem resides in your recursive call. The line is "SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID"
Here you have to add a inner join to the NewUserDetail table.
I am also giving you an example. Hopefully it will solve your problem. The example is given below:
WITH TemplateTopic(parent_id, child_id, child_name, child_type, Level)AS
(
-- Anchor member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type, 0 AS Level
FROM tbltemplate_topics a Where Child_Id = 16
UNION ALL
---- Recursive member definition
SELECT a.parent_id,a.child_id, a.child_name, a.child_type,
Level + 1 AS Level
FROM tbltemplate_topics a
INNER JOIN TemplateTopic AS d
ON a.parent_id = d.child_id
)
-- Statement that executes the CTE
SELECT * FROM TemplateTopic
(Make changes in your code accordingly)
Do good and have good.
|
|
|
|
|
Oh...thanks for pointing. I missed the inner join part, dont know why..
Here is the modified cte
WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail Manager WHERE u2.ManagerID=Manager.UserID
)
SELECT * FROM NewUserDetail
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
