GregorySinger
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.Acc ount
>
>
>
protected void btnAddCo_Click(object sender, EventArgs e)
{
string conn = "server=mysqlcluster8.regi steredsite .com;user id=glsinger684;persistsecu rityinfo=T rue;passwo rd=<mypass word>;data base=estim ating";
MySqlConnection sql_conn = new MySqlConnection(conn);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = sql_conn;
sql_conn.Open();
cmd.CommandType = CommandType.StoredProcedur e;
cmd.CommandText = "CALL create_co_record (PrincipalID)";
cmd.Parameters.AddWithValu e("Princip alID", "TESTTEST");
cmd.Parameters["PrincipalI D"].Direct ion = 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.
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.
using Owin;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
namespace EstimatingSheet_080916.Acc
>
>
>
protected void btnAddCo_Click(object sender, EventArgs e)
{
string conn = "server=mysqlcluster8.regi
MySqlConnection sql_conn = new MySqlConnection(conn);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = sql_conn;
sql_conn.Open();
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "CALL create_co_record (PrincipalID)";
cmd.Parameters.AddWithValu
cmd.Parameters["PrincipalI
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You hit the nail on the head. After a few minor modifications to other code lines, it worked just fine.
Thanks again!
Greg