Insert into sql database from a textbox when a button is clicked

Question

4.50/5 (2 votes)

See more:

Hello,

What I want is to get the info from 2 textboxes inserted into a sql database when the user clicks a button.

I have a line in my web.config with the connection string, and the name property of the connection string is "StrawDBConnectionString"
\
What I have working tests the connection string:

C#

string DBConn;
        protected void Page_Load(object sender, EventArgs e)
        {
          DBConn = ConfigurationManager.ConnectionStrings["StrawDBConnectionString"].ConnectionString;

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            // create the connection
           SqlConnection myConnection = new SqlConnection(DBConn);
           myConnection.Open();
            Label1.Text = ("ServerVersion: " + myConnection.ServerVersion +
                            "\nState: " + myConnection.State.ToString());
           myConnection.Close();

        }

So I have added another button to insert two textboxes into two columns of this database. The code from the book I am using says to add the following code:

C++

string _queryString;
           int _count;
           _queryString = "INSERT INTO Products (Product_Name, Product_Price) Values ('"+ TextBox1.Text + "' , '" + TextBox2.Text + "');";
           SqlCommand cmd = new SqlCommand(_queryString, DBConn);

At this point the code was already giving errors.
Then the book goes on about using OledbConnection but started out with a sql Database, so it does not work. The rest of the code from the book:

C#

using (OleDbConnection_connection = new OleDbConnection (connectionString))
{
OleDbCommand_command = new OleDbCommand (_queryString, _connection);
_connection.open();
_count = _command.ExecuteNonQuery();
}
return _count

The closest I got this working was with the following code:

C++

SqlConnection myConnection = new SqlConnection(DBConn);
            myConnection.Open();

            SqlCommand MyCommand = new SqlCommand("INSERT INTO Products (Product_Name, Product_Price) Values ('"+ TextBox1.Text + "' , '" + TextBox2.Text + "');");



            MyCommand.ExecuteNonQuery();
            myConnection.Close();
            myConnection.Dispose();

after getting it to run, when I click the button I get this error

Message="ExecuteNonQuery: Connection property has not been initialized."

I am not sure what to do next, my lecturer said this was the best site to come to for answers. I am hoping there will be someone who can help me.

Thanks in advance...

Posted 30-Jun-11 0:57am

censurl

Add a Solution

Comments

Tarun.K.S 30-Jun-11 7:09am

SqlCommand MyCommand = new SqlCommand("INSERT INTO Products (Product_Name, Product_Price) Values ('"+ TextBox1.Text + "' , '" + TextBox2.Text + "');", myConnection);
This should do the trick.

6 solutions

Solution 1

You need to set the connection property of your command object

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.connection.aspx[^]

You can do it via one of the overloads on the command construct

Also, try not to use concatenated SQL strings like you have, use named parameters instead

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx[^]

Posted 30-Jun-11 1:06am

Dylan Morley

Comments

Tarun.K.S 30-Jun-11 7:09am

Right. 5+

Solution 4

Firstly thanks to SanjeevSingh after putting in this line I was able to get it to work.

The I would like to thank Wayne Gaylard for bringing it all together so well.

This is what it looks like :

C#

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

using System.Configuration;
using System.Data.SqlClient;
using System.Data;



namespace PetStore
{
    public partial class _Default : System.Web.UI.Page
    {

        string DBConn;
        protected void Page_Load(object sender, EventArgs e)
        {
            DBConn = ConfigurationManager.ConnectionStrings["PetstoreDBConnectionString"].ConnectionString;

        }

        int InsertProduct()
        {
            using (SqlConnection myConnection = new SqlConnection(DBConn))
            {
                SqlCommand MyCommand = new SqlCommand("INSERT INTO TheTableName (Column1, Column2, Column3) Values (@Column1, @Column2, @Column3)", myConnection);
                MyCommand.Parameters.AddWithValue("@Column1", TextBox1.Text);
                MyCommand.Parameters.AddWithValue("@Column2", TextBox2.Text);
                MyCommand.Parameters.AddWithValue("@Column3", TextBox3.Text);
                myConnection.Open();
                return MyCommand.ExecuteNonQuery();

            }
        }

then simply added the following to the button_click event:

C#

protected void btnInsert_Click(object sender, EventArgs e)
        {
            InsertProduct();

        }

Now what I want to do is display the table in the database, this is what I have tried:

C++

int DisplayTable()
        {
            using (SqlConnection myConnection2 = new SqlConnection(DBConn))
           {
              SqlCommand MyCommand2 = new SqlCommand("SELECT * from TheTableName;", myConnection2);
              myConnection2.Open();
              SqlDataReader reader = MyCommand2.ExecuteReader();

           GridView1.DataSource  = reader;
           GridView1.DataBind();

           return MyCommand2.ExecuteNonQuery();
           }
      }

Then put in the button like I did before:

C#

protected void btnDisplay_Click(object sender, EventArgs e)
        {
            DisplayTable();

        }

but this does not work and gives the eorr:

"Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition."

What should I do?

Posted 2-Jul-11 10:04am

censurl

Solution 6

<%@page import ="java.sql.*"%>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("Jdbc:Odbc:gcms","root","priti12345");
Statement stm = con.createStatement();
int i=stm.executeUpdate("insert into admin(id,pass) values(5,'delhi')");
out.println("inserted");
%>

Posted 8-Dec-14 9:21am

Member 10655077

Solution 5

have a look at this:
http://webgeek.elletis.com/how-to-enter-html-code-into-database-from-a-textbox/[^]

Posted 8-Jun-12 21:50pm

Lizzee Lott

Comments

Member 11392252 6-May-15 17:51pm

i created a table product and it has data in it. But my ques is i want to add the data from that product table into myproduct table by just entering the ProductID and not the other fields, how can i do this??

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

SanjeevSingh · Accepted Answer · 2011-06-30T01:11:00

Solution 2

hello,

SqlCommand takes two argument one string for the query and second is connection.

you provided the query to command but dint provide the connection so write
MyCommand.connection=myConnection;

then execute.Hope it works.

thanks
sanjeev

Posted 30-Jun-11 1:11am

SanjeevSingh

Wayne Gaylard · Accepted Answer · 2011-06-30T01:12:00

You need to set the commands connection property to myConnection. Try something like this:

C#

/// <summary>
        /// Inserts Product into database
        /// </summary>
        /// <returns>number of affected rows</returns>
        int InsertProduct()
        {
            using (SqlConnection myConnection = new SqlConnection(DBConn))
            {
                SqlCommand MyCommand = new SqlCommand("INSERT INTO Products (Product_Name, Product_Price) Values (@ProductName, @productPrice)", myConnection);
                MyCommand.Parameters.AddWithValue("@ProductName", textBox1.Text);
                MyCommand.Parameters.AddWithValue("@ProductPrice", textBox2.Text);
                myConnection.Open();
                return MyCommand.ExecuteNonQuery();
            }
        }

Note how I used the constructor for the SqlCommand that takes a query as well as a SqlConnection object.

Please note that instead of directly inserting the textbox values, I used parameters to insert the values. You should always use parameterised queries in future.

Hope this helps

Insert into sql database from a textbox when a button is clicked

6 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 6

Solution 5

Add your solution here

Preview 0

Insert into sql database from a textbox when a button is clicked

6 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 6

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us