I have a simple web application in vb.net (on .net 2.0) that takes data from an excel sheet and stores it into a sql server database using typed datasets.
My problem is in the follwing code. This is an event handler for the "Import into SQL" button, after the excel file has been uploaded.
Protected
Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonImport.Click
PanelUpload.Visible =
False
PanelView.Visible =
False
PanelImport.Visible =
True
' retrieve the Select Command for the worksheet data
Dim objCommand As New OleDbCommand()
objCommand = ExcelConnection()
' create a DataReader
Dim reader As OleDbDataReader
reader = objCommand.ExecuteReader()
Dim counter As Integer = 0
While reader.Read()
Dim ORIGINATOR_ID As String = Convert.ToString(reader("LBF_PREFIX"))
'Dim SMPL_DTE As Integer = Convert.ToInt64reader("LBF_DATE")
'Dim LAB_NUM As Double = Convert.ToDouble(reader("LBF_LAB_NUM"))
Dim LAB_NUM As Double = "1"
'''I get a IndexOutOfRangeException right here, or where I try to read any row into its respective variable to be passed on to an import function. In the line above, if I would try to read the column value into LAB_NUM for instance,
Dim LAB_NUM as double = Convert.ToDouble(reader("LBF_LAB_NUM")) as I did in the commented code above it, I get the same error. What am I doing wrong here??? I have read over the suggestions and tips and they keep hinting at how this happens when the index is larger than the max index in the list, or if its a negative index, or when the column names are not defined right, but I'm still stumped:( Any help would be most appreciated.
Thanks,
'REST OF THE METHOD
--->>>>Dim TURN As String = Convert.ToString(reader("LBF_TURN"))
Dim SMPL_ANALYS_DTE As Integer = Convert.ToInt64(reader("ANALYS_HOUR"))
Dim SMPL_ANALYS_MIN As Integer = Convert.ToInt64(reader("ANALYS_MIN"))
'ADD THE NEXT COLUMN "ANALYS_MIN" AS "ANALYS_MIN TO SQL TABLE'
'Or append hour and min before inputting it into SMPL_ANALYS_DTE
'Dim SMPL_ANALYS_DTE As String = Convert.ToString(reader("ANALYS_MIN"))
...
...
...
...
Dim STABILITY As Double = Convert.ToDouble(reader("STABILITY"))
Dim HARDNESS As Double = Convert.ToDouble(reader("HARDNESS"))
Dim QRT_TUMBLE As Double = Convert.ToDouble(reader("QRT_TUMBLE"))
Dim TUMBLE_30M As Double = Convert.ToDouble(reader("30M_TUMBLE"))
Dim APTSPC_GRAV As Double = Convert.ToDouble(reader("APTSPC_GRAV"))
Dim FREE_SWL_I As Double = Convert.ToDouble(reader("FREE_SWL_I"))
Dim PULV8TH As Double = Convert.ToDouble(reader("8TH_PULV"))
Dim QRT_PULV As Double = Convert.ToDouble(reader("QRT_PULV"))
Dim SCR_4 As Double = Convert.ToDouble(reader("SCR_4"))
...
...
...
...
'Ensure the import data includes an ORIGINATOR_ID for each member
'If ORIGINATOR_ID.Length = 0 Then
'LabelImport.Text &= "You must provide an ORIGINATOR_ID!"
'End If
ImportIntoMembers(ORIGINATOR_ID, SMPL_DTE, LAB_NUM, TURN, SMPL_ANALYS_DTE, _
MOIS_GRAV, MOISTURE, S, VM, ASH, STABILITY, HARDNESS, QRT_TUMBLE, TUMBLE_30M, _
APTSPC_GRAV, FREE_SWL_I, PULV8TH, QRT_PULV, SCR_4, SCR_3, SCR_2, SCR_1NHALF, _
SCR_1, SCR_3QRT, SCR_HALF, SCR_38THS, SCR_QRT, SCR_8TH, SCR_20M, SCR_30M, SCR_50M, _
SCR_100M, SCR_PAN, SCR_QRT_PLUS, SAMPL_LOC)
End While
reader.Close()
End Sub