CODEWITHSUNDEEP
sql-serverexcelvbastored-procedures
ajudi
ajudi

Reputation: 53

"Error converting datatype varchar to int" when updating from Excel macro through SQL Server stored procedure

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.

Upvotes: 0

Views: 576

Answers (2)

ajudi
ajudi

Reputation: 53

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!

Upvotes: 1

Mr.J
Mr.J

Reputation: 440

the error is here

 elsosor = elsosor + 1

declare elsosor as integer, then initialize its value

Upvotes: 0

Related Questions