VBA not handling end of For...Next when inserting into SQL

Hi Experts -

I have a spreadsheet that is looking at a concatenated list of values in column c and inserting them into a SQL table.  It's working great until it hits the first empty cell in C.  It's trying to send a null value to the table, so I'm getting this error:  error pic
Here's the code:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cell As Range
Dim con As Object
'connect and clear existing data in table
Set con = CreateObject("adodb.connection")
    con.connectionstring = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=BTS;Initial Catalog=vmfgtest; password=blocked"
    sSQL = "delete osk_release_num1"
    con.Open
    con.Execute sSQL

'loop through data in column c and insert into table
Set rng = Worksheets("sheet1").Range("c2:c170")
For Each cell In rng
    Set con = CreateObject("adodb.connection")
    con.connectionstring = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=BTS;Initial Catalog=vmfgtest; password=blocked"
    sSQL = "insert into osk_release_num1 (release_num, part_num, due_date) values ( " & cell & " )"
    con.Open
    con.Execute sSQL
 Next cell
 
 a = MsgBox("Success!", vbOKOnly)
 End Sub

Open in new window


I tried using Do Until IsEmpty with a loop, that was even worse.

I just am looking as to how to make this end neatly and produce the "Success" message box.   Thanks so much!!
LVL 1
Tina KSystems SpecialistAsked:
Who is Participating?
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 ChongCommented:
try do a
debug.print sSQL

Open in new window

before:
con.Open
    con.Execute sSQL

Open in new window

to see whether it's a valid SQL statement or not. it seems you got problem with that.
0
Rgonzo1971Commented:
Hi,

pls try

Set rng = Worksheets("sheet1").Range("c2:c170").SpecialCells(xlCellTypeConstants,2)

Open in new window

or
Set rng = Worksheets("sheet1").Range("c2:c170").SpecialCells(xlCellTypeFormulas,2)

Open in new window

Regards
0
Tina KSystems SpecialistAuthor Commented:
The problem is not with the SQL, it is opening and populating the table correctly.  

Using the xlcelltypeformulas would normally work, but I have a logic formula filled  in C2:C170:  

=IF(D2="","",CONCATENATE("  '",A2,"', '",E2,"', '",B2,"' "))

I will not know how long the data list will be that fills column C, so I'd need something that could loop through C until the formula result is false.
0
Tina KSystems SpecialistAuthor Commented:
I got it - I changed my logic formula on the sheet to say:

=IF(D2="","FALSE",CONCATENATE("  '",A2,"', '",E2,"', '",B2,"' "))

This column is hidden anyways, so it doesn't matter what it says if it's not concatenating.

I then added  
if cell.value = false then exit for

Open in new window

to my vba and it worked as I hoped.

Thanks for the help
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
Tina KSystems SpecialistAuthor Commented:
I figured it out on my own
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.