CommandBuilder adds database & Schema to table name

CommandBuilder adds database & Schema to table name

Postby jeffc » Tue 10 Jan 2006 20:59

I've been getting an error that "Cross-Database References are not implemented". After hunting down the problem, instead of the table name ("Addresses"), the CommandBuilder Update command includes the database name and Schema ("ambitpos.public.addresses").

When I set the dataAdapter.Select command, I'm just using the table ("Addresses"). With the schema included, the system thinks I am referring to a different database.

Any suggestions?
jeffc
 
Posts: 8
Joined: Thu 05 Jan 2006 17:21

Postby SecureGen » Wed 11 Jan 2006 08:00

Please e-mail small example that reproduce this issue on support address. I will analyze this it and answer question.
SecureGen
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27

Code Sample

Postby jeffc » Wed 11 Jan 2006 14:06

When I run the following code, I get the cross-database error. I'm using the production version of .net and the latest PostgreSql library.

Thanks for your help.

private void btnSaveTest_Click(object sender, EventArgs e)
{
PgSqlConnection cnData = new PgSqlConnection("user=Ambit;password=aardvark;host=Tesla;database=AmbitPOS;unicode=True");
PgSqlDataAdapter daData = new PgSqlDataAdapter();
DataSet dsData = new DataSet("SQLData");


PgSqlCommandBuilder oCB = new PgSqlCommandBuilder(daData);

daData.SelectCommand = new PgSqlCommand("SELECT * FROM Addresses ORDER BY City", cnData);

if (daData.Fill(dsData, "SQLData") > 0)
{ // We have data
try
{
dsData.Tables["SQLData"].Rows[0]["State"] = "MO";
daData.Update(dsData, "SQLData"); // This line causes the error
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
}
jeffc
 
Posts: 8
Joined: Thu 05 Jan 2006 17:21

Postby SecureGen » Fri 13 Jan 2006 08:07

I can not reproduce your problem using this example. I changed only connection string and select statement(in order to select from existing table) and this works OK without any error.
Please retest it on your environment in order to make sure that it generates problem and send me also DDL statement(s) for your table. Did you create it in default(public) schema?
SecureGen
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27

Postby jeffc » Fri 13 Jan 2006 14:29

The table is created in the public schema. The error message indicates the errant table as "ambitpos.public.addresses'. All I changed was from the demo version (2.2 I believe) to the current production version when I bought the software.

Here is the code that creates the table.


PgSqlConnection oDB = new PgSqlConnection("user=Ambit;password=aardvark;host=localhost;database=AmbitPOS;unicode=True");
PgSqlCommand oCmd = new PgSqlCommand();
PgSqlScript oScript = new PgSqlScript();

oDB.Open();
oCmd.Connection = oDB;

oCmd.CommandText =
"CREATE TABLE Addresses (" +
"AddressesKey GUID PRIMARY KEY, " +
"LastUpdate TIMESTAMP NOT NULL, " +
"Address1 VARCHAR (50) NOT NULL, " +
"Address2 VARCHAR (50) DEFAULT '', " +
"City VARCHAR (50) NOT NULL, " +
"State VARCHAR (50) NOT NULL, " +
"PostalCode VARCHAR (15) NOT NULL, " +
"Country VARCHAR (25) DEFAULT 'USA' ); ";
oCmd.ExecuteNonQuery();

oCmd.CommandText = "CREATE INDEX AddCity ON Addresses (City); ";
oCmd.ExecuteNonQuery() ;

oCmd.CommandText = "CREATE INDEX AddStateCity ON Addresses (State, City); ";
oCmd.ExecuteNonQuery();

oCmd.CommandText = "CREATE INDEX AddPCode ON Addresses (PostalCode, State, City); ";
oCmd.ExecuteNonQuery();
jeffc
 
Posts: 8
Joined: Thu 05 Jan 2006 17:21

Postby SecureGen » Fri 13 Jan 2006 16:46

I tried to reproduce this issue in for .NET1, .NET2 using the PostgreSQLDirect .NET Data Provider 2.40.13 and it works did not succeed. It works OK on my environment. By the way, which Postgre SQL server and .NET framework do you use? I tried to execute your script on Postgre SQL 8.1 and it raised error that GUID is unknown type for "AddressesKey GUID PRIMARY KEY, " string. I changed 'GUID' to 'int' in order to create the table.
Since our exploration go deep into details could you please send answers on our support e-mail.
SecureGen
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27


Return to dotConnect for PostgreSQL