SQL statement working in SQL Server but not in .aspx.cs page

[Garfield]

I have written the following query with a subquery to add a counter to be
added to a drop down list, which works in SQL Server,
but when I transfer the statement into my .aspx page it doesn't.

I keep on getting the error : Line 1: Incorrect syntax near ','.

The SQL statement is :


if (SqlConn.State == ConnectionState.Closed) SqlConn.Open();


sqlString ="SELECT
course_schedule.ScheduleID,coursename,startdate,en ddate,ctr FROM
course_schedule," +

"(SELECT count(*) as ctr,ScheduleID FROM TraineeCourses GROUP BY ScheduleID)
,courses " +

"WHERE course_schedule.ScheduleID = TraineeCourses.ScheduleID AND
course_schedule.CourseID = courses.CourseID";

SqlCommand cmdGetCourses = new SqlCommand(sqlString,SqlConn);


SqlDataReader readCourses = cmdGetCourses.ExecuteReader();

while (readCourses.Read()) {

ListItem NewItem = new ListItem();

NewItem.Text = readCourses.GetString(1).ToString() + " : " +

readCourses.GetDateTime(2).ToShortDateString() + " to " +

readCourses.GetDateTime(3).ToShortDateString() + " : Places (" +

readCourses.GetValue(4).ToString() + ")";

NewItem.Value = readCourses.GetValue(0).ToString();

this.drpCourses.Items.Add(NewItem);

}

readCourses.Close();

And I cannot see where the problem lies. Can somebody help?

Regards
Garfield

[Bob Barrows]

This is a classic asp newsgroup. While you may be lucky enough to find a
dotnet-savvy person here who can answer your question, you can eliminate the
luck factor by posting your question to an appropriate group. I suggest
microsoft.public.dotnet.framework.adonet.

[MS News \(MS ILM\)]

SELECTcourse_schedule. are you missing a blank here or Am I blind.
SELECT course_schedule.
"Garfield" <gboodie@hotmail.com> wrote in message
news:u2HZ8NDaDHA.2592@TK2MSFTNGP09.phx.gbl...

> I have written the following query with a subquery to add a counter to be
> added to a drop down list, which works in SQL Server, but when I transfer
> the statement into my .aspx page it doesn't.
>
> I keep on getting the error : Line 1: Incorrect syntax near ','.
>
> The section of code with the SQL statement is :
>
> if (SqlConn.State == ConnectionState.Closed) SqlConn.Open();
>
> sqlString
> ="SELECTcourse_schedule.ScheduleID,coursename,star tdate,enddate,ctr FROM
> course_schedule," +
> "(SELECT count(*) as ctr,ScheduleID FROM TraineeCourses GROUP BY

ScheduleID)

> ,courses " +
> "WHERE course_schedule.ScheduleID = TraineeCourses.ScheduleID AND
> course_schedule.CourseID = courses.CourseID";
>
> SqlCommand cmdGetCourses = new SqlCommand(sqlString,SqlConn);
> SqlDataReader readCourses = cmdGetCourses.ExecuteReader();
>
> while (readCourses.Read())
> {
> ListItem NewItem = new ListItem();
> NewItem.Text = readCourses.GetString(1).ToString() + " : " +
>
> readCourses.GetDateTime(2).ToShortDateString() + " to " +
>
> readCourses.GetDateTime(3).ToShortDateString() + " : Places (" +
> readCourses.GetValue(4).ToString() + ")";
>
> NewItem.Value = readCourses.GetValue(0).ToString();
> this.drpCourses.Items.Add(NewItem);
> }
> readCourses.Close();
>
>
> And I cannot see where the problem lies. Can somebody help?
>
> Regards
> Garfield
>
>
>
>

[William \(Bill\) Vaughn]

Shouldn't
sqlString
="SELECTcourse_schedule.ScheduleID,coursename,star tdate,enddate,ctr FROM
be
sqlString
="SELECT course_schedule.ScheduleID,coursename,startdate,en ddate,ctr FROM

Of course, if this was a SP, you would not have this problem... ;)
--
____________________________________
Bill Vaughn
MVP, hRD
[url]www.betav.com[/url]
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Garfield" <gboodie@hotmail.com> wrote in message
news:u2HZ8NDaDHA.2592@TK2MSFTNGP09.phx.gbl...

> I have written the following query with a subquery to add a counter to be
> added to a drop down list, which works in SQL Server, but when I transfer
> the statement into my .aspx page it doesn't.
>
> I keep on getting the error : Line 1: Incorrect syntax near ','.
>
> The section of code with the SQL statement is :
>
> if (SqlConn.State == ConnectionState.Closed) SqlConn.Open();
>
> sqlString
> ="SELECTcourse_schedule.ScheduleID,coursename,star tdate,enddate,ctr FROM
> course_schedule," +
> "(SELECT count(*) as ctr,ScheduleID FROM TraineeCourses GROUP BY

ScheduleID)

> ,courses " +
> "WHERE course_schedule.ScheduleID = TraineeCourses.ScheduleID AND
> course_schedule.CourseID = courses.CourseID";
>
> SqlCommand cmdGetCourses = new SqlCommand(sqlString,SqlConn);
> SqlDataReader readCourses = cmdGetCourses.ExecuteReader();
>
> while (readCourses.Read())
> {
> ListItem NewItem = new ListItem();
> NewItem.Text = readCourses.GetString(1).ToString() + " : " +
>
> readCourses.GetDateTime(2).ToShortDateString() + " to " +
>
> readCourses.GetDateTime(3).ToShortDateString() + " : Places (" +
> readCourses.GetValue(4).ToString() + ")";
>
> NewItem.Value = readCourses.GetValue(0).ToString();
> this.drpCourses.Items.Add(NewItem);
> }
> readCourses.Close();
>
>
> And I cannot see where the problem lies. Can somebody help?
>
> Regards
> Garfield
>
>
>
>

[Garfield Boodie]

Thanks for your response. The query did work within
SQL Server, but I don't know what happeened.

I added a table Alais as suggested to my subquery
and that solved my problem.

Thanks

Garfield



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!