Conversion failed when converting the nvarchar value 'XXXX' to data type int. How to solve?

I have a workbook with a sheet that extract data from SQL and copy it into a table using VBA. Here is the SQL Statement used:

sSQLSting = "SELECT DayID, DieID, ActualQty FROM [tiMEMSnew].[dbo].[tblDataBdy] order by DieID, DayID desc

Open in new window


When testing the code, it works and it can copy all data into Excel. Now I want to change the code so that it can read a cell value for DieID and will show the display based of the cell value. Using this statement:

sSQLSting = "SELECT DayID, DieID, ActualQty FROM [tiMEMSnew].[dbo].[tblDataBdy] where DieID = " & Range("g4") & " order by DayID desc"

Open in new window


Note that for DieID in SQL I have values that are numbers and some that are a mixture of numbers and letters.

However when I tried it I get the Conversion Failed error as seen below:

Capture11.PNG
I don't quite understand why now its causing an issue when my 1st code was working just fine... Please help.

Here is the full code for your reference. Code is placed inside sheet.

Sub Main()

On Error GoTo EH
  
    'Step 1: Create the Connection String with Provider and Data Source options
    Dim sSQLQry As String
    Dim ReturnArray
    
    Dim Conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    Dim DBPath As String, sconnect As String
    
    sconnect = "driver={SQL Server};server=servername;database=db;uid=sa;pwd=abcd1111"
    
    'Step 2: Open the Connection to data source
    Conn.Open sconnect
    
    'Step 3: Create SQL Command String
    'sSQLSting = "SELECT DayID, DieID, ActualQty FROM [tiMEMSnew].[dbo].[tblDataBdy] order by DieID, DayID desc"
    sSQLSting = "SELECT DayID, DieID, ActualQty FROM [tiMEMSnew].[dbo].[tblDataBdy] where DieID = " & Range("g4") & " order by DayID desc"
  
    'Step 4: Get the records by Opening this Query with in the Connected data source
       rs.Open sSQLSting, Conn

    'Step 5: Copy the reords into our worksheet
        Call DeleteTableRows 'delete old data
        Sheet4.Range("A2").CopyFromRecordset rs
 
    'Step 6: Close the Record Set and Connection
           'Close Recordset
            rs.Close

          'Close Connection
           Conn.Close
           
           Exit Sub
EH:

     MsgBox Err.Description
     
End Sub

Sub DeleteTableRows()

    With Sheet4.ListObjects("Table2")
    
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
        
    End With
    
End Sub

Open in new window

Hans J.HauAsked:
Who is Participating?
 
Ryan ChongCommented:
Note that for DieID in SQL I have values that are numbers and some that are a mixture of numbers and letters.

quick try, you need single quotes for alpha-numeric values:

change it to:

sSQLSting = "SELECT DayID, DieID, ActualQty FROM [tiMEMSnew].[dbo].[tblDataBdy] where DieID = '" & replace(Range("g4"),"'","''") & "' order by DayID desc"
0
 
Hans J.HauAuthor Commented:
Thanks. It works now. I also find that using

DieID ='" & Range("g4").Value & "'

Open in new window


works just as well.

Thanks for the suggestion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.