ODBCCommandBuilder creating incomplete commands

Posted by bgourdie on 31-Dec-2014 11:14

I'm trying to make a bare-bones CRUD app in C# with a DataGridView, DataTable, and DataAdapter. I can connect to the Progress database just fine but the ODBCCommandBuilder is creating SQL with only the number of indexes and the last index repeated across all of them.

For example, updating a table with 4 indexes (and 13 or so columns) returns something like "UPDATE pub.Grade_Map SET mill = ?, mill = ?, mill = ?, mill = ? WHERE ((mill = ?) AND (mill = ?) AND (mill = ?) AND (mill = ?))". Likewise, for insert: "INSERT INTO pub.Grade_Map (mill, mill, mill, mill) VALUES (?, ?, ?, ?)".

These commands "work" if there's a change in the index value, in which case it updates/inserts with the index value(s). Everything else is blank or remains untouched.

Yeah, sure, I can build the insert, delete, and update statements myself, but then what am I getting out of using .NET?

My code is about the minimum I'd expect to get this done, as I've created a SQL Server equivalent that works and simply changed the SQL classes to ODBC and altered the connection string.

Using Progress 10.2B.

---------------------------------------------------- Code Start

using System;
using System.Data;
using System.Data.Odbc;
using System.Windows.Forms;

namespace BareBonesODBCTableMaintenance
{
public partial class ODBCForm : Form
{
private const string connectionString = "Dsn=test;uid=BGOURDIE;host=main;port=2557;db=test102b";
private const string selectQuery = "select * from pub.Grade_Map";
private OdbcConnection con = new OdbcConnection(connectionString);
private DataTable dt = new DataTable();
private static OdbcDataAdapter adapter = new OdbcDataAdapter(selectQuery, connectionString);
private static OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);

public ODBCForm()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}

private void commitToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show(builder.GetInsertCommand().CommandText);
adapter.Update(dt);
}
}
}

-------------------------------------------------------- Code End

All Replies

Posted by Jean Richert on 13-Jan-2015 03:43

Hi bgourdie,

I just saw your thread didn't get any sort of assistance and/or answer yet. I'm not sure I fully understand your issue so could you please confirm the following:

- You're trying to make an ODBC connection to an OpenEdge database from within a self-built C# application, using standard .NET framework classes.

- Apparently the SQL statements used to create/update records are failing

Would that be a correct description of the issue you're dealing with?

Posted by James Palmer on 13-Jan-2015 04:07

The user has asked the same question over on ProgressTalk: www.progresstalk.com/.../odbccommandbuilder-generating-incomplete-sql.135422

Not that we've been much help there either, but it's a good idea to have the two linked.

Posted by Stefan Marquardt on 13-Jan-2015 08:21

The issue is that .NET CommandBuilder doesn't work with the Progress ODBC driver.

I reported many bugs for the odbc driver and .NET Framework too and spent many hours/days.

Most of them were fixed in a timeline of about 2 years from Progress and Microsoft (they were faster).

Not all of the known problems were fixed. One of them was the CommandBuilder.

From my side, I gave up and used manual SQL statements with parameters.

I think Libor should know about all of them and the current state.

This thread is closed