Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Upload from Excel to SQL Server
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Upload from Excel to SQL Server | Excel Discussion (Misc queries) | |||
Data Upload from Excel to SQL Server | Excel Worksheet Functions | |||
Data Upload from Excel to SQL Server | Links and Linking in Excel | |||
Data Upload from Excel to SQL Server | Excel Programming | |||
Data Upload from Excel to SQL Server | Excel Programming |