W
Winshent
I have some code which attempts to load the contents of a dataset into
a sql server db.
the code fills the dataadapter with the datset no prob, but fails on
'ExecuteNonQuery'.
the function arr here returns the following where 'XLData' is my
dataset.
arr(1) = INSERT INTO UP_1_Results_Hourly ( SessionID, mDate, HourID,
LineID, CallVolume, CallLength ) SELECT 1, [XLData].[Date],
[XLData].[HourID], [XLData].[LineID], [XLData].[CallVolume],
[XLData].[CallLength] FROM XLData
Private Sub PopTempTables(ByVal XlPathFileName As String)
Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; "Data
Source=" & XlPathFileName & ";" & "Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(sConnString)
objConn.Open()
Dim str As String = "SELECT * FROM " & arr(2)
Dim objCmdSelect As New OleDbCommand(str, objConn)
Dim xlDA As New OleDbDataAdapter
xlDA.SelectCommand = objCmdSelect
DataSetName = "XLData"
Dim objDS As New DataSet(DataSetName)
xlDA.Fill(objDS, DataSetName)
Dim sqlCmd As New Data.SqlClient.SqlCommand(arr(1)
sqlCmd.Connection = myConnection
sqlCmd.Connection.Open()
sqlCmd.ExecuteNonQuery() '**** FAILS HERE***
objConn.Close()
End Sub
The error is Invalid Object Name 'XLData'
any ideas?
I am still new to dotnet, and only spend about 10% of my time on this
project.. help would be much appreciated..
a sql server db.
the code fills the dataadapter with the datset no prob, but fails on
'ExecuteNonQuery'.
the function arr here returns the following where 'XLData' is my
dataset.
arr(1) = INSERT INTO UP_1_Results_Hourly ( SessionID, mDate, HourID,
LineID, CallVolume, CallLength ) SELECT 1, [XLData].[Date],
[XLData].[HourID], [XLData].[LineID], [XLData].[CallVolume],
[XLData].[CallLength] FROM XLData
Private Sub PopTempTables(ByVal XlPathFileName As String)
Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; "Data
Source=" & XlPathFileName & ";" & "Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(sConnString)
objConn.Open()
Dim str As String = "SELECT * FROM " & arr(2)
Dim objCmdSelect As New OleDbCommand(str, objConn)
Dim xlDA As New OleDbDataAdapter
xlDA.SelectCommand = objCmdSelect
DataSetName = "XLData"
Dim objDS As New DataSet(DataSetName)
xlDA.Fill(objDS, DataSetName)
Dim sqlCmd As New Data.SqlClient.SqlCommand(arr(1)
sqlCmd.Connection = myConnection
sqlCmd.Connection.Open()
sqlCmd.ExecuteNonQuery() '**** FAILS HERE***
objConn.Close()
End Sub
The error is Invalid Object Name 'XLData'
any ideas?
I am still new to dotnet, and only spend about 10% of my time on this
project.. help would be much appreciated..