VBA update SQL Error Converting varchar to data type numeric

Question

I'm trying to update a SQL table from Excel using VBA. Here is my code:

Private Sub cmdImport_Click()
On Error GoTo ErrExit

Dim cn_ADO As ADODB.Connection
Dim cmd_ADO As ADODB.Command
    
Dim SQLUser As String
Dim SQLPassword As String
Dim SQLServer As String
Dim DBName As String
Dim DbConn As String

Dim SQLQuery As String
Dim strWhere As String

'Dim strStatus As String
Dim i As Integer
'Dim j As Integer
Dim jOffset As Integer
Dim iStartRow As Integer
'Dim iStep As Integer

'Data Columns
Dim strMaterialNo As String
Dim strMaterialName As String
    
'iStep = 100
jOffset = 4
iStartRow = 9
i = iStartRow

SQLUser = "sa"
SQLPassword = "password"
SQLServer = "AZSQLSERVER"
DBName = "MAS_SQL"

DbConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & SQLUser & ";Password=" & SQLPassword & ";Initial Catalog=" & DBName & ";" & _
        "Data Source=" & SQLServer & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
        "Use Encryption for Data=False;Tag with column collation when possible=False"

Set cn_ADO = New ADODB.Connection
cn_ADO.Open DbConn

Set cmd_ADO = New ADODB.Command

While Cells(i, jOffset).Value <> ""
    strMaterialNo = Cells(i, 0 + jOffset).Value
    strMaterialName = Cells(i, 1 + jOffset).Value
    
    strWhere = "[Material Number] = " & strMaterialNo
    
    SQLQuery = "update dbo.ListPrices " & _
               "set " & _
               "[Material Name] =  '" & strMaterialName & "' " & _
               "where " & strWhere
               
    cmd_ADO.CommandText = SQLQuery
    cmd_ADO.ActiveConnection = cn_ADO
    cmd_ADO.Execute
    
    i = i + 1
Wend

Set cmd_ADO = Nothing
Set cn_ADO = Nothing

Exit Sub

ErrExit:
        MsgBox "Error: " & Err & " " & Error(Err)
        Application.StatusBar = False
        Application.Cursor = xlDefault

        If Not cn_ADO Is Nothing Then
            Set cn_ADO = Nothing
        End If
        If Not cmd_ADO Is Nothing Then
            Set cmd_ADO = Nothing
        End If

  End Sub

When I try to update the Material Name I get an error Arithmetic overflow error converting varchar to data type numeric. My VBA variable is a String going to a SQL VARCHAR(50), and I've made sure that the character count is not over 50. Any help would be appreciated.

What column type is [Material Number] ? Sounds like the error is there and not in the Name part of the query — Tim Williams
– Tim Williams, Commented Feb 15, 2021 at 18:33
Double quotes? If I do that is says invalid column name. I see what you mean though. — Lars Hovden
– Lars Hovden, Commented Feb 15, 2021 at 19:02
No - single quotes, as you're already doing for strMaterialName — Tim Williams
– Tim Williams, Commented Feb 15, 2021 at 19:03

Tim Williams · Accepted Answer · 2021-02-15 19:03:24Z

2

Values intended for character fields need quotes as you have already for strMaterialName:


    strWhere = "[Material Number] = '" & strMaterialNo & "'"
    
    SQLQuery = "update dbo.ListPrices " & _
               "set " & _
               "[Material Name] =  '" & strMaterialName & "' " & _
               "where " & strWhere

edited Feb 15, 2021 at 19:03

answered Feb 15, 2021 at 18:38

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lars Hovden Over a year ago

I changed it to that and it doesn't give an error, but doesn't update the table either.

braX Over a year ago

Double check the value of strMaterialNo then. Open the table manually and verify it exists.

Collectives™ on Stack Overflow

VBA update SQL Error Converting varchar to data type numeric

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related