CODEWITHSUNDEEP
mysqlvba
Wizard
Wizard

Reputation: 11

Inserting data into MySQL database with VBA

I have a simple macro to insert data from Excel into a MySQL database: 

Dim conn As ADODB.Connection
Dim sqlstr As String
Dim rs As ADODB.Recordset
Dim Crow As Long, Lrow As Long
Dim Item As String, Price As Long, weight As Long, category As String

Set conn = New ADODB.Connection

conn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
                                            "SERVER=localhost;" & _
                                            "PORT=3306" & _
                                            "DATABASE=spendings;" & _
                                            "USER=root;" & _
                                            "PASSWORD=root;" & _
                                            "Option=3"


Set rs = New ADODB.Recordset

Lrow = Cells(Rows.Count, 1).End(xlUp).Row

 For Crow = Lrow To 2 Step -1

 Item = Sheets("Sheet1").Range("A" & Crow).Value
 Price = Sheets("Sheet1").Range("B" & Crow).Value
 weight = Sheets("Sheet1").Range("C" & Crow).Value
 category = Sheets("Sheet1").Range("D" & Crow).Value


 sqlstr = "INSERT INTO items VALUES('" & Item & "','" & Price & "','" & weight _
 & "', (SELECT idCategory FROM category WHERE Name='" & category & "'))"

 rs.Open sqlstr, conn, adOpenStatic

 Next

I am getting an error on the following line: 

rs.Open sqlstr, conn, adOpenStatic

and no idea how to proceed.

Upvotes: 1

Views: 9429

Answers (2)

Wizard
Wizard

Reputation: 11

Query was wrong. Thank you. Fixed it to

sqlstr = "INSERT INTO items (Name, Price, weight, store_Id, category_Id) VALUES('" & Item & "','" & Price & "','" & weight_
 & "', (SELECT idCategory FROM category WHERE Name='" & category & "'),'" & store & "');"

Copied into workbench and it works: mysql

But I am still getting the same error: run time error -2147217887(80040e21)

Upvotes: 0

bm13563
bm13563

Reputation: 698

As others have suggested, there is definitely a problem with your SQL string. Check out the syntax for the INSERT INTO SELECT statement here. Its often useful to test and debug your SQL strings in a database design tool such as MySQL Workbench before putting them into your code. You could also debug.print(sqlstr) to see what your string is returning e.g:

sqlstr = "INSERT INTO items ('" & Item & "','" & Price & "','" & weight _
& "', (SELECT idCategory FROM category WHERE Name='" & category & "'))"

debug.print(sqlstr)

rs.Open sqlstr, conn, adOpenStatic

I don't know your database structure but I can see some syntax errors in your string. You don't need VALUES before your columns (see above documentation). Also, you're trying insert data into 3 columns:

INSERT INTO items VALUES('" & Item & "','" & Price & "','" & weight _ & "')

but your SELECT query is only returning 1 column:

SELECT idCategory FROM category WHERE Name='" & category & "'.

Posting the debug.print output might help with further debugging.

Upvotes: 2

Related Questions