Friday, February 17

INSERT IN TO MYSQL WITH ASP.NET


Here are the scripts you’ll need to create the verse schema (database). To execute the scripts, you’ll need to use the mysql command line utility or MySQL Query Browser application. You can create any database with this table and change the database name.

CREATE TABLE IF NOT EXISTS `membership` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `first_name` varchar(25) NOT NULL,

  `last_name` varchar(25) NOT NULL,

  `username` varchar(25) NOT NULL,

  `password` varchar(10) NOT NULL,

  `email_address` varchar(50) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

we have to use MySql.Data.MySqlClient; to use mysql database.

We use ? as mysql Parameters.

Here is the html markup code





<%@ Page Language="C#" AutoEventWireup="true" CodeFile="InsertDataInAsp.aspx.cs" Inherits="InsertDataInAsp" %>


<!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 id="Head1" runat="server">


    <title>Insert Into Mysql with asp.net</title>


</head>




<body>

    <form id="form1" runat="server" a>


    <div>

<table width="300px" >


    <tr>


    <td>First Name</td>
    <td>


        <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
        </td>


    </tr>


        <tr>



    <td >Last Name</td>


    <td>


        <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>


            </td>


    </tr>


        <tr>


    <td>User Name</td>



    <td>





        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>




            </td>


    </tr>


        <tr>


    <td>Password</td>
    <td>


        <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
       </td>


    </tr>


        <tr>



    <td>Email Address</td>


    <td>




        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>


            </td>


    </tr>


        <tr>




    <td>&nbsp;</td>




    <td>



        <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />


       </td>


    </tr>



        <tr>




    <td>&nbsp;</td>


    <td>


        <asp:Label ID="lblError" runat="server" Text=""></asp:Label>




            </td>


    </tr>


    </table>




    </div>




    </form>




</body>


</html>



Here’s the C# code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

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

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            string connectn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection con;
            SqlCommand cmd;
            string comstring = "INSERT INTO membership (first_name ,last_name ,username ,password ,";
            comstring += "email_address)VALUES (@first_name ,@last_name ,@username ,@password ,@email_address);";
            con = new SqlConnection(connectn);
            cmd = new SqlCommand(comstring, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = txtFirstName.Text;
            cmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = txtLastName.Text;
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = txtUserName.Text;
            cmd.Parameters.Add("@password",SqlDbType.VarChar).Value = txtPassword.Text;
            cmd.Parameters.Add("@email_address", SqlDbType.VarChar).Value = txtEmail.Text;
            con.Open();
            int result = cmd.ExecuteNonQuery();
            lblError.Text = "data inserted successfully";

        }

        catch
        {
            lblError.Text = "error occured";
        }
    }
}

My web.config connection strings



<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>



Bellow is the html form picture and successful message after insert data in to mysql database.



You can download the full code with the MySql.Data.MySqlClient; DLL

Download



Thanks Shibashish Mohanty. 

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty