November 3, 2017 at 2:58 pm
This working code lets the user use a click event in Excel/VBA to separately insert values into the same field in SQL. I would like to know how I can rewrite this code in order to have the user insert an entire column into a field when the user does a click event. Please note I am using the (?) as a way to insert a value when the user presses the click button in excel. I know of no other way.
Sub ClickButton()
Dim SQL As ADODB.Command
Set SQL = New ADODB.Command
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (?)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.Execute
End Sub()
I would like to know how I can rewrite this code in order to have the user insert an entire column into a field when the user does a click event.
Please note I am using the (?) as a way to insert a value when the user presses the click button in excel. I know of no other way.
Private Sub MyButton1_Click()Dim SQL As ADODB.Command
Set SQL = New ADODB.Command
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (?)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.Execute
End Sub()
November 3, 2017 at 3:16 pm
???
Don't understand the question
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2017 at 8:19 pm
GilaMonster - Friday, November 3, 2017 3:16 PM???
Don't understand the question
Sorry about that 🙂 Please have a look at the question now.
November 5, 2017 at 6:55 am
leavesandmusic - Friday, November 3, 2017 2:58 PM... have the user insert an entire column into a field when the user does a click event.
What do you mean by this? Can you give an example?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 6, 2017 at 6:46 am
leavesandmusic - Friday, November 3, 2017 2:58 PMThis working code lets the user use a click event in Excel/VBA to separately insert values into the same field in SQL. I would like to know how I can rewrite this code in order to have the user insert an entire column into a field when the user does a click event. Please note I am using the (?) as a way to insert a value when the user presses the click button in excel. I know of no other way.Sub ClickButton()
Dim SQL As ADODB.Command
Set SQL = New ADODB.CommandSQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (?)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.Execute
End Sub()This working code lets the user use a click event in Excel to separately insert values into the same field in SQL.I would like to know how I can rewrite this code in order to have the user insert an entire column into a field when the user does a click event.
Please note I am using the (?) as a way to insert a value when the user presses the click button in excel. I know of no other way.
Private Sub MyButton1_Click()
Dim SQL As ADODB.Command
Set SQL = New ADODB.CommandSQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (?)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.ExecuteEnd Sub()
Obviously, if you continue to use a method that causes Excel to prompt the user for a value, inserting an entire column is no longer practical using the same methodology. Let's also consider how you determine exactly which column... Additionally, why are we inserting just one column? What about duplication of values? Or does that matter? I can provide a better methodology, but I need a more complete picture of why you're using a prompted method to begin with, when you can easily determine things like which cell is "currently active" (meaning selected), but as you are then clicking on a button, that might change which cell is active, so I wonder if you might be better off NOT using a click event... Let's talk about the ENTIRE purpose of the spreadsheet, along with the ultimate objective.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 10, 2017 at 9:17 am
Thank you for your response.
I am constrained by user needs.
Therefore I am only inserting one column on a click event due to a user need.
The duplication of values does not matter. However, it must be accomplished using a click event.
It would be greatly appreciated if you could modify my code so that I may accomplish this missing link in my code.
November 10, 2017 at 10:06 am
Do you mean the values in the column for all rows in the spreadsheet?
Far away is close at hand in the images of elsewhere.
Anon.
November 10, 2017 at 11:31 am
I would like only one column: for example,
Dim arr As Variant
arr = Sheets("Sheet1").Range("E3:E20").Value
November 10, 2017 at 11:46 am
leavesandmusic - Friday, November 10, 2017 11:31 AMI would like only one column: for example,Dim arr As Variant
arr = Sheets("Sheet1").Range("E3:E20").Value
Have you tried changing your VBA to produce SQL like this?INSERT MyDatabase.dbo.MyTable ([my_id])
VALUES (),(),(),(),(), ..., ()
All you need to do is fill in each bracket with a single value from your spreadsheet. No need for parameters, though you are potentially opening yourself up to SQL Injection by doing this.
Alternatively (and probably better, because you get more control), create a stored proc in your database which accepts a delimited list of values as a parameter, and let the proc handle it all. Your VBA just has to submit the values to the proc.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2017 at 11:51 am
Thank you, so I assume the code would look like this?
Sub ClickButton()
Dim SQL As ADODB.Command
Set SQL = New ADODB.Command
Dim A As Variant
A = Sheets("Sheet1").Range("E1").Value
Dim B As Variant
B = Sheets("Sheet1").Range("E2").Value
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.Execute
End Sub()
I tested the code. It does not work. It says "object required" for this line of code: SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
November 10, 2017 at 1:09 pm
leavesandmusic - Friday, November 10, 2017 11:51 AMThank you, so I assume the code would look like this?Sub ClickButton()
Dim SQL As ADODB.Command
Set SQL = New ADODB.Command
Dim A As Variant
A = Sheets("Sheet1").Range("E1").Value
Dim B As Variant
B = Sheets("Sheet1").Range("E2").ValueSQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
SQL.ActiveConnection = Cn
SQL.Execute
End Sub()I tested the code. It does not work. It says "object required" for this line of code: SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
Been a while since I wrote VBA, but I think there are a few problems with your code:
So instead of
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
I think you'll need something more like (untested)
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES ('" + A + "'), ('" + B+ "')"
Obviously, this hard-coded approach works only when you know how many items are being inserted. I'm assuming that you don't & therefore that you'll have to use a loop to build up your VALUES clause.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2017 at 1:37 pm
Thank you for your contribution, but no matter what variation of your code I try, it does not work.
November 10, 2017 at 1:42 pm
leavesandmusic - Friday, November 10, 2017 1:37 PMThank you for your contribution, but no matter what variation of your code I try, it does not work.
OK, but if you expect any further help, telling us that something 'does not work' does not provide enough information to go on.
I really suggest that you go down the stored procedure route which I suggested for this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2017 at 1:49 pm
Because of user constraints, I cannot follow through with your stored procedures suggestion.
I tried to debug your previous code, but no error messages execute when the code is called.
It would be greatly appreciated if you can first test the code, make sure that it works, and then offer it as
a suggestion. Thank you.
November 10, 2017 at 1:57 pm
leavesandmusic - Friday, November 10, 2017 1:49 PMBecause of user constraints, I cannot follow through with your stored procedures suggestion.
I tried to debug your previous code, but no error messages execute when the code is called.
It would be greatly appreciated if you can first test the code, make sure that it works, and then offer it as
a suggestion. Thank you.
Sorry, but there are limits to the time which I am prepared to invest setting up test harnesses free of charge.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply