Last post Aug 13, 2012 11:38 AM by Blubb21
Aug 04, 2012 10:37 AM|Blubb21|LINK
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
Aug 04, 2012 12:26 PM|web_web|LINK
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.
Aug 08, 2012 05:27 PM|Blubb21|LINK
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...
Aug 08, 2012 08:44 PM|Lannie|LINK
the call from .NET and the database stored procedure.
then maybe we forum visitors can help you better
Aug 09, 2012 12:01 PM|Blubb21|LINK
here is my code:
CREATE PROCEDURE insert_prod(IN prod_name VARCHAR(30), IN price DOUBLE, IN stock_quantity INT(11) )
Insert into product (prod_name, price, stock_quantity) VALUES (prod_name, price, stock_quantity);
connection string <connectionStrings>
<add name="testConnectionString" connectionString="server=localhost;User Id=root;database=test; Allow User Variables=True"
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;
Aug 09, 2012 05:13 PM|Lannie|LINK
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.
Aug 12, 2012 09:26 AM|Blubb21|LINK
this looks like VB.net to me, could you give me an example code for C#?
Aug 12, 2012 06:48 PM|Lannie|LINK
dynamic decPrice = Convert.ToDecimal(Preis.Text);
cmd.Parameters.Add("price", MySqlDbType, decPrice, ParameterDirection.Input).Value == decPrice;
useful converter for light conversion jobs
Aug 13, 2012 11:38 AM|Blubb21|LINK
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