Query fails in PgSQLcommand but works in PgAdminIII

Query fails in PgSQLcommand but works in PgAdminIII

Postby sdeese » Thu 16 Jun 2011 01:57

I am getting an error using PgSQLcommand.

The code works for several queries, but then gives a strange error for others. The strangest thing is that I cut/paste the SQL command string it failed on into the PgAdminIII query and it runs no proglem.

1. Here is the C# bits:
PgSqlConnection pgSqlConnection1 = new PgSqlConnection();
pgSqlConnection1.Host = "localhost";
pgSqlConnection1.Port = 5432;
pgSqlConnection1.UserId = "mUserId";
pgSqlConnection1.Password = "mypassword";
pgSqlConnection1.Database = DBName;
PgSqlDataTable dt = new PgSqlDataTable(command, pgSqlConnection1);

try
{
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandTimeout = 0;
cmd.CommandText = command;
cmd.Connection = pgSqlConnection1;

pgSqlConnection1.Open();

dt.FetchAll = true;
dt.Active = true;
return dt;

This is the Error:
at Devart.Data.PostgreSql.PgSqlDataTable.CreateColumnsInternal(Boolean throwOnEmptySchemaTable)
at Devart.Common.DbDataTable.CheckColumnsCreated(Boolean throwOnEmptySchemaTable)
at Devart.Common.DbDataTable.Open()
at Devart.Common.DbDataTable.set_Active(Boolean value)
at DataVisualizer.postgresFunctions.RunQuerty(String DBName, String command) in C:\Users\Sdeese\Desktop\GridClear\DataVisualization\dv\DataVisualizer\postgresFunctions.cs:line 104


This is the query text (which runs fine on PgAdminIII):

SELECT "DA_RT_LMPs"."DateTime","DA_RT_LMPs"."DA_Minus_RT","EnergyPrices"."DAEnergyPrice",
"EnergyPrices"."RTEnergyPrice","EnergyPrices"."DA_Minus_RT" FROM "DA_RT_LMPs" INNER JOIN "EnergyPrices" ON "DA_RT_LMPs"."DateTime" = "EnergyPrices"."DateTime" WHERE "DA_RT_LMPs"."DateTime" >= '6/1/2011 12:00:00 AM' AND "DA_RT_LMPs"."DateTime" < '6/15/2011 12:00:00 AM'




This is the query:
sdeese
 
Posts: 2
Joined: Thu 16 Jun 2011 01:47

Postby sdeese » Thu 16 Jun 2011 03:08

Update....

If I select the columns that I "joined" on ... that is when I get the error.

For example.. in this query:
"DA_RT_LMPs"."DateTime","DA_RT_LMPs"."DA_Minus_RT","EnergyPrices"."DAEnergyPrice",
"EnergyPrices"."RTEnergyPrice","EnergyPrices"."DA_Minus_RT" FROM "DA_RT_LMPs" INNER JOIN "EnergyPrices" ON "DA_RT_LMPs"."DateTime" = "EnergyPrices"."DateTime" WHERE "DA_RT_LMPs"."DateTime" >= '6/1/2011 12:00:00 AM' AND "DA_RT_LMPs"."DateTime" < '6/15/2011 12:00:00 AM'


its the SELECT "DA_RT_LMPs"."DateTime" that blows it up because its the col it joins on.

Any suggestions?
sdeese
 
Posts: 2
Joined: Thu 16 Jun 2011 01:47

Postby Shalex » Mon 20 Jun 2011 12:34

Please give us the following information:
1) the exact text of the error you are getting;
2) send us the script to create your "DA_RT_LMPs" and "DA_RT_LMPs" tables with their test data to reproduce the issue in our environment.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL