There has to be a better way

I'm running into a lot of problems with passing a lot of parameters from VB6 to a SQL server stored procedure.

The following code works but I feel like there is a better way to handle the data types...basically, I'm doing too much conversion.

The raw data is read from a text file and parsed into 2 different string arrays. I'm populating the database table from the string arrays.

I'm looking for a better way to do this b/c it's a pretty common occurence with the projects I'm working on right now.

Thanks for the help...and here's the code...

VB for creating the adodb.command object
********************************
Private Sub InsertHeader()
' Create ADO Command Object to handle sproc
' Execute Insert SQL statement

Dim cmdInfoHeader As ADODB.Command

'Create ADO Command Object to handle sproc
Set cmdInfoHeader = New ADODB.Command
With cmdInfoHeader
Set .ActiveConnection = g_cnDB
.CommandText = "usp_Insert_Header"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Model", adVariant, adParamInput, , astrHeader(0))
.Parameters.Append .CreateParameter("@PartNumber ", adVariant, adParamInput, , astrHeader(4))
.Parameters.Append .CreateParameter("@PCB", adVariant, adParamInput, , astrHeader(6))
.Parameters.Append .CreateParameter("@Weekday", adVariant, adParamInput, , astrTimeInfo(0))
.Parameters.Append .CreateParameter("@Month", adVariant, adParamInput, , astrTimeInfo(1))
.Parameters.Append .CreateParameter("@Date", adInteger, adParamInput, , astrTimeInfo(2))
.Parameters.Append .CreateParameter("@Time", adVariant, adParamInput, , astrTimeInfo(3))
.Parameters.Append .CreateParameter("@Year", adInteger, adParamInput, , astrTimeInfo(4))
.Parameters.Append .CreateParameter("@FileName", adVariant, adParamInput, , FileName)
.Parameters.Append .CreateParameter("@Line#", adInteger, adParamInput, , x)
End With

cmdInfoHeader.Execute

End Sub
********************************

And my Stored procedure

CREATE PROCEDURE usp_Insert_Header
@Model sql_variant,
@PartNumber sql_variant,
@PCB sql_variant,
@Weekday sql_variant,
@Month sql_variant,
@Date int,
@Time sql_variant,
@Year int,
@FileName sql_variant,
@Line# int

AS

INSERT INTO tblHeader
(
Model,
PartNumber ,
PCB,
Weekday,
[Month],
[Date],
[Time],
[Year],
FileName,
Line#
)
Values
(
convert(char(10),@Model),
convert(char(14),@PartNumber),
convert(char(8),@PCB),
convert(char(3),@Weekday),
convert(char(3),@Month),
@Date,
convert(char(10),@Time),
@Year,
convert(char(30),@FileName),
@Line#
)
GO

You could convert the data on your vb side so that when you pass it on ur stored procedure, its already the data type you want...

ex. Instead of using the sql_variant, you could directly put on the data type of it
-----------------------------
CREATE PROCEDURE usp_Insert_Header
@Model CHAR(10), -- or you could use VARCHAR(10)
@PartNumber CHAR(14), -- or VARCHAR(14)
... etc...
-----------------------------

then on your VB side...
-----------------------------
.Parameters.Append .CreateParameter("@Model", adVarChar, adParamInput, , cStr(astrHeader(0)))
-----------------------------

By the way, are the data you need to insert in your database already stored in the text file? if so, you could check out the BULK INSERT functionality