Re: how to get table names in MS Access database[ .mdb] - Database Discussion Boards

Re: Problem with subquery returning more than 1 value

dan!sh 11-Jan-10 3:40

11-Jan-10 3:40

You must be having multiple prices for that date. If you need the top record only, use order by and top 1 in you statement.

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...!!

Re: Problem with subquery returning more than 1 value

James Shao11-Jan-10 4:36

James Shao

11-Jan-10 4:36

Thank you D@nish for the tip, yes indeed there are multiple prices on same dates belonging to a ticker, though that shouldn't happen. Bad data, may need to redownload.

Top 1 solves this for now. Many thanks again.

Re: Problem with subquery returning more than 1 value

Corporal Agarn11-Jan-10 0:58

Corporal Agarn

11-Jan-10 0:58

You can always you TOP 1.

SELECT TOP 1 mycolumn FROM mytable

This probably will not work for your case but I thought I would throw it in.

Re: Problem with subquery returning more than 1 value

James Shao11-Jan-10 4:33

James Shao

11-Jan-10 4:33

Thank you this also works djj55 Smile | :)

how to get table names in MS Access database [Solved]

K V Sekhar9-Jan-10 6:15

K V Sekhar

9-Jan-10 6:15

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.

Re: how to get table names in MS Access database[ .mdb]

Eddy Vluggen9-Jan-10 6:54

Eddy Vluggen

9-Jan-10 6:54

SQL

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 Suspicious | :suss:

Re: how to get table names in MS Access database[ .mdb]

K V Sekhar9-Jan-10 19:04

K V Sekhar

9-Jan-10 19:04

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();

Re: how to get table names in MS Access database[ .mdb]

Eddy Vluggen9-Jan-10 23:40

Eddy Vluggen

9-Jan-10 23:40

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 Smile | :)

I are Troll Suspicious | :suss:

sqlite

jashimu8-Jan-10 4:02

jashimu

8-Jan-10 4:02

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.

Re: sqlite

Dimitri Witkowski8-Jan-10 4:07

Dimitri Witkowski

8-Jan-10 4:07

And what is the problem? Smile | :)

See my article about Windows 7 Taskbar timer here on CodeProject

Re: sqlite

jashimu8-Jan-10 4:19

jashimu

8-Jan-10 4:19

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).

Re: sqlite

Dimitri Witkowski8-Jan-10 4:36

Dimitri Witkowski

8-Jan-10 4:36

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.

See my article about Windows 7 Taskbar timer here on CodeProject

Re: sqlite

Bassam Saoud8-Jan-10 4:38

Bassam Saoud

8-Jan-10 4:38

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.

Re: sqlite

Dimitri Witkowski8-Jan-10 4:43

Dimitri Witkowski

8-Jan-10 4:43

SQLite doesn't have DatePart; you should save dates as long in sqlite.

See my article about Windows 7 Taskbar timer here on CodeProject

Re: sqlite

jashimu8-Jan-10 5:03

jashimu

8-Jan-10 5:03

Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.

Re: sqlite

Bassam Saoud8-Jan-10 6:58

Bassam Saoud

8-Jan-10 6:58

My bad - missed that fact that its a SQlLite question. Nice article by the way.

I want to use @@indentity Scalar variable in Stored procedure

Muhammad Fahim Baloch8-Jan-10 3:56

Muhammad Fahim Baloch

8-Jan-10 3:56

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....

Re: I want to use @@indentity Scalar variable in Stored procedure

Bassam Saoud8-Jan-10 4:32

Bassam Saoud

8-Jan-10 4:32

if you have a column set as identity you can use SCOPE_IDENTITY()

DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()

Re: I want to use @@indentity Scalar variable in Stored procedure

Mycroft Holmes8-Jan-10 16:49

Mycroft Holmes

8-Jan-10 16:49

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Niladri_Biswas8-Jan-10 17:51

Niladri_Biswas

8-Jan-10 17:51

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Mycroft Holmes9-Jan-10 13:43

Mycroft Holmes

9-Jan-10 13:43

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Niladri_Biswas10-Jan-10 2:06

Niladri_Biswas

10-Jan-10 2:06

Hi Mycroft,
You are right.
It is applicable for Insert, Update & Delete.

Have a look

a) OUTPUT Clause (Transact-SQL)[^]

b) SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE[^]

Niladri Biswas

Accidentally posted to wrong forum. My apologies--- Trigger Help [modified]

spankyleo1238-Jan-10 3:05

spankyleo123

8-Jan-10 3:05

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

Re: Accidentally posted to wrong forum. My apologies--- Trigger Help

Bassam Saoud8-Jan-10 4:36

Bassam Saoud

8-Jan-10 4:36

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[^]

Database Design

Gjm8-Jan-10 2:14

Gjm

8-Jan-10 2:14

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.