Cindy:
Two things. First, ODBC doesn't support named parameters if I remember
correctly so even if you name them, they appear in the same respect that
they were entered. As long as you add them to the collection so that the
First parameter you need in the Sql Statement corresponds to the first
parameter in the collection, you'll be fine. This isn't your problem though,
I mention it only for informational purposes.
Where are you filling your DataTable though? I'm guessing it's filled
somewhere else. So first you need to make sure it's filled.
The problem appears to be that you don't call Update on the adapter
anywhere. Somewhere you'll need to call
adapter.Update(DataSetOrDataTableYou Filled).
Assuming that you are calling Update, the problem very well may be that you
don't have changes in the DataTable.
When an adapter's Update method is called, it loops through every row and
checks the RowState value of each row. If the row is Updated, then it uses
the Update command provided one is specified. If not, an exception is
thrown. If the rowstate is Deleted, then it will use the Deletecommand.
Same for Inserted.
So right before the Update call, you need to verify that you have changes.
If you don't, then nothing will get sent to the DB. You can use
Debug.Assert(DataSetOrDataTableName.HasChanges, "No Changes are present so
nothing will be updated");
Check the assertion, if it doesn't fail, then the problem is either with the
Update logic, the parameter/column mapping or something else. Most of the
stuff that would be 'something else' would throw an exception though .
Also, be very careful about the state of the datatable. On Page_Load, you
probably want to use
private DataTable CurrentData = null;
private OdbcDataAdapter MyAdapter = null;
if(!Page.IsPostBack){
OdbcConnection connection = new OdbcConnection("DSN=PFW52");
MyAdapter = CreateDataAdapter(connection);
Session["MyAdapter"] = MyAdapter;//In each case you could use ViewState
instead of Session (same goes for CurrentTable - I just used Session for
simplicity
try{
MyAdapter.Fill(CurrentData);
Session["CurrentData"] = CurrentData;
}
catch(OdbcException ex){
//Log and respond to exception
}
}
else{
if(Session["CurrentData"] != null){
CurrentData = Session["CurrentData"] as DataTable;
}
}
In each block, after you have CurrentData populated you can use it as you
normally would.
To update, you'd have a button or somethign else that calls something like
this...
private Boolean UpdateDataTable(){
try{
if(Session["MyAdapter"] == null){
MyAdapter = CreateDataAdapter(connection);
}
MyAdapter.Update(MyDataTable);
Session["MyDataTable"] = MyAdapter;
}
catch(OdbcException ex){
//Log and respond to exception according to your application's rules
}
}
Remember that each case where I use Session, it could just as easily be
viewstate. And the exact logic of what you store and where will depend on
your application. This is by no means the only way to handle State of your
objects, I just used this method for clarity. The main point is making sure
you have changes in your dataTable and calling Update. (also, i used a
datatable here for clarity, DataTables don't have a HasChanges method, so if
you want to check Rowstate that way, you'll need to create a DataSet and add
CustomTable to it. Again, I did it the way I did just for brevity.
If you don't understand anything, if I didn't understand the problem and
answered a different question from the one you asked, or if this doesn't fix
your problem, please let me know and I'll do my best to help.
Cheers,
Bill
cindy said:
this is the call
private void Page_Load(object sender, System.EventArgs e)
{
OdbcConnection connection = new OdbcConnection ("DSN=PFW52");
CreateDataAdapter(connection);
}
this is the code, no errors, but NO UPDATE I have to use ODBC I just
need to update a field based on a key, EMBARASSED to say days going
around syntax PLEASE SOMEONE
public static OdbcDataAdapter CreateDataAdapter(OdbcConnection
connection)
{
string selectCommand ="SELECT rtrim(CommodityKey),
CommodityShortDescr FROM INCOMMOD";
OdbcDataAdapter adapter = new OdbcDataAdapter(selectCommand,
connection);
adapter.UpdateCommand = new OdbcCommand( "UPDATE INCOMMOD SET
CommodityShortDescr = ? WHERE rtrim(CommodityKey) = ?");
adapter.UpdateCommand.Parameters.Add("@CommodityKey",
OdbcType.Char, 8, "CommodityKey");
adapter.UpdateCommand.Parameters.Add("@CommodityShortDescr",
OdbcType.Char, 1, "CommodityShortDescr");
adapter.UpdateCommand.Parameters["@CommodityShortDescr"].Value = "X";
return adapter;
}
no errors, no update occurs, the statement runs on server engine using
pervasive SQL and control center the update statement works.
