Reading CSV file via COM+ & ASP

[dschumann]

Hello,

I am creating a COM+ object which will read a csv file that has been up loaded via an ASP page.

I am able to read the file line by line and no errors are generated but when I look in the table only one line was inserted via the stroed proc. When I step through the code I can see that all lines are being read (only 2 lines at the moment).

I am inserting this via a stored proc using SQL Server 2000.

Below is what I have as of now.

**** Code in ActiveX dll ****
Option Explicit

Private m_FilePath As String
Private m_StoredProcName As String
Private m_ConnString As String

Public Property Get FilePath() As String
FilePath = m_FilePath
End Property

Public Property Let FilePath(ByVal rhs As String)
m_FilePath = rhs
End Property

Public Property Get StoredProcName() As String
StoredProcName = m_StoredProcName
End Property

Public Property Let StoredProcName(ByVal rhs As String)
m_StoredProcName = rhs
End Property

Public Property Get ConnString() As String
ConnString = m_ConnString
End Property

Public Property Let ConnString(ByVal rhs As String)
m_ConnString = rhs
End Property

Public Function FileDataTransfer() As Boolean
Dim objFs As FileSystemObject
Dim objStream As TextStream
Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim strLineRead As String
Dim arrLineArray() As String

Set objFs = New FileSystemObject
Set objStream = objFs.OpenTextFile(m_FilePath, ForReading)
Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command

objCon.CursorLocation = adUseClient
objCon.Open m_ConnString

strLineRead = objStream.ReadLine

With objCom
.ActiveConnection = objCon
.CommandText = m_StoredProcName
.CommandType = adCmdStoredProc
End With

Do While objStream.AtEndOfLine <> True
strLineRead = objStream.ReadLine
arrLineArray = Split(strLineRead, ",")
With objCom
.Parameters.Append .CreateParameter("@RaceDate", adDate, adParamInput, 8, arrLineArray(0))
.Parameters.Append .CreateParameter("@Location", adVarChar, adParamInput, 100, arrLineArray(1))
.Parameters.Append .CreateParameter("@Driver", adVarChar, adParamInput, 50, arrLineArray(2))
.Parameters.Append .CreateParameter("@Round1", adVarChar, adParamInput, 50, arrLineArray(3))
.Parameters.Append .CreateParameter("@Round2", adVarChar, adParamInput, 50, arrLineArray(4))
.Parameters.Append .CreateParameter("@Round3", adVarChar, adParamInput, 50, arrLineArray(5))
.Parameters.Append .CreateParameter("@Round4", adVarChar, adParamInput, 50, arrLineArray(6))
.Parameters.Append .CreateParameter("@Outcome", adVarChar, adParamInput, 1, arrLineArray(7))
End With
objCom.Execute , , adExecuteNoRecords
On Error Resume Next
If Err.Number <> 0 Then
FileDataTransfer = False
Else
FileDataTransfer = True
End If
Loop
End Function

**** SQl Script for SQL Server ****
CREATE DATABASE [RaceReults]
GO

use [RaceReults]
GO

CREATE TABLE [dbo].[results] (
[RaceDate] [datetime] (8),
[Location] [varchar] (100),
[Driver] [varchar] (50),
[Round1] [varchar] (50),
[Round2] [varchar] (50),
[Round3] [varchar] (50),
[Round4] [varchar] (50),
[Outcome] [varchar] (1)
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [stoInsertRaceResults]
(@RaceDate [datetime](8),
@Location [varchar](100),
@Driver [varchar](50),
@Round1[varchar](50),
@Round2[varchar](50),
@Round3 [varchar](50),
@Round4 [varchar](50),
@Outcome [varchar](1))

AS INSERT INTO [RaceReults].[dbo].[results]
( [RaceDate],
[Location],
[Driver],
[Round1],
[Round2],
[Round3],
[Round4],
[Outcome])

VALUES
( @RaceDate,
@Location,
@Driver,
@Round1,
@Round2,
@Round3,
@Round4,
@Outcome)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

**** Content of CSV file ****
RaceDate,Location,Driver,Round1,Round2,Round3,Round4,Outcome
11/15/2003,Palmdale,Bill Cramer,8.33 167.59,NULL,NULL,NULL,W
11/15/2003,Palmdale,Aaron Pine,8.35 166.85,NULL,NULL,NULL,L

Once again, all I am able to insert is the first line even though it reads all lines and no errors are generated.

Any help would be appreciated.

Thanks,
Doug

[aspdaddy]

Two lines will be read but only the second is being inserted.

Looks like you are missing a loop.

>Do While objStream.AtEndOfLine <> True

This reads the words on one line, but theres no outer loop to read the lines in the file

[dschumann]

I have things working now.

strLineRead = objStream.ReadLine <-- Reads column names

With objCom
.ActiveConnection = objCon
.CommandText = m_StoredProcName
.CommandType = adCmdStoredProc
End With

Do While objStream.AtEndOfStream <> True

strLineRead = objStream.ReadLine <-- Reads first row of data
arrLineArray = Split(strLineRead, ",")

With objCom
.Parameters.Refresh
.Parameters("@RaceDate").Value = arrLineArray(0)
.Parameters("@Location").Value = arrLineArray(1)
.Parameters("@Driver").Value = arrLineArray(2)
.Parameters("@Class").Value = arrLineArray(3)
.Parameters("@Round1ET").Value = arrLineArray(4)
.Parameters("@Round1Spd").Value = arrLineArray(5)
.Parameters("@Round2ET").Value = arrLineArray(6)
.Parameters("@Round2Spd").Value = arrLineArray(7)
.Parameters("@Round3ET").Value = arrLineArray(8)
.Parameters("@Round3Spd").Value = arrLineArray(9)
.Parameters("@Round4ET").Value = arrLineArray(10)
.Parameters("@Round4Spd").Value = arrLineArray(11)
.Parameters("@Outcome").Value = arrLineArray(12)
End With

objCom.Execute , , adExecuteNoRecords

On Error Resume Next
If Err.Number <> 0 Then
FileDataTransfer = False
Else
FileDataTransfer = True
End If

Loop <-- **** LOOP ****

Basically my previous code was retaining what was origianlly being appended. So I am doing a refresh and passing the values in.