Re: MS Access Relationships in Tables - Database Discussion Boards

Re: Pulling the latest entry for a product BY DATE from a table

Thea Burger14-Jun-04 4:09

Thea Burger

14-Jun-04 4:09

Hi,

What about this:
SELECT * FROM sales WHERE productdate = (SELECT max(productdate) FROM sales)

HTH,
Thea

Re: Pulling the latest entry for a product BY DATE from a table

lostsheep00715-Jun-04 16:59

lostsheep007

15-Jun-04 16:59

Thank you very much, Thea

Everything & Nothing
http://www.lostsheep.com

Re: Pulling the latest entry for a product BY DATE from a table

Colin Angus Mackay15-Jun-04 4:26

Colin Angus Mackay

15-Jun-04 4:26

SELECT TOP 1 * FROM table WHERE condition ORDER BY productdata DESC

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

My Blog

Re: Pulling the latest entry for a product BY DATE from a table

lostsheep00715-Jun-04 17:00

lostsheep007

15-Jun-04 17:00

Thanks for for that too Colin

Everything & Nothing
http://www.lostsheep.com

Updating Unique Primary Keys

JosePidal13-Jun-04 22:34

JosePidal

13-Jun-04 22:34

I'm accessing Oracle data using oo4o VB. Here's the problem:
I'd like to update the unique keys like below. How would I
implement it using oo40 oradynaset. If it can't be done,
how to implement it in ADO?

Ukey (Original -> Target Data)
----
10 -> 11
11 -> 12
12 -> 10

Have tried it but there's a unique key violation error
after I call the Oradynaset.Update method for each row.
I use begintrans/committrans.

Re: Updating Unique Primary Keys

Steven Campbell14-Jun-04 7:33

Steven Campbell

14-Jun-04 7:33

I am a little unclear about why you would be doing this from code. Updating primary keys should never be a part of an application, unless it is a one-time fixup of data.

That said, the problem is that the database will not allow 2 items to share a primary key value, even within a transaction. So, the solution would be do it in two steps, e.g.

Step 1

<br />
10 -> 1011<br />
11 -> 1012<br />
12 -> 1010

Step 2

<br />
1011 -> 11<br />
1012 -> 12<br />
1010 -> 10<br />

You could still keep the above in a single transaction.

MS Access Relationships in Tables

brdavid13-Jun-04 5:32

brdavid

13-Jun-04 5:32

Now the database I have has relationships defined through the tables, albiet the design is more implied in the design of the fields and tables. Realizing that MS Access is nothing more than a front end to the Jet Database, is it worth it to setup relationships in MS Access through the Tools/Relationships menu option? That is, it seems in using this menu option it shows subsheets for the tables that have relationships and perhaps you have a design look and feel in the Relationship window, but on a programmical scale is it worth setting this up? What are the benefits in using that menu option?

Thanks!

Re: MS Access Relationships in Tables

Mike Ellison13-Jun-04 15:38

Mike Ellison

13-Jun-04 15:38

Hi there. I would say yes, establish relationships using the front-end provided by Access. Jet will enforce those relationships, and you would rather have the database engine itself enforce data integrity than to code for it in an application.

I want to Learn Mysql Databse

JawedVikia8012-Jun-04 21:50

JawedVikia80

12-Jun-04 21:50

I want to Learn Mysql Databse is there andy body who can help me in this regard
Thanks
Jawed

Re: I want to Learn Mysql Databse

Mekong River14-Jun-04 5:46

Mekong River

14-Jun-04 5:46

Try to get the this books "PHP/MySql Programming for the absolute beginner" its ISBN is: 1-931841-32-2. You can purchase it from Amazon.com or use google to search for PHP documentation over the internet.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

Re: I want to Learn Mysql Databse

mahsa shahi1-Mar-14 7:06

mahsa shahi

1-Mar-14 7:06

http://dev.mysql.com/[^]

How to Display Photo in sharepoint ?

gangigangi11-Jun-04 18:18

gangigangi

11-Jun-04 18:18

Hi pls let me know anybody how to insert Photo into database and how to display in webpart using C#.

sree

SQL Help/Sample

karitoppinen11-Jun-04 10:00

karitoppinen

11-Jun-04 10:00

If I INSERT data into a table 'table_1' from my C++ application and want the SQL Server to INSERT portions of data from 'table_1' into 'table_2' and 'table_3', how do I solve that in a correct way? Do I have to do INSERR from my C++ application into 'table_2' and 'table_3' or is there a better way to let the SQL Server handle that??

KT

Re: SQL Help/Sample

-Dr_X-14-Jun-04 19:39

-Dr_X-

14-Jun-04 19:39

Put an insert trigger on table_1.
With in the trigger do the insert into table_2 & table_3.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Re: SQL Help/Sample

karitoppinen15-Jun-04 6:00

karitoppinen

15-Jun-04 6:00

Thank you for your answer. I will try that...

keyboard shortcuts for sql database managment tool

amitmerla11-Jun-04 9:59

amitmerla

11-Jun-04 9:59

Hello, I'm new at this tables. I'm getting into this microsoft sql server enterprise manager, and I have on my database 50 or more tables. looking for a keyboard shortcuts for this tool. anyone knows?

Re: keyboard shortcuts for sql database managment tool

Thea Burger13-Jun-04 8:41

Thea Burger

13-Jun-04 8:41

In Enterprise Manager, go to the help menu. Then see Accessibility for MMC.

HTH,
Thea

Output Parameters - Help!

mikasa11-Jun-04 9:16

mikasa

11-Jun-04 9:16

Ok, I have been mapping DataColumns to DataParameters for a while, and getting great results with output Parameters! All of the sudden, it doesn't work anymore!!! WHY!!!???

Can anyone point me into the direction of where to look for troublesome code?

P.S.
No, I cannot supply any code...

Stored Procedures in C#

dtbiju10-Jun-04 19:25

dtbiju

10-Jun-04 19:25

I am using SQL 2K and C#. I want to keep all the database manipulation code at the db itself and so i decided to use Stored Procedures. Now when i tried to use Stored Procs with parameters, i find that i have to supply the datatype as well as the data length when i create the Parameter.
***************************************
SqlParameter parm = new SqlParameter();
parm.ParameterName = "@employeeName";
parm.DbType = DbType.Varchar;
parm.Size = 50;
parm.Direction = ParameterDirection.Input;
cmd.Parameters.Add(parm);
**************************************
Doesn't this DEFEAT the whole purpose(keeping the code independent from database) of using stored procedures This means if i change the size of a parameter in the database, i have to modify my c# code and recompile. Am i missing something or is this the only way to do it?

Re: Stored Procedures in C#

Michael Potter11-Jun-04 4:31

Michael Potter

11-Jun-04 4:31

There are ways to query the DB to set up the parameters for you unfortunatly, it is much slower.

It is a pain to deal with parameter size changes but, if your DB design is well thought out it becomes a very minor issue over time.

This is really no different than coding your own SQL statements at the client. If you changed a column size and passed in data the exceeds it, SQL will throw an error and you will have to change your client code. Knowing your data size limits at compile time is a good thing.

Re: Stored Procedures in C#

VenkatFor.NET11-Jun-04 8:37

VenkatFor.NET

11-Jun-04 8:37

I work in VB.NET but I guess, ADO.NET must be same in C#. So, here is how we use DB procedures for data handling.

We have some SQL Helper classes defined parallel to Data.SQLClient methods. These methods will discover the parameters of the procedures at runtime using SQLCommandBuilder.DeriveParameters. This gives us the current parameter information of Procedures we have in DB.Though it is a bit slow, we will have current parameter collection information. If size of a parameter in underlying SP is changed this method will have it too. So, while referencing this method, we dont have to mention datatype or size of parameters.

This works fine without changing the client or recompiling, as long as the singnature of SP is not changed. Else obviously we have to change the code in the client application.

Bhaskara

Re: Stored Procedures in C#

mikasa11-Jun-04 9:13

mikasa

11-Jun-04 9:13

Tadaaaaa!

Select Parameter_Name, Parameter_Mode, Data_Type, Character_Maximum_Length, SysTypes.Length
From Information_Schema.Parameters
Inner Join SysTypes ON Information_Schema.Parameters.DATA_TYPE = SysTypes.Name
Where Specific_Name = '[Sotred Proc Name]'

It's fast and efficient...

Re: Stored Procedures in C#

Steven Campbell11-Jun-04 18:36

Steven Campbell

11-Jun-04 18:36

There are very few reasons to go with stored procedures at all. The possible valid reasons are:
* better database security
* allows dba to have better control

The invalid reasons are:
* speed (minimal at best, occasionally worse)
* keeping code independent of the database (it does the opposite by locking you into a db vendor)
* strongly typed parameters (you can do this with normal queries)

Good reasons not to do it are:
* hides business logic away
* SQL expertees required to maintain
* difficult to write search queries
* locks you into a specific database vendor
* adds another level of complexity to your app

Actually, for a new application, there is very little need to write any data access code at all. I use Codesmith to generate my data access code just the way I like it.

Re: Stored Procedures in C#

Arjan Einbu15-Jun-04 2:00

Arjan Einbu

15-Jun-04 2:00

For input parameters you don't need to set the length or the type...

SqlCommand cmd = ...;
cmd.Parameters.Add("@ParamName", obj);

Have a look at my latest article about Object Prevalence with Bamboo Prevalence.

MFC/DAO temporary table

IQ010-Jun-04 10:59

IQ0

10-Jun-04 10:59

i don't know if this is the right place to post this, but is database related so i hope someone can help me ...

!!! all this is done in vc++ 6.0 using MFC/DAO !!!
i have a database with a table that has one numeric field called ID...
in my program, i assign a string to every value of this field...
so if the table has the following data

1
3
2
1
2

then my data will be in the following form

cow
chicken
wolf
cow
wolf

that is, every number has an assigned string.
the problem is that i want to store only the numeric field in the table and do all the processing in the program
so how can i have a recordset that contains the strings and to be able to sort it ?

another example :

i have a table that contains two fields : DATE and TEXT
but i want to have in my program three fields : DATE, TIME, and TEXT, where DATE and TIME is extracted from the DATE field in the database.
also i want to be able to sort by DATE or TIME in my program, so a database with folowing data

DATE TEXT
---------------- -----------
15/02/2001 12:28 Chris
19/02/2002 15:42 George
25/07/2003 09:48 John

will show up like this in my program

DATE TIME TEXT
---------- ----- -----------
15/02/2001 12:28 Chris
19/02/2002 15:42 George
25/07/2003 09:48 John

and i should be able to sort by DATE, or TIME, or TEXT

i'm showing data using a CListCtrl (not virtual)

please can somebody help me up with this ?

thanks ! (at least for reading this)

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