Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-03
5
Medium Priority
?
30 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 54

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 53

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

824 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