SQLCommand Parameters Error

  • Thread starter Thread starter Jorge Cavalheiro
  • Start date Start date
J

Jorge Cavalheiro

Hello
I've just read in Visual Studio Magazine Nov 2003 that
using parameters in a sqlcommand is 37% faster than 'Exec
procedure_name param1,param2,etc'. So i was trying to use
it.

<code>
Me.SqlCommand2.Connection = Me.SqlConnection1
Me.SqlCommand2.CommandType = CommandType.StoredProcedure
Me.SqlCommand2.CommandText = "imprime_linha_mapa"
With Me.SqlCommand2.Parameters
.Add("@worker", SqlDbType.Char).Value = "-1"
.Add("@posto", SqlDbType.Decimal).Value = 0
.Add("@obra", SqlDbType.Char).Value = ""
.Add("@inicio", SqlDbType.Char).Value = ""
.Add("@fim", SqlDbType.Char).Value = ""
.Add("@deltah", SqlDbType.Char).Value = ""
.Add("@delta", SqlDbType.Decimal).Value = 0
.Add("@mov", SqlDbType.Decimal).Value = mov
End With

'Me.SqlCommand2.CommandText = "exec imprime_linha_mapa '-
1',0,'','','','',0," & mov.ToString

Try
Me.SqlCommand2.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Me.SqlConnection1.Close()
Exit Sub
End Try
</code>
I get the following exception : Procedure or function
imprime_linha_mapa has too many arguments specified.
But that doesn't make any sense since the stored
procedure 'imprime_linha_mapa' has the following
declaration:
<code>
ALTER PROCEDURE imprime_linha_mapa

@worker char(255),
@posto numeric(3,0),
@obra char(8),
@inicio char(20),
@fim char(20),
@deltah char(8),
@delta numeric(6,2),
@mov numeric(9,0)


AS
</code>
What am i doing wrong? Any suggestion would be most
welcome, thanks!

Kind Regards
Jorge Cavalheiro
 
Jorge:

Try this before you call the execute non query:

Debug.Assert(cmd.Parameters.Count == 8);
If the assertion fails, make (which means that you have <> 8 which is not
right), sure you call cmd.Parameters.Clear before you add anything.

As an aside, you may want to move connection.close to a finally block b/c
the way it's now, it doesn't look like it's getting closed

HTH,

Bill
 
Try dragging the SP from the Server explorer to the Form. This will generate
a prototype Command object that should have the "right" number of parameters
based on the current SP definition.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top