Database Discussion Boards

First Prev Next

Re: how i can take the sum of times

syed saba16-Jun-04 3:50

syed saba

16-Jun-04 3:50

Thanks let me check it out

Pulling the latest entry for ALL products BY DATE from a table

lostsheep00715-Jun-04 22:22

lostsheep007

15-Jun-04 22:22

Hi
As almost a continuation of my last question, I'm now trying to select the latest date entry record for ALL products out of a table - the latest date varies for the products. I'm using a SELECT DISTINCT productID, but when I try to use MAX(date) it of course is choosing only those with literally the latest date....

Hope you can help again! I am learning.. Smile | :)

Patrick Collins

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

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

WoutL15-Jun-04 22:53

WoutL

15-Jun-04 22:53

Try:

<br />
Select ProductID, Max(Date)<br />
Group by ProductID<br />

Wout Louwers

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

lostsheep00715-Jun-04 23:24

lostsheep007

15-Jun-04 23:24

Thank you Wout,

How embarrassingly easy! Blush | :O

Just to check, if I select other fields for the product from that record, it has effectively limited them to the record with the Max Date for that product?

Thanks in advance

Patrick Collins

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

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

Colin Angus Mackay16-Jun-04 0:14

Colin Angus Mackay

16-Jun-04 0:14

No, the GROUP BY clause will only permit you to select the fields in the GROUP BY clause itself and any field in an aggregate function.

To do what you want you would have to create some sort of subquery

SELECT * FROM my_table 
    INNER JOIN 
        (SELECT product_id, MAX(date_field) 
         FROM my_table GROUP BY product_id) 
    AS inner_table 
    ON my_table.product_id = inner_table.product_id

This will allow you to return all the fields from your table.

"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 ALL products BY DATE from a table

lostsheep00716-Jun-04 21:48

lostsheep007

16-Jun-04 21:48

Thanks again Colin,

Got there - It would have helped if any of the tables I was working with were normalized!

Patrick

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

Enterprise Manager problem

TPN15-Jun-04 21:44

TPN

15-Jun-04 21:44

I'm currently using Enterprise Manager to connect to some SQL 2000 Servers to manage them. The problem is the Enterprise cannot connect with some servers but can with other servers, the error is: "SQL Server does not existe or access denied ConnectionOpen(Connect())". To verify the connection, I use Query Analyzer and it can connect all servers. (All connection library was enable for client and servers).

Please help me.

Re: Enterprise Manager problem

RichardGrimmer15-Jun-04 22:15

RichardGrimmer

15-Jun-04 22:15

Would check that the registration properties are correct, and that they are using the default instance name (May be barking up the wrong tree however!)

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: Enterprise Manager problem

fweinrebe16-Jun-04 1:59

fweinrebe

16-Jun-04 1:59

It could be that your connection times out before it can connect to the SQL Server. Try a larger setting.

How to set timeout for SQL server check access rights ?

vgrigor115-Jun-04 21:06

vgrigor1

15-Jun-04 21:06

How to set timeout for SQL server check access rights ?

SQL server on remote computer either
connects very fast if succeeded,
but if user has no access right connects 10-30 seconds
and makes imression that application is hanged.

So question is:
how to decrease access rights time of check ?
Mostly needed to requrement to program it fast.

How such a problem is usually resolved in else ways ?

Asyncro connection works bad due to driver
not releases Connection.open() request fully,
but sends messages after it,
during that peiod application hangs.

Does that need full multi - threading programing?

Thanks.

Re: How to set timeout for SQL server check access rights ?

RichardGrimmer15-Jun-04 22:16

RichardGrimmer

15-Jun-04 22:16

Believe that one of the properties of the connection string is "ConnectionTimeout"

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: How to set timeout for SQL server check access rights ?

vgrigor116-Jun-04 1:02

vgrigor1

16-Jun-04 1:02

I tried to set all connection properties
wit word "Timeout"
but for checking rights it os not works.

Re: How to set timeout for SQL server check access rights ?

RichardGrimmer16-Jun-04 1:50

RichardGrimmer

16-Jun-04 1:50

My mistake, it's a property of the connection Object rather than the connstring....

An example can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdproconstringx.asp[^]

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: How to set timeout for SQL server check access rights ?

vgrigor116-Jun-04 4:00

vgrigor1

16-Jun-04 4:00

For check rights it is not working.

Re: How to set timeout for SQL server check access rights ?

RichardGrimmer16-Jun-04 4:08

RichardGrimmer

16-Jun-04 4:08

Can you clarify what you mean by access rights. When forming a connection string, you can specify a UserID / Password combination, if you set the connection timeout to a small value and then attempt to connect using a specified user account, then if the login is not found straight away (as seems to be the case from your original post), then the timeout will kick in and the connection attempt will be refused...

Can you give me a little more info please?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: How to set timeout for SQL server check access rights ?

vgrigor116-Jun-04 5:02

vgrigor1

16-Jun-04 5:02

When I connect to SQL server to which I has permission.

than I wait 20-30 secondes,
instead of 2-3 suffitient.

connection timeout not affects on this.

Getting table structure out of MS-Access

Member 64506715-Jun-04 19:39

Member 645067

15-Jun-04 19:39

Hi,
I need to know the table structure of an MS-Access database. I tried Desc table sql and it doesnt seem to work. Is there any other method to know the structure of a table. I need to know it from VB/VC code and populate the result to a recordset.

Thanks for any help
Vikram Attiganal

Re: Getting table structure out of MS-Access

RichardGrimmer15-Jun-04 22:21

RichardGrimmer

15-Jun-04 22:21

Using ADO, you can call the Connection.OpenSchema method with the adSchemaTables flag. This returns a recordset with all the tables in. You can then iterate through this and call OpenSchema with asSchemaColumns and a table name specified.

Alternatively, you could use ADOX - this exposes a Catalog object, which if you give it an active connection object, holds an object model of the general structure

Catalog+
|
+
Tables+
|
+
Table+
|
+
Columns+
|
+
Column

To use ADOX, set a reference to ADO Ext. For DDL and Security

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Returning a unique value

Vicetta15-Jun-04 4:00

Vicetta

15-Jun-04 4:00

I have a vb.net script which inserts a new record into a sqlserver database.

I have ListID as the primary key (autonumber). I need to find a way of returning this number once I have saved the first part of the record, as on the next page I save some other fields of the same record.

Does anyone have any ideas?

*wasn't sure whether to post to here or to VB.net so sorry if on the wrong board*

Cheers, V

Re: Returning a unique value

Mike Ellison15-Jun-04 14:18

Mike Ellison

15-Jun-04 14:18

Hi there. You can use a select command with commandText "Select @@Identity" to get the inserted key from SqlServer. Here's a link[^] to a discussion forum thread that shows an example of this in context.

Re: Returning a unique value

Vicetta15-Jun-04 22:41

Vicetta

15-Jun-04 22:41

Thank you very much,

I have it returning the value now.

Cool | :cool:

Cheers, V

Inserting & Updating A Row With Unique Key

sanjaysagrawal15-Jun-04 2:45

sanjaysagrawal

15-Jun-04 2:45

Hi all,

I have a table with one column set as primary key, which
is not supposed to be duplicated. This key has a corresponding textbox on the form which the user enters manually.

I use .ExecuteNonQuery() after setting up the parameters of a SQLCommand object to insert a row. The SP does a simple insert routine.

Using one single button for updating the table (new row/existing row), is there a simple / automatic method to modify an existing row in the table (using Triggers, etc.) ? Or, do I have to write the code in the button that will -

first, fire an sp that returns back a boolean indicating whether a row with the user-entered primary key already exists, then

second, conditionally set the .commandtext value to either of the two SPs (INSERTSP / UPDATESP) depending on whether the boolean was row-exists/does-not-exist.

finally, fire the .executenonquery() ?

Any help will be highly appreciated.

Thanks in advance,
Sanjay.

Re: Inserting & Updating A Row With Unique Key

VenkatFor.NET15-Jun-04 5:14

VenkatFor.NET

15-Jun-04 5:14

How about this?
Instead of having two procedures for a simple task, have a single procedure which will check if the record is existing. If exists, update else insert. And finally this procedure can return you a status if you want to know if the record is inserted or udpated.

Bhaskara

Re: Inserting & Updating A Row With Unique Key

nbhairav12315-Jun-04 5:19

nbhairav123

15-Jun-04 5:19

Thanks, Bhaskara ! Smile | :)

Regards,

Sanjay.

Re: Inserting & Updating A Row With Unique Key

Anonymous21-Jun-04 12:56

Anonymous

21-Jun-04 12:56

How can I check whether the data I would like to enter exist in the table or not.

For example, the primary key I would like to enter is 123. How do I check if this primary key already exist?

Please kindly guide me.

Thank you very much in advance.

Last Visit: 31-Dec-99 18:00 Last Update: 22-May-24 21:41

Refresh

ᐊ Prev 1...2239 2240 2241 224222432244 2245 2246 2247 2248 Next ᐅ

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