Linked server in code or not?

[pachjo]

I have discovered that I need to provide users with the ability to import a csv file into a table in my app.

I tried using this line found on this site:

[SIZE=2]
strSQL = [/SIZE][SIZE=2][COLOR=#a31515]"Select * "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]" INTO user_import FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\; Extensions=CSV; HDR=No;','SELECT * FROM myfile.csv') "
[/COLOR][/SIZE][SIZE=2]
[/SIZE]

But I get an error about ad hoc distrubed queires.

I have read about the surface area configuration tool (which I can't find), but I see it say best not to enable ad hoc distrubed queires.

Better to use a linked server.

Question is........

How do I create a linked server to a .csv file that the user will provide?

Thanks

 

[pachjo]

I always seem to get lost when trying to get to grips with BOL as it always confuses me?

For example I search on how to create a csv as a linked server and I find this:

[URL="javascript:CopyCode('ctl00_LibFrame_ctl05VisualBasic');"][COLOR=#0000ff][/COLOR][/URL]
[COLOR=green]'Connect to the local, default instance of SQL Server.[/COLOR]
[COLOR=blue]Dim[/COLOR] srv [COLOR=blue]As[/COLOR] Server
srv = [COLOR=blue]New[/COLOR] Server
[COLOR=green]'Create a linked server.[/COLOR]
[COLOR=blue]Dim[/COLOR] lsrv [COLOR=blue]As[/COLOR] LinkedServer
lsrv = [COLOR=blue]New[/COLOR] LinkedServer(srv, [COLOR=maroon]"OLEDBSRV"[/COLOR])
[COLOR=green]'When the product name is SQL Server the remaining properties are not required to be set.[/COLOR]
lsrv.ProductName = [COLOR=maroon]"SQL Server"[/COLOR]
lsrv.Create()

No this does not help me as I dont understand what this has to do with csv?

So,

does......

javascript:CopyCode('ctl00_LibFrame_ctl05VisualBasic');
Dim srv As Server
srv = New Server


Create a server ojbect connected to my SQLExpress instance?

I know this makes a linked server, but to what? what is OLEDBSRV?

Dim lsrv As LinkedServer
lsrv = New LinkedServer(srv, "OLEDBSRV")


I know this makes an SQL Server link, but I want CSV!


lsrv.ProductName = "SQL Server"
lsrv.Create()


I always find the BOL just whets my appetite leaving me stuck not know where to find the bits I am missing
javascript:CopyCode('ctl00_LibFrame_ctl05VisualBasic');

 

[pachjo]

OK I apparently have created a linked server by doing this:

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] srvAdminServer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Server = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnSQLConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MMConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] scnServerConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lsrv [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] LinkedServer
cnSQLConnection.Open()
scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(scnServerConnection)
lsrv = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] LinkedServer(srvAdminServer, [/SIZE][SIZE=2][COLOR=#a31515]"CSV-IMPORT"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] lsrv
.ProductName = [/SIZE][SIZE=2][COLOR=#a31515]"SQL Server"
[/COLOR][/SIZE][SIZE=2].ProviderName = [/SIZE][SIZE=2][COLOR=#a31515]"Microsoft.Jet.OLEDB.4.0"
[/COLOR][/SIZE][SIZE=2].DataSource = [/SIZE][SIZE=2][COLOR=#a31515]"C:\FullStatement.csv"
[/COLOR][/SIZE][SIZE=2].ProviderString = [/SIZE][SIZE=2][COLOR=#a31515]"Excel 8.0"
[/COLOR][/SIZE][SIZE=2].Create()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE]

This has created a linked server named CSV-IMPORT, however how do I see it or drop it?

When I ran my code again it errors as it says it already exists.

I cannot see it in Server Explorer in the VS.NET IDE or in SSMSE?

Any tips please?

Thanks

 

[pachjo]

OK....for what it is worth my day has taken me on this journey...

This block of code creates a linked server named CSVIMPORT but I get the displayed error after trying to run a query against it

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] srvAdminServer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Server = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnSQLConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] scnServerConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lsrvCSV [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] LinkedServer
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strsql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT * INTO user_import FROM CSVIMPORT...FullStatement$"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand
cnSQLConnection.Open()
scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
[/SIZE][SIZE=2][COLOR=#008000]' create a server
[/COLOR][/SIZE][SIZE=2]srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(scnServerConnection)
[/SIZE][SIZE=2][COLOR=#008000]' check if we have a linked server, there will only ever be one, delete it if present
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] srvAdminServer.LinkedServers.Count > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]srvAdminServer.LinkedServers.Item(0).Drop()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' setup a new linked server
[/COLOR][/SIZE][SIZE=2]lsrvCSV = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] LinkedServer(srvAdminServer, [/SIZE][SIZE=2][COLOR=#a31515]"CSVIMPORT"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]' set its properties
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] lsrvCSV
.ProviderName = [/SIZE][SIZE=2][COLOR=#a31515]"Microsoft.Jet.OLEDB.4.0"
[/COLOR][/SIZE][SIZE=2].ProductName = [/SIZE][SIZE=2][COLOR=#a31515]"Excel"
[/COLOR][/SIZE][SIZE=2].DataSource = [/SIZE][SIZE=2][COLOR=#a31515]"C:\FullStatement.csv"
[/COLOR][/SIZE][SIZE=2].ProviderString = [/SIZE][SIZE=2][COLOR=#a31515]"Excel 8.0"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' creates ok with no errors
[/COLOR][/SIZE][SIZE=2]lsrvCSV.Create()
[/SIZE][SIZE=2][COLOR=#008000]' setup an sql command to run against linked server
[/COLOR][/SIZE][SIZE=2]objCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strsql, cnSQLConnection)
objCommand.CommandText = strsql
objCommand.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#008000]' doh!! get the following error
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CSVIMPORT" reported an error. The provider did not give any information about the error.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CSVIMPORT"."
[/COLOR][/SIZE]

So I thought stuff this lets go back to the begining and did this:

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] m_strConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand
objConn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection
objConn.ConnectionString = m_strConnection
objConn.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] System.IO.File.Exists([/SIZE][SIZE=2][COLOR=#a31515]"C:\FullStatement.csv"[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' ------ Load the data from the .CSV file: ----------
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]strSQL = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT * INTO user_import FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\FullStatement.csv;Extended Properties=Excel 8.0')...FullStatement$"
[/COLOR][/SIZE][SIZE=2]objCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strSQL, objConn)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()
objConn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MessageBox.Show(ex.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]

Now I am back to square 1 where I get a message saying I cant do this as the component of my instance of SQL Express has ad hoc distributed querires turned off.

So I have trudged through the web and bol etc and found how to turn this on via SQL Surface Area Configuration.

But I still get the same error.

So I think I am missing something here?

Within SSMSE I do not see my applications .mdf file (does not seem to matter?)

Within the VB.NET 2005 IDE I have my .mdf as a connection in server explorer.

When my app connects to the .mdf file does it create a 'virtual sql express' instance and if so do I need to somehow progmatically turn on the setting in this instance?

Or am I off beam and it is only ever my .\SQLExpress instance involved and if so why does the setting now turned on not allow the query to run?

Lastly.....when the end user eventually installs my app thro the publish clickonce and the install downloads and installs sql express onto their computer how can I be sure it has the required setting turned on?

Please if anyone can help this is driving me bonkers

 

[pachjo]

Please can anyone help?

I feel that a linked server approach would be better, but from above you see I cannot get it to work.

If I go for ad hoc distributed queries from what I understand I have to alter the surface area of not only my instance (which even tho I have done this I still get an error saying it is disabled), but also the end user which they might not like the idea of.

I am searching and searching but am going round in circles....

 

[pachjo]

Will no one throw me a bone???

This is where I am now...

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] srvAdminServer [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Server = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnSQLConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] scnServerConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lsrvExcelFile [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] LinkedServer
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQLString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT * FROM CSVIMPORT...FullStatement$"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] taImportData [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtImportData [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] taUserImport [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtUserImport [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cbUserImport [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommandBuilder
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] drUserImport [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
cnSQLConnection.Open()
scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(scnServerConnection)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] srvAdminServer.LinkedServers.Count > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]srvAdminServer.LinkedServers.Item(0).Drop()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]lsrvExcelFile = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] LinkedServer(srvAdminServer, [/SIZE][SIZE=2][COLOR=#a31515]"CSVIMPORT"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] lsrvExcelFile
.ProviderName = [/SIZE][SIZE=2][COLOR=#a31515]"Microsoft.Jet.OLEDB.4.0"
[/COLOR][/SIZE][SIZE=2].ProductName = [/SIZE][SIZE=2][COLOR=#a31515]"Excel"
[/COLOR][/SIZE][SIZE=2].DataSource = [/SIZE][SIZE=2][COLOR=#a31515]"C:\FullStatement.xls"
[/COLOR][/SIZE][SIZE=2].ProviderString = [/SIZE][SIZE=2][COLOR=#a31515]"Excel 8.0"
[/COLOR][/SIZE][SIZE=2].Create()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2]taImportData = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter(strSQLString, glb_cnMM2007)
taImportData.Fill(dtImportData)
taUserImport = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#a31515]"SELECT * FROM User_Import"[/COLOR][/SIZE][SIZE=2], glb_cnMM2007)
cbUserImport = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommandBuilder(taUserImport)
taUserImport.Fill(dtUserImport)
dtUserImport.Rows.Clear()
taUserImport.Update(dtUserImport)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intX [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intX = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] dtImportData.Rows.Count - 1
drUserImport = dtUserImport.NewRow
drUserImport([/SIZE][SIZE=2][COLOR=#a31515]"dated"[/COLOR][/SIZE][SIZE=2]) = dtImportData.Rows(intX).Item(0)
drUserImport([/SIZE][SIZE=2][COLOR=#a31515]"tran_description"[/COLOR][/SIZE][SIZE=2]) = dtImportData.Rows(intX).Item(1)
drUserImport([/SIZE][SIZE=2][COLOR=#a31515]"credit"[/COLOR][/SIZE][SIZE=2]) = dtImportData.Rows(intX).Item(2)
drUserImport([/SIZE][SIZE=2][COLOR=#a31515]"debit"[/COLOR][/SIZE][SIZE=2]) = dtImportData.Rows(intX).Item(3)
dtUserImport.Rows.Add(drUserImport)
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]taUserImport.Update(dtUserImport)
[/SIZE]

Now this appears bleedin overly complicated for what I want, and although it imports an Excel spreadsheet (not csv as originally wanted) it does not import the very first row of the spreadsheet?

There are no column headings and even if I insert a blank row it still starts with the first row of data missing?

Come folkes, do you want me on my knees begging for help

 

[pachjo]

For those interested:

http://support.microsoft.com/kb/257819/en-gb

 

[cjard]

You know.. i'd have read the CSV data into my app and then uploaded it to SQLServer.. but if you got SQLServer to do it direct, then great

 

[pachjo]

lol, it was a long painful journey....but not wasted as it help me learn how to search and search and search without giving up