Write from TXT file to SQL table . Problem with strings/intergers

[garethn]

Im trying to write from a text file to a table in SQL . Ive dimmed everything correctly (i Think) but when it comes to write the line into the table the code doesnt accept my VALUE as a string....it thinks it is a number...so ignores what is in the text file (which is a char)

Private Type Fieldattr
Pos As Integer
Len As Integer
Value As String
End Type .......................

Dim ColumnAttr() As Fieldattr
Case "SALESPRICE"
ReDim ColumnAttr(2)
ColumnAttr(0).Pos = 1 'MaterialCode
ColumnAttr(0).Len = 18
ColumnAttr(1).Pos = 19 'List Price
ColumnAttr(1).Len = 11
ColumnAttr(2).Pos = 30 'Price Unit
ColumnAttr(2).Len = 5
End Select .......................


Case "SALESPRICE"
rst.Open "sp_qryAddSalesPriceRecord '" & ColumnAttr(0).Value & "','" _
& ColumnAttr(1).Value & "','" _
& ColumnAttr(2).Value & "'", adoConnection, adOpenDynamic, adLockReadOnly

End Select

 

[sunaj]

You haven't put anything in
ColumnAttr(0-2).Value
in the code you show.... Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[garethn]

While Not EOF(1)
Input #1, strLine

' I THINK THIS next LINE IS THE PROBLEM ** I need to figure out if its a val or a string some how ????

For x = 0 To UBound(ColumnAttr) 'MaxFields
ColumnAttr(x).Value = Val(Mid(strLine, ColumnAttr(x).Pos, ColumnAttr(x).Len))
Next x
rv = UpdateTable()
DoEvents
lblProgress.Width = lblProgress.Width + ProgressChunk
Wend
Close #1
lblProgress.BackColor = Me.BackColor

 

[sunaj]

Hi

You should not use val here. columnAttr().Value is 'Dim as string' - so it does not make sence to assing a numeric value to it (the string may contain a number though).

Try something like:

For x = 0 To UBound(ColumnAttr) 'MaxFields
ColumnAttr(x).Value = Mid$(strLine, ColumnAttr(x).Pos, ColumnAttr(x).Len)
Next x

Use the debugger (or debug.print) to test that the right text in inserted in columnAttr().value and the SQL statement is as you expect it to be:

SQL = "sp_qryAddSalesPriceRecord '" & ColumnAttr(0).Value & "','" _
& ColumnAttr(1).Value & "','" _
& ColumnAttr(2).Value & "'"

debug.print SQL
rst.Open SQL, adoConnection, adOpenDynamic, LockReadOnly

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[garethn]

thanks will try that