November 15, 2017 at 3:13 pm
I have to add incrementing integer values within closed quotation marks and parentheses.
Question 1. How can I increment the integer values so that each value goes up by 1. How do I make the [the_id] = 1, [the_id] = 2 and so on within the context of quotation marks found below?
Dim Z As Integer
For Z = 1 To 5
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = " & Z;"
Next Z
The intended result is an increment like the following:
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 1;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 2;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 3;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 4;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 5;"
Second, how do I do the same thing within this query?
Would it be like this?
Dim i As Integer
For i = 1 To 5
SQL.Parameters.Append SQL.CreateParameter("MyField" & i, adVarChar, adParamInput, 50, MyField & i)
Next i
The intended result is an increment like the following.
SQL.Parameters.Append SQL.CreateParameter("MyField1", adVarChar, adParamInput, 50, MyField1)
SQL.Parameters.Append SQL.CreateParameter("MyField2", adVarChar, adParamInput, 50, MyField2)
SQL.Parameters.Append SQL.CreateParameter("MyField3", adVarChar, adParamInput, 50, MyField3)
SQL.Parameters.Append SQL.CreateParameter("MyField4", adVarChar, adParamInput, 50, MyField4)
SQL.Parameters.Append SQL.CreateParameter("MyField5", adVarChar, adParamInput, 50, MyField5)
November 16, 2017 at 12:49 am
You need to cast your integer values to string in order to achieve this.For loop will increment your integer number but not really sure what you want to achieve through it as you are limiting your for loop to 5 as per your code.
Can you please share what problem you want to solve by doing this? it would help to understand a little better.
November 16, 2017 at 7:12 am
leavesandmusic - Wednesday, November 15, 2017 3:13 PMI have to add incrementing integer values within closed quotation marks and parentheses.
Question 1. How can I increment the integer values so that each value goes up by 1. How do I make the [the_id] = 1, [the_id] = 2 and so on within the context of quotation marks found below?
Dim Z As Integer
For Z = 1 To 5
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = " & Z;"
Next Z
The intended result is an increment like the following:
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 1;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 2;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 3;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 4;"
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = 5;"
Second, how do I do the same thing within this query?
Would it be like this?Dim i As Integer
For i = 1 To 5
SQL.Parameters.Append SQL.CreateParameter("MyField" & i, adVarChar, adParamInput, 50, MyField & i)
Next i
The intended result is an increment like the following.
SQL.Parameters.Append SQL.CreateParameter("MyField1", adVarChar, adParamInput, 50, MyField1)
SQL.Parameters.Append SQL.CreateParameter("MyField2", adVarChar, adParamInput, 50, MyField2)
SQL.Parameters.Append SQL.CreateParameter("MyField3", adVarChar, adParamInput, 50, MyField3)
SQL.Parameters.Append SQL.CreateParameter("MyField4", adVarChar, adParamInput, 50, MyField4)
SQL.Parameters.Append SQL.CreateParameter("MyField5", adVarChar, adParamInput, 50, MyField5)
Not sure why you insist on using parameters for a query that you are going to construct dynamically to begin with. As twin.devil has suggested, we can help far more easily if we know what your final objective is, and why you are seeking to use procedural methods for something that might be better accomplished using the query itself and a set-based methodology. However, we can't know which is needed without a lot more details.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 16, 2017 at 2:53 pm
Hi Twin Devil and sgmunson
I'm sorry if I was not clear enough.
The purpose of this project is to use the code I wrote to loop through values in VBA to perform an insert, update, or delete using SQL.CommandText and SQL.Parameters.Append SQL.CreateParameter when I run a macro
The code already works, its just that I do not want to have to manually enter thousands of insert, update, or delete statements as they go through the spreadsheet.
I am not looking for ways to improve my code or anything along those lines.
Since my code works, I would simply like to know how to add incrementing integers as I explained in my original post.
Thank you.
November 17, 2017 at 3:30 am
Your response wasn't very clear this time around too. Having said that i have tried to answers with my best guess.
Question 1. How can I increment the integer values so that each value goes up by 1. How do I make the [the_id] = 1, [the_id] = 2 and so on within the context of quotation marks found below?
Dim Z As Integer
For Z = 1 To 5
SQL.CommandText = SQL.CommandText + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = " & Z;"
Next Z
This working will give you your increment by one value. Like i said in my earlier response you need to type cast your integer to String to make it work.
Secondly, its better if you it in a local variable and then pass that to SQL.CommandText. Something like
Dim Z As Integer
Dim UpdateQuery as String
For Z = 1 To 5
UpdateQuery = UpdateQuery + "UPDATE [MyDatabase].[dbo].[tbl_MyTable] SET [MyField] =(?) WHERE [the_id] = " & CStr(Z);" + vbCrLf ' Added for newLine
Next Z
SQL.CommandText = UpdateQuery
Now for your second question
Second, how do I do the same thing within this query?
Would it be like this?Dim i As Integer
For i = 1 To 5
SQL.Parameters.Append SQL.CreateParameter("MyField" & i, adVarChar, adParamInput, 50, MyField & i)
Next i
Is MyField is a Column list in your excel sheet like MyField1, MyField2, MyField3 etc, if Yes then your method is not correct. its suppose to use Cell function
Something like this Read or Get Data from Worksheet Cell to VBA in Excel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply