Excel Run-time error for database update

[RTFentem]

Hello everyone,

I am new to excel and sql server. I have created a sub with the help of others to update a table called CUSTINFO in a database called PMCustomers

here is the code

Private Sub CommandButton3_Click()
'to Update or Insert new into the form
 
' Create a connection object.
Dim cnPMCustomers As ADODB.Connection
Set cnPMCustomers = New ADODB.Connection
 
' Provide the connection string.
Dim strConn As String
 
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
 
'Connect to the PMCustomers database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=PMCustomers;"
 
'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
'Now open the connection.
cnPMCustomers.Open strConn
 
' declare the variable, strPhoneNum as a string
Dim strPhoneNum As String
 
' Direct variable name where to get value
strPhoneNum = ActiveWorkbook.Sheets("Sales Invoice").Range("B10").Value
Debug.Print (strPhoneNum)
 
' declare strSQL as a string
Dim strSQL As String
 
' create the columnname to columnvalue pairs in the UPDATE statement using
' the passed parameter PhoneNum in the WHERE statement
strSQL = "UPDATE PMCustomers.CUSTINFO SET "
strSQL = strSQL & "Name = '" & Worksheets("Sales Invoice").Range("B9").Value & "', "
strSQL = strSQL & "CompanyName = '" & Worksheets("Sales Invoice").Range("E9").Value & "', "
strSQL = strSQL & "Phone = '" & Worksheets("Sales Invoice").Range("B10").Value & "', "
strSQL = strSQL & "CellPhone = '" & Worksheets("Sales Invoice").Range("E10").Value & "', "
strSQL = strSQL & "BillToAddress1 = '" & Worksheets("Sales Invoice").Range("B13").Value & "', "
strSQL = strSQL & "BillToAddress2 = '" & Worksheets("Sales Invoice").Range("B14").Value & "', "
strSQL = strSQL & "BillToCity = '" & Worksheets("Sales Invoice").Range("B15").Value & "', "
strSQL = strSQL & "BillToState = '" & Worksheets("Sales Invoice").Range("B16").Value & "', "
strSQL = strSQL & "BillToZip = '" & Worksheets("Sales Invoice").Range("B17").Value & "', "
strSQL = strSQL & "Country = '" & Worksheets("Sales Invoice").Range("B18").Value & "', "
strSQL = strSQL & "ShipToAddress1 = '" & Worksheets("Sales Invoice").Range("F13").Value & "', "
strSQL = strSQL & "ShipToAddress2 = '" & Worksheets("Sales Invoice").Range("F14").Value & "', "
strSQL = strSQL & "ShipToCity = '" & Worksheets("Sales Invoice").Range("F15").Value & "', "
strSQL = strSQL & "ShipToState = '" & Worksheets("Sales Invoice").Range("F16").Value & "', "
strSQL = strSQL & "ShipToZip = '" & Worksheets("Sales Invoice").Range("F17").Value & "', "
strSQL = strSQL & "ContactPhone = '" & Worksheets("Sales Invoice").Range("F18").Value & "', "
strSQL = strSQL & "PaymentType = '" & Worksheets("Sales Invoice").Range("A21").Value & "', "
strSQL = strSQL & "CCNumber = '" & Worksheets("Sales Invoice").Range("C21").Value & "', "
strSQL = strSQL & "CCExpireDate = '" & Worksheets("Sales Invoice").Range("E21").Value & "', "
strSQL = strSQL & "Email = '" & Worksheets("Sales Invoice").Range("C23").Value & "'"
strSQL = strSQL & " Where Phone = '" & strPhoneNum & "'"
 
Debug.Print (strSQL)
cnPMCustomers.Execute strSQL
' close connection
cnPMCustomers.Close
End Sub

I get a Run-time error '-2147217900(80040e37) Automation error
when I step through the code. I get this output in the immediate window

1-111-222-3333
UPDATE PMCustomers.CUSTINFO SET Name = 'John Q2 Test1', CompanyName = 'Q''s 4Wheel Toys', Phone = '1-111-222-3333', CellPhone = '1-222-333-4444', BillToAddress1 = '123 Somewhere Street', BillToAddress2 = 'Building 99', BillToCity = 'Somecity1', BillToState = 'Somestate1', BillToZip = '12345-54321', Country = 'USA', ShipToAddress1 = 'Ship to Street1', ShipToAddress2 = 'Depot 11', ShipToCity = 'Shipcity1', ShipToState = 'Shipstate1', ShipToZip = '54321-12345', ContactPhone = '1-333-444-5555', PaymentType = 'Visa', CCNumber = '1234-5678-9012-3456 / 123', CCExpireDate = '09/2009', Email = 'someone@someplace.net' Where Phone = '1-111-222-3333'

I really need to get this working as my job is on the line because of it.

Thanks
R

 

[Richard Schollar]

Hi

I suspect it has something to do with the doubled up single quotes you appear to have in CompanyName = 'Q''s 4Wheel Toys'. I would try inputting it without the punctuation in the string (ie 'Qs 4 Wheel Toys' instead) and see if it works any better. It would be worth going thru the rest of the values to ensure you aren't getting any other string terminators in the SQL.

 

[RTFentem]

I removed the single quote from both the excel form and in the database, but I still get the same runtime error. It occurs at the

cnPMCustomers.Execute strSQL

I also went through the rest of the code and did not find any other single quotes

R

 

[RTFentem]

I removed the single quote from both the excel form and in the database, but I still get the same runtime error. It occurs at the

cnPMCustomers.Execute strSQL

I also went through the rest of the code and did not find any other single quotes

R

 

[Richard Schollar]

Are you sure you have the write format for data in each of those fields? Things like the phone number actually do have '-' in them? The dashes are not just the result of an input mask in the table (ie purely presentational)?

 

[RTFentem]

Hello Richard,
I physically typed the test line of data into the database using the "-". The end user will also be using the "-" when they type in the phone number. the column in the table is formated as a varchar.

Ideas?
R