Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error converting data type varchar to bigint. Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:52 AM
Points: 8, Visits: 35
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
Post #1530628
Posted Tuesday, January 14, 2014 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1530641
Posted Tuesday, January 14, 2014 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:52 AM
Points: 8, Visits: 35
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
Post #1530644
Posted Tuesday, January 14, 2014 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
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.

Post #1530654
Posted Tuesday, January 14, 2014 8:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1530699
Posted Tuesday, January 14, 2014 11:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 341, Visits: 2,461
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!
Post #1530800
Posted Wednesday, January 15, 2014 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
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.

Post #1530969
Posted Thursday, January 16, 2014 1:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:52 AM
Points: 8, Visits: 35
Thanks so much for all responses and your help
Problem solved.......
.........
Post #1531457
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse