Stored procedures error

[geoffHome]

I'm getting this error and can't understand why.

Incorrect number of arguments for PROCEDURE recruitment.JobsbyType; expected 1, got 0

My Stored procedure is:

PROCEDURE `recruitment`.`JobsbyType`(IN x INT)
BEGIN
SELECT * FROM jobs WHERE jobTypeID=x;
END

My code to use this stored procedure is as follows:

GridView1.DataSource = CallProcedure("JobsbyType", typeID)
GridView1.DataBind()


Function CallProcedure(ByVal storedProcedure As String, ByVal typeID As Int32) As MySqlDataReader

Dim myCommand As New MySqlCommand(storedProcedure, dbConn)
myCommand.CommandType = CommandType.StoredProcedure

Dim dbParam1 As New MySqlParameter
dbParam1.ParameterName = "@typeID"
dbParam1.Value = typeID
dbParam1.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam1)

Try
dbConn.Open()
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Emsg = ex.ToString
Return Nothing
End Try

End Function

It's probably very obvious but not to me yet. Thanks in anticipation,

Geoff

[Alexey]

Code: Select all

Dim myCommand As New MySqlCommand(storedProcedure, dbConn) 
dbCommand.Parameters.Add(dbParam1) 

You have declared myCommand, but add the parameter to dbCommand.

[geoffHome]

OOPs!!

[geoffHome]

Now I get 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':9)' at line 1'

I can't figure what I'm doing wrong. Can you?

---------------------------------------------------------------------

eg:

GridView1.DataSource = obj.CallProcedure("JobsbyType", 9)
GridView1.DataBind()

Function CallProcedure(ByVal storedProcedure As String, ByVal typeID As Int32) As MySqlDataReader

Dim cmd As New MySqlCommand(storedProcedure, dbConn)
cmd.CommandType = CommandType.StoredProcedure

Dim dbParam1 As New MySqlParameter
dbParam1.ParameterName = "@typeID"
dbParam1.Value = typeID
dbParam1.DbType = DbType.Int32
cmd.Parameters.Add(dbParam1)

Try
dbConn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Emsg = ex.ToString
Return Nothing
End Try

End Function

---------------------------------------------------------------------
Stored Procedure:

PROCEDURE `recruitment`.`JobsbyType` (x INT)
BEGIN
SELECT * FROM jobs WHERE jobTypeID=x;
END
---------------------------------------------------------------------

[Alexey]

Use "typeID" as a name of the parameter.

[geoffHome]

The only way I can make it work is to not pass a parameter.

Can you prove to me that it works with a demonstration.

This is wasting so much time and it is trivial.

Thanks,

Geoff

[geoffHome]

I put a ; at the end of the stored procedure.

[geoffHome]

I'm getting this error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':6,:'2006-07-27')' at line 1
when issuing the following commands.

Any suggestions?


GridView1.DataSource =CallProcedure("JobsbyType", typeID, Now())
GridView1.DataBind()
.....

---------------------------------------------------

Function CallProcedure(ByVal storedProcedure As String, ByVal typeID As Int32, ByVal today As Date) As MySqlDataReader

Dim cmd As New MySqlCommand(storedProcedure, dbConn)
cmd.CommandType = CommandType.StoredProcedure

Dim param1 As New MySqlParameter
param1.ParameterName = "@typeID"
param1.Value = typeID
param1.MySqlType = MySqlType.Int
cmd.Parameters.Add(param1)

Dim Param2 As New MySqlParameter
Param2.ParameterName = "@today"
Param2.Value = today
Param2.MySqlType = MySqlType.Date
cmd.Parameters.Add(Param2)

Try
dbConn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Emsg = ex.ToString
Return Nothing
End Try

End Function

---------------------------------------------------
Stored Procedure:
PROCEDURE recruitment.JobsbyType(IN _jobTypeID INT, _today DATE)
BEGIN
SELECT * FROM jobs
WHERE _today between publishSTART and publishSTOP AND (jobTypeID = _jobTypeID);
END
;
---------------------------------------------------

[Alexey]

I recommended you to use "typeID" as a name of the parameter, didn't i?
It also applies to another parameter: use "today" instead of "@today".