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

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.
LVL 5
PabilioAsked:
Who is Participating?
 
PabilioConnect With a Mentor Author Commented:
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
 
MikeTooleCommented:
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
 
PabilioAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
MikeTooleCommented:
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
 
josepvallsCommented:
How are you calling this procedure from the Excel macro?
Can you copy that code too?
0
 
PabilioAuthor Commented:
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
 
PabilioAuthor Commented:
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
 
josepvallsConnect With a Mentor Commented:
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
 
MikeTooleConnect With a Mentor Commented:
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
 
PabilioAuthor Commented:
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
 
PabilioAuthor Commented:
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
 
PabilioAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.