sql server msde: I assume that I'll need to write a cursor to accomplish this -- how would I
write a cursor to upload data from Excel to SQL Server (1 row at a time)?

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.

Normally what I would recommend is using DTS, BULK INSERT, or bcp to load
your data in. This isn't really a cursor-based solution though... Are you
looking for a server-side solution or a client-side replacement for what you
posted?

[quoted text, click to view]

Mike,

Thanks for the reply -- ideally, I'm looking for a Client-Side solution
(such as a variable/loop) which will iterate through each row in the Excel
worksheet (after the DeleteBlankRows() routine) and upload the remaining
data to SQL Server. I tried running the following code, but get the
following error:

When I run the code, I get the following error: "Runtime error '3001'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

Any ideas into what's causing the error? Could it be a conflict in data
types that's causing this?

Here's my code:

1. The following Stored Procedure on SQL Server
==================================
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

1. The following VBA code in Excel Module
===============================

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 20
intParams = objCmd.Parameters.Count - 1 'first one is RETURN value

DIM rng as Range
DIM ccell as Range
set rng = range("A2:T20") ' change this range to include all your data
For Each ccell 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


[quoted text, click to view]

[quoted text, click to view]

I haven't done VBA scripting in quite a while, but it looks like your code
is trying to use ADODB and a parameterized query, but you're not actually
creating any ADODB Parameters. Here's a sample script that uses ADODB and
adds parameters. At some point you should call the objCmd.Parameters.Append
method to add your parameters to the parameter collection. Sorry I can't be
of more help, but I haven't messed with VBA in years.

Sorry, this link should have been included in the above message:
http://www.freevbcode.com/ShowCode.asp?ID=3687

You are correct. By default, the Parameters Collection is empty (and the
range 0), which explains the error message.

As you append each parameter, you must assign a name that matches those
found in the procedure, and it must match in compatible data type.

The order is unimportant.

You can, but it is not required, to add a parameter of type Return to
capture the result code.

Hope this helps.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]