[RESOLVED] MySQL 5.0 and Vb.NET 2003 problem

[Asgorath]

My stored procedure called devolve_proxima_encomenda doesn't return any values both using a reader or .ExecuteScalar. I am using the .net mysqlclients.

DELIMITER $$

DROP PROCEDURE IF EXISTS devolve_proxima_encomenda$$
CREATE PROCEDURE devolve_proxima_encomenda()
BEGIN
    declare num int(10);
    select num = max(numenc)+1 from encomendas;
    if (num is null) then set num =1;
    end if;
    select num;
END$$

DELIMITER ;

Imports MySql.Data.MySqlClient
 
        Dim oMySqlConn As New MySqlConnection
        Dim oMySqlCom As New MySqlCommand
        Dim reader As MySqlDataReader
 
        oMySqlConn.ConnectionString = "Server=10.1.1.17;Database=mindsolutions;Uid=root;Pwd=123;"
 
        Try
            oMySqlConn.Open()
            oMySqlCom.Connection = oMySqlConn
            oMySqlCom.CommandType = CommandType.StoredProcedure
            oMySqlCom.CommandText = "devolve_proxima_encomenda"
            oMySqlCom.Parameters.Clear()
 
            ''Console.WriteLine("numenc" & oMySqlCom.ExecuteScalar().ToString)
            reader = oMySqlCom.ExecuteReader
            While reader.Read
                Console.WriteLine("numero da encomenda=" & reader(0).ToString)
            End While
            reader.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        oMySqlConn.Close()

Regards
Jorge

[Asgorath]

Solved -- mofied mysql procedure to include an OUT statement and modified vb code to use a output parameter.

Regards
Jorge