Solved

how do I fix mysql errors?

Posted on 2008-10-21
2
588 Views
Last Modified: 2013-12-17
Okay, I installed the mysql connector version 5.2.3.0, however I am getting a list of errors:

MySql.Data.MySqlClient.MySqlException: SELECT command denied to user 'etcmktngl_pxy'@'10.4.1.54' for table 'proc' at MySql.Data.MySqlClient.MySqlStream.OpenPacket() at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId) at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet() at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.SchemaProvider.GetProcedures(String[] restrictions) at MySql.Data.MySqlClient.ISSchemaProvider.GetProcedures(String[] restrictions) at MySql.Data.MySqlClient.ISSchemaProvider.GetSchemaInternal(String collection, String[] restrictions) at MySql.Data.MySqlClient.SchemaProvider.GetSchema(String collection, String[] restrictions) at MySql.Data.MySqlClient.MySqlConnection.GetSchema(String collectionName, String[] restrictionValues) at MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName) at MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName) at MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName) at MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName) at MySql.Data.MySqlClient.StoredProcedure.Resolve() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at ContractDetail.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\NGL Marketing System\ContractDetail.aspx.cs:line 46


I was wondering what the possible issue(s) can be. Here is the code to the page that I am getting the errors on.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Text;
public partial class ContractDetail : System.Web.UI.Page
{
    MySqlConnection con;
    MySqlCommand cmd;
    MySqlDataReader drCont,dr_prod;
    int cmctnum;
    protected void Page_Load(object sender, EventArgs e)
    {
 
        phHeader.Controls.Add(LoadControl("ucHeader.ascx"));
        try
        {
 
            if (Session["uid"] == null) //To check if userid is null..
                Response.Redirect("errorPage.aspx");
            cmctnum = Convert.ToInt32(Request.QueryString["ContractNo"]);//Contract number
            string sConnection;
            sConnection = System.Configuration.ConfigurationManager.AppSettings["Connection"];
            con = new MySqlConnection(sConnection);
            if (!IsPostBack)
            {
            cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "proc_contact_details"; //Stored procedure for contact details detail view..
            con.Open();
            //Adding parameter to stored procedure
            cmd.Parameters.Add("cmctnum", MySqlDbType.Int32);
            cmd.Parameters["cmctnum"].Value = cmctnum ;//1015;
            cmd.Parameters.Add("compno", MySqlDbType.Int32);
            cmd.Parameters["compno"].Value = Session["compno"];
            try
            {
                drCont = cmd.ExecuteReader();
 
                if (drCont.Read()) //If there are values..
                {
                    // lbl_comp.Text = drCont["BZNUM"].ToString()+" "+ drCont["BZUNIT"];
                    lbl_comp.Text = Session["compname"].ToString();
                    lbl_contno.Text = cmctnum.ToString();
                    lbl_contname.Text = drCont["CMNAME"].ToString();
                    if (!drCont["CMCODT"].Equals("00/00/0000"))
                        lbl_contdate.Text = drCont["CMCODT"].ToString();
                    else
                        lbl_contdate.Text = "";
                    lbl_conttype.Text = drCont["TYP"].ToString();
                    //lbl_contsrc.Text = drCont["srce"].ToString();
                    //lbl_evgrn.Text = drCont["grn"].ToString();
                    lbl_intcomp.Text = drCont["INTC"].ToString();
                    lbl_prtyid.Text = drCont["CMPARI"].ToString();
                    lbl_prtyname.Text = drCont["NNAME"].ToString();
                    lbl_sapvenno.Text = drCont["CMSAPV"].ToString();
                    lbl_sapcustno.Text = drCont["CMSAPC"].ToString();
                    lbl_contpty.Text = drCont["CMPARC"].ToString();
                    //if (!drCont["CMEFBDT"].Equals("00/00/0000"))
                    //    lbl_effdate.Text = drCont["CMEFBDT"].ToString();
                    //else
                    //    lbl_effdate.Text  = "";
                    //if (!drCont["CMTEBDT"].Equals("00/00/0000"))
                    //    lbl_contterm.Text = drCont["CMTEBDT"].ToString();
                    //else
                    //    lbl_contterm.Text  = "";
                    //if (!drCont["CMTEBDT"].Equals("00/00/0000"))
                    //    lbl_thrudt.Text = drCont["CMTEEDT"].ToString();
                    //else
                    //    lbl_thrudt.Text  = "";
                    lbl_ptterm.Text = drCont["TERMTYPE"].ToString();
                    lbl_pmtdday.Text = drCont["CMPMTD"].ToString();
                    lbl_ddaydesc.Text = drCont["DES"].ToString();
                    drCont.Close();
                }//If(contract details dataread) block close
                drCont.Close();
            }
                finally
            {
                con.Close();
            }
            //Product details..
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT PCCODE,PCLDES FROM etcmarketingngl.gmpprod g;"+
                "SELECT PRCODE,PRDESC FROM etcmarketingngl.gmppric g;"; //Query for Products and price index drop downlists..
            con.Open();
            dr_prod = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            ddl_product.DataSource = dr_prod;
            ddl_product.DataTextField = "PCLDES";
            ddl_product.DataValueField = "PCCODE";
            ddl_product.DataBind(); //Data binding into product drop down list
            dr_prod.NextResult();
            ddl_price.DataSource = dr_prod;
            ddl_price.DataTextField="PRDESC";
            ddl_price.DataValueField="PRCODE";
            ddl_price.DataBind();  //Data binding into price Drop Down list
            dr_prod.Close();
            pricedetails(1); //By default first product's details will be displayed
           
          }//End of If(postback) block
           
       }//End of Try Block
        catch(Exception ex)
        {
            Response.Write(ex.ToString()) ;
        }
    }
         void pricedetails(int code)
    {
        //PriceIndex, Account Number and Account title
        //In products drop down list, which product is selected(code), corresponding values will be displayed for that company and contract number.
        string cmdproc;
        cmd = new MySqlCommand();
        cmd.Connection = con;
        cmdproc = "SELECT" +
                     "(SELECT UTDESC FROM GMPUTYPE,GMPCONT WHERE UTTYPE=CMRT0" + code + " AND CMCTNO=" + cmctnum + " AND CMCONO= " + Session["compno"] + ") as rep," +
                     "(SELECT UTDESC FROM GMPUTYPE,GMPCONT WHERE UTTYPE=CMVT0" + code + " AND CMCTNO=" + cmctnum + " AND CMCONO= " + Session["compno"] + ") as vol," +
                     "CMPPC" + code + ",CMTPC" + code + ",CMMPC" + code + ",CMRCG" + code + ",CMRCS" + code + ",CMTAG" + code + ",CMTAS" + code + ",CMMFG" + code + ",CMMFS" + code + "," +
                     "(SELECT GCLDES FROM GLPCOA WHERE GCACGN=CMRCG" + code + " AND GCACSB=CMRCS" + code + " AND GCCOMP=CMCONO)AS PACTTITLE," +
                     "(SELECT GCLDES FROM GLPCOA WHERE GCACGN=CMTAG" + code + " AND GCACSB=CMTAS" + code + " AND GCCOMP=CMCONO)AS TACTTITLE," +
                     "(SELECT GCLDES FROM GLPCOA WHERE GCACGN=CMMFG" + code + " AND GCACSB=CMMFS" + code + " AND GCCOMP=CMCONO)AS MACTTITLE" +
                     " FROM GMPCONT WHERE CMCTNO=" + cmctnum + " AND CMCONO=" + Session["compno"];         
       cmd.CommandText = cmdproc ; 
       con.Open();
       dr_prod = cmd.ExecuteReader(CommandBehavior.CloseConnection);
       if (dr_prod.Read())
       {
           lbl_uvol.Text = dr_prod["vol"].ToString();
           lbl_ureport.Text = dr_prod["rep"].ToString();
           lbl_pprofit.Text = dr_prod[2].ToString();
           lbl_tprofit.Text = dr_prod[3].ToString();
           lbl_mprofit.Text = dr_prod[4].ToString();
           if (!dr_prod[5].ToString().Equals("0"))
               lbl_pactgen.Text = dr_prod[5].ToString();//By defaul this label contains 000. if there is data available in database for this column,that data will be shown, otherwise, 000 will be shown
           else
               lbl_pactgen.Text = "000";
           if (!dr_prod[6].ToString().Equals("0"))
               lbl_pactsub.Text = dr_prod[6].ToString();
           else
               lbl_pactsub.Text = "0000";
           if (!dr_prod[7].ToString().Equals("0"))
               lbl_tactgen.Text = dr_prod[7].ToString();
           else
               lbl_tactgen.Text = "000";
           if (!dr_prod[8].ToString().Equals("0"))
               lbl_tactsub.Text = dr_prod[8].ToString();
           else
               lbl_tactsub.Text = "0000";
           if (!dr_prod[9].ToString().Equals("0"))
               lbl_mactgen.Text = dr_prod[9].ToString();
           else
               lbl_mactgen.Text = "000";
           if (!dr_prod[10].ToString().Equals("0"))
               lbl_mactsub.Text = dr_prod[10].ToString();
           else
               lbl_mactsub.Text = "0000";
           try
           {
               if (Convert.ToInt32(lbl_pactgen.Text) != 0 || Convert.ToInt32(lbl_pactsub.Text) != 0)
                   if (dr_prod[11].ToString() != "")
                   {
                       lbl_pacttitle.Text = dr_prod[11].ToString();
                       lbl_pacttitle.BackColor = System.Drawing.Color.White;
                   }
                   else
                   {
                       lbl_pacttitle.Text = "Account Number Not found";
                       lbl_pacttitle.BackColor = System.Drawing.Color.Red;
                   }
               else
               {
                   lbl_pacttitle.Text = dr_prod[11].ToString();
                   lbl_pacttitle.BackColor = System.Drawing.Color.White;
               }
           }
           catch
           {
               lbl_pacttitle.Text = dr_prod[11].ToString();
               lbl_pacttitle.BackColor = System.Drawing.Color.White;
 
           }
           //lbl_pacttitle.Text = dr_prod[11].ToString();
           try
           {
               if (Convert.ToInt32(lbl_tactgen.Text) != 0 || Convert.ToInt32(lbl_tactsub.Text) != 0)
                   if (dr_prod[12].ToString() != "")
                   {
                       lbl_tacttitle.Text = dr_prod[12].ToString();
                       lbl_tacttitle.BackColor = System.Drawing.Color.White;
                   }
                   else
                   {
                       lbl_tacttitle.Text = "Account Number Not found";
                       lbl_tacttitle.BackColor = System.Drawing.Color.Red;
                   }
               else
               {
                   lbl_tacttitle.Text = dr_prod[12].ToString();
                   lbl_tacttitle.BackColor = System.Drawing.Color.White;
               }
           }
           catch
           {
               lbl_tacttitle.Text = dr_prod[12].ToString();
               lbl_tacttitle.BackColor = System.Drawing.Color.White;
 
           }
           // lbl_tacttitle.Text = dr_prod[12].ToString();
           try
           {
               if (Convert.ToInt32(lbl_mactgen.Text) != 0 || Convert.ToInt32(lbl_mactsub.Text) != 0)
                   if (dr_prod[13].ToString() != "")
                   {
                       lbl_macttitle.Text = dr_prod[13].ToString();
                       lbl_macttitle.BackColor = System.Drawing.Color.White;
                   }
                   else
                   {
                       lbl_macttitle.Text = "Account Number Not found";
                       lbl_macttitle.BackColor = System.Drawing.Color.Red;
                   }
               else
               {
                   lbl_macttitle.Text = dr_prod[13].ToString();
                   lbl_macttitle.BackColor = System.Drawing.Color.White;
               }
           }
           catch
           {
               lbl_macttitle.Text = dr_prod[13].ToString();
               lbl_macttitle.BackColor = System.Drawing.Color.White;
 
           }
           // lbl_macttitle.Text = dr_prod[13].ToString();
           dr_prod.Close();
       }
   }
      
    protected void ddl_product_SelectedIndexChanged(object sender, EventArgs e)
    {
        int code = Convert.ToInt16(ddl_product.SelectedValue);
        pricedetails(code);//Function to display product details
        
    }
 
}

Open in new window

0
Comment
Question by:bschave2
2 Comments
 
LVL 1

Accepted Solution

by:
sadshead earned 500 total points
ID: 22768792
Looks to me like you have not setup the user etcmktngl_pxy  in the privledges for your mysql database.
0
 

Author Closing Comment

by:bschave2
ID: 31508365
this wasn't it. the try and catch wasn't using the right close for the connection and some other issues were the casue. I really appreciate your help though.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I bind a WPF ComboBox to an ItemSource using XAML? 2 32
Vb.net threads keep increasing 2 50
EditableGrid how to fetch rows from MySql in php 14 48
MySQL-Design Help 12 44
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question