SQL Confusion in DA.Fill()

[Christopher Weaver]

The SQL that I'm sending to my server doesn't work unless I run it through
an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field. For
example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the line
before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?

 

[Earl]

Wouldn't it be:

"SELECT * FROM \" + tblTasks + "\ ORDER BY " + OrderBy;

 

[Rogas69]

well might be this is the problem with doublequotes.. just guessing but i
had similar problem several yrs ago afair. try to change them to [] brackets
or throw them at all
you can also look what is passed to db in profiler

hth

peter

 

[Christopher Weaver]

I see your point, but 'tblTasks' is the literal name of the table on the
back end.

 

[Christopher Weaver]

I don't know if my server (InterBase 7.5) will tolerate anything less than
double quotes with mixed case identifiers in dialect 3, but I will try it.
Thanks for mentioning the profiler.

well might be this is the problem with doublequotes.. just guessing but i
had similar problem several yrs ago afair. try to change them to []
brackets or throw them at all
you can also look what is passed to db in profiler

hth

peter

The SQL that I'm sending to my server doesn't work unless I run it
through an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field. For
example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the
line before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?

 

[Christopher Weaver]

Rogas69,

When you suggest the use of the profiler, do you mean the SQL Server
profiler that comes with the Enterprise manager? This only works with MS
SQL Server and I'm constrained by my client to use InterBase.

well might be this is the problem with doublequotes.. just guessing but i
had similar problem several yrs ago afair. try to change them to []
brackets or throw them at all
you can also look what is passed to db in profiler

hth

peter

The SQL that I'm sending to my server doesn't work unless I run it
through an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field. For
example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the
line before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?

 

[Rogas69]

Well you did not say what database you use in the first post. I assumed that
it was SQL Server somehow.
Does InterBase not have anything to trace queries?

peter

Rogas69,

When you suggest the use of the profiler, do you mean the SQL Server
profiler that comes with the Enterprise manager? This only works with MS
SQL Server and I'm constrained by my client to use InterBase.

well might be this is the problem with doublequotes.. just guessing but i
had similar problem several yrs ago afair. try to change them to []
brackets or throw them at all
you can also look what is passed to db in profiler

hth

peter

The SQL that I'm sending to my server doesn't work unless I run it
through an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field.
For example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the
line before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?

 

[Christopher Weaver]

There are problems there. The IBConsole tool is fairly good and has a trace
feature, but in its current form it does have a problem connecting to remote
servers, a feature that I need. The next version of the IBConsole is in
beta but does connect well to remote servers; it does not support
monitorring. IBExpert, from HK Software has a companion trace and
monitorring tool, but I haven't gotten it to work yet.

Well you did not say what database you use in the first post. I assumed
that it was SQL Server somehow.
Does InterBase not have anything to trace queries?

peter

Rogas69,

When you suggest the use of the profiler, do you mean the SQL Server
profiler that comes with the Enterprise manager? This only works with MS
SQL Server and I'm constrained by my client to use InterBase.

well might be this is the problem with doublequotes.. just guessing but
i had similar problem several yrs ago afair. try to change them to []
brackets or throw them at all
you can also look what is passed to db in profiler

hth

peter

The SQL that I'm sending to my server doesn't work unless I run it
through an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field.
For example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the
line before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?

 

[Christopher Weaver]

Just to put a closing note on this,

The problem lay in that a space found its way into the fieldname between the
first character and the leading double quote. The font used by the
messagebox is small on my screen and proportional as well, so I didn't
notice it. FieldName = FieldName.Trim(); did the trick.

Thanks for your help.

Well you did not say what database you use in the first post. I assumed
that it was SQL Server somehow.
Does InterBase not have anything to trace queries?

peter

Rogas69,

When you suggest the use of the profiler, do you mean the SQL Server
profiler that comes with the Enterprise manager? This only works with MS
SQL Server and I'm constrained by my client to use InterBase.

well might be this is the problem with doublequotes.. just guessing but
i had similar problem several yrs ago afair. try to change them to []
brackets or throw them at all
you can also look what is passed to db in profiler

hth

peter

The SQL that I'm sending to my server doesn't work unless I run it
through an interactive tool.

I've done this in VS 2003
cmd.CommandText = "SELECT * FROM \"tblTasks\" ORDER BY " + OrderBy;
odbcDA_TaskActivities.SelectCommand = cmd;
MessageBox.Show(cmd.CommandText);
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");

The Fill method fails whenever OrderBy contains more than one field.
For example, this works in the ISQL window of my DBMS controller:
SELECT * FROM "tblTasks" ORDER BY "AssignTo", "DueDate", "Category",
"SubCategory"

It is also the exact text shown in the MessageBox.Show window from the
line before the Fill call in my code when OrderBy is set to
"AssignTo", "DueDate", "Category", "SubCategory" .

But I still get a non-specific system error. When I set OrderBy to
"uidTask" or "Description" the Fill call fills the DataSet.

Any ideas on this?