Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
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
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.

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[^]
 
Share this answer
 
Comments
Tarun.K.S 30-Jun-11 7:09am    
Right. 5+
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
 
Share this answer
 
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
 
Share this answer
 
Comments
wahmed821 12-Sep-12 14:05pm    
how to store a file with other data like Name, Address in database in asp.net ?
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?
 
Share this answer
 
<%@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");
%>
 
Share this answer
 
 
Share this answer
 
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??

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900