Stored procedures error

Stored procedures error

Postby geoffHome » Tue 25 Jul 2006 22:56

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
geoffHome
 
Posts: 28
Joined: Tue 09 May 2006 23:55

Postby Alexey » Wed 26 Jul 2006 08:10

Code: Select all
Dim myCommand As New MySqlCommand(storedProcedure, dbConn)
dbCommand.Parameters.Add(dbParam1)

You have declared myCommand, but add the parameter to dbCommand.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Thanks soooo much!

Postby geoffHome » Wed 26 Jul 2006 08:28

OOPs!!
geoffHome
 
Posts: 28
Joined: Tue 09 May 2006 23:55

I'm now getting a different error

Postby geoffHome » Wed 26 Jul 2006 10:21

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
---------------------------------------------------------------------
geoffHome
 
Posts: 28
Joined: Tue 09 May 2006 23:55

Postby Alexey » Wed 26 Jul 2006 11:06

Use "typeID" as a name of the parameter.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

I've tried everything and nothing helps

Postby geoffHome » Wed 26 Jul 2006 20:08

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
 
Posts: 28
Joined: Tue 09 May 2006 23:55

Solved it!

Postby geoffHome » Wed 26 Jul 2006 20:36

I put a ; at the end of the stored procedure.
geoffHome
 
Posts: 28
Joined: Tue 09 May 2006 23:55

Now a problem dealing with DATES in the query

Postby geoffHome » Wed 26 Jul 2006 23:10

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
;
---------------------------------------------------
geoffHome
 
Posts: 28
Joined: Tue 09 May 2006 23:55

Postby Alexey » Thu 27 Jul 2006 06:25

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".
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL