• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

UPDATE SQL FROM EXCEL

I think that I posted this tread in the wrong places...
So let´s see if here at VB I could find a good soul who could help me.

Attached is the spreadsheet that I´m working with, which have some macros in it to allow the user to create a New Final Tax Report according to my Country Regulations.

The RED Sheet (DATOS) is filled with the get external data tool in excel from My Data Base (CEDRO)

The formulas in the GREEN Sheet (CAMBIAR AQUI) are made in the way to determinate if there is wrong entrys in the Data Base before to Print the Final TAX report in the Blue Sheet (LIBRO DE IVA)

The original SQL that I Copy from the MSQUERY to import the data is as Follows:

SELECT cast(CONVERT(varchar(8), B.FechaE, 112) AS datetime) , B.NumeroD, B.Descrip, B.ID3, B.Monto, B.MtoTax, B.RetenIVA, A.TipoCli, A.EsGranCont, B.CodUsua, B.CodVend
FROM CEDRO.dbo.SACLIE A, CEDRO.dbo.SAFACT B
WHERE A.CodClie = B.CodClie AND ((B.FechaE Between ? And ?) AND (B.TipoFac Between ? And ?))
ORDER BY cast(CONVERT(varchar(8), B.FechaE, 112) AS datetime), B.NumeroD

What I need is to give the user the chance to update the field ID3 (Column D in DATOS Sheet) and then with a Button UPDATE the SQL  TABLES: SACLIE and SAFACT (Both Tables linked by CodClie (KEY) and the ID3 which is the field I need to Change.

Other Info:
SERVER NAME: SERVIDOR
DATA BASE: CEDRO
TABLES: SACLIE (Main Table...contains all Customer Details) and SAFACT (Contains Invoice Info)
WORKBOOK NAME: CREAR NUEVO LIBRO DE VENTAS

I´m really new in VB features...some superficial knoledge only and even when I find some similar situations here I´m not able to use it in my case...If you can help me please do it...I´ll put your picture as my default desktop
CREAR-NUEVO-LIBRO-DE-VENTAS-CORR.xls
0
Pabilio
Asked:
Pabilio
  • 2
1 Solution
 
borgunitCommented:
Not quite sure what you need but maybe have a user form with some text fields that a user can fill in and then use a command button to start the update. From there use your database connection and then use the sql UPDATE someTable SET someField=someInfo WHERE someField=someOtherInfo. Is this helpful?
0
 
PabilioAuthor Commented:
Hi Borgunit,

That is exactly what I need.

I need the Command Button you said in the Sheet DATOS or in a new Sheet as the User Form you mention (That would be great !!!).

If you can help me to do this please send me your picture to use it as my default Desktop.  :-)

Regards,
Roberto.
0
 
PabilioAuthor Commented:
Here is the Solution:
 Stored Procedure in SQL:
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 code to make the call from VB is attached in the COde Snippet.

Regards,
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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now