How do I: Programmatically build SQLCommand to Read, Save, Delete.

Lizard:
 
Was I suppose to do something or just wait for you. You are probably busy.
 

Lizard:
 
I guess the CreateDate and CreateTime can be changed to CreateDateTiime. I see your point.
 
The 'Note' ??? I have one (main) note per record. But, I also have additional notes in a seperate 'Note' table that keeps all additional notes for all tables.
 
 

Yes, yes,  datecreated also contains the time created so date and time can be extracted separately you do not need 2 fields for this.

I have put notes as an index because I think that notes should be stored in a seperate table which can then be categorized and all notes for a given identity can be viewed at the same time in a single location or on the form relative to the note type.

Lizard:
 
I dont understand.
You changed:
    [CreateDate] [datetime] NOT NULL,
    [Note] [int] NULL,
 
The 'Note" field is text that the user can type.
I need the 'CreateDate' and 'CreateTime' seperate. You removed the 'CreateTime'.
 

It is what I expected, these fields do not need to be varchar, I have changed them to int, all these should do is point to the records in the tables.  your data entry form will show these as Combo Box's.

I have changed table to this

USE [IPPilot]
GO
/****** Object:  Table [dbo].[Company]    Script Date: 03/23/2010 06:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Company](
    [SEQNO] [int] NOT NULL,
    [Status_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Class_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Group_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Type_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CreateBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [Note] [int] NULL,
    [Company_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Address_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Address_2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Address_3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [State_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Zip_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Fax] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WebSite] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Lizard:
 
I am just playing around with some code. I am sure you have a better way. Here is what I have the program doing. I enclosed a word document of what the program gives me and what I want to have it do. If I can just get the column names, I can write code to fill the changes in... I think.
 
 

These are varchar(10), what are they used for, give an example of the data you will store i.e.  Status_ID = "Active" what can it hold?

    Status_ID
    Class_ID
    Group_ID
    Type_ID

The sample code is probably ok for all fields in a table but that is not what needs to be done.

as far as a select statement goes, we do not need a table, depending on factors to be determined we can give the function a list of fields or use the global * to get all fields so the sample code you posted is equivalent to simply  saying

"SELECT* FROM Company";

Give me some time to look at the company table, i will put together some code and post.

Lizard:
 
This is what I changed the code to. It give me an error at the marked statement.
 
public void BuildSelect(string DBCommandText, DataTable DBDataTable)
{
ClassDB DBConn = new ClassDB();
//
Connection();
createAdapter();
MessageBox.Show("DBCommandText=" + DBCommandText);
DBCommand.CommandText = DBCommandText;
DBDataReader = DBCommand.ExecuteReader();
DataTable table = new DataTable();
//
try
{
string sql = "";
foreach (DataColumn column in DBDataTable.Columns) // <<< ERROR: DBDataTable.Columns
{
if (sql.Length > 0)
sql += ", ";
sql += column.ColumnName;
}
MessageBox.Show("sql=" + sql);
}
catch (Exception Error)
{
MessageBox.Show("ClassDB: CATCH: BuildSelect: " + Error);
}
finally
{
// DO NOT TERMINATE HERE
}
}

Lizard:
 
I found this program code and what I wanted to do, is to implement this. What do you think?
 
public static string BuildAllFieldsSQL ( DataTable table )
{
string sql = "";
foreach ( DataColumn column in table.Columns )
{
if ( sql.Length > 0 )
sql += ", ";
sql += column.ColumnName;
}
return sql;
}
 
 
But I cant figure out how to pass it the DataTable table.

Lizard:
 
Ok, here is the Company tables.
 

Ok.  while you are doing that, think about what can be left as null and what cannot because we can deal with those sorts of things when building sql.

Lizard:
 
OK, I will do it to the Compnay and another. Will take me a while.
 
 

I understand, but there is more to building an sql statement than just looping through the table.

I know that you want to see things happening but it is really important to get your tables organized with the right data types so please do that with company, post it back to me and I will show you how to proceed.

Lizard:
 
I dount have all the fields in all my filles/tables yet. But here is the Company & User.
 
My idea was to loop thru the datatable.colums and build the sql statement.
 

Ok then STOP right here.

Before you can go any further you MUST know what each field will contain.

id's should be of type int

date should be datetime so you don't need a create date and a create time.

length of varchars is not important at this time.

I know I may be saying what you already know but these need to be done before you can write code for them.

You cannot create a dynamic build function without having the correct functions to determine what to do for what type of field.

Also send me another table like you have done with this, I want to compare fields that you may have duplicated...

Do one table first then in sql server (MSSMSE if you dont have this get it, its free) right click on the table name -> Script Table As -> Clipboard then paste into a text document or here.

While I look at things, do the other tables, it will take time but they need to be done otherwise you will be writing code that then needs to be changed to something completely different to what you may think.

Ok?

this is what the Script Table As will do..

USE [temp]
GO
/****** Object:  Table [dbo].[users]    Script Date: 03/22/2010 14:36:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [pwd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Lizard:
 
I took a snipit of it. I hope its good enough.
The SEQNO is the id of the record and auto-increment.
The fields that end with *_ID are linked to other tables for validation and to be shown as combobox.
In this example the table name is "Company" and the Company number is Company_ID.
The fields are at a lenght of 50 for now. They will be chnaged later. the date and time is just a text for now.
 
 

Forget the code example, take one table that you have with ALL the fields and list them in a text file and post it, I want to see the field name, the data type etc.

I also want to see how you will show these on a form ie.  which will be TextBoxes, or other type of control, this will help me understand how to do things so that I can show you the easy way.

My common practice for identity fields (auto increment) is to simply call it id,  when when the id needs to be referanced in child tables I call them tableId  table being the parent table so if you have table called client and a table called transaction then client has id, the transaction has id and clientId, if transaction has dependencies on other tables, for example, a table that stores notes then you would also have in transaction a field that stores the notes id notesId.

Lets look at the client table, it may have a dependency on say a postcode in this case you may have a table called postcodes so the client table would also have a field called postcodeId

This way you are firstly not duplicating fields and secondly saving on space, this is how relational database's work, so if you change the field value in one it will show in all records that is linked to it by it's id.

You will understand more as we go..

Forget the code example, take one table that you have with ALL the fields and list them in a text file and post it, I want to see the field name, the data type etc.

I also want to see how you will show these on a form ie.  which will be TextBoxes, or other type of control, this will help me understand how to do things so that I can show you the easy way.

My common practice for identity fields (auto increment) is to simply call it id,  when when the id needs to be referanced in child tables I call them tableId  table being the parent table so if you have table called client and a table called transaction then client has id, the transaction has id and clientId, if transaction has dependencies on other tables, for example, a table that stores notes then you would also have in transaction a field that stores the notes id notesId.

Lets look at the client table, it may have a dependency on say a postcode in this case you may have a table called postcodes so the client table would also have a field called postcodeId

This way you are firstly not duplicating fields and secondly saving on space, this is how relational database's work, so if you change the field value in one it will show in all records that is linked to it by it's id.

You will understand more as we go..

BTW:
 
This is the code I already have. I have the error marked.
 
public void BuildSelect(string DBCommandText, string TableCommand, string TableName)
{// DBConn.BuildSelect(DBCommand, textBoxTableCommand);
try
{
ClassDB DBConn = new ClassDB();
//
Connection();
createAdapter();
DBCommandText = "SELECT * FROM [" + TableName + "] LIMIT 1";
DBCommand.CommandText = DBCommandText;
//
DBDataReader = DBCommand.ExecuteReader(); // <<< ERROR HERE
//
//DataTable DBDataTable = new DataTable();
//DBDataAdapter.Fill(DBDataTable);
//----------------------------------------------------
TableCommand = "";
TableCommand += "SELECT";
TableCommand += " *";
TableCommand += " FROM";
TableCommand += " [" + TableName + "]";
MessageBox.Show("TableCommand= " + TableCommand);
//
DataTable DBDataTable = new DataTable();
//
foreach (DataColumn TableColumn in DBDataTable.Columns)
{
TableCommand += " [" + TableColumn.ColumnName + "]";
}
//
MessageBox.Show("TableCommand = " + TableCommand);
//
}
catch
{
MessageBox.Show("ClassDB: CATCH: BuildSelect()");
}
finally
{
MessageBox.Show("ClassDB: FINALLY: BuildSelect()");
}