Parameters.Append CreateCommandParameter("Test", test) Parameters.Append CreateCommandParameter("Date", theDate) Parameters.Append CreateCommandParameter("CD", CD) Now, InsertOrUpdateEstimate can look like this: Private Sub InsertOrUpdateEstimate(ByVal conn As ADODB.Connection, ByVal CD As String, ByVal theDate As Date, ByVal test As Single, ByVal EDtm As Date, ByVal UserID As String) The body of that procedure will need to create an ADODB.Parameter object for each parameter value: instead of repeating the code for that 5 times, make another function that's responsible for creating an ADODB parameter - something like this: Private Function CreateCommandParameter(ByVal name As String, ByVal value As Variant, Optional ByVal numPrecision As Integer = 4, Optional ByVal numScale As Integer = 4) As ADODB.ParameterĬase VbVarType.vbByte, VbVarType.vbInteger, VbVarType.vbLongĮrr.Raise 5, Description:="Data type not supported" parameters: Private Sub InsertOrUpdateEstimate(ByVal CD as String, ByVal theDate As Date, ByVal test As Single, ByVal EDtm As Date, ByVal UserID As String) The command takes 5 parameters make the procedure receive 5 values as. You want to run a command for each row of a given range: you need a procedure that runs a command for a single row, and call it from another procedure. You don't need to "consolidate" the code, you need to tear it apart and extract each responsibility into its own function, break it down into multiple, smaller, more specialized procedures. You've essentially written a script - it goes top to bottom and executes an ordered sequence of operations and then completes. The problem is exactly that: the scope of the procedure is too wide - it's doing too many things. If a variable stands for "the UserID parameter", then it's reasonable to expect that variable to mean the same thing throughout the scope of the procedure. You're defining 5 parameters per command, but declare only 1 ADODB.Parameter object variable this changes the meaning of the variable as the procedure executes, and this makes for bug-prone and confusing code. Procedures do something, their names should start with a verb, and you should be able to read it and know roughly what it does just by the name.Ĭonsider: Public Sub InsertOrUpdateEstimates()Īlso indent your code - it's harder to read code that begins in column 1 no matter what. Not only it doesn't say anything about what it does, it looks like the name of some standard code module. Sub Module2 is a terrible name to use for a procedure. SqlCommand.Parameters("UserID").Value = Worksheets("Sheet1").Range("G6") SqlCommand.Parameters("EDtm").Value = Worksheets("Sheet1").Range("D6") SqlCommand.Parameters("Test").Value = Worksheets("Sheet1").Range("E6") SqlCommand.Parameters("Date").Value = Worksheets("Sheet1").Range("F6") SqlCommand.Parameters("CD").Value = Worksheets("Sheet1").Range("B6") SqlCommand.Parameters("UserID").Value = Worksheets("Sheet1").Range("G5")ĬnSQL.Open "Provider=SQLOLEDB.1 uid=test Pwd=test Initial Catalog = PMW Data source=test" Set prm = sqlCommand.CreateParameter("UserID", adVarChar, adParamInput, 10) SqlCommand.Parameters("EDtm").Value = Worksheets("Sheet1").Range("D5") Set prm = sqlCommand.CreateParameter("EDtm", adDate, adParamInput) SqlCommand.Parameters("Test").Value = Worksheets("Sheet1").Range("E5") Set prm = sqlCommand.CreateParameter("Test", adDecimal, adParamInput) SqlCommand.Parameters("Date").Value = Worksheets("Sheet1").Range("F5") Set prm = sqlCommand.CreateParameter("Date", adDate, adParamInput) SqlCommand.Parameters("CD").Value = Worksheets("Sheet1").Range("B5") Set prm = sqlCommand.CreateParameter("CD", adVarChar, adParamInput, 10) SqlCommand.CommandText = "EstimateInsertUpdate" Is there a way to consolidate the code so that I can reference cells A1:A10 for parameter 1, cells B1:B10 for parameter 2, cells C1:C10 for parameter 3 and so on without copying the code over again? Sub Module2()ĬnSQL.Open "Provider=SQLOLEDB.1 uid=test Pwd=test Initial Catalog = test Data source=test" In order to have the procedure run multiple times and insert multiple sets of information into the database, I copied and pasted the parameter section of the code each time I wanted to insert new data and hard coded which excel cell to pull data from. I am trying to use this VBA code to pass a SQL stored procedure multiple values from an excel sheet.
0 Comments
Leave a Reply. |