By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Run-time error in excel update to sql

P: 4
Hello,
I am new to Excel and SQL server, so please be patient with me. I have written code with the help of others to update a table [CUSTINFO] in a sql database [PMCustomers] from an excel form.

The connection code works as I have used it for a [SELECT] query before.

Here is the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton3_Click()
  2. 'to Update or Insert new into the form
  3.  
  4. ' Create a connection object.
  5. Dim cnPMCustomers As ADODB.Connection
  6. Set cnPMCustomers = New ADODB.Connection
  7.  
  8. ' Provide the connection string.
  9. Dim strConn As String
  10.  
  11. 'Use the SQL Server OLE DB Provider.
  12. strConn = "PROVIDER=SQLOLEDB;"
  13.  
  14. 'Connect to the PMCustomers database on the local server.
  15. strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=PMCustomers;"
  16.  
  17. 'Use an integrated login.
  18. strConn = strConn & " INTEGRATED SECURITY=sspi;"
  19.  
  20. 'Now open the connection.
  21. cnPMCustomers.Open strConn
  22.  
  23. ' declare the variable, strPhoneNum as a string
  24. Dim strPhoneNum As String
  25.  
  26. ' Direct variable name where to get value
  27. strPhoneNum = ActiveWorkbook.Sheets("Sales Invoice").Range("B10").Value
  28. Debug.Print (strPhoneNum)
  29.  
  30. ' declare strSQL as a string
  31. Dim strSQL As String
  32.  
  33. ' create the columnname to columnvalue pairs in the UPDATE statement using
  34. ' the passed parameter PhoneNum in the WHERE statement
  35. strSQL = "UPDATE PMCustomers.CUSTINFO SET "
  36. strSQL = strSQL & "Name = '" & Worksheets("Sales Invoice").Range("B9").Value & "', "
  37. strSQL = strSQL & "CompanyName = '" & Worksheets("Sales Invoice").Range("E9").Value & "', "
  38. strSQL = strSQL & "Phone = '" & Worksheets("Sales Invoice").Range("B10").Value & "', "
  39. strSQL = strSQL & "CellPhone = '" & Worksheets("Sales Invoice").Range("E10").Value & "', "
  40. strSQL = strSQL & "BillToAddress1 = '" & Worksheets("Sales Invoice").Range("B13").Value & "', "
  41. strSQL = strSQL & "BillToAddress2 = '" & Worksheets("Sales Invoice").Range("B14").Value & "', "
  42. strSQL = strSQL & "BillToCity = '" & Worksheets("Sales Invoice").Range("B15").Value & "', "
  43. strSQL = strSQL & "BillToState = '" & Worksheets("Sales Invoice").Range("B16").Value & "', "
  44. strSQL = strSQL & "BillToZip = '" & Worksheets("Sales Invoice").Range("B17").Value & "', "
  45. strSQL = strSQL & "Country = '" & Worksheets("Sales Invoice").Range("B18").Value & "', "
  46. strSQL = strSQL & "ShipToAddress1 = '" & Worksheets("Sales Invoice").Range("F13").Value & "', "
  47. strSQL = strSQL & "ShipToAddress2 = '" & Worksheets("Sales Invoice").Range("F14").Value & "', "
  48. strSQL = strSQL & "ShipToCity = '" & Worksheets("Sales Invoice").Range("F15").Value & "', "
  49. strSQL = strSQL & "ShipToState = '" & Worksheets("Sales Invoice").Range("F16").Value & "', "
  50. strSQL = strSQL & "ShipToZip = '" & Worksheets("Sales Invoice").Range("F17").Value & "', "
  51. strSQL = strSQL & "ContactPhone = '" & Worksheets("Sales Invoice").Range("F18").Value & "', "
  52. strSQL = strSQL & "PaymentType = '" & Worksheets("Sales Invoice").Range("A21").Value & "', "
  53. strSQL = strSQL & "CCNumber = '" & Worksheets("Sales Invoice").Range("C21").Value & "', "
  54. strSQL = strSQL & "CCExpireDate = '" & Worksheets("Sales Invoice").Range("E21").Value & "', "
  55. strSQL = strSQL & "Email = '" & Worksheets("Sales Invoice").Range("C23").Value & "'"
  56. strSQL = strSQL & " Where Phone = '" & strPhoneNum & "'"
  57.  
  58. Debug.Print (strSQL)
  59. cnPMCustomers.Execute strSQL
  60. ' close connection
  61. cnPMCustomers.Close
  62. End Sub
  63.  
I am getting a Run-time error '-2147217900(80040e14) Automation error
when it is run. The database has a test line in it that I use so I know that the data is there.

Could someone please help me get this update to work?

Thank you for your help in advance,

Deirdre
Sep 24 '07 #1
Share this Question
Share on Google+
2 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

U r updating based on the Phone Number, are u sure it is unique.
usually that error is due to duplication of a Field Value.. Check the PK/Unique key of the table, check if it is being repeated with ur update query...

Regards
Veena
Sep 24 '07 #2

P: 4
Hello Veena, and thank you for responding!!

There is only one entry in the db that has the phone number in it. I realized that I have not included some information, so here goes....

When I step through the code, I get the following in the immederate window

Expand|Select|Wrap|Line Numbers
  1. 1-111-222-3333
  2. 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'
  3.  
  4.  
I removed an .execute line that was in the wrong place, and now I get this error Run-time error '-2147217900(80040e37) Automation error


The phone number 1-111-222-3333 is the test line that I have in the db and it is the only one like that.

Any ideas?

Deirdre
Sep 24 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.