Solved

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

Posted on 2016-08-03
5
26 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 52

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 51

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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