import text and numbers

[mustangBE]

hello

i have an appl. in vb2008 with a datagridview.
when i import my data from excel (colum with text and numbers)it only imports the numbers or the text , not the both.

example :
120
150
set25
160
"set25" is not imported

example :
set120
150
120
set130
"150 , 120" is not imported

Public Class Form1
#Region "Importeer artikelen uit Excel"


    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Load_excel()
    End Sub

    Private Sub Load_Excel()
        Dim opendlg As New OpenFileDialog
        opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

        If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim pathandfile As String = opendlg.FileName
            Dim connection As OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & pathandfile & ";Extended Properties=Excel 8.0;")
            Dim Command = New OleDb.OleDbCommand("select * from [Blad1$]", connection)

            connection.Open()
            Dim dr As Data.IDataReader = Command.ExecuteReader
            Dim bulkCopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
            bulkCopy.DestinationTableName = "tabel2"
            bulkCopy.WriteToServer(dr)

            Dim adapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Blad1$]", connection)
            adapter.Fill(Me.Database1DataSet1.tabel2)
            connection.Close()

        End If
    End Sub

#End Region

End Class

[the_carpenter]

While not a great expert at importing excel data, I tried your code and it does the same thing to me as well.

I'm pretty sure it something to do with the Bulk import function and it applying datatypes to each column based on what's in the first row. Is there some other method to import data other than SQLBulkCopy?

[the_carpenter]

I just thought of something.

Save your data on the Excel spreadsheet as string.

When you enter a number in Excel precede it with an apostrophe ( ' )

This tells Excel to treat the data in this cell as a string value and not a number.

[mustangBE]

that does not work !
it has to be changed in the code

[the_carpenter]

You'll have to import it row by row then

[mustangBE]

how do i do that , i'm a beginner !