|
Thank you this also works djj55 
|
|
|
|
|
Hi,
What is the query to get the list of table names for MS Access.
In Sql Server :
USE [MyDatabase]
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
In MS Access ?
Thanks in advance.
|
|
|
|
|
SELECT
Table_Name = Name,
Row_Count = DCount("*",[MSysObjects].[Name])
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
Source can be found here.
I are Troll 
|
|
|
|
|
Hi Eddy,
Thanks for your response.
I got one more alternative for that...
OleDbConnection con = new OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source="+Server.MapPath("~/App_Data/Sample.mdb"));
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
con.Close();
|
|
|
|
|
sekhar.k wrote: I got one more alternative for that...
The .NET version would be preferable over an SQL-statement; it's obvious what the code does if you read your version. It's not that obvious what the query on the MSys tables does.
Well done 
I are Troll
|
|
|
|
|
Hi All, I am trying to create a report for every hour based on a selected day.
I am trying to use sql statement. For example, I have dateTimePicker and I select a dy of a month
then it should get all the data that took place from 12am to 11pm for every hour.
FYI : my database sqlite. coding in C#
thanks for any help in advance.
|
|
|
|
|
And what is the problem?
|
|
|
|
|
Thanks Dmitry, I am not an expert in sql. therefore, I don't know how to do that.
FYI: TableOne has Column Sell, quantity, emID,orderDate,shipDate.
I want to know how many sell was made on a given day. And how many sell was made per hour in a gaven day(24 hours).
|
|
|
|
|
You should create a query with condition, like this:
select count(*) from TaleOne where shipDate between <start_date> and <end_date>
You can google and find how to use queries in sqlite, there are plenty of such info.
|
|
|
|
|
SELECT COUNT(SELL)
FROM TableOne
GROUP BY CONVERT(DATETIME, orderDate, 101)
The convert is to use short date
For the hour look at DatePart[^]
It is always good to read a book or tutorials before jumping to development.
|
|
|
|
|
SQLite doesn't have DatePart; you should save dates as long in sqlite.
|
|
|
|
|
Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.
|
|
|
|
|
My bad - missed that fact that its a SQlLite question. Nice article by the way.
|
|
|
|
|
Hy to All;
I want to use two insert statement in one stored procedure
I have Two Tables "Activitity" (ActivitityId as a primery key)
and "TODo" Second Table (TODOId as a primery) and (ActivitityId as a foriegn key) ....
So when a value insert into "Activitity" Table ,I want to insert its primery key into "TODO" Table as Foriegn key in a single Stored procedure...
So for this purpose I want to use @@indentity variable
how can I use @@indentity variable in my stored procedure....
|
|
|
|
|
if you have a column set as identity you can use SCOPE_IDENTITY()
DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()
|
|
|
|
|
So store the @@Identity or Scope_Identity() in a variable directly after inserting the activity record and use it in the second insert for todo, there you have your relationship
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even you can go ahead with OUTPUT.INSERTED
e.g.
insert into Activitity
output inserted.ActivitityId into TODo(ActivitityId)
values(1,'somerecord')
select * from TODo
Output:
TODOId ActivitityId
1 1
Niladri Biswas
|
|
|
|
|
Now that's cool, I was not aware INSERTED worked for an ordinary insert statement, I thought it was only for triggers. Now to go play, I wonder is UPDATED works as well
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys,
I have created a trigger for auditing ..
I want to keep track of changes made to each field by using a counter.
can you let me I go about achieving this task....
modified on Wednesday, January 13, 2010 5:34 AM
|
|
|
|
|
If I understand your questions correctly, why dont you create an audit table and always add a record before each update.. so you want an update triger[^]
|
|
|
|
|
I am developing an ecommerce website, aimed at selling electronic products.
So for storing the product details I use a product table in which I have the fields for storing the product specifications. My problem is how to include the specifications of different products like motherboard, mouse, Monitor in a single table (product table).
How should I design the product table to hold the specifications of different products. Suggest me a good database design for this problem.
|
|
|
|
|
You can have two tables like this:
In product table:
ProductId
ProductName
ProductType
and in ProductTypeCodes:
ProductTypeCode
ProductType
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
You need to think about the data - I'd get plenty of examples and see how they look - what attributes define a particular product for example.
A possible suggestion is two tables: one to hold products and the other to hold attributes. This is a simplified example of this approach.
Product Table
-------------
ProductID
ProductName
ProductType
Attribute Table
---------------
AttributeID
ProductID
Attribute
Value
Examples:
Products
1, LG XYZ, TFT Monitor
2, AX-999, Motherboard
Attributes
1, 1, Screen Size, 22
2, 1, Colour, Black
3, 2, CPU Type, AMD
4, 2, PCI Sockets, 3
You need to link the tables. Also this is probably too simplistic - think about the units for attributes. Some will numeric others strings.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
