ERROR: Arguments are of the wrong type, etc.

[booboo0912]

I'm pulling my hair out with this one! I'm trying to learn from examples on how to pass parameters to a stored procedure. My first problem is not knowing much about VB & stored procedures! I have the following code...

<%
Dim strFName, strLName
strFName = Request.Form(&quot;FirstName&quot
strLName = Request.Form(&quot;LastName&quot

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 1
adLockReadOnly = 1
adCmdTable = 3

Dim conn, cmd
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open = &quot;Provider=SQLOLEDB.1;Persist....etc
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot
cmd.CommandText = &quot;addName&quot;
cmd.ActiveConnection = conn
cmd.CommandType = &H0004
cmd.Parameters.Append cmd.CreateParameters(&quot;txtFN&quot;, adVarChar, adParamInput, , strFName)
cmd.Parameters.Append cmd.CreateParameters(&quot;txtLN&quot;, adVarChar, adParamInput, , strLName)
cmd.Execute
cmd.Close()
Set cmd = nothing
%>

I assume there's something wrong with the above code and not my stored procedure???? Does anyone see anything I'm missing?
Thanks in advance!!!!!

 

[LazyMe]

conn.Open = &quot;Provider=SQLOLEDB.1;Persist....etc

Open is not a property of the connection object; it's a method, so you can't assign to it.

Loose the &quot;=&quot; and see what happens then....

Greetings,
Rick

 

[booboo0912]

Oops...that was a typo...I don't have the &quot;=&quot; in my actual code! Anything else??

 

[LazyMe]

In which line is it crashing?
Greetings,
Rick

 

[booboo0912]

Hi Rick! Thanks for checking back! It doesn't like this line:

cmd.Parameters.Append cmd.CreateParameters(&quot;txtFN&quot;, adVarChar, adParamInput, , strFName)

Any ideas?? If everything else looks ok, could it be my stored procedure???

Thanks!

 

[booboo0912]

Hello again! I'm just checking to see if anyone can help with this problem...if not, please let me know if I need to try another forum. Thanks!

 

[CCLINT]

cmd.CreateParameter

remove the &quot;S&quot;

 

[booboo0912]

Thanks for responding, CCLINT!
But I'm still getting the error message!

 

[CCLINT]

Do a keyword search in this forum, any date, on:

CreateParameter

 

[CCLINT]

oh, and just what error are you receiving?

 

[TheConeHead]

ERROR: Arguments are of the wrong type

 

[mattKnight]

Just something, that johnwm (I think)pointed out...

If you create the command object and set the active connection, it will automagically pull in the parameters from the stored proc.

So you should be able to

dim adoPar as adodb.parameter
 for each adopar in cmd.parameters
debug.print "name: "; adopar.Name " Value "; adopar.value
next

and it should print out the parameter list Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[booboo0912]

Hi Matt...you said 'pull in the parameters from the stored proc'... I might be misinterpreting you, but I'm trying to send the parameters to the stored procedure to update the table.

Here's my stored procedure...

Create Procedure myProc @txtFN varchar(50), @txtLN varchar (50)

AS

Update tblName Set FirstName = @txtFN, LastName = @txtLN;

Does this help any?? I did a Key Word search like CCLINT recommended, and it looks like I have the correct syntax based on what others had...but like I said, I'm new to VB and Stored Procedures...

Thank You!

 

[mattKnight]

>'pull in the parameters from the stored proc'

Sorry, I wasn't clear enough

If you create the command object, roughly like you have in VB to connect to you stored proc (myProc)
VB (at runtime) is smart enough to connect to the SQL server and populate the parameters collection with the parameter names, types sizes etc.
In short, you may be trying to CreateParameter for a parameter that exists already,as VB may have already created it for you.
All that you need to do is supply the value so, Try this (modified code)

Dim strFName as String , strLName as String 
'I consider it better to EXPLICITLY type variables (IMHO)
  strFName = Request.Form("FirstName")
  strLName = Request.Form("LastName")

' CODE HERE TO

'Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
 ' adOpenForwardOnly = 1
 ' adLockReadOnly = 1
 ' adCmdTable = 3

' HERE NOT NEEDED
Dim conn as ADODB.Connection, cmd as ADODB.Command
  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open = "Provider=SQEDB.1;Persist....etc
  Set cmd = Server.CreateObject("ADODB.Command")

with cmd
  .CommandText = "addName"
  .ActiveConnection = conn
  .CommandType = adCmdStoredProc
end with
' CODE HERE to
'  cmd.Parameters.Append cmd.CreateParameters("txtFN",
'  adVarChar, adParamInput, , strFName)
' cmd.Parameters.Append cmd.CreateParameters
' ("txtLN", 'adVarChar, adParamInput, , strLName)
'HERE NOT NEEDED

'Show that parameters exist in collection
dim adoPar as ADODB.Parameter
for each adoPar in cmd.Parameters
   Debug.print "Parameter Name: ";adoPar.Name; " adoPar.Type
next

'Now set Values

  cmd.parameters("@txtFN").Value = strFName

  cmd.parameters("@txtLN").Value = strLName
  cmd.Execute
  cmd.Close()
  Set cmd = nothing

Try it and see

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[booboo0912]

Hi Matt! After much research, and many hours of trying to figure this out, I have discovered I need to go about this another way. Thank you very much for your time and effort to help me...back to the drawing board!

 

[mattKnight]

Thanks for letting us know!
Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[cathyw]

Hi booboo0912,

I know this may be an afterthought now, but I think the problem is that you haven't declared a size for your varchar parameter. The 4th argument (which is blank at the moment) should be whatever the size of the varchar is in the stored procedure.
i.e.
cmd.Parameters.Append cmd.CreateParameter(&quot;txtFN&quot;, adVarChar, adParamInput, 50, strFName)

I know this may be too late to help now, but hopefully next time...

Cathy