Importing from excel to sql2000

[adrian0605]

Hi,

I have a issues regarding the import from excel to sql2000.
The person that will do that will have no knowledge of programming, it will use an interface that will have a browse button, will import the excel, the data will be dispalyed and, if agreed will import the data to sql2000.

Well, I have all good to the point that the data is displayed in a DataGrid, and then I can't figure out the proper import.

I saw the microsoft thread on MSDN, and still I haven't complete it.

Could someone guide me on the right direction?
All I want is to import (UPDATE,DELETE,INSERT) each row in the datagrid.

Thanks in advance.

 

[RiverGuy]

First, some questions:

1. When you bring the data into your DataGridView from Excel, are you filling a DataSet/DataTable with data from Excel, and then setting the DataSource of your DataGridView -or- are you referencing the Excel library and looping through the rows to add unbound rows to your DataGridView? Or another method altogether?

2. Are the source (Excel) and destination (SQL Server) schemas always the same? What I mean by this is do you have an Excel workbook with predefined columns to map to a predefined table -or- are you allowing your users to select any workbook and any SQL table and define the column mappings themselves?

 

[adrian0605]

1. I'm filling the DataAdapter from Excel and then populating the DataGrid with the DataSet.

2. Yes, the columns in Excel are the same as the SQL, the excel docs contain exactly the same columns are the SQL, so it's predefined.

 

[RiverGuy]

The approach will vary depending on how reusable and dynamic you want to make this. The simplest would be something like the following:

Dim SQLCon As New SqlClient.SqlConnection("YourConnectionString")
Dim SQLCmd As New SqlClient.SqlCommand
SQLCmd.CommandText = "INSERT INTO SomeTable (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)
SQLCmd.Connection = SQLCon
SQLCon.Open
For Each dr As DataRow In YourExcelDataTable.Rows
  SQLCmd.Parameters.Clear
  SQLCmd.Parameters.AddWithValue("@Column1", dr.Item("Column1"))
    SQLCmd.Parameters.AddWithValue("@Column1", dr.Item("Column2"))
    SQLCmd.Parameters.AddWithValue("@Column1", dr.Item("Column3"))
  SQLCmd.ExecuteNonQuery()
Next

Of course, you could make this more dynamic by building the SQL string based off the the DataColumns in your DataTable. Or you could take another approach and create a DataTable and DataAdapter with an INSERT command for your SQL database. With that approach, you would add DataRows to the other DataTable, and then call the Update method of the DataAdapter.

 

[adrian0605]

What if I want to use SqlCommandBuilder that will contain Insert, Update and Delete.
I can figure out how to use it, altough I saw the MSDN SqlCommandBuilder.

the code you post is good, will Insert the row, my problem now is that if the user import againg same excel but with different data, I need the update method or delete old one and Insert new one.

That will be possible if 2 parameters from DataTable will coincid with some values from sql table.

Can you light my way?

Thanks for all your support.

 

[adrian0605]

I used IF Exists in SQL Query, and seems like the problem is solved, I just wondering how can achieve this using DataAdapter Update method using INSERT, UPDATE, DELETE .
Thanks