1 Replies - 468 Views - Last Post: 11 April 2013 - 10:47 PM

#1 gutchman84  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 27-March 11

Cannot update records into MySQL database

Posted 11 April 2013 - 10:12 PM

I am working on part of a Web-Based Employee web application for Senior Project in Computer Science, creating a web form for adminstrators. I cannot get the UPDATE statement to update the values for the current E-mail address stored in the admin_business_profile (gutchman84@yahoo.com). Our Senior Project group is using MySQL Workbench to manage the database.

And here is the website showing how to connect to a MySQL Database using Microsoft Visual Studio 2010:
My link

Below are the credentials for the database we are currently using:

MySQL/SQL Server dcm.uhcl.edu
MySQL/SQL Server Username c486813sp01g1
MySQL/SQL Server Password ****
MySQL Database Name c486813sp01g1



Here is my code for the Business_Profile.aspx web form:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Business_Profile_Page.aspx.cs" Inherits="Employee_Scheduling.Business_Profile_Page" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
<div>

    <h1>Business Profile</h1>

        <table>
        
        <tr>
        <!-- Enter Business Login ID-->
        <td align="left">Business Login ID</td>
        <td align="left"><asp:TextBox ID="BusLogin"  Width="320" runat="server"/></td>
        <td align="left">

        <!-- Add RequiredFieldValidator for Business Login ID-->
        <asp:RequiredFieldValidator id="ReqBusLogin" runat="server" 
        ControlToValidate="BusLogin"> </asp:RequiredFieldValidator>
    
        <!-- Add RegularExpressionValidator for Business Login ID-->
        <asp:RegularExpressionValidator ID="ValBusLogin" runat="server"
        ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
        ControlToValidate="BusLogin" > </asp:RegularExpressionValidator>
        </td>
        </tr>


        <tr>
        <!-- Enter Business Password-->
        <td align="left">Business Password</td>
        <td align="left"><asp:TextBox ID="BusPass"  Width="132" runat="server"/></td>
        <td align="left">

        <!-- Add RequiredFieldValidator for Business Password-->
        <asp:RequiredFieldValidator id="ReqBusPass" runat="server" ControlToValidate="BusPass" > 
        </asp:RequiredFieldValidator>
    
        <!-- Add RegularExpressionValidator for Business Password-->
        <asp:RegularExpressionValidator ID="ValBusPass" runat="server"
        ValidationExpression="^(?=\D*\d)(?=[^a-zA-Z]*[a-zA-Z])(?!.*(.)\1).{8,}$" 
        ControlToValidate="BusPass"> </asp:RegularExpressionValidator>
        </td>
        </tr>


        <tr>
        <!-- Enter Business Name-->
        <td align="left">Business Name</td>
        <td align="left"><asp:TextBox ID="BusName"  Width="320px" runat="server"/></td>
        <td align="left">
        <!-- Add RequiredFieldValidator for Business Name-->
        <asp:RequiredFieldValidator id="ReqBusName" runat="server" ControlToValidate="BusName"> 
        </asp:RequiredFieldValidator>
        </td>
        </tr>

        
        <tr>
        <!-- Enter Address -->
        <td align="left">Address</td>
        <td align="left"><asp:TextBox ID="BusAddr"  Width="320px" runat="server"/></td>
        <td align="left">
        <!-- Add RequiredFieldValidator for Address-->
        <asp:RequiredFieldValidator id="ReqAddr" runat="server" 
        ControlToValidate="BusAddr"></asp:RequiredFieldValidator>
        </td>
        </tr>

        
        <tr>
        <!-- Enter City -->
        <td align="left">City</td>
        <td align="left"><asp:TextBox ID="BusCity"  Width="215px" runat="server"/></td>
        <td align="left">
        <!-- Add RequiredFieldValidator for City-->
        <asp:RequiredFieldValidator ID="ReqCity" runat="server" ControlToValidate="BusCity"></asp:RequiredFieldValidator>
    
        <!-- Add RegularExpressionValidator for City-->
        <asp:RegularExpressionValidator 
        ID="valCity" runat="server" ValidationExpression="^([a-zA-Z]+|[a-zA-Z]+\s[a-zA-Z]+)$"
        ControlToValidate="BusCity" ></asp:RegularExpressionValidator>
        </td>
        </tr>

        
        <tr>
        <!-- Select State from DropDownList-->
        <td align="left">State</td>
        <td align="left">
        <asp:DropDownList ID="BusState" runat="server" >
	<asp:ListItem Value="AL">AL</asp:ListItem>
	<asp:ListItem Value="AK">AK</asp:ListItem>
	<asp:ListItem Value="AZ">AZ</asp:ListItem>
	<asp:ListItem Value="AR">AR</asp:ListItem>
	<asp:ListItem Value="CA">CA</asp:ListItem>
	<asp:ListItem Value="CO">CO</asp:ListItem>
	<asp:ListItem Value="CT">CT</asp:ListItem>
	<asp:ListItem Value="DE">DE</asp:ListItem>
	<asp:ListItem Value="FL">FL</asp:ListItem>
	<asp:ListItem Value="GA">GA</asp:ListItem>
	<asp:ListItem Value="HI">HI</asp:ListItem>
	<asp:ListItem Value="ID">ID</asp:ListItem>
	<asp:ListItem Value="IL">IL</asp:ListItem>
	<asp:ListItem Value="IN">IN</asp:ListItem>
	<asp:ListItem Value="IA">IA</asp:ListItem>
	<asp:ListItem Value="KS">KS</asp:ListItem>
	<asp:ListItem Value="KY">KY</asp:ListItem>
	<asp:ListItem Value="LA">LA</asp:ListItem>
	<asp:ListItem Value="ME">ME</asp:ListItem>
	<asp:ListItem Value="MD">MD</asp:ListItem>
	<asp:ListItem Value="MA">MA</asp:ListItem>
	<asp:ListItem Value="MI">MI</asp:ListItem>
	<asp:ListItem Value="MN">MN</asp:ListItem>
	<asp:ListItem Value="MS">MS</asp:ListItem>
	<asp:ListItem Value="MO">MO</asp:ListItem>
	<asp:ListItem Value="MT">MT</asp:ListItem>
	<asp:ListItem Value="NE">NE</asp:ListItem>
	<asp:ListItem Value="NV">NV</asp:ListItem>
	<asp:ListItem Value="NH">NH</asp:ListItem>
	<asp:ListItem Value="NJ">NJ</asp:ListItem>
	<asp:ListItem Value="NM">NM</asp:ListItem>
	<asp:ListItem Value="NY">NY</asp:ListItem>
	<asp:ListItem Value="NC">NC</asp:ListItem>
	<asp:ListItem Value="ND">ND</asp:ListItem>
	<asp:ListItem Value="OH">OH</asp:ListItem>
	<asp:ListItem Value="OK">OK</asp:ListItem>
	<asp:ListItem Value="OR">OR</asp:ListItem>
	<asp:ListItem Value="PA">PA</asp:ListItem>
	<asp:ListItem Value="RI">RI</asp:ListItem>
	<asp:ListItem Value="SC">SC</asp:ListItem>
	<asp:ListItem Value="SD">SD</asp:ListItem>
	<asp:ListItem Value="TN">TN</asp:ListItem>
	<asp:ListItem Value="TX">TX</asp:ListItem>
	<asp:ListItem Value="UT">UT</asp:ListItem>
	<asp:ListItem Value="VT">VT</asp:ListItem>
	<asp:ListItem Value="VA">VA</asp:ListItem>
	<asp:ListItem Value="WA">WA</asp:ListItem>
	<asp:ListItem Value="WV">WV</asp:ListItem>
	<asp:ListItem Value="WI">WI</asp:ListItem>
	<asp:ListItem Value="WY">WY</asp:ListItem>
</asp:DropDownList>
        </td>
        </tr>


        <tr>
        <!-- Enter Zip Code-->
        <td align="left">Zip Code</td>
        <td align="left"><asp:TextBox ID="BusZip"  Width="93px" runat="server"/></td>
        <td align="left">

        <!-- Add RequiredFieldValidator for Zip Code-->
        <asp:RequiredFieldValidator id="ReqZipCode" runat="server" 
                ControlToValidate="BusZip"></asp:RequiredFieldValidator>
    
        <!-- Add RegularExpressionValidator for Zip Code-->
        <asp:RegularExpressionValidator id="ValZipCode" runat="server"
        ValidationExpression="^(\d{5}-\d{4}|\d{5}|\d{9})$|^([a-zA-Z]\d[a-zA-Z] \d[a-zA-Z]\d)$"
        ControlToValidate="BusZip" ></asp:RegularExpressionValidator>
        </td>
        </tr>

        </table>
    <br />
     
    <br />
    <br />
    <asp:Button ID="SaveAndCont" runat="server" Text="Save and Continue" onclick="SaveAndContinue" />
</div> 
</form>
</body>
</html>




and here is the C# code behind for the 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;
using System.Windows.Forms;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;

namespace Employee_Scheduling
{
    public partial class Business_Profile_Page : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void SaveAndContinue(object sender, EventArgs e)
        {
            string BusinessID = Convert.ToString(BusLogin);
            string BusinessPassword = Convert.ToString(BusPass);
            string BusinessName = Convert.ToString(BusName);
            string BusinessAddress = Convert.ToString(BusAddr);
            string BusinessCity = Convert.ToString(BusCity);
            string BusinessState = Convert.ToString(BusState.SelectedValue);
            string BusinessZip = Convert.ToString(BusZip);
            string source = "server=dcm.uhcl.edu;userid=c486813sp01g1;password=1845160;database=c486813sp01g1";

            MySqlConnection connections = new MySqlConnection(source);

            try
            {
                connections.Open();
                MySqlCommand updatecmd = new MySqlCommand("Select count(*) from admin_business_profile", connections);
                int numberOfRows = System.Convert.ToInt32(updatecmd.ExecuteScalar());

                if (numberOfRows == 0)
                {
                    string insert1 = "INSERT INTO admin_business_profile(Business_ID, Business_Password, Business_Name, Address, City, State, Zip_Code) VALUES ('" + BusLogin.Text + "', '" + BusPass.Text + "', '" + BusName.Text + "', '" + BusAddr.Text + "', '" + BusCity.Text + "', '" + BusinessState + "', '" + BusZip.Text + "')";
                    MySqlCommand cmd1 = new MySqlCommand(insert1, connections);
                    cmd1.ExecuteNonQuery();
                }
                else             
                    if (numberOfRows != 0)
                    {
                        string update1 = "Update admin_business_profile Set Business_ID='" + BusLogin.Text + "', Business_Password='" + BusPass.Text + "', Business_Name='" + BusName.Text + "', Address='" + BusAddr.Text + "', City='" + BusCity.Text + "', State='" + BusinessState + "', Zip_Code='" + BusZip.Text + "' Where Business_ID=gutchman84@yahoo.com)";
                        MySqlCommand updatecm = new MySqlCommand(update1, connections);
                        updatecm.ExecuteNonQuery();
                    }
            }

            catch (MySqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connections.Close();
            }
        }
    }
}



Originally, I used an integer value for the Zip Code, but whenever I clicked on the "Save and Continue" button on the web form, I got an error message as shown below "Error when using Zip Code as integer" jpeg file. However, right now the zip code data type has changed from int to varchar(5). But after filling all of the fields and clicking on the "Save and Continue" button, the message as shown in the attached "MySQL exception message - email address" jpeg file. Should i consider changing something in the UPDATE statement?

Attached image(s)

  • Attached Image
  • Attached Image

This post has been edited by Dormilich: 11 April 2013 - 10:44 PM
Reason for edit:: it’s usually not a good idea to post your passwords


Is This A Good Question/Topic? 0
  • +

Replies To: Cannot update records into MySQL database

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3515
  • View blog
  • Posts: 10,144
  • Joined: 08-June 10

Re: Cannot update records into MySQL database

Posted 11 April 2013 - 10:47 PM

well, @ is a special character in SQL ('username'@'database'). and strings usually do not go unquoted into queries.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1