0

I have an Excel macro for updating data in SQL Server through a stored procedure.

Stored procedure is:

ALTER PROCEDURE [dbo].[modkulfadquery] 
    @steuerID nvarchar(20) = null,
    @kulfirsz integer = null,
    @kulfvaros nvarchar(50) = null,
    @kulfutca nvarchar(50) = null,
    @lakcimbejdatum date = null,
    @kulfbankszlaszam nvarchar(50) = null,
    @nevID integer = 0
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE nevlista 
    SET steuerID = @steuerID,
        kulfirsz = @kulfirsz,
        kulfvaros = @kulfvaros,
        kulfutca = @kulfutca,
        lakcimbejdatum = CONVERT(DATE, @lakcimbejdatum),
        kulfbankszlaszam = @kulfbankszlaszam
    WHERE nevID = @nevID
END

The macro is:

With Munka3
    elsosor = 3
    Do Until .Cells(elsosor, 1) = ""
        nevID = .Cells(elsosor, 1).Value
        steuerID = .Cells(elsosor, 3).Value
        kulfirsz = .Cells(elsosor, 4).Value
        kulfvaros = .Cells(elsosor, 5).Value
        kulfutca = .Cells(elsosor, 6).Value
        lakcimbejdatum = .Cells(elsosor, 7).Value
        kulfbankszlaszam = .Cells(elsosor, 8).Value

        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "modkulfadquery"
        cmd.Parameters.Append cmd.CreateParameter("@nevID", adInteger, adParamInput, , nevID)
        cmd.Parameters.Append cmd.CreateParameter("@steuerID", adVarChar, adParamInput, 20, steuerID)
        cmd.Parameters.Append cmd.CreateParameter("@kulfirsz", adInteger, adParamInput, , kulfirsz)
        cmd.Parameters.Append cmd.CreateParameter("@kulfvaros", adVarChar, adParamInput, 50, kulfvaros)
        cmd.Parameters.Append cmd.CreateParameter("@kulfutca", adVarChar, adParamInput, 50, kulfutca)
        cmd.Parameters.Append cmd.CreateParameter("@lakcimbejdatum", adDate, adParamInput, , lakcimbejdatum)
        cmd.Parameters.Append cmd.CreateParameter("@kulfbankszlaszam", adVarChar, adParamInput, 50, kulfbankszlaszam)
        cmd.Execute

        elsosor = elsosor + 1
    Loop
End With

Running the macro, I get the error message:

Error converting data type varchar to int.

I checked the types of the parameters in the stored procedure, in the macro, and the Excel sheet too, and the table in SQL Server. They are all the same.

I have a similar stored procedure (does the same) for another sheet with fewer input parameters and just nvarchar types (except nevID), and there it works.

7
  • Which line errors out? Commented Dec 28, 2017 at 7:03
  • line cmd.execute Commented Dec 28, 2017 at 7:06
  • Try to use adVariant Commented Dec 28, 2017 at 7:09
  • I mean to get to know, which parameter namely errors out, you 1) make all types as adVariant and then 2) change types one-by-one to their original types, i.e. changed to adVarchar -> run proc; changed next type to adInteger -> run proc etc. This way you'll find out your trouble parameter. Commented Dec 28, 2017 at 7:15
  • ok, I changed the macro to advariant where it was advarchar. I gave two inputs from the excel sheet (steuerID and kulfirsz). Now the error message was: "Implicit conversion from data type sql_variant to int is not allowed. Use CONVERT function to run this query"... (meanwhile I changed all the types to advariant and got the same error) Commented Dec 28, 2017 at 7:18

2 Answers 2

1

Ok, I found the solution. For everyone who is dealing with similar problems:

The last parameter in the stored procedure was the first in the macro, so I guess the types of the input parameter were in the wrong order. I moved the @nevID parameter in the stored procedure to the first place, since then it works!

Anyway thanks for the help!

Sign up to request clarification or add additional context in comments.

1 Comment

I can't believe that was my problem. Always be cautious when using Microsoft products! Please mark your answer as "correct answer" with the green check mark !
0

the error is here

 elsosor = elsosor + 1

declare elsosor as integer, then initialize its value

1 Comment

elsosor is declared as integer, I just didnt copy the whole code. Its declared at the top of the module before the sub of this macro begins (dim elsosor as integer).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.