Import sheet excel to table in Sql Server with Asp.net (VB)

Sandraa

Hi all, I need import from excel to a table Sql-Server with asp.net and i am using vb.net, i have seen some sample but even I don't understand how work and all samples is in c# and i need vb.net. I have my conexion to sql server in web.config

<appSettings>
<add key="ConnectionString" value="Data Source=Mymachine;Initial Catalog=Database;Persist Security Info=False;user id=User;password=\*\*\*\*\*\*"/>

</appSettings>

for later I use a function for call it:

Public Shared Function ObtenerCadenaConexion() As String
    Return ConfigurationManager.AppSettings("ConnectionString")
End Function

Someboy can help me? I need a sample easy for can import it from a web page Thanks very much

Colin Angus Mackay

The code you have written retrieves a connection from the web.config. It looks correct to me. What is the problem?

*Developer Day Scotland - Free community conference Delegate Registration Open

Sandraa

I believe that I explained bad, my problem is that I don't know import a sheet of excel to sql-server with my present connection. Regards

Colin Angus Mackay

Sandraa wrote:

I don't know import a sheet of excel to sql-server with my present connection.

How do you want it imported? Do you want the excel file to be inserted as a binary object? Or do you want the excel spreadsheet to be translated into tables?

*Developer Day Scotland - Free community conference Delegate Registration Open

Sandraa

I want import data from excel to a table sql-server. Not binary object. And if is possible can use my present conection to sql server from web.config Thanks

Colin Angus Mackay

Sandraa wrote:

And if is possible can use my present conection to sql server from web.config

The connection is the least of your problems. Personally, I'd look at using something like SSIS for what you want (SQL Server Integration Services) because that is what it is good at: Taking data from one source and loading it into SQL Server.

*Developer Day Scotland - Free community conference Delegate Registration Open

Sandraa

I am beginner, I accept all solutions :) The target is that from a web page a lot users can export data with a same template of excel in them hard disk to same table of sql-server. Later with all these data of table i will do web reports. Is not possible create a form for upload these data because these users has all data in excel and they want continue using excel. Thanks for help me.

Sandraa

somebody can help me? I have this code of a button the idea is export from excel to a table of sql-server but i received a OleDbException "ODBC: falló la llamada." that the translate is "ODBC: error failed the call" I put you my code:

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Partial Class Formularios_ImportarExcel
Inherits System.Web.UI.Page

Protected Sub Page\_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Protected Sub Button1\_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim connString As String = \_
        "Provider = Microsoft.Jet.OLEDB.4.0;" & \_
        "Data Source = C:\\test.xls;" & \_
        "Extended Properties = 'Excel 8.0;HDR=Yes'"

    Dim cnn As New OleDbConnection(connString)

    Try
        Dim sql As String = \_
            "SELECT \* INTO T\_Recibos " & \_
            "IN ''\[ODBC;DRIVER={SQL Server};" & \_
                 "Server=localmachine;" & \_
                 "Database=NAMEDATABASE;" & \_
                 "UID=Sandra;" & \_
                 "PWD=Sandra\]" & \_
            "FROM \[Hoja1$\]"

        Dim cmd As New OleDbCommand(sql, cnn)

        cnn.Open()

        Dim n As Integer = cmd.ExecuteNonQuery()

        Label1.Text = "Número de registros afectados: " & n.ToString

    Catch ex As OleDbException
        Label1.Text = (ex.Errors(0).Message)

    Catch ex As Exception
        Label1.Text = (ex.Message)

    Finally
        cnn.Close()
        cnn = Nothing

    End Try
End Sub

Sandraa

I found the error, the problem was that this part

            "SELECT \* INTO T\_Recibos " & \_                "IN ''\[ODBC;DRIVER={SQL Server};" & \_                     "Server=localmachine;" & \_                     "Database=NAMEDATABASE;" & \_                     "UID=Sandra;" & \_                     "PWD=Sandra\]" & \_                "FROM \[Hoja1$\]"

create a new table T_Recibos but I had it created in Sql-server, I changed it by T_Recibos2 (what doesn't exist) and work! Now my problem is that I want to have a table (created into sql) and little to little that users add to it more rows from excel. any idea?