Insert SQL on Button Click

I am trying to insert data into a table using SQL. I am inserting the data into a table called Stock. Most of the data is coming from a table called AllStock, and one field from a table called Portfolio.

The user selects the portfolio name and the stock name to be entered into the table. I then have 2 invisible grid views (not needed?) returning the PortfolioId and StockAllId from the two tables. These are both foreign keys in the Stock table. I then need the rest of the fields in the AllStock table associated with the StockAllId (primary key) in the AllStock table.

This will all be called on a button click.

Any help will be appreciated. Please see the code below for what I have so far.

Thanks.
protected void btn_AddStock_Click(object sender, EventArgs e)
    {
        SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString);
        SqlCommand cmd = Conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        Conn.Open();

        cmd.CommandText = @"INSERT INTO Stock(StockName, StockSymbol, StockQuantity, 
                            StockBuyPrice, StockBuyDate, PortfolioId, StockAllId)
        SELECT ast.StockName, ast.StockSymbol, @StockQuantity, ast.StockPrice, ast.Date, @PortfolioId, ast.StockAllId, 
        FROM AllStocks AS ast 
        WHERE ast.StockAllId = @StockAllId";


        cmd.Parameters.AddWithValue("@StockQuantity", txt_Quantity.Text);
        cmd.Parameters.AddWithValue("@PortfolioId", GridView_PortfolioId.ToString());
        cmd.Parameters.AddWithValue("@StockAllId", GridView_StockId.ToString());

    }

Open in new window

richgnAsked:
Who is Participating?
 
chrisgreavesConnect With a Mentor Commented:
Hi again,
If the parameter value is in a gridview then the value would be
gridview1.rows(n).cells(m).text
where n and m are zerobased indexes. If there is only one row and one cell then it would be rows(0).cells(0)
so...
lbl_PortfolioId.Text = GridView_PortfolioId.rows(0).cells(0).text
Chris
0
 
Amandeep Singh BhullarConnect With a Mentor Commented:
Check the attached code.
Hope this will help you
protected void btn_AddStock_Click(object sender, EventArgs e)
    {
        SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString);
        SqlCommand cmd = Conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = Conn;
        Conn.Open();

        cmd.CommandText = @"INSERT INTO Stock(StockName, StockSymbol, StockQuantity, 
                            StockBuyPrice, StockBuyDate, PortfolioId, StockAllId)
        SELECT ast.StockName, ast.StockSymbol, @StockQuantity, ast.StockPrice, ast.Date, @PortfolioId, ast.StockAllId, 
        FROM AllStocks AS ast 
        WHERE ast.StockAllId = @StockAllId";


        cmd.Parameters.AddWithValue("@StockQuantity", txt_Quantity.Text);
        cmd.Parameters.AddWithValue("@PortfolioId", GridView_PortfolioId.ToString());
        cmd.Parameters.AddWithValue("@StockAllId", GridView_StockId.ToString());
        int intResult = cmd.ExecuteNonQuery();
        Conn.Close(); //Close the connection with database
        if (intResult == 0)
        {
            //No Row inserted
        }
        else
        {
            //Row inserted into DB
        }
    }

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
richgnAuthor Commented:
Thanks AmanBhullar,

I tried running it and I've got an error on this line.
int intResult = cmd.ExecuteNonQuery();

The error message is:
 Incorrect syntax near the keyword 'FROM'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FROM'.

Any ideas?
0
 
Amandeep Singh BhullarCommented:
Check the attached query
You are using extra comma in "ast.StockAllId,"
INSERT INTO Stock(StockName, StockSymbol, StockQuantity, StockBuyPrice, StockBuyDate, PortfolioId, StockAllId)
        SELECT ast.StockName, ast.StockSymbol, @StockQuantity, ast.StockPrice, ast.Date, @PortfolioId, ast.StockAllId  
        FROM AllStocks AS ast 
        WHERE ast.StockAllId = @StockAllId

Open in new window

0
 
richgnAuthor Commented:
I've tried your solution there and it seemed to get a bit closer. I tried adding some labels in to use as place holders for testing and while moving things round a bit I started getting an error about the grid views.

Here is the error:
Conversion failed when converting the nvarchar value 'System.Web.UI.WebControls.GridView' to data type int.
protected void btn_AddStock_Click(object sender, EventArgs e)
    {
        SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString);
        SqlCommand cmd = Conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        Conn.Open();

        cmd.CommandText = @"INSERT INTO Stock(StockName, StockSymbol, StockQuantity, 
                            StockBuyPrice, StockBuyDate, PortfolioId, StockAllId)
        SELECT ast.StockName, ast.StockSymbol, @StockQuantity, ast.StockPrice, ast.Date, @PortfolioId, ast.StockAllId 
        FROM AllStocks AS ast 
        WHERE ast.StockAllId = @StockAllId";

        lbl_PortfolioId.Text = GridView_PortfolioId.ToString();
        lbl_StockId.Text = GridView_StockId.ToString();

        cmd.Parameters.AddWithValue("@StockQuantity", txt_Quantity.Text);
        cmd.Parameters.AddWithValue("@PortfolioId", lbl_PortfolioId.Text);
        cmd.Parameters.AddWithValue("@StockAllId", lbl_StockId.Text);

        int intResult = cmd.ExecuteNonQuery();
        Conn.Close(); //Close the connection with database
        if (intResult == 0)
        {
            //No Row inserted
        }
        else
        {
            //Row inserted into DB
        }

    }

Open in new window

0
 
Dhanasekaran SengodanCommented:

GridView_PortfolioId.ToString();

are get any values from GRIDVIEW Control?
0
 
richgnAuthor Commented:
Each GridView just displays one value I want to pass. (The PortfolioId and then the StockAllId)
0
 
chrisgreavesCommented:
Hi, I'm not familiar with 'AS ast' in the select statement. Try omitting it.
Chris.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.