Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Populate MySql database fields in C#

I'm new to C# and Visual Studio so please excuse me. I created a page to add a user to an existing database. I've created the connection to the (remote) database and provided variables. I run the page and it doesn't add the user to my database. What am I missing?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// Add database connectors
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Windows.Forms;



/// <summary>
/// Summary description for clsDataLayer
/// </summary>



public class clsDataLayer
{
   

//internal static bool CreateUser(System.Web.UI.WebControls.TextBox custFName,System.Web.UI.WebControls.TextBox custLName,System.Web.UI.WebControls.TextBox custEmail,System.Web.UI.WebControls.TextBox custLogin,System.Web.UI.WebControls.TextBox custPassword)
public static bool CreateUser(string custFName, string custLName, string custLogin, string custEmail, string custPassword)
{
    {

        using (MySqlConnection connection = new MySqlConnection("server=74.50.87.131;user id=---USERID---;database=tcamach_WSC;password=---PASSWORD---;persistsecurityinfo=True"))
{
try{
string query = "Insert into customer (custFName, custLName, custLogin, custEmail, custPassword) values (@custFName, @custLName, @custLogin, @custEmail, @custPassword);";
              connection.Open();
            // set the command text of the command object0
               using (MySqlCommand cmd = new MySqlCommand(query, connection))
{

                cmd.Parameters.AddWithValue("@custFname", custFName);
                cmd.Parameters.AddWithValue("@custLName", custLName);
                cmd.Parameters.AddWithValue("@custLogin", custLogin);
                cmd.Parameters.AddWithValue("@custEmail", custEmail);
                cmd.Parameters.AddWithValue("@custPassword", custPassword);

                cmd.ExecuteNonQuery();
               }
   

             
    }
    catch (MySqlException)
{
        return false;
    }

    }

   
}
       throw new NotImplementedException();
}
}
0
Robin Harris
Asked:
Robin Harris
  • 10
  • 8
  • 6
4 Solutions
 
AndyAinscowCommented:
For your own safety please don't provide real username/password when you quote a connection string for a database.  I've asked for a moderator to edit it out of the question.
0
 
AndyAinscowCommented:
catch (MySqlException)
{
        return false;
    }

I would suggest you provide some feedback mechanism rather than returning false.  At a guess you have some sort of error and would see at that point what is possibly going wrong.
0
 
Robin HarrisAuthor Commented:
I'm going to delete the question and start over.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Robin HarrisAuthor Commented:
I've requested that this question be deleted for the following reason:

I added a username and password in my code. It needs to be removed.
0
 
AndyAinscowCommented:
I've already asked a moderator to edit the question.  The delete process takes a number of days before it is completed when an expert has made a comment.
I'm putting in an object to the delete (because anyone can read it even if a delete is pending) so we can continue here.
0
 
gr8gonzoConsultantCommented:
Is this an ASP.NET web page written in C#? Or is this an application? One way or another, I don't see anything that calls your static method. Is there any more code than what you've shown?
0
 
Robin HarrisAuthor Commented:
This is an ASP.Net web page written in C#. I have a second page that has the click event in it. I've taken one C# class two years ago, most of this is German to me. Here is the code from the second page:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;


namespace WSC1
{
    public partial class Registration : System.Web.UI.Page
    {
       
       protected void btnCreateAccount_Click(object sender, EventArgs e)
        {
           
            if (TxtPassword1.Text != custPassword.Text)
            {
                lblPasswordStatus.Text = "The passwords do not match";

            }



            if (clsDataLayer.CreateUser(custFName.Text , custLName.Text , custEmail.Text , custLogin.Text , custPassword.Text ) == true)
            {
                lblNewCustomerStatus.Text = "The user was successfully added!";
            }

            else
                lblNewCustomerStatus.Text = "The user could not be added";
        }

       
    }

}
0
 
AndyAinscowCommented:
What happens after the click - lblNewCustomerStatus.Text is what, success or failure?
0
 
Robin HarrisAuthor Commented:
The user could not be added
0
 
gr8gonzoConsultantCommented:
You don't return a true in your clsDataLayer after the query executes.
0
 
AndyAinscowCommented:
OK, back to what I said earlier.
At the exception display details of what the exception is
air code:

catch (MySqlException e)
{
MessageBox.Show(e);
        return false;
    }
0
 
Robin HarrisAuthor Commented:
I added these two lines:

connection.Close();
                return true;


I still get "The user could not be added" message. I realized that I wasn't closing the connection also.
0
 
AndyAinscowCommented:
Yes, but what is the exception - that will tell you what is failing (and hopefully what simple change is required to make it work).  Without that information anything is just a guess.
0
 
gr8gonzoConsultantCommented:
There might be an error happening, although the MessageBox.Show doesn't work ASP.NET, I didn't think. Try storing the last Exception as a static property and let the calling script handle it if it wants to:

Add this line into your class:
public static Exception lastException = null;

Change:
catch (MySqlException)
{
        return false;
    }

Open in new window


to:
catch (Exception ex) // <-- Catch the generic type, which should INCLUDE MySQL exceptions
{
        // Store the exception for later
        lastException = ex;

        return false;
}

Open in new window


Then in your calling script, change:
lblNewCustomerStatus.Text = "The user could not be added";

to:
lblNewCustomerStatus.Text = "The user could not be added. <!-- " + clsDataLayer.lastException.ToString() + " -->";

Then try again, and when it fails, view the source of the page and you should see the exception error message inside the <!-- --> HTML comment brackets.
0
 
Robin HarrisAuthor Commented:
Ok I will give that a shot and let you know how it turns out.
0
 
Robin HarrisAuthor Commented:
It looks like the exception isn't returning a value. Here is page 1:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;


namespace WSC1
{
    public partial class Registration : System.Web.UI.Page
    {
       
       protected void btnCreateAccount_Click(object sender, EventArgs e)
        {
           
            if (TxtPassword1.Text != custPassword.Text)
            {
                lblPasswordStatus.Text = "The passwords do not match";

            }



            if (clsDataLayer.CreateUser(custFName.Text , custLName.Text , custEmail.Text , custLogin.Text , custPassword.Text ) == true)
            {
                lblNewCustomerStatus.Text = "The user was successfully added!";
            }

            else
               
            lblNewCustomerStatus.Text = "The user could not be added. <!-- " + clsDataLayer.lastException.ToString() + " -->";


        }




Here is page 2:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// Add database connectors
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Windows.Forms;



/// <summary>
/// Summary description for clsDataLayer
/// </summary>



public class clsDataLayer{
public static Exception lastException = null;


   
   

//internal static bool CreateUser(System.Web.UI.WebControls.TextBox custFName,System.Web.UI.WebControls.TextBox custLName,System.Web.UI.WebControls.TextBox custEmail,System.Web.UI.WebControls.TextBox custLogin,System.Web.UI.WebControls.TextBox custPassword)
public static bool CreateUser(string custFName, string custLName, string custLogin, string custEmail, string custPassword)
{
    {

        using (MySqlConnection connection = new MySqlConnection("server=*****;user id=*****;database=*****;password=*****;persistsecurityinfo=True"))
{
try{
string query = "Insert into customer (custFName, custLName, custLogin, custEmail, custPassword) values (@custFName, @custLName, @custLogin, @custEmail, @custPassword);";
              connection.Open();
            // set the command text of the command object0
               using (MySqlCommand cmd = new MySqlCommand(query, connection))
{

                cmd.Parameters.AddWithValue("@custFname", custFName);
                cmd.Parameters.AddWithValue("@custLName", custLName);
                cmd.Parameters.AddWithValue("@custLogin", custLogin);
                cmd.Parameters.AddWithValue("@custEmail", custEmail);
                cmd.Parameters.AddWithValue("@custPassword", custPassword);

                cmd.ExecuteNonQuery();
                connection.Close();
                return true;

               }
   

             
    }
catch (Exception ex) // <-- Catch the generic type, which should INCLUDE MySQL exceptions
{
    // Store the exception for later
    lastException = ex;

    return false;

    }

    }

   
}
       throw new NotImplementedException();
}


}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// Add database connectors
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Windows.Forms;



/// <summary>
/// Summary description for clsDataLayer
/// </summary>



public class clsDataLayer{
public static Exception lastException = null;


    
    

//internal static bool CreateUser(System.Web.UI.WebControls.TextBox custFName,System.Web.UI.WebControls.TextBox custLName,System.Web.UI.WebControls.TextBox custEmail,System.Web.UI.WebControls.TextBox custLogin,System.Web.UI.WebControls.TextBox custPassword)
public static bool CreateUser(string custFName, string custLName, string custLogin, string custEmail, string custPassword)
{
    {

        using (MySqlConnection connection = new MySqlConnection("server=*****;user id=*****;database=*****;password=*****;persistsecurityinfo=True"))
{
try{
string query = "Insert into customer (custFName, custLName, custLogin, custEmail, custPassword) values (@custFName, @custLName, @custLogin, @custEmail, @custPassword);";
              connection.Open();
            // set the command text of the command object0
               using (MySqlCommand cmd = new MySqlCommand(query, connection))
{

                cmd.Parameters.AddWithValue("@custFname", custFName);
                cmd.Parameters.AddWithValue("@custLName", custLName);
                cmd.Parameters.AddWithValue("@custLogin", custLogin);
                cmd.Parameters.AddWithValue("@custEmail", custEmail);
                cmd.Parameters.AddWithValue("@custPassword", custPassword);

                cmd.ExecuteNonQuery();
                connection.Close();
                return true;

               }
    

             
    }
catch (Exception ex) // <-- Catch the generic type, which should INCLUDE MySQL exceptions
{
    // Store the exception for later
    lastException = ex;

    return false;

    }

    }

   
}
 	throw new NotImplementedException();
}


}

Open in new window

0
 
gr8gonzoConsultantCommented:
What's the result? (When you look at view source)
0
 
AndyAinscowCommented:
Do you mean the label shows:
The user could not be added. <!--  -->
0
 
Robin HarrisAuthor Commented:
<!-- MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'tcamach_WSC.customer' doesn't exist
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at clsDataLayer.CreateUser(String custFName, String custLName, String custLogin, String custEmail, String custPassword)

Looks like I have a problem with the database somehow. The database is connected and the table is there.
0
 
gr8gonzoConsultantCommented:
Are you certain that the user account has permissions to access that table?
0
 
Robin HarrisAuthor Commented:
Yep I just verified.
0
 
gr8gonzoConsultantCommented:
Well, the error message doesn't lie, so for whatever reason, it thinks the table's not there.

One way to help debug this would be to simply run the query SHOW TABLES using the server's conncetion and then dump the results to the screen:

query = "SHOW TABLES";
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
    MySqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        // Log the result of reader.GetString(0) to the screen or a file
    }
}

Open in new window

0
 
Robin HarrisAuthor Commented:
Got it working. The table names are case sensitive.
0
 
AndyAinscowCommented:
Also for the future you have seen you would have had enough information before asking the question to solve it.
Use the information supplied in the exception rather than throwing it away without looking at it.
Read the message supplied.
Check what was different between your SQL and the real situation inside the database.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now