locked
Stored Procedures, Parameter is not found in collection RRS feed

  • Question

  • User-340371059 posted

    Hey there,

    since i managed to connect to my local mysql database through VS 2010 with Mysql Connector i try to get my queries running properly.
    Up to know i worked with oracle and used single SQLDataSources which i connected to my Textboxes, buttons etc.
    For me this doesn´t work with mysql at all. Now i read somethin stored procedures and i think this could be the thing i need, because most of the queries i want to execute from my asp .net site can be preformated as stored procedures.

    But when i try to start my stored procedure from asp .net i get an error which tells me, that my parameter isn´t found in the collection. Which ist strange, because i can call the procedure, enter parameter value and run it from my database manager...

    Maybe this problem sounds familiar to some of you? I am pretty sure that it is a mistake in my asp .net code and not in the sql

    greetings,

    Saturday, August 4, 2012 10:37 AM

All replies

  • User-1419831625 posted

    Could you check if firstly if the connection string is the correct, then secondly check the owner of the stored procedure

    something like this dbo.exampley_myquery and make sure your connection string user owner is dbo or at least has the right access to it.

    Saturday, August 4, 2012 12:26 PM
  • User-340371059 posted

    hey there,

    the connection string works fine, test selects working.
    and i set up the procedures with an Uid with all rights. (root on localhost via xampp)
    what do you mean by dbo.example_myquery?

    any other suggestions, why this doesn´t work? It´s really time to get this stuff running...

    Wednesday, August 8, 2012 5:27 PM
  • User269602965 posted

    Show code

    the call from .NET and the database stored procedure.

    then maybe we forum visitors can help you better

    Wednesday, August 8, 2012 8:44 PM
  • User-340371059 posted

    hey Lannie,

    here is my code:
    Procedure:

    DELIMITER //
    CREATE PROCEDURE insert_prod(IN prod_name VARCHAR(30), IN price DOUBLE, IN stock_quantity INT(11) )
    BEGIN
        Insert into product (prod_name, price, stock_quantity) VALUES (prod_name, price, stock_quantity);
    END //
    DELIMITER ;

    connection string <connectionStrings>
       
        <add name="testConnectionString" connectionString="server=localhost;User Id=root;database=test; Allow User Variables=True"
          providerName="MySql.Data.MySqlClient" />
      </connectionStrings>

    asp code
      MySqlConnection con = new MySqlConnection("server=localhost;User Id=root;database=test");
                MySqlCommand cmd = new MySqlCommand();
                MySqlParameter sp1 = new MySqlParameter();
                MySqlParameter sp2 = new MySqlParameter();
                MySqlParameter sp3 = new MySqlParameter();
                cmd.Parameters.Add("prod_name", MySqlDbType.VarChar).Value = Name.Text;
                cmd.Parameters.Add("price", MySqlDbType.Double).Value = Preis.Text;
                cmd.Parameters.Add("stock_quantity", MySqlDbType.Int16).Value = Lagerbestand.Text;
                cmd = new MySqlCommand("insert_prod", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();


    Thursday, August 9, 2012 12:01 PM
  • User269602965 posted

    Try explicit conversion of text value to Decimal datatype

    Dim decPrice = CDec(Preis.Text)

    Then pass as decimal

    In addition specify the parameter direction as Input

    cmd.Parameters.Add("price", MySqlDbType, decPrice, ParameterDirection.Input).Value = decPrice

    do the same for Lagerbestand.text

    may have to convert the Name.Text explicitly to String as well.

     

    Thursday, August 9, 2012 5:13 PM
  • User-340371059 posted

    hey Lannie,

    this looks like VB.net to me, could you give me an example code for C#?

    greetings

    Sunday, August 12, 2012 9:26 AM
  • User269602965 posted

    dynamic decPrice = Convert.ToDecimal(Preis.Text);

    cmd.Parameters.Add("price", MySqlDbType, decPrice, ParameterDirection.Input).Value == decPrice;

     

    useful converter for light conversion jobs

    http://www.developerfusion.com/tools/convert/vb-to-csharp/

     

     

    Sunday, August 12, 2012 6:48 PM
  • User-340371059 posted

    Hey Lannie,

    Visual Studio doesn´t accept code like you postet it. the form it wants to have is:

    cmd.Parameters.Add("price", MySqlDbType.Decimal).Value = decPrice;

    and it says it wants the size of the parameter to, have to look which size is defined in the database, but no way to type mysqgldatatype without the type, then the variable and the parameter direction.

    when i run the asp.net page on my machione it still tells me that the parameter isn´t find in the collection,

    but what is funny is: it always tells me that parameter prod_name isn´t found in the collection, no matter where i type the code (below or above of the other two) so could it be possible that this one ist the one which produces trouble?

    thank you so lot for your patience

    Monday, August 13, 2012 11:38 AM