Page 1 of 1
Stored procedures error
Posted: Tue 25 Jul 2006 22:56
by 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
Posted: Wed 26 Jul 2006 08:10
by 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.
Thanks soooo much!
Posted: Wed 26 Jul 2006 08:28
by geoffHome
OOPs!!
I'm now getting a different error
Posted: Wed 26 Jul 2006 10:21
by 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
---------------------------------------------------------------------
Posted: Wed 26 Jul 2006 11:06
by Alexey
Use "typeID" as a name of the parameter.
I've tried everything and nothing helps
Posted: Wed 26 Jul 2006 20:08
by 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
Solved it!
Posted: Wed 26 Jul 2006 20:36
by geoffHome
I put a ; at the end of the stored procedure.
Now a problem dealing with DATES in the query
Posted: Wed 26 Jul 2006 23:10
by 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
;
---------------------------------------------------
Posted: Thu 27 Jul 2006 06:25
by 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".