Hi
 
I am having problems with the NpgsqlDataAdapter. I created an insert command and when I call the update method, I get an error:  ERROR: 42601: syntax error at or near":"
 
If I remove some of the columns from the insert statement, it works. It seems like there is a limit to the length of the command text.
 
I am using C# 2003 (.NET Framework 1.1) on Windowx XP.
 
 
I create the data adapter using the following code:

string poSelect = "SELECT po_number, supplier, originated_by, deliver_to, " +

"po_status_code, shipping_company, ship_point, service_order, " +

"fob, freight, internal_comments, external_comments, currency, " +

"hide_cost_on_printout " +

"FROM purchase_order";

m_daPO = new NpgsqlDataAdapter(poSelect, m_posDBConn);

string poInsert = "INSERT INTO purchase_order (po_number, supplier, currency, " +

"originated_by, deliver_to, po_status_code, shipping_company, fob, freight, " +

"internal_comments, external_comments, service_order, hide_cost_on_printout) " +

"VALUES (:po_number, :supplier, :currency, " +

":originated_by, :deliver_to, :po_status_code, :shipping_company, :fob, :freight, " +

":internal_comments, :external_comments, service_order, :hide_cost_on_printout)";

NpgsqlCommand poInsertCmd = new NpgsqlCommand(poInsert, m_posDBConn);

poInsertCmd.Parameters.Add(new NpgsqlParameter("po_number", DbType.Int16));

poInsertCmd.Parameters["po_number"].SourceColumn = "po_number";

poInsertCmd.Parameters.Add(new NpgsqlParameter("supplier", DbType.Int16));

poInsertCmd.Parameters["supplier"].SourceColumn = "supplier";

poInsertCmd.Parameters.Add(new NpgsqlParameter("currency", DbType.String));

poInsertCmd.Parameters["currency"].SourceColumn = "currency";

poInsertCmd.Parameters.Add(new NpgsqlParameter("originated_by", DbType.Int16));

poInsertCmd.Parameters["originated_by"].SourceColumn = "originated_by";

poInsertCmd.Parameters.Add(new NpgsqlParameter("deliver_to", DbType.Int16));

poInsertCmd.Parameters["deliver_to"].SourceColumn = "deliver_to";

poInsertCmd.Parameters.Add(new NpgsqlParameter("po_status_code", DbType.Int16));

poInsertCmd.Parameters["po_status_code"].SourceColumn = "po_status_code";

poInsertCmd.Parameters.Add(new NpgsqlParameter("shipping_company", DbType.Int16));

poInsertCmd.Parameters["shipping_company"].SourceColumn = "shipping_company";

poInsertCmd.Parameters.Add(new NpgsqlParameter("freight", DbType.String));

poInsertCmd.Parameters["freight"].SourceColumn = "freight";

poInsertCmd.Parameters.Add(new NpgsqlParameter("fob", DbType.String));

poInsertCmd.Parameters["fob"].SourceColumn = "fob";

poInsertCmd.Parameters.Add(new NpgsqlParameter("internal_comments", DbType.String));

poInsertCmd.Parameters["internal_comments"].SourceColumn = "internal_comments";

poInsertCmd.Parameters.Add(new NpgsqlParameter("external_comments", DbType.String));

poInsertCmd.Parameters["external_comments"].SourceColumn = "external_comments";

m_daPO.InsertCommand = poInsertCmd;

 

After that I load the data using the Fill method of the data adapter. Then a new row is inserted into the "po" DataTable. I check the DataSet and the row has been added.

Then I save the data. Here is first part of the code for the update:

try

{

m_posDBConn.Open();

poUpdate = m_posDBConn.BeginTransaction();

m_daPO.InsertCommand.Transaction = poUpdate;

try

{

m_daPO.Update(m_dsPO, "po");      // this causes error

poUpdate.Commit();

...

 

I would appreciate help with this as soon as possible because it is impeding development.

Thanks.

Evan