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.
"Duke Carey" <(E-Mail Removed)> wrote in message
news:5A336058-1B63-4854-B0C8-(E-Mail Removed)...
> Try this
> First, change your insert query into a Stored Procedure in SQL Server,
> using
> parameters
> I GUESSED AT DATA TYPES FOR THE PARAMETERS. Make them the same as the
> table's field/column types
>
> create procedure dbo.usp_Insert_Upload_Specific
> @Loc vchar(5),
> @PType vchar(5),
> @Quant integer,
> @PName vchar(25),
> @Style vchar(5),
> @Features vchar(25)
>
> AS
>
> INSERT INTO Upload_Specific
> (
> Location,
> [Product Type],
> Quantity,
> [Product Name],
> Style,
> Features
> )
> VALUES
> (
> @Loc,
> @PType,
> @Quant,
> @PName,
> @Style,
> @Features,
> )
>
> GO
>
> Now your code can be a little easier
>
> 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"
>
> ' NEW CODE HERE
> Dim intParams As Integer
> Dim objCmd As New ADODB.Command
>
> ' Connect the Command object to the data source.
> objCmd.ActiveConnection = objConn
>
> ' Set CommandText equal to the stored procedure name.
> objCmd.CommandText = "dbo.usp_Insert_Upload_Specific"
> objCmd.CommandType = adCmdStoredProc
>
> ' Automatically fill in parameter info from stored procedure.
> objCmd.Parameters.Refresh
>
> ' Get the count of required parameters SHOULD BE 6
> intParams = objCmd.Parameters.Count - 1 'first one is RETURN value
>
> DIM rng as Range
> DIM ccell as Range
> set rng = range("A2:A20") ' change this range to include all your data
> For Each cc in rng
> ' call the stored procedure
>
> for x = 1 to intParams
> objCmd(x) = ccell.offset(0,x-1)
> next x
>
> ' now that all the parameters have been assigned values
> ' execute the query
> objCmd.execute
>
> next ccell
>
> 'close the connection
> oConn.Close
> Set oConn = Nothing
> End Sub
>
>
> "Doctorjones_md" wrote:
>
>> 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 --
>> how
>> would I modify the code to upload all rows, or iterate on each row having
>> data?
>>
>> 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.
>>
>>
>>
Similar Threads
