Database

4-May-05 12:00

vishalmishra wrote:
the networking components are not installed..

wat to do now

If you haven't installed the networking components then you should do that. If you have installed them then maybe the installation is damaged and you should try and install them again.

DISCLAIMER: I know nothing about Oracle, it just seems to me that the error message is fairly self explanitory.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Prob using Oracle 9i

vishalmishra4-May-05 12:14

vishalmishra

4-May-05 12:14

Actually wat networking components i need to install ..??? i could not get it...

the real errror was

Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed

and i went for Full Oracle 9i installation..

Newbie GROUP BY question

moredip4-May-05 11:01

moredip

4-May-05 11:01

Hi,

I'm currently working on some SQL stuff as part of a project I've been assigned to, and I have a newbie question regarding the GROUP BY clause in a SELECT statement.

I have a table, Activity_Session_Summary, that contains information about the results of the online quizzes that our product administers. Each tuple represents a summary of a user's results for a specific attempt at a specific quiz. Its columns include 'ActivityID', 'PercentCorrect' and 'WhenCompleted'.

What I need is a SELECT statement that returns the PercentCorrect value for the most receently completed quiz attempt, for each activity. So far I have

SELECT ActivityID, MAX(WhenCompleted) FROM Activity_Session_Summary
WHERE StudentID = '%s' AND ActivityID IN ( %s )
GROUP BY ActivityID

where the %s's are format placeholders.

This statement almost does what I need, but obviously doesn't give me the PercentCorrect. However I can't just add PercentCorrect to the SELECT's column list, because SQL Server politely tells me

"'PercentCorrect' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Can anyone point out what I need to do to get this to work as required?

Colin Angus Mackay4-May-05 11:36

4-May-05 11:36

Okay - Before I answer your question I am going to do my usual pointer to this article on SQL Injection Attacks and How to Prevent Them[^] whenever I see code that could potentially be attacked.

On to your question. What you need to do is take the statement you have already and use that as a subquery and have the table join onto itself via the subquery. It sounds more difficult than it is.

SELECT main.ActivityID, main.PercentComplete, main.WhenComplete
FROM Activity_Session_Summary AS main
INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenComplete
            FROM Activity_Session_Summary
            WHERE StudentID = %s AND ActivityID IN ( %s )
            GROUP BY ActivityID) AS sub
ON main.ActivityID = sub.ActivityID AND main.WhenComplete = sub.WhenComplete
WHERE StudentID = '%s' AND ActivityID IN ( %s )

I've left your format placeholders even though they represent a potential security risk.

Your original query has now been made the subquery and as you can see, nothing has changed there except that I named the aggregate column so I could refer to it later.

The main query selects the data that you want and joins onto the subquery. You will notice from the join that it is joined on the result of the aggregate as well as the ActivityID (I'm guessing that is a foreign key onto another table). Finally, your where clause is repeated on the main query, although it may not be needed - I don't know enough about your data model to make that determination. If it isn't needed it won't do any harm.

Does this help?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

moredip4-May-05 11:50

moredip

4-May-05 11:50

Hi Colin,

Wow! Thanks for such a rapid and comprehensive reply, I really appreciate it.

I also appreciate your warning re: injection attacks. However, this project is for internal use with hardcoded input values, and will not be accessible in any way outside the machine it's run on. Famous last words? Well, if our security is so screwed that someone can access the script and modify the SQL statement then I can think of many easier ways for them to accomplish their evil ends. But I appreciate your concern!

I think I understand the SQL you gave. I was hoping that there was a way to do this without subqueries (they make my head hurt a little at times), but suspected that they would be necessary.

I think that the StudentID /is/ necessary in the main query. It's theoretically possible that two rows in the result set don't differ in ActivityID or WhenCompleted, but only by StudentID. Not likely, as it would involve two students completing the same activity within a second of each other (or whatever the resolution of DateTime is in SQL Server), but possible none the less.

Again, thanks a lot Colin, you've really helped me out here, and hopefully helped me learn a little so I don't ask the same question in a different form in 2 months time!

Cheers,

Pete

Colin Angus Mackay4-May-05 11:57

4-May-05 11:57

moredip wrote:
I think that the StudentID /is/ necessary in the main query. It's theoretically possible that two rows in the result set don't differ in ActivityID or WhenCompleted, but only by StudentID. Not likely, as it would involve two students completing the same activity within a second of each other (or whatever the resolution of DateTime is in SQL Server), but possible none the less.

Best to leave it in then. If it is taken out then it can be a nasty time-consuming bug to track down if that condition does happen. I know, I spent two days tracking something similar, which was why I was cautious about removing it without knowing more about your data.

moredip wrote:
thanks a lot Colin, you've really helped me out

Your most welcome.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

moredip4-May-05 12:11

moredip

4-May-05 12:11

Hi again Colin,

I modified the SQL you provided slightly from this:

Colin Angus Mackay wrote:

SELECT main.ActivityID, main.PercentComplete, main.WhenComplete
FROM Activity_Session_Summary AS main
INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenComplete
FROM Activity_Session_Summary
WHERE StudentID = %s AND ActivityID IN ( %s )
GROUP BY ActivityID) AS sub
ON main.ActivityID = sub.ActivityID AND main.WhenComplete = sub.WhenComplete
WHERE StudentID = '%s' AND ActivityID IN ( %s )

to this:

SELECT main.ActivityID, main.PercentCorrect
        FROM Activity_Session_Summary AS main
        INNER JOIN (SELECT ActivityID, MAX(WhenCompleted) AS WhenCompleted
                    FROM Activity_Session_Summary
                    WHERE StudentID = '%s' AND ActivityID IN ( %s )
                    GROUP BY ActivityID) AS sub
        ON main.ActivityID = sub.ActivityID AND main.WhenCompleted = sub.WhenCompleted
        WHERE main.StudentID = '%s'

I just wanted to confirm with you that removing the final AND ActivityID IN ( %s ) makes sense. It seems to me that it's redundant, since the main.ActivityID = sub.ActivityID join will ensure that only ActivityID's in the supplied ( %s ) list will be considered. Is that correct?

Cheers,

Pete

Colin Angus Mackay4-May-05 12:13

Average formula with SQL table

4-May-05 12:13

moredip wrote:
Is that correct?

Yes, you are quite right. Since it is on the join already, it is redundant in the WHERE clause.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Joey Picerno4-May-05 10:20

4-May-05 10:20

I have a table with two columns
ServiceDate(datetime) & Attendance(int)

I wish to add another column that calcs the attendance average. How do i write
the formulate for this column. Not for each record but for all the records in the table
everytime a new one is added. That sounds confusing. If anyone knows what to do i would
appreciate the help.

Colin Angus Mackay4-May-05 11:42

4-May-05 11:42

So, every time you insert, update or delete a row in the table you want a column that represents the average updated on every row?

On the assumtion that the answer to the previous question is "yes": Why not just use AVG(Attendance) in a select statement when you want that information? Is retrieving the information so time critical that you want to hurt the insert/update/delete performance by having those operations hit every row in the table?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Joey Picerno4-May-05 15:33

4-May-05 15:33

Here is my code, i am getting
ExecuteReader: Connection property has not been initialized.
message but this is how all my other connections are setup.

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim str As String
Dim reader As SqlDataReader

conn = New SqlConnection(ConfigurationSettings.AppSettings("connstring"))
conn.Open()

cmd = New SqlCommand("SELECT AVG(Attendance) FROM tblAttendance")
str = cmd.ExecuteReader().ToString()

Joey Picerno4-May-05 15:45

4-May-05 15:45

also notice i am reading my connection string from my Appconfig file but i know its not the config file becuase i tested it with the connection string.

Colin Angus Mackay4-May-05 19:33

4-May-05 19:33

You need to also tell the command about the connection. At the moment the command doesn't know what connection to use. Use:

cmd = New SqlCommand("SELECT AVG(Attendance) FROM tblAttendance", conn)

Also, the next problem you are going to have is this line:

str = cmd.ExecuteReader().ToString()

That line will not return the result that you want. You should use the following instead:

str = cmd.ExecuteScalar().ToString();

Does this help?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Joey Picerno5-May-05 3:51

tactDatagrid with VB.net and SQL

5-May-05 3:51

That helps alot actually. It is working now. I really appreciate the help.
I am about as begginer as they come.

gingesh4-May-05 8:35

gingesh

4-May-05 8:35

Hi

I need some help, I have created a datagrid using sql connection and data adapter and when I press a button I fill the data grid with contacts, however I want to be able type a name in a text box and get contacts with that name only. I cant get it to work i am pulling my hair out. It is probably something simple but I cant see how to do it for looking

Re: tactDatagrid with VB.net and SQL

keith maddox4-May-05 10:31

keith maddox

4-May-05 10:31

If I understand your problem correctly, I think what you want to do is make a new adapter with a query something like the following (use you immagination since I don't know your schema) select x,y,z from mytable where x = someNameHere

In otherwords, use a different adapter when you want to pull up just specific info.

Probably too vague on my part but I need more specifics from you.

Bulk Copy Tables from .net to sql

Mtognetti4-May-05 4:51

Mtognetti

4-May-05 4:51

I orignally posted this in the VB/VB .Net area, but am now thinking this may have been the more appropriate location...

I have several dataset tables that I want to export directly into my sql server as tables (or as inserts to tables).

Assumptions:
I have the dataset tables populated (I do)
I have a valid (open) connection to SQL Server (I do)

In order to transfer/export/update the sql db tables do I need to bind this dataset to a vb.data grid control, or data adapter, etc?

Or is there an easy (relative) command that I can utilize within the System.Data.SQL Client (or other namespace) that I can use to get these tables to populate in my SQL Environment?

Thanks,

mtognetti

Re: Bulk Copy Tables from .net to sql

MBGeorge4-May-05 20:45

MBGeorge

4-May-05 20:45

To transfer/export/update ur data u could use SqlDataAdapter , witch is viable for small opeartions. If u plan to transport/export large number of rows u have 2 options :
1. u can create a dts on ur sql server who use as entry data a txt file separated with tabs. This file u can create from ur app and then call ur dts
(use DTS - namespace, u must add msdts.dll ( i think ) as ref )
2. use bulk insert with a user with rights to bulk insert on sql server , using as data source a txt file. ( use SqlCommand and bulk insert from T-Sql)

If sql server is on other machine, u will need a way to transfer ur files to that machine.

Hope that helps.

OracleHelper for testing

Nizar Abdeljaoued4-May-05 4:17

Nizar Abdeljaoued

4-May-05 4:17

I migrate the microsoft class : SqlHelper to OracleHelper, i used in this class ODP .NET 10..
I need some person to do the test. if there is someone who is interested by this this request ,please send me an email
at nizar@myway.com and i will response him.
thnx

ENSI TUNISIA

Dumb Q: C++, a DB and regional settings

CherezZaboro4-May-05 3:50

CherezZaboro

4-May-05 3:50

Ok. In C++ I have code that passed coded in SQL statements that update data/time fields like so:
"UPDATE mytable SET f_date1='05/04/2005 12:01:02'". So the obvious problem is that if windows set to anything (or close to anything) other than USA region the date gets reversed in the DB itself. So when I query this field again it's '04/05/2005'. So is there an easy way to go around this? Or do I have to reformat SQL statement for each region?
Urgent!!! Please help!!!
Thanks a lot in advance!

Re: Dumb Q: C++, a DB and regional settings

Colin Angus Mackay4-May-05 4:14