Help !!! struggling with run-time error '-2147352571 (80020005)': Type Mismatch

[RajivS]

Hi Im a noob and am struggling with this error. I am trying to turn an excel spread sheet into an app that inserts some stuff on SQL using a stored procedure :

My code is as follows :


Private Sub CommandButton1_Click()


Dim con As Variant
Dim cmd As Variant
Dim StoredProc As String
Dim WSP1 As Worksheet
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")


Set parm = cmd.CreateParameter("(atSymbol)ChosenBDM", adVarChar, , 4, TextBox1.Value())
Set parm2 = cmd.CreateParameter("(atSymbol)NewBDM", adVarChar, , 4, TextBox2.Value())


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


ServerName = "----" ' Enter your server name here
DatabaseName = "----" ' Enter your database name here
UserID = "----" ' Enter your user ID here
Password = "----" ' Enter your password here
StoredProcedure = "[New_BDM_Creation]" ' Enter Stored Procedure here


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserID & ";Password=" & Password & ";Trusted_Connection=no"
cmd.ActiveConnection = con


Application.StatusBar = "Running stored procedure..."


cmd.CommandTimeout = 900
Set cmd = cmd.Execute(parm, parm2, StoredProcedure) ----> Error is highlighted here


Set cmd = Nothing
con.Close
Set con = Nothing


Application.StatusBar = "Data successfully updated."


End Sub

[Aflatoon]

The parameters argument of the cmd.Execute method requires an array of parameter values, not two parameter objects. You should be appending the parameter objects to the Parameters collection of the command, then executing it.

[RajivS]

what would be the best way to get this to execute as I said I am a noob and what you told me just went over my head.

[Aflatoon]

Seems an odd place to start learning VBA!

Anyway, something like this (obviously untested):


Application.DisplayStatusBar = True Application.StatusBar = "Contacting SQL Server..."




ServerName = "----" ' Enter your server name here
DatabaseName = "----" ' Enter your database name here
UserId = "----" ' Enter your user ID here
Password = "----" ' Enter your password here
StoredProcedure = "[New_BDM_Creation]" ' Enter Stored Procedure here




' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserId & ";Password=" & Password & ";Trusted_Connection=no"
With cmd
.ActiveConnection = con
Set parm = cmd.CreateParameter("(atSymbol)ChosenBDM", adVarChar, , 4, TextBox1.Value())
Set parm2 = cmd.CreateParameter("(atSymbol)NewBDM", adVarChar, , 4, TextBox2.Value())
With .Parameters
.append parm
.append parm2
End With
.CommandText = StoredProcedure
Application.StatusBar = "Running stored procedure..."


.CommandTimeout = 900
.Execute
End With

[RajivS]

It was a request to give a user a simple way to upload a bunch of stuff easily without having to touch SQL or any other app. So I was forced to do it thru EXCEL wanted to do it via a desktop app C# but got out ranked.

I now have a new error - Run time error '3708' Parameter object is improperly defined.
.append parm

[Aflatoon]

Do you have a reference set to an ActiveX Data objects library? If not, you need to provide a value for adVarChar (200).

[RajivS]

I set it as asked and it did work but it fails at .Execute giving a run time error '-2147217904(80040e10)': Procedure or function 'New_BDM_Creation expects parameter '(atSymbol)ChosenBDM' which was not supplied.

Its not reading or associating the parameters.

[Aflatoon]

Please post the exact code you are using now.

Also, do you really have a parameter with the actual name "(atSymbol)ChosenBDM"?

[RajivS]

no but if I put the symbol than the rules of this site will prevent me from posting.



Private Sub CommandButton1_Click()


Dim con As Variant
Dim cmd As Variant
Dim WSP1 As Worksheet
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


ServerName = "----" ' Enter your server name here
DatabaseName = "----" ' Enter your database name here
UserID = "----" ' Enter your user ID here
Password = "-----" ' Enter your password here
StoredProcedure = "[New_BDM_Creation]" ' Enter Stored Procedure here


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserID & ";Password=" & Password & ";Trusted_Connection=no"
With cmd
.ActiveConnection = con
Set parm = cmd.CreateParameter("(atSymbol)ChosenBDM", adLongVarChar, , 4, TextBox1.Value())
Set parm2 = cmd.CreateParameter("(atSymbol)NewBDM", adLongVarChar, , 4, TextBox2.Value())
With .Parameters
.append parm
.append parm2
End With
.CommandText = StoredProcedure
Application.StatusBar = "Running stored procedure..."
.CommandTimeout = 900
.Execute -----> Error
End With


Application.StatusBar = "Data successfully updated."


End Sub

[Aflatoon]

OK, you should not be including it in the parameter name when creating them. Try this:


Const adLongVarChar As Long = 201 With cmd
.ActiveConnection = con
.CommandText = StoredProcedure
Set parm = cmd.CreateParameter("ChosenBDM", adLongVarChar, , 4, TextBox1.Value)
Set parm2 = cmd.CreateParameter("NewBDM", adLongVarChar, , 4, TextBox2.Value)
With .Parameters
.append parm
.append parm2
End With
Application.StatusBar = "Running stored procedure..."
.CommandTimeout = 900
.Execute
End With