Error converting data type varchar to bigint.

Question

Post reply

Error converting data type varchar to bigint.

lu - Si

Old Hand

Points: 312
More actions
January 14, 2014 at 5:31 am

#303006

Hi
Can someone help me... i get the error ---> Error converting data type varchar to bigint.
when i write the code below
thanks in advance
Regards,
sqlcmd = "Create table #Eft_Transfer( " & vbCrLf _
& " EFT_No varchar(20), null , " & vbCrLf _
& " Effective_Date datetime , " & vbCrLf _
& " Service_Code Varchar(10) , " & vbCrLf _
& " Reference_Number Varchar(30)) " & vbCrLf
With grdCreateGrid
For lngRowCount = 1 To .MaxRows
.GetText(ConstgrdCEftNum, lngRowCount, varValue)
strEftNo = varValue
.GetText(constgrdCEffectiveDte, lngRowCount, varValue)
strDate = varValue
.GetText(constgrdCServiceCodeCode, lngRowCount, varValue)
strCode = varValue
.GetText(constgrdCRefNo, lngRowCount, varValue)
strReference = varValue
sqlcmd = sqlcmd & _
" insert into #Eft_Transfer values ('" & strEftNo & "','" & strDate & "','" & strCode & "','" & strReference & "')" & vbCrLf
Next lngRowCount
End With
sqlcmd = sqlcmd & "Execute TransferFileCreate '" & txtFileName.Text & "'" & vbCrLf _
& "Drop Table #Eft_Transfer"
' Debug.Print sqlcmd
' GoTo ErrorHandler
If objGeneral.Execute32(sqlcmd, SqlResults, SqlDataRowss, False, False, True) <> -1 Then GoTo ErrorHandler

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

I assume the error is genereated in the following line (can you confirm?)

sqlcmd = sqlcmd & _

" insert into #Eft_Transfer values ('" & strEftNo & "','" & strDate & "','" & strCode & "','" & strReference & "')" & vbCrLf

The error indicates a string value is entered in a field (or variable) with the BIGINT datatype. It looks like one of your string-values doesn't contain a value that can be converted to a integer vale. To troubleshoot you can print the values just before inserting them. That way you can manually check if the values are correct.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

lu - Si Old Hand Points: 312 More actions · Answer 2

Thanks for responding

Yes the error happens there

it does get the correct value

which is : strEFTNo = 'EFT0000000220'

But i wants this as numeric

and my

SELECT ISNUMERIC('EFT0000000220')

Results = 0 instead of 1

i tried casting and converting the strEft but i haven't come right

David Burrows SSC Guru Points: 65143 More actions · Answer 3

You cannot cast 'EFT0000000220' to bigint.

You can cast the numerical part, ie CAST(SUBSTRING('EFT0000000220',4,10) as bigint)

Far away is close at hand in the images of elsewhere.
Anon.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

David Burrows (1/14/2014)
You cannot cast 'EFT0000000220' to bigint.
You can cast the numerical part, ie CAST(SUBSTRING('EFT0000000220',4,10) as bigint)

What David is saying: the text part "EFT" can not be converted to a numeric value. Therefor you have to remove these characters from the value (using the SUBSTRING function). The remaing text represents a value tht can be converted to a numeric (BIGINT) value.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

x SSC-Insane Points: 23660 More actions · Answer 5

In this snippet:

sqlcmd = "Create table #Eft_Transfer( " & vbCrLf _

& " EFT_No varchar(20), null , " & vbCrLf _

& " Effective_Date datetime , " & vbCrLf _

& " Service_Code Varchar(10) , " & vbCrLf _

& " Reference_Number Varchar(30)) " & vbCrLf

you have an extra comma between the "EFT_No varchar(20)" and "null" and that might create unexpected results. That might just have been an error in posting though!

David Burrows SSC Guru Points: 65143 More actions · Answer 6

patrickmcginnis59 10839 (1/14/2014)
In this snippet:
sqlcmd = "Create table #Eft_Transfer( " & vbCrLf _
& " EFT_No varchar(20), null , " & vbCrLf _
& " Effective_Date datetime , " & vbCrLf _
& " Service_Code Varchar(10) , " & vbCrLf _
& " Reference_Number Varchar(30)) " & vbCrLf
you have an extra comma between the "EFT_No varchar(20)" and "null" and that might create unexpected results. That might just have been an error in posting though!

Yes otherwise the sql would fail in parsing not execution, notwithstanding, the code and sql is OK.

I suspect the error is in the procedure TransferFileCreate

Far away is close at hand in the images of elsewhere.
Anon.

lu - Si Old Hand Points: 312 More actions · Answer 7

Thanks so much for all responses and your help

Problem solved.......

:-D:-D:-D:-D.........