Database Discussion Boards

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.

Re: easy join question

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

Re: easy join question

blakey40413-Jul-07 0:29

blakey404

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.

I want to Connect to MySql Server using .NET.

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

blakey404

12-Jul-07 2:46

i would try www.connectionstrings.com

Program is blocked because of a SQL-Query on a locked table

zepl12-Jul-07 2:04

zepl

12-Jul-07 2:04

Hi,

I have a problem with a query on a locked table.
The Database I use is mySQL and I want to get a exception when the program try's
to execute a query on a locked table. I tried to set the CommandTimeout in the
OdbcCommand-Object and I also tried to set the "innodb lock wait timeout"-Attribut
from MysQL but it doesn't work.

Has anybody an idea..?

Re: Program is blocked because of a SQL-Query on a locked table

Paul Conrad13-Jul-07 11:47

Paul Conrad

13-Jul-07 11:47

Have you figured out the problem?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: Program is blocked because of a SQL-Query on a locked table

zepl16-Jul-07 3:36

zepl

16-Jul-07 3:36

Hi,

not realy... I've found an ugly workaround but that was not what
I'm searching for. In other Database Management Systems is something
like LOCK NOWAIT.

Do you have any idea?

Thanks

Convert array to a recordset in asp

anilsingh1976@gmail.com12-Jul-07 1:57

anilsingh1976@gmail.com

12-Jul-07 1:57

I have a difficulty in putting the array in to a recordset.
Please suggest.

Thanks in advance

Re: Convert array to a recordset in asp

Paul Conrad13-Jul-07 11:47

Paul Conrad

13-Jul-07 11:47

What difficulty are you having? Be a bit more descriptive about your problem...

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: Convert array to a recordset in asp

anilsingh1976@gmail.com13-Jul-07 12:16

anilsingh1976@gmail.com

13-Jul-07 12:16

I am getting the data from database as usual in to a recordset.
I am having some data in array.I want to put the data back in some form in to a recordset so that the existing functionlity does not change.

Remark: The recordset has to be disconnected and database need not be updated.
It is just the calculation that I want to do on the recordset.

Please suggest.

Thanks and regards,
Anil

Re: Convert array to a recordset in asp

Paul Conrad13-Jul-07 12:23

Paul Conrad

13-Jul-07 12:23

Just insert the individual elements of the array into the record set. The recordset class should have an Add method to allow you to add the new data into the recordset. I hope this sheds some light for you.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Quick Question.. Affected rows

Peterixina11-Jul-07 21:51

Peterixina

11-Jul-07 21:51

In my case I want to get all rows from a table (however it could be the case I [or someone else] only want to get a specific few). How would I count how many rows have been effected by the SQL query?

Heres a bit of my code:

private const string sqlGetAll =
"SELECT ID , Name , PortalMarkupFile AS Portal " +
"FROM Swin_Devices";

command = new SqlCommand(sqlGetAll, connection);

SqlDataReader reader = command.ExecuteReader();

So I'd like something like reader.getAffectedRows or something similar?

Thanks in advance,

Pete

Re: Quick Question.. Affected rows

Giorgi Dalakishvili11-Jul-07 23:25

Giorgi Dalakishvili

11-Jul-07 23:25

When you are just selecting data none of the rows get affected. Why don't you use sql Count function?

#region signature
my articles
#endregion

Re: Quick Question.. Affected rows

Peterixina12-Jul-07 0:10

Peterixina

12-Jul-07 0:10

Quick bit of research into COUNT and using SELECT COUNT(*) worked a treat.

Thank you!

Re: Quick Question.. Affected rows

Giorgi Dalakishvili12-Jul-07 0:16

Giorgi Dalakishvili

12-Jul-07 0:16

You are welcome Smile | :)

#region signature
my articles
#endregion

Paging

Pdh098211-Jul-07 7:22

Pdh0982

11-Jul-07 7:22

Hi All,

I need to get rid of paging. In other words, I have a webpage in which the data is now being displayed in 3 pages but I want to bring the data alll in one page. Would really appreciate if someone could give me the idea?

Thanks in advance

Re: Paging

Pete O'Hanlon11-Jul-07 10:13

Pete O'Hanlon

11-Jul-07 10:13

How is your paging being accomplished? Is it via a datagrid/repeater or through the database select? If it's via the datagrid, you can switch paging off by setting AllowPaging = false;

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

Re: Database Object.

Colin Angus Mackay11-Jul-07 7:05

Colin Angus Mackay

11-Jul-07 7:05

No idea what you are talking about.

Can you clarify please.

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

Re: Database Object.

Pdh098211-Jul-07 8:42

Pdh0982

11-Jul-07 8:42

I am sorry but now I would try to be more clear.

I have a webpage where the results are shown in more than one page(which is called paging in CF). Now, what I want to do is to put all the results in one page. so, basically no paging.

I hope this helps.
Thanks very much

Re: Database Object.

Harini N K11-Jul-07 19:01

Harini N K

11-Jul-07 19:01

Hi

Are you using ASP.Net(C#.Net or VB.Net)? Are you using any .Net server control to display data on the webpage?

Paging can be done by setting the attribute AllowPaging to TRUE in your .Net Server control. Check your code / HTML page of your web page and change it to FALSE.

Hope this helps.

Harini

SQL script [modified]

kibromg11-Jul-07 6:05

kibromg

11-Jul-07 6:05

Hi All,

I have a single table that contains the following Columns

As you can see i generated the data base on ASc on id's

Select * from Call_register order by id

Table Call_Register
id Call(in Hour) Seconds
76 00:04:23 263
76 00:05:58 358
76 00:06:38 398
77 00:06:22 382
77 00:01:29 89
77 00:10:28 628
77 00:01:18 78
78 00:03:35 215
79 00:07:26 446

I want to regroup them as follows : want to also keep track of number of time id occurs:Like

id occurance call(in Hour) totalSeconds
76 3 times 00:04:23 +00:05:58+00:0638(= 263+358+398=
77 4 times 382+89+628+78=1019seconds

Could you please help in getting me the script that enables me to generate in this format

Id number of times id appeard(frequency) : sum(totalHour) sum(seconds)
76 3 times
77 4 times
78

etc..

Please advice
Thanks

-- modified at 12:33 Wednesday 11th July, 2007

Re: SQL script

RepliCrux11-Jul-07 19:42

RepliCrux

11-Jul-07 19:42

Just in case you are still struggling:

select
id,
cast(count(id) as varchar(10)) + ' times' as 'Frequency',
sum(call) as 'TotalCALL', --this was int in my case
sum(seconds) as 'TotalSeconds' --this was int in my case

from Table
group by id

Cheers

SQL and VB.NET identity insert problem.

aqzman_11-Jul-07 5:45

aqzman_

11-Jul-07 5:45

Hi guys, I hope this is the right section, but as it's more of a SQL problem, it most likly is.

Anyway, my problem is that I get this error:

"DEFAULT or NULL are not allowed as explicit identity values."

Everytime I try to run my code, but of all the searhing I've done on Google and Google code search I really can't find anything amiss. I'll post my source (What I think is needed) at the bottem. If there are any important lines of code that I've left out ask and I'll promptly post them.

Dim mySelectQuery As String = "SELECT * FROM tblCallers SET IDENTITY_INSERT tblCallers ON"
Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"
Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
Dim myCommand2 As New Data.SqlClient.SqlCommand(myInsertQuery, myConnection)
Dim retvalue As Integer
myConnection.Open()
retvalue = myCommand2.ExecuteNonQuery()
Console.WriteLine(retvalue)
Dim myReader As Data.SqlClient.SqlDataReader
myReader = myCommand.ExecuteReader()
myReader.Close()
myConnection.Close()

I know that the code works because I use took it from another program I wrote and added the "SET IDENTITY_INSERT tblCallers ON" line. Also, the CallerID field is an Identity field.

Thanks,
aqzman

Re: SQL and VB.NET identity insert problem.

Colin Angus Mackay11-Jul-07 5:51

Colin Angus Mackay

11-Jul-07 5:51

aqzman_ wrote:
I know that the code works because I use took it from another program

Uh-huh...

aqzman_ wrote:
Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"

You may wish to read about SQL Injection Attacks and how to prevent them[^] becuase your code is vulnerable right now.

My suggestion, don't include the column that will automatically receive the identity in the list of columns you are inserting.

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