Solved

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

Posted on 2016-08-03
5
19 Views
Last Modified: 2016-08-08
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!!
0
Comment
Question by:tkriegel
  • 3
5 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41740779
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41740807
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
 
LVL 1

Author Comment

by:tkriegel
ID: 41740874
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
 
LVL 1

Accepted Solution

by:
tkriegel earned 0 total points
ID: 41740956
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
 
LVL 1

Author Closing Comment

by:tkriegel
ID: 41746981
I figured it out on my own
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now