CommandBuilder adds database & Schema to table name

[jeffc]

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?

[SecureGen]

Please e-mail small example that reproduce this issue on support address. I will analyze this it and answer question.

[jeffc]

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);
}
}
}

[SecureGen]

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?

[jeffc]

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();

[SecureGen]

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.