Avatar of GregorySinger
GregorySinger
Flag for United States of America asked on

Execute stored procedure in mysql database

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.
MySQL ServerC#

Avatar of undefined
Last Comment
GregorySinger

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Olaf Doschke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GregorySinger

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

Greg
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23