Database

12-Jul-07 4:04

SQL is designed to get you information into/out of the database. This problem has to do with the display of the information, which you really should decouple from the idea of storing the information. You really need to do this via your view code otherwise you will end up storing information in your database purely for the purposes of viewing the other fields.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Query Problem

ragavan12-Jul-07 3:14

ragavan

12-Jul-07 3:14

Hi I have the following table with the values

Name Number username displayname kind path
gane 0 ganesh gane 0 \Root\
bane2 0 ganesh bane2 1 \Root\gane
aane2 0 ganesh aane2 0 \Root\
hane1 0 ganesh hane1 1 \Root\gane

I need a query which will give the output

Name Number username displayname kind path
aane2 0 ganesh aane2 0 \Root\
bane2 0 ganesh bane2 1 \Root\gane
hane1 0 ganesh hane1 1 \Root\gane
gane 0 ganesh gane 0 \Root\

Thanks in advance

Re: Query Problem

Pete O'Hanlon12-Jul-07 3:24

12-Jul-07 3:24

I assume you are wanting to sort on the name column here. If so, add ORDER BY Name to the end of your select.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: Query Problem

TomGarth15-Jul-07 9:27

TomGarth

15-Jul-07 9:27

In looking at your data sorting wish, the only organization I see that makes any sense is to sort like "ORDER BY LEN([Name]) DESC, [Name] ASC".

Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia

Connection problem

matjame12-Jul-07 2:50

matjame

12-Jul-07 2:50

Hi guys.I want to establish a connection to my MS Access database using my HTML or ASP code.The problem is that I have to load database values onto the dropdownlist when I run the page but I say its the connection because the page run with no values and no dropdown.I havent been able to run any values in other examples that Ive tries.Is there configurations that I need to change in ASP or HTML to run database connections in ASP and HTML.

Here is the code I got a problem with

<%@LANGUAGE="VBSCRIPT" %>

<%
Option Explicit
Dim objConn
Dim objRS
Dim objRS2
Dim strSQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")

objConn.ConnectionString = Server.MapPath("dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

'-- Sub Procedure that builds the dropdown list.
'-- Parameters c, r and table are passed when the sub is built.
Sub showDropDownList(c, r, table)

'-- SQL Statement
strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
'-- Execute our SQL statement and store the recordset
Set r = c.Execute(strSQL)

'-- START MAIN CODE BLOCK
'-- If we have records to return
if not r.eof = false then
'-- Open a form/select tag
'-- This particular example has no action attribute, which would normally...
'-- ...be required in order to post the data to another page for processing.
Response.Write ""
Response.Write "" & vbCrLf
Response.Write "Choose...."

'-- loop and build each database entry as a selectable option
While r.EOF = false
Response.Write ""
_ & r.Fields("Title").Value & "" & vbCrLf

'-- Move recordset to the next value
r.movenext
Wend
end if
'--END OF MAIN CODE BLOCK

'-- close select/form tags
Response. Write "" & vbCrLf

End Sub

%>

<title>

Dynamic dropdown lists using Active Server Pages and Microsoft Access

Below is an example of a drop down list, dynamically populated from
a database table.

<%
' -- Call the sub procedure to build the drop down list, with the connection
and recordset objects and the source table (In quotes).
call showDropDownList(objConn, objRS, "tblDropDownTest")
%>

kagiso

andyharman12-Jul-07 5:52

andyharman

12-Jul-07 5:52

Shouldn't it say "if r.eof = false then"?

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

matjame12-Jul-07 20:22

matjame

12-Jul-07 20:22

Thatz what I said on there.If rs.EOF then

Unless there is someother rs.EOF that I forgot to change.But ive done that already and nothing.

kagiso

Pete O'Hanlon12-Jul-07 22:49

12-Jul-07 22:49

After Set r = c.Execute(strSQL) put r.MoveFirst. I seem to remember that there are some circumstances where you have to do this - although it's been a very long time since I've done ASP coding.

Plus, as was said above - it should be If r.EOF = False Then ...

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

matjame12-Jul-07 23:09

matjame

12-Jul-07 23:09

Thanx but still nothing.Im thinking it could be some settings on my ADO or something.Ive don all that u said and still nothing
I still get no values.

But anyways, thanx guys.As for ur link, it tells me thise website is not avaialable.I will try again.

kagiso

easy join question

blakey40412-Jul-07 2:43

12-Jul-07 2:43

her is a simplified version of my problem...

2 tables...

CUSTOMER
customerId
...

ORDER
orderId
customerId
...

the relationship is clearly one (customer) to many (order)

i want to do a join which will only bring back firt order found.

so

select * from customer
inner join order on order.customerId = customer.customerId

but in my "bigger" query this brings back all orders for each customer. so do any outer joins.

IDEAS?

Thanks in advance

Anthony "having a thick day" Blake

Colin Angus Mackay12-Jul-07 2:46

Colin Angus Mackay

12-Jul-07 2:46

SELECT TOP 1 ...

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

Ready to Give up - Your help will be much appreciated.

My website

blakey40412-Jul-07 2:48

12-Jul-07 2:48

just gives the first customer - i want the first order found for each customer

cheers

anthony

Pete O'Hanlon12-Jul-07 2:54

12-Jul-07 2:54

You could always try this:

select * from customer
inner join order a on a.customerId = customer.customerId
and a.orderId = (SELECT First 1 b.orderID FROM order b WHERE b.customerID = customer.customerId)

It's not going to be the most efficient SQL ever, but it should do the trick.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

blakey40412-Jul-07 2:57

12-Jul-07 2:57

i will try something like that... i think that it will make a difference in speed but the overall query i'm working on returns 100,000+ rows so i'll have to just try and find out. cheers.

DQNOK12-Jul-07 8:41

DQNOK

12-Jul-07 8:41

Rename your ORDER table to ORDERS since ORDER is a keyword (ORDER BY) that conflicts. I also took the liberty of renaming CUSTOMER to CUSTOMERS (since all table names should probably reflect a "plural" sense).

The key is to form a "virtual" orders table that only contains the first order for each customer, and then join it to the CUSTOMERS INNER JOIN ORDERS table to get the other fields you want while eliminating records you don't want. Using the vendor-specific TOP keyword should work, but a more standard SQL way might be:

SELECT * 
FROM       (ORDERS AS O
INNER JOIN CUSTOMERS AS C ON O.customerId = C.customerId)
INNER JOIN (SELECT customerId, MIN(orderId) AS M
            FROM ORDERS
            GROUP BY customerId) AS GB ON O.orderId = GB.M;

Assuming your orderId is incresing, this will give it to you. The GROUP BY is how to eliminate all the rows you don't want. HOWEVER, you'll notice that the output reproduces O.customerId, C.customerId, and GB.customerId, all of which are redundant. The only way I know to preventing listing them all is to spell-out the fields you want instead of using '*'.

Hope this helps.

David

blakey40413-Jul-07 0:29

I want to Connect to MySql Server using .NET.

13-Jul-07 0:29

Thats great. Don't worry about the table names - they were hyperthetical - trust me postng the actual query wouldnt have provoked any response it's huge.

vikasviswan12-Jul-07 2:42

vikasviswan

12-Jul-07 2:42

I want to Connect to MySql Server using .NET but I dont have MySql client installed in my machine.
I am also connecting to SqlServer in my application using this Connection String

add name="localServer" connectionString="Data Source=
TRVWSPW001\SQLExpress;Initial Catalog=Dashboard;User Id=Dashboard_Users;Password=passw0rd@123;" providerName="System.Data.SqlClient"

Wat would be connection string to connect to MySql
i know these details Server name,database name,User id
Pwd

Re: I want to Connect to MySql Server using .NET.

blakey40412-Jul-07 2:46