Solved

Populate MySql database fields in C#

Posted on 2015-02-16
25
269 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
  • 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
 

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 34

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 34

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 34

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 34

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 34

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 34

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now