Update architectural confusion: lost in the design maze

[r]

Trying to design a simple admin windows form app to update and insert from 2
MySQL tables.

I'm using the ByteFX dll which looks almost identical in function to the
native ODBC classes.

I'm able to connect and fill two data adapters with info from the MySQL
tables and display the results on 2 datagrids on my form but I'm lost in the
woods trying to figure out the best way to get the updates and inserts to
work.

I'd like to somehow get the changed info from the display grid into the
'haschanged' collection?, temp table? or something that I'm led to believe
exists somewhere...and just have the adapter's update command walk this row
collection? (or whatever it is) and send the data back to my database using
the update sql query I've defined.

I think I've got a vague idea of what's possible but I'm just lost figuring
out the how. This stuff seems very flexible and as a result, very complex.

Thanks for any tips. Here's a snip from my code:


mDataSetSlots= new DataSet();
DataAdapter_Slots=new MySqlDataAdapter();

SQL_Cmd_UpdateSlot = myConn.CreateCommand();
SQL_Cmd_UpdateSlot.CommandType=CommandType.Text;

SQL_Cmd_UpdateSlot.CommandText=
"UPDATE Slots SET" +
"JobID = @JobID," +
"stDate = @stDate," +
"TimeStart = @TimeStart," +
"TimeEnd = @TimeEnd," +
"Pool = @Pool," +
"Workers_Needed = @Workers_Needed," +
"Workers_Enlisted=@Workers_Enlisted" +
"where SlotID =@SlotID;";

DataAdapter_Slots.UpdateCommand=SQL_Cmd_UpdateSlot;
DataAdapter_Slots.UpdateCommand.Parameters.Add("@JobID", MySqlDbType.Short);
DataAdapter_Slots.UpdateCommand.Parameters.Add("@stDate",
MySqlDbType.Date,8,"stDate");
DataAdapter_Slots.UpdateCommand.Parameters.Add("@TimeStart",
MySqlDbType.VarChar,8,"TimeStart");
DataAdapter_Slots.UpdateCommand.Parameters.Add("@TimeEnd",
MySqlDbType.VarChar,8,"TimeEnd");
DataAdapter_Slots.UpdateCommand.Parameters.Add("@Pool",
MySqlDbType.VarChar,1,"Pool");
DataAdapter_Slots.UpdateCommand.Parameters.Add("@Workers_Needed",
MySqlDbType.Short,2,"Workers_Needed");
DataAdapter_Slots.UpdateCommand.Parameters.Add("@Workers_Enlisted",
MySqlDbType.Short,2,"Workers_Enlisted");

 

[Andy Gaskell]

If you are indeed binding the dataset to datagrids then the update is pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges property
of the DataSet will be true. You might also be interested in the GetChanges
method.

HasChanges -
http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclasshaschangestopic.asp
GetChanges -
http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

 

[r]

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R

 

[Andy Gaskell]

I think you'll need to set the TableMappings property of the DataAdapter. Do
you have more than one DataTable in your DataSet?

http://msdn.microsoft.com/library/d...bcon/html/vbcontablemappingindataadapters.asp
http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R

If you are indeed binding the dataset to datagrids then the update is pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges property
of the DataSet will be true. You might also be interested in the GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclasshaschangestopic.asp

GetChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

from in
the this
row

 

[r]

Only one table per dataset. Here's a snip:

mDataSetJobs= new DataSet();

DataAdapter_Jobs.Fill(mDataSetJobs,"Jobs");
dataJobs.DataSource = mDataSetJobs.DefaultViewManager;

I added this:
DataAdapter_Jobs.TableMappings.Add("Jobs","Jobs");

Now I do the update and get an sql error. I think I'll poke around for a
bit with the debugger and see whether any data from the grid is getting
transferred to the variable parameters I declared.
I appreciate your help. Please check back; I have a feeling I'm a long way
from being out of the woods...
r

I think you'll need to set the TableMappings property of the DataAdapter. Do
you have more than one DataTable in your DataSet?

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R

If you are indeed binding the dataset to datagrids then the update is pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges property
of the DataSet will be true. You might also be interested in the GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

insert
from

lost

in inserts
to

 

[Andy Gaskell]

Something else to try is to pass in the table name to the update call.

DataAdapter_Jobs.Update(mDataSetJobs, "Jobs");

Only one table per dataset. Here's a snip:

mDataSetJobs= new DataSet();

DataAdapter_Jobs.Fill(mDataSetJobs,"Jobs");
dataJobs.DataSource = mDataSetJobs.DefaultViewManager;

I added this:
DataAdapter_Jobs.TableMappings.Add("Jobs","Jobs");

Now I do the update and get an sql error. I think I'll poke around for a
bit with the debugger and see whether any data from the grid is getting
transferred to the variable parameters I declared.
I appreciate your help. Please check back; I have a feeling I'm a long way
from being out of the woods...
r

I think you'll need to set the TableMappings property of the

DataAdapter.

Do
you have more than one DataTable in your DataSet?

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the update is
pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges property
of the DataSet will be true. You might also be interested in the
GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

to
the lost into
the

 

[r]

Good suggestion but I'm already doing it...
am I supposed to be doing a getchanges() before executing the update command
or should it automatically use the current info in the data grid?
r

Something else to try is to pass in the table name to the update call.

DataAdapter_Jobs.Update(mDataSetJobs, "Jobs");

Only one table per dataset. Here's a snip:

mDataSetJobs= new DataSet();

DataAdapter_Jobs.Fill(mDataSetJobs,"Jobs");
dataJobs.DataSource = mDataSetJobs.DefaultViewManager;

I added this:
DataAdapter_Jobs.TableMappings.Add("Jobs","Jobs");

Now I do the update and get an sql error. I think I'll poke around for a
bit with the debugger and see whether any data from the grid is getting
transferred to the variable parameters I declared.
I appreciate your help. Please check back; I have a feeling I'm a long way
from being out of the woods...
r







DataAdapter.

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table'] or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the update is
pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges
property
of the DataSet will be true. You might also be interested in the
GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp function

to

 

[Andy Gaskell]

You do not need to call GetChanges. People usually call GetChanges if they
want to save bandwidth (which is why I recommended that you look into it -
because I didn't know if you were interested in saving bandwidth).

Can you post the full text of the exception you're getting
(exceptionObject.ToString()) ?

Good suggestion but I'm already doing it...
am I supposed to be doing a getchanges() before executing the update command
or should it automatically use the current info in the data grid?
r

Something else to try is to pass in the table name to the update call.

DataAdapter_Jobs.Update(mDataSetJobs, "Jobs");

for

a long
way

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find

TableMapping['Table']
or

DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@' param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the

update
is

pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges
property
of the DataSet will be true. You might also be interested in the
GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

function

led

to

 

[r]

having a little trouble getting that to work. What's the namespace for
exceptionObject.ToString()?
Do I use it like
ExceptionObject.ToString( DataAdapter_Jobs.Update(mDataSetJobs,"Jobs"));?
and how would I send the output to the immediate window?

You do not need to call GetChanges. People usually call GetChanges if they
want to save bandwidth (which is why I recommended that you look into it -
because I didn't know if you were interested in saving bandwidth).

Can you post the full text of the exception you're getting
(exceptionObject.ToString()) ?

Good suggestion but I'm already doing it...
am I supposed to be doing a getchanges() before executing the update command
or should it automatically use the current info in the data grid?
r

for

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table']
or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@'
param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the update
is
pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges
property
of the DataSet will be true. You might also be interested in the
GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

from
the led command
walk just
lost result,
very

 

[Andy Gaskell]

Here's an example if it's a WinForms application:

try
{
DataAdapter_Jobs.Update(mDataSetJobs,"Jobs")
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}

I'm not sure what kind of application you're developing, but it's important
to get the full text of the exception so we can try to figure out where it's
failing. So if it's a Console app do Console.WriteLine(ex.ToString()) and if
it's a ASP.NET app you could use Response.Write(ex.ToString()).

having a little trouble getting that to work. What's the namespace for
exceptionObject.ToString()?
Do I use it like
ExceptionObject.ToString( DataAdapter_Jobs.Update(mDataSetJobs,"Jobs"));?
and how would I send the output to the immediate window?

You do not need to call GetChanges. People usually call GetChanges if they
want to save bandwidth (which is why I recommended that you look into it -
because I didn't know if you were interested in saving bandwidth).

Can you post the full text of the exception you're getting
(exceptionObject.ToString()) ?

around
for

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find TableMapping['Table']
or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the sql '@'
param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the update
is
pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the HasChanges
property
of the DataSet will be true. You might also be interested in the
GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

but
I'm I'm
led

 

[r]

I'll try that, Thanks. At the moment I've been waylaid by another proj
deadline but I'll get back tomorrow.
R

Here's an example if it's a WinForms application:

try
{
DataAdapter_Jobs.Update(mDataSetJobs,"Jobs")
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}

I'm not sure what kind of application you're developing, but it's important
to get the full text of the exception so we can try to figure out where it's
failing. So if it's a Console app do Console.WriteLine(ex.ToString()) and if
it's a ASP.NET app you could use Response.Write(ex.ToString()).

having a little trouble getting that to work. What's the namespace for
exceptionObject.ToString()?
Do I use it like
ExceptionObject.ToString( DataAdapter_Jobs.Update(mDataSetJobs,"Jobs"));?
and how would I send the output to the immediate window?

http://msdn.microsoft.com/library/d...acommondataadapterclasstablemappingstopic.asp

Thanks for helping...
I tried
DataAdapter_Slots.Update(mDataSetSlots);

and got an error box:
'Additional information: Update unable to find
TableMapping['Table']
or
DataTable 'Table'.

Would these lines in my code adequately bind the data grid?
DataAdapter_Slots.Fill(mDataSetSlots,"Slots");
dataSlots.DataSource = mDataSetSlots.DefaultViewManager;

How does the framework map the columns in the grid to the

sql
'@'

param
variables?

R









"Andy Gaskell" <pubb AT hotmail DOT com> wrote in message
If you are indeed binding the dataset to datagrids then the
update
is
pretty
simple.

DataAdapter_Slots.Update(mDataSetSlots);

After you've changed some of the data in the grid, the
HasChanges
property
of the DataSet will be true. You might also be interested

in
the

GetChanges
method.

HasChanges -

http://msdn.microsoft.com/library/d...rlrfsystemdatadatasetclassgetchangestopic.asp

update
and updates
and display
grid

to

my