Stored procedures error
Stored procedures error
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
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
Code: Select all
Dim myCommand As New MySqlCommand(storedProcedure, dbConn)
dbCommand.Parameters.Add(dbParam1)
I'm now getting a different error
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
I've tried everything and nothing helps
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
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!
I put a ; at the end of the stored procedure.
Now a problem dealing with DATES in the query
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
;
---------------------------------------------------
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
;
---------------------------------------------------