For/Next loop to process INSERT INTO statement

D

Doctorjones_md

I reposted this because I was unable to achieve desired results from
previous recommendations.

Previous Post:
===================
I have the following code which does the following:

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet. My
thought is that I might need a For/Next Loop, but I'm not sure where in the
code to place it. Any ideas on how/where would I modify the code to enable
it to (loop through) upload all rows, or iterate on each row having
data (those not deleted by the DeleteBlankRows procedure)?



Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
 
B

Bernie Deitrick

Try changing

sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL

to

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i

HTH,
Bernie
MS Excel MVP
 
D

Doctorjones_md

Bernie -- Thanks a Bunch -- that did the trick Brillantly!!

I was getting "Wrapped Around the Axle" trying to deal with Stored
Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
(somewhat), but just needed some tweaking -- thanks again for you help and
quick response. :)


Bernie Deitrick said:
Try changing

sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL

to

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
'" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i

HTH,
Bernie
MS Excel MVP


Doctorjones_md said:
I reposted this because I was unable to achieve desired results from
previous recommendations.

Previous Post:
===================
I have the following code which does the following:

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet.
My thought is that I might need a For/Next Loop, but I'm not sure where
in the code to place it. Any ideas on how/where would I modify the code
to enable it to (loop through) upload all rows, or iterate on each row
having
data (those not deleted by the DeleteBlankRows procedure)?



Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
 
B

Bernie Deitrick

Doctor Jones,

I have to believe that there is a way to move an entire table into a database without looping. But
I have no experience with SQL, and have never used code like yours - still, I'm glad to hear that my
(probably sub-optimal) code worked out for you.

Bernie
MS Excel MVP


Doctorjones_md said:
Bernie -- Thanks a Bunch -- that did the trick Brillantly!!

I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts
(and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks
again for you help and quick response. :)


Bernie Deitrick said:
Try changing

sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL

to

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i

HTH,
Bernie
MS Excel MVP


Doctorjones_md said:
I reposted this because I was unable to achieve desired results from previous recommendations.

Previous Post:
===================
I have the following code which does the following:

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet. My thought is that I
might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on
how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on
each row having
data (those not deleted by the DeleteBlankRows procedure)?



Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
 
D

Doctorjones_md

Bernie,

There are a plethora of methods to Insert an entire Table, but your For/Next
code does exactly what I needed it to do.

Several other methods (OPENROWSET -- BULK INSERT) weren't working for me --
based on SQL Server Security Settings and/or other issues.

Thanks again for your help! :)
Bernie Deitrick said:
Doctor Jones,

I have to believe that there is a way to move an entire table into a
database without looping. But I have no experience with SQL, and have
never used code like yours - still, I'm glad to hear that my (probably
sub-optimal) code worked out for you.

Bernie
MS Excel MVP


Doctorjones_md said:
Bernie -- Thanks a Bunch -- that did the trick Brillantly!!

I was getting "Wrapped Around the Axle" trying to deal with Stored
Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
(somewhat), but just needed some tweaking -- thanks again for you help
and quick response. :)


Bernie Deitrick said:
Try changing

sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
'" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL

to

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
'" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i

HTH,
Bernie
MS Excel MVP


I reposted this because I was unable to achieve desired results from
previous recommendations.

Previous Post:
===================
I have the following code which does the following:

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet.
My thought is that I might need a For/Next Loop, but I'm not sure where
in the code to place it. Any ideas on how/where would I modify the
code to enable it to (loop through) upload all rows, or iterate on each
row having
data (those not deleted by the DeleteBlankRows procedure)?



Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
'" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top