?
Solved

Where is my mistake ? (Macro to Update SQL From Excel)

Posted on 2009-02-13
12
Medium Priority
?
713 Views
Last Modified: 2012-05-06
Hi,

I´m triying to UPDATE a SQL Table from Excel...

I´m calling a Stored Procedure from a VB Macro... I get connected but the results are not what I´m looking for.

I don´t know If the problem comes from the VB code I scrath from other web related post or from the procedure I get from a post on this web.
 
The Stored Procedure I´m calling from my SQL Server is:

USE[CEDRO]
GO
/****** Object: StoredProcedure [dbo].[up_UpdateSpreadsheetData] Script Date: 02/13/2009 07:00:28 ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTERPROCEDURE[dbo].[up_UpdateSpreadsheetData]@CodClievarchar,@Descripvarchar(10),@ID3varchar15)AS
IFEXISTS(SELECTCodClieFROMSACLIEWHERECodClie=@CodClieAND(Descrip<>@DescripORID3<>@ID3))
BEGIN
UPDATESACLIESETDescrip=@Descrip,ID3=@ID3
END

What I got from the procedure is a column added in my spreadsheet (with the name of the db) to the left of the data that I want to update in the SQL Table and two more columns added to the right of the data. (one blank the other with the name of the DB).

If you can help me to determinate where if my mistake it will Help me A LOT to achieve what I´m looking for a very long time.

Thank you for reading untill this point and I´ll be eternally gratefull if you can help me to do what I need.

Regards,
Roberto.
0
Comment
Question by:Pabilio
  • 7
  • 3
  • 2
12 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 23631941
Your Update statement doesn't have a Where clause - every row in the table will be updated to the values supplied.

Something mangled your code terribly, please post it again if you want furhter help.
0
 
LVL 5

Author Comment

by:Pabilio
ID: 23632485
Hi Mike,

I´m posting the Stored procedure asi I could copy  it from the SQL Server 2.008 management tool.

It is suposed, when called from a Command Button on the spreadsheet (VB Code) to take the first three columns on my excel spreadsheet, compare it with the Table SACLIE and update The fields in SACLIE  differents from the ones I have in my spreadsheet.

All columns in my spreadsheet has the same name as in the SQL Table.

Thank you very much for taking your time to help me.

Regards,
Roberto.
USE [CEDRO]
GO
/****** Object:  StoredProcedure [dbo].[up_UpdateSpreadsheetData]    Script Date: 02/13/2009 07:15:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[up_UpdateSpreadsheetData] @CodClie varchar, @Descrip varchar(10), @ID3 varchar(15) AS
IF EXISTS(SELECT CodClie FROM SACLIE WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
BEGIN
  UPDATE SACLIE SET Descrip = @Descrip, ID3 = @ID3
END 

Open in new window

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 23632737
As I said, because your UPDATE statement does not have a WHERE clause, it will update every row in your table. If CodClie is the primary key of your table this should do it:
IF EXISTS(SELECT CodClie FROM SACLIE WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
BEGIN
  UPDATE SACLIE
  SET Descrip = @Descrip, ID3 = @ID3
  WHERE CodClie = @CodClie
END
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:josepvalls
ID: 23633367
How are you calling this procedure from the Excel macro?
Can you copy that code too?
0
 
LVL 5

Author Comment

by:Pabilio
ID: 23634290
Mike...
This is the code I´m trying to use from the excel Command Button.
Thank you for your time.
Roberto.
Private Sub CommandButton1_Click()
 With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=VAIO\SERVIDOR;UID=ROBERTO;Trusted_Connection=Yes;APP=Microsoft Office XP;WSID=VAIO;DATABASE=CEDRO" _
        , Destination:=Range("A1"))
        .CommandText = Array("EXEC [up_UpdateSpreadsheetData] 10, 2197, 'Test'")
        .Name = "CLIENTES SQL"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=True
    End With
 
End Sub

Open in new window

0
 
LVL 5

Author Comment

by:Pabilio
ID: 23634918
Josep,

I´m sorry that in my last post I didn´t read the name in the post and wrote to Mike.

I made the changes recomended by Mike but still can not have the result from the Excel Command Button....so the mistake should be in the Macro´s code as well.

I hope you both could help me on this.

Regards,
Roberto.
0
 
LVL 1

Assisted Solution

by:josepvalls
josepvalls earned 1000 total points
ID: 23634989
Besides fixing the update statement as mike mentioned, the way you are calling the stored procedure doesn't look fine to me if all you want is to execute the update and you don't care about dumping the results returned to the workbook.
Look for some other example or explain what you want to do.
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=database;" & _
    "Data Source=server"
cnn.Open sConnString
 
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
 
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "your_stored_proc"
End With
 
 
Set param = New ADODB.Parameter
With param
    .Name = "@your_param_name"
    .Type = adDate (or the type)
    .Size = 8 (depends on the type)
    .Value = your_sheet.Range("B2_or_where_your_parameter_lives").Value 
End With
cmd.Parameters.Append param
   
Set rs = New ADODB.Recordset
Set rs = cmd.Execute

Open in new window

0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 1000 total points
ID: 23635237
I agree with Josep that the call doesn't look right - It looks to be set up to return values to the worksheet, but your stored proc doesn't return a recordset.
His ADO code looks good with the one comment that you the recordset is optional when executing a command.
Instead of:
  Set rs = New ADODB.Recordset
  Set rs = cmd.Execute
Code:
  cmd.Exececute
0
 
LVL 5

Author Comment

by:Pabilio
ID: 23637125
I´m sorry guys....

As Josep says I only want to Update the SQL Table...I do not want the Data returned to my spreadsheet.

I wish to have the minimun knowledge to 'really' understand what are you talking about :)

My knowledge in VB goes only to Record a Macro from Excel and then scratch it and have hundread of crashes and accidents and hangs and so on....that´s why there is a data called to excel.

I´ll give a try to Josep code to see if I´m able to run the S.P. right from my (really ugly) Command Button in excel....I´ll let you know.

Thank you both.
Roberto.
0
 
LVL 5

Author Comment

by:Pabilio
ID: 23637320
As I was thinking...
More error messages in what I´m doing...
Here´s how I put the code in my command button to trigger the S.P.
I´m having a 3001 error on execucion time on VB..."Error defined By aplication or Object" (Sorry if translation is not Acurate....my VB is in Spanish)
What I´m doing wrong now ?
Thank you for your patience and support.
Roberto.
Private Sub CommandButton1_Click()
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=CEDRO;" & _
    "Data Source=VAIO\SERVIDOR"
cnn.Open sConnString
 
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
 
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "up_UpdateSpreadsheetData"
End With
 
Set param = New ADODB.Parameter
With param
    .Name = "@CodClie"
    .Type = varchar
    .Size = 10
    .Value = CLIENTES_SQL.Range("A2").Value
End With
cmd.Parameters.Append param
   
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
End Sub

Open in new window

0
 
LVL 5

Author Comment

by:Pabilio
ID: 23638813
Mike and Josep,

This is as far is I could go today....

I apreciatte your directions  on this.

Thank you for your time.
Roberto.
Private Sub CommandButton1_Click()
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=CEDRO;" & _
    "Data Source=VAIO\SERVIDOR"
cnn.Open sConnString
 
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
 
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "up_UpdateSpreadsheetData"
End With
 
Set param = New ADODB.Parameter
With param
    .Name = "@CodClie"
    .Type = adVarChar
    .Size = (10)
    .Value = Prueba.xls.Range("A1").Value
End With
cmd.Parameters.Append param
   
Code:
  cmd.Exececute
 
End Sub

Open in new window

0
 
LVL 5

Accepted Solution

by:
Pabilio earned 0 total points
ID: 23648611
Hello Mike and Josep,

Finally I can update a single record from excel....
It couldn´t be done without your help.

The final Stored procedure is as follows:

USE [CEDRO]
GO
/****** Object:  StoredProcedure [dbo].[up_UpdateSpreadsheetData]    Script Date: 02/16/2009 06:07:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[up_UpdateSpreadsheetData] @CodClie varchar (10), @Descrip varchar(40), @ID3 varchar(15) AS
IF EXISTS(SELECT CodClie FROM SACLIE WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
IF EXISTS(SELECT CodClie FROM SAFACT WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
BEGIN
  UPDATE SACLIE SET Descrip = @Descrip, ID3 = @ID3
  WHERE CodClie = @CodClie
  UPDATE SAFACT SET Descrip = @Descrip, ID3 = @ID3
  WHERE CodClie = @CodClie
END

The call from VB is in the Code Snippet.

With this codes I can Update only one row at a time....Now I would like to update more rows at a time...
Any idea, code change o hint you would like to share with me  will help me a lot.

Thanks for your time,
Roberto.
Public Sub CommandButton1_Click()
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=CEDRO;" & _
    "Data Source=VAIO\SERVIDOR"
cnn.Open sConnString
 
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = cnn
 
With Cmd
    .CommandType = adCmdStoredProc
    .CommandText = "up_UpdateSpreadsheetData"
End With
 
Set param = New ADODB.Parameter
With param
    .Name = "@CodClie"
    .Type = adVarChar
    .Size = 10
    .Value = Range("A2").Value
End With
Set param2 = New ADODB.Parameter
With param2
    .Name = "@Descrip"
    .Type = adVarChar
    .Size = 40
    .Value = Range("B2").Value
End With
Set param3 = New ADODB.Parameter
With param3
    .Name = "@ID3"
    .Type = adVarChar
    .Size = 15
    .Value = Range("C2").Value
End With
    
Cmd.Parameters.Append param
Cmd.Parameters.Append param2
Cmd.Parameters.Append param3
Code:
    Cmd.Execute
 
End Sub

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question