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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.