vj#: The problem is I have a .CVS file which I need to read and insert into a DB.
The CSV file and the DB table have the exact same fields.

My solution was to build a form which has an sqlDataAdapter with a generated
dataset, dsSQL. My form also has an odbcDataAdapter to read the CSV data.
The odbcDataAdapter has it’s tableMappings set to dsSQL. I created a
copy-and-paste of dsSQL, call it dsSQL2. So, dsSQL reads the DB, dsSQL2
reads the CSV.

I read in the CSV file and the DB table ok. I merge the datasets and
update. I can display the merged data, but the DB is never updated!

dsSQL.Merge( dsSQL2, false, MissingSchemaAction.Error);
sqlAdapter1.Update(dsSQL);
….. and many combinations…

I don’t know if my solution is wrong or I am using the language wrong.

I am lost . Your help is much appreciated.

[quoted text, click to view]

I have written a example for you. I'm not using a DataAdapter. You don't
show us any code, so I would guess you are forgetting to make a
CommandBuilder on the DataAdapter.

I love to write code, so here is an other solution:


Please see "// PLEASE LOOK HERE!!!!!!"




import System.Drawing.*;
import System.Collections.*;
import System.ComponentModel.*;
import System.Windows.Forms.*;
import System.Data.*;

/**
* Summary description for Form1.
*/
public class Form1 extends System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.ComponentModel.Container components = null;

public Form1()
{
InitializeComponent();
}

protected void Dispose(boolean disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
super.Dispose(disposing);
}

#region Windows Form Designer generated code
/**
* Required method for Designer support - do not modify
* the contents of this method with the code editor.
*/
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.set_Location(new System.Drawing.Point(40, 320));
this.button1.set_Name("button1");
this.button1.set_TabIndex(0);
this.button1.set_Text("button1");
this.button1.add_Click( new System.EventHandler(this.button1_Click) );
//
// dataGrid1
//
this.dataGrid1.set_DataMember("");
this.dataGrid1.set_HeaderForeColor(System.Drawing.SystemColors.get_ControlText());
this.dataGrid1.set_Location(new System.Drawing.Point(16, 24));
this.dataGrid1.set_Name("dataGrid1");
this.dataGrid1.set_Size(new System.Drawing.Size(464, 280));
this.dataGrid1.set_TabIndex(1);
//
// Form1
//
this.set_AutoScaleBaseSize(new System.Drawing.Size(5, 13));
this.set_ClientSize(new System.Drawing.Size(496, 438));
this.get_Controls().Add(this.dataGrid1);
this.get_Controls().Add(this.button1);
this.set_Name("Form1");
this.set_Text("Form1");
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);

}
#endregion

/**
* The main entry point for the application.
*/
/** @attribute System.STAThread() */
public static void main(String[] args)
{
Application.Run(new Form1());
}


// PLEASE LOOK HERE!!!!!!
private void button1_Click (Object sender, System.EventArgs e)
{
// READ THE cvs file into a DataTable
System.IO.StreamReader f = new System.IO.StreamReader("Book1.csv");
System.String line = null;
System.Data.DataTable dataTable = new System.Data.DataTable("Your table");
System.String headers = "";
boolean header = true;
while ( (line = f.ReadLine()) != null )
{
int numberOfCols = line.Split(new char[]{';'}).length;

// Make the header and the SQL column header statements
if ( header == true )
{
for ( int headercounter = 0; headercounter < numberOfCols;
headercounter++ )
{
System.Data.DataColumn colHeader = dataTable.get_Columns().Add();
System.String value = line.Split(new char[]{';'})[headercounter];
colHeader.set_ColumnName( value );
if ( headers.length() > 0 )
headers = headers + ", " + value;
else
headers = value;
}
header = false;
}
else // Builds the DataTable.
{
System.String arr[] = new System.String[numberOfCols];
for( int counter = 0; counter < numberOfCols; counter++ )
{
System.String value = line.Split(new char[]{';'})[counter];
arr[counter] = value;
}
dataTable.get_Rows().Add( arr );
}
}




// Show the cvs file in the GUI_____________
this.dataGrid1.set_DataSource( dataTable );




// Write the datatable into the database

// Insert your connection string here!!!
System.String conStr = "workstation id=TARGUS;packet size=4096;integrated
security=SSPI;data source=\"TARGUS\\MinDB\";persist security
info=False;initial catalog=ReserveringsDB";

System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection( conStr );
conn.Open();
for( int rowcounter = 0; rowcounter < dataTable.get_Rows().get_Count();
rowcounter++ )
{
System.String values = "";
// Make the column values SQL-Statement
for ( int colCounter = 0; colCounter <
dataTable.get_Columns().get_Count(); colCounter++)
{
if ( values.length() > 0 )
values = values + ", '" +
dataTable.get_Rows().get_Item(rowcounter).get_Item(colCounter)+"'";
else
values =
"'"+dataTable.get_Rows().get_Item(rowcounter).get_Item(colCounter)+"'";
}

System.String sql = "INSERT INTO tbl_cvsupdate ("+headers+") VALUES
("+values+")";
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand( sql, conn );

command.ExecuteNonQuery();
}
conn.Close();

}
}



Best Regards,
Lars-Inge Tønnessen
www.larsinge.com

Lars-Inge,
Thank-you. Your complete code surpassed my expectations.
It worked perfectly, and was a good insight into how to
use J#.

You are a Microsoft VJ# MVP extraordinaire.

With thanks,
Peter

- I enjoyed your http://www.larsinge.com/, and especially