We help IT Professionals succeed at work.

Execute stored procedure in mysql database

GregorySinger
on
122 Views
Last Modified: 2016-09-07
I have setup a mysql database that successfully connects and populates datagrids in my VS2015-c# application under development.
I have created a stored procedure:

CREATE DEFINER=`glsinger684`@`%` PROCEDURE `create_co_record`(IN `PrincipalID` VARCHAR(50))
      LANGUAGE SQL
      NOT DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT 'Creates new company record with PrincipalID'
BEGIN
insert into company_info (PrincipalID)
values (PrincipalID);
END

I can successfully execute the stored procedure from a query using the management utility.
Next to test from my asp.net app I have the following code linked to a button:

using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Configuration;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.Owin;
using Owin;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;

namespace EstimatingSheet_080916.Account
>
>
>
     protected void btnAddCo_Click(object sender, EventArgs e)
        {
            string conn = "server=mysqlcluster8.registeredsite.com;user id=glsinger684;persistsecurityinfo=True;password=<mypassword>;database=estimating";
            MySqlConnection sql_conn = new MySqlConnection(conn);

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = sql_conn;
            sql_conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "CALL create_co_record (PrincipalID)";          
            cmd.Parameters.AddWithValue("PrincipalID", "TESTTEST");
            cmd.Parameters["PrincipalID"].Direction = ParameterDirection.Input;

When I run the app and hit the button nothing appears to happen.  I get no errors, but the desired effect of the SP does not occur.  Since the datagrids populate I don't see why this would be a permissions issue, but in any event any research on granting permissions that I have tried does not work.
Any suggestions would be much appreciated.
Comment
Watch Question

Software Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Olaf,
You hit the nail on the head.  After a few minor modifications to other code lines, it worked just fine.
Thanks again!

Greg
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.