Error converting data type varchar to bigint.

  • 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

  • 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’! **
  • 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

  • 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.

  • 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’! **
  • 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!

  • 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.

  • Thanks so much for all responses and your help

    Problem solved.......

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

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

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