Multi Form, Multi Tables: What would be the best way to update the tables?

yes, start a new thread.

The read record is dependant on the other things so that can wait a little.

Can't do the build? stuff just yet, to do this stuff, we need to know other things first so post all the fields of a (1) table (record) that  you want to read, save or delete.  this part will be show you how to do things for ALL tables in your application.

Remember, one step at a time, when you have finished this app you will have learned how to create any application faster than using any databound control because all the functions we are creating will work in any application using databases you do create.

You will even be able to create an application where all you know is the connection string and from this you will be able to load a treeview with the tables, fields, field data types and keys of the catalog for the connection string.

Your application will not need to know anything about the database.

Lizard:
 What I have left is: SELECT, INSERT, UPDATE and DELETE. But I would like to do those after I have the BuildSelect, BuildInsert, BuildUpdate and BuildDelete.
 
 

Lizard:
 
The ReadRecord routine is done. I think its pretty clean. Code listed below:
BTW: YOu want to create another thread?
 
public void ReadRecord(string DBCommandText, DataTable DBDataTable)
{
ClassDB DBConn = new ClassDB();
//
Connection();
createAdapter();
DBCommand.CommandText = DBCommandText;
DBDataReader = DBCommand.ExecuteReader();
try
{
DBDataReader = DBCommand.ExecuteReader();
}
catch (Exception Error)
{
//MessageBox.Show("ClassDB: CATCH: LoadDataGridView: " + Error); // DO NOT CATCH
}
finally
{
// DO NOT TERMINATE HERE
}
}

Great, what is your next step...

Lizard:
 
Yep, its cool. I will be keeping the delimiter to comma for all my applications. But I will put the code in there for future references. Thanks
 

Do what you think you are comfortable with, you could also do the same for ListBox if you have not already decided to.

Can you see how one function will now load any combo box?  as an enhancement  you can do this

LoadComboBox(string DBCommandText, ComboBox DBComboBox, string[] Fields, string delimiter)

s+= (i < fields.Count? delimiter : "");

Can you now imagine what else can be done!.

Lizard:
 
Ok, got it to work. This was fun and learned a few things...LOL. I could not get the formatValue to work. I guess it can wait.
I changed your code to the following:
I did not need the -1 and the Count to Count(). I tested it with 1,2 and 3 fields. Do you want me to implement this into the comboBox or wait till the ListBox enhancements are complete?

while (DBDataReader.Read())
{
string TheString = "";
for(int i=0; i < Fields.Count() ; i++)
{
TheString += DBDataReader[Fields[i]].ToString();
TheString += (i < Fields.Count() ? ", " : "");
}
DBListBox.Items.Add(TheString);
}

This is also why I wanted you to hold back on the other things,  loading records can be done in similar fashion (but different)

yes, we are now talking dynamic.

ps  change (i < fields.count to i < fields.Count-1

Lizard:
 
Ok, let me work it out. Will get back to you when I have it working. That will be cool, so I can send as many fields as I want...

Lizard:
 
Ok, I took out:
DataTable DBDataTable = new DataTable();
DBDataAdapter.Fill(DBDataTable);

and createadapter();
 
It still does not work. I tried also with a: DBDataReader = DBCommand.ExecuteReader(); and it still does not work.

Lizard:
 
I did put the .ToString(). But it still does NOT work if I pass: DBConn.LoadListBox(DBCommand, listBoxList, "Company_ID", "");
 
But it DOES work if I pass: DBConn.LoadListBox(DBCommand, listBoxList, "Company_ID", "Name");

whenever we are using a data reader we do not need a data adapter so you can get rid of these

DataTable DBDataTable = new DataTable();
DBDataAdapter.Fill(DBDataTable);

and createadapter();

first try putting a . ToString() at the end of this DBDataReader[Field_2]

but we can treat those cases.  this can be done in a number of ways but to keep it simple and always be available we can create a function that will fix the problem

from this

ComboBox.Items.Add(DBDataReader[Field_1].ToString() +  ", " + DBDataReader[Field_2]);


to this

ComboBox.Items.Add(formattedValue(DBDataReader[Field_1].ToString()) +  ", " + formattedValue(DBDataReader[Field_2]).ToString());

private string formattedValue(string field)
   {
   //we could also format the string any way that we like for argument sake if it is a double value
   // formattedValue(double field)   r---> return( field.ToString("###,##0.00"));

    return( String.IsNullOrEmpty(field)? "" : field);
   }
Ok.

Lizard:
 
I just put in your code and I get the error:
LoadComboBox: System.NullReferenceException: Object reference not set to an instance of an object. Did I forget something?
 
Here is the code that in there now:
 
public void LoadComboBox(string DBCommandText, ComboBox DBComboBox, string Field_1, string Field_2)
{
ClassDB DBConn = new ClassDB();
DBComboBox.Items.Clear();
//
Connection();
createAdapter();
DBCommand.CommandText = DBCommandText;
try
{
DataTable DBDataTable = new DataTable();
DBDataAdapter.Fill(DBDataTable);
//
//foreach (DataRow DBDataRow in DBDataTable.Rows)
//{
// TheID = DBDataRow[Field_1].ToString();
// TheList = TheID;
// if (Field_2 != "")
// {
// TheDescription = DBDataRow[Field_2].ToString();
// TheList += ", " + TheDescription;
// }
// DBComboBox.Items.Add(TheList);
//}
//
while (DBDataReader.Read())
{
if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString()))
DBComboBox.Items.Add(DBDataReader[Field_1].ToString() + ", " + DBDataReader[Field_2]);
}
//
Terminate();
}
catch (Exception Error)
{
MessageBox.Show("ClassDB: CATCH: LoadComboBox: " + Error);
}
finally
{
Terminate();
}

Lizard:
 
From the main program. I the ListBox routine, I pass this:
DBConn.LoadListBox(DBCommand, listBoxList, "Company_ID", "Name");
 
But if I pass this, it will mess it up:
DBConn.LoadListBox(DBCommand, listBoxList, "Company_ID", "");
 

I dont understand what you mean, where do you pass the blank?

Lizard:
 
I did the:
               while (DBDataReader.Read())
                {
                    if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString()))
                        ComboBox.Items.Add(DBDataReader[Field_1].ToString() +  ", " + DBDataReader[Field_2]);
                }
But: I pass a "" (blank) to the routine it messed it up. So I put it back with the if/else. What I want to do is if I pass 1 value then the comboBox will only show that column, if I pass 2 fields then show both with a selerator of a ','. Does your code do that?
 
 
The BuildSelect can wait. I was just doing it to keep me busy.
 

@Sam,

Giorgio will be a better programmer for his effort, he is learning to do things that you take for granted, he will develop better applications by knowing how to do things him self rather than having an IDE do the work which MUST ultimately control how and what you are able to do taking away the CONTROL you sometimes need.

Forgot, Sam, are you able to write algorithms to dynamically create insert, update and other sql given just the table name?  or is this too hard for you to do!

load combo box should be like this as well

               while (DBDataReader.Read())
                {
                    if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString()))
                        ComboBox.Items.Add(DBDataReader[Field_1].ToString() +  ", " + DBDataReader[Field_2]);
                }

basically you can just copy the code from list box to combo box.

From the BuildSelect, I now know your are learning,

tip, you do not need the brackets " [" or "] " these are primarily used if you have field names that are the same as system names eg if you had a field in your table called user then you would use the square brackets like '[user]'  but if you changed user to appUser you would not need the brackets.  Allother fields

                    TableCommand += " [" +  TableColumn.ColumnName + "]";

But lets just wait a little for that, lets go to the insert, update and delete parts.


all you need for these is this one function (change the vars to yours, good practice)  this function should not care what you want, just do it or fail.

    public bool execSQL(string s) 
        {
        bool success = true;  //is Always true until an exception is encountered...

        sqlCon con = new sqlCon();
        try
            {
            con.connect();         //connect to db
            con.command(s);    //set the command text
            con.beginTrans();   //begin the transaction
            con.write();            //write the transaction,   this is another function you should implement, see my original code.
            con.commitTrans();//commit it to db if success
            }
        catch (Exception err)
            {
            con.rollbackTrans();  //if one part fails they ALL fail, this is especially true in MULTI statement sql
            success = false;        //show that it failed
            }
        finally
            {
            con.terminate();
            }
        return (success);

the build parts  should be made as dynamic functions to be handled by a different class, remember I had a con.GetSchema()!  this will be used for doing what you are trying to do in your BuildSelect or BuildUpdate etc.

The first thing I have noticed in BuildInsert is one very IMPORTANT thing that you are NOT doing, (I know it is early stages) depending on the field type the value part of the sql statement should have the appropriate token (I call them tokens you don't need to)

eg

                TableCommand = "";
                TableCommand += "INSERT";
                TableCommand += " [" + TableName + "]";
                TableCommand += " (";
                TableCommand += " fieldname...";
                TableCommand += " )";
                TableCommand += " VALUES";
                TableCommand += " (";
at this point you need to know what data type the field value is so that you can do the necessary things

string token = "";
switch(fieldNameDataType(fieldName))  //this will go and fetch the data type for the field and return it's type.  Also this would be done in its own function so that you only get the combined token, fieldvalue token  '2010/10/03'
   {
   case SqlString:   //these are not the real data type names, we will use the correct ones when we get there.
   case SqlText:
   case SqlDate:
       token = "'";  //single quote
       break;
  
   }
                TableCommand += token + " fieldvalue..." + token [+ ","] ;
                TableCommand += " )";
 
 do you undestand what I mean?

Yes, there absolutely definintely are much easier ways to do what you are doing. The idea to "create a program/routine to generate SELECT/INSERT/UPDATE/DELETE commands" is nothing new. That capability already exists in VS and is much more advanced and powerful than you can accomplish.
 
It is a little late to switch to the easy way to do it. You and theLizard are too quick for me. I think you would have accomplished more by now if you had invested more time in learning. Learning takes time in the front-end and saves time in the back-end.

Lizard:
 
I just looked at it and I think I am done. What did I miss? I am enclosing a recent zip.
 
It has some new routines at the bottom that I started working on to keep me busy. I will stop work on these for now.
 
 
 

Lizard:
 
Ok, I thought we where going to only do the ListBox routine.
OK, I will fix up the ComboBox and look at the ReadRecord.

Giorgio, one thing at a time,  from the last zip you posted you have not finished the sql connection class you need to change the combo box, ReadRecord needs to be looked at.

I already said that once the class is finished these other things will be easier.

FYI:
 
I think I will try to create a program/routine to generate SELECT/INSERT/UPDATE/DELETE commands. (Will try...)