import data of excel to sql server

:cry: 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

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?

Quote:

---

Originally Posted by 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?

---

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.

Quote:

---

Originally Posted by 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?

---

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 ????:cry:

Quote:

---

Originally Posted by gooden

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???:(

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.

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

vb.net Code:

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

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

Quote:

---

Originally Posted by GaryMazzone

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 :)

Quote:

---

Originally Posted by 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.

---

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

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