Solved

Populate MySql database fields in C#

Posted on 2015-02-16
25
279 Views
Last Modified: 2015-02-16
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
Comment
Question by:Robin Harris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 6
25 Comments
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 125 total points
ID: 40612226
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612229
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
 

Author Comment

by:Robin Harris
ID: 40612241
I'm going to delete the question and start over.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Robin Harris
ID: 40612258
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612259
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
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40612275
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
 

Author Comment

by:Robin Harris
ID: 40612285
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612291
What happens after the click - lblNewCustomerStatus.Text is what, success or failure?
0
 

Author Comment

by:Robin Harris
ID: 40612296
The user could not be added
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40612300
You don't return a true in your clsDataLayer after the query executes.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612316
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
 

Author Comment

by:Robin Harris
ID: 40612325
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612338
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
 
LVL 35

Assisted Solution

by:gr8gonzo
gr8gonzo earned 375 total points
ID: 40612351
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
 

Author Comment

by:Robin Harris
ID: 40612362
Ok I will give that a shot and let you know how it turns out.
0
 

Author Comment

by:Robin Harris
ID: 40612627
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
 
LVL 35

Assisted Solution

by:gr8gonzo
gr8gonzo earned 375 total points
ID: 40612638
What's the result? (When you look at view source)
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40612639
Do you mean the label shows:
The user could not be added. <!--  -->
0
 

Author Comment

by:Robin Harris
ID: 40612655
<!-- 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
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 375 total points
ID: 40612665
Are you certain that the user account has permissions to access that table?
0
 

Author Comment

by:Robin Harris
ID: 40612677
Yep I just verified.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 40612803
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
 

Author Comment

by:Robin Harris
ID: 40612804
Got it working. The table names are case sensitive.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40613584
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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