Solved

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

Posted on 2016-08-03
5
25 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 51

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 50

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Building JSON Results Table FROM DB 9 32
Excel Named Range 31 44
Hash on Excel 13 34
Need age at date of document 3 9
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

740 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