import data of excel to sql server

[ghizounette]

Hello,
I want a small program which makes it possible to import data of Excel file or txt towards sql server (table) in vb.net 2003 or recupére the data of Excel file towards a datagrid
thank you in advance

[Hack]

Well, we aren't going to write it for you but we will help you write if for yourself.

Do you have anything at all written yet?

[gooden]

Well, we aren't going to write it for you but we will help you write if for yourself.

Do you have anything at all written yet?

like hack say it we are not going to do the program. but we can help you with code.

now my Opinion. the best way of do that is using the excel file like a database. read and at the same time insert at a sqlserver database.

[ghizounette]

Well, we aren't going to write it for you but we will help you write if for yourself.

Do you have anything at all written yet?

but I don't know how work with DTS by exmeple I want to then fill the data of fihcier Excel towards DTS how I can do that? and how the data lira data of DTS and to register it on sql server ????

[ghizounette]

like hack say it we are not going to do the program. but we can help you with code.

now my Opinion. the best way of do that is using the excel file like a database. read and at the same time insert at a sqlserver database.

but how I can read the data of Excel file and store them in a dataset???

[Tom Sawyer]

Use an OleDbConnection. It can read the excel file just like it was a table in a database. Google "Excel" and "ConnectionString" and you should get some examples of syntax.

[gooden]

if you want to put in a sql server you dont need dataset....
you have here a example

Public SQL As New SqlClient.SqlConnection("server=xxx.xxx.xxx.xxx\SQLEXPRESS;database=database;Uid=user;pwd=pass")
 
    Function GetData() As System.Data.DataSet
 
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\file_acess.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
 
        Dim queryString As String = "SELECT * FROM members"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
 
        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)
 
        Return dataSet
    End Function
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SQL.Open()
        Dim dd As DataSet = GetData()
        Dim table As New DataTable
        Dim colum As New DataColumn
        Dim rows As DataRow
        For Each table In dd.Tables
            For Each rows In table.Rows
                Dim Query1 As String = "INSERT INTO members_sql(username,userpassword) values(@username,@userpassword)"
                Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(Query1, SQL)
                cmd.Parameters.AddWithValue("@username", rows("member_login"))
                cmd.Parameters.AddWithValue("@userpassword", rows("member_password"))
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            Next
        Next
        SQL.Close()
    End Sub

hope it helps

[GaryMazzone]

Go to www.ConectionStrings.com all the connection strings you would need can be found there

[gooden]

Go to www.ConectionStrings.com all the connection strings you would need can be found there

that is always the right place to get the strings for connections to many tipes of databases systems

[ghizounette]

Use an OleDbConnection. It can read the excel file just like it was a table in a database. Google "Excel" and "ConnectionString" and you should get some examples of syntax.

good I am sorry I included/understood anything and thank you

[gooden]

ghizounette see the code i posted. it have all the code. i cant do more then that don't you think?