asked on

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

SOLUTION

Amandeep Singh Bhullar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knuttelmarc

maybe this could help: http://www.dbforums.com/microsoft-access/1608720-access-submit-button-sql-insert.html

richgn

ASKER

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?

Amandeep Singh Bhullar

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

richgn

ASKER

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

Dhanasekaran Sengodan

GridView_PortfolioId.ToString();

are get any values from GRIDVIEW Control?

richgn

ASKER

Each GridView just displays one value I want to pass. (The PortfolioId and then the StockAllId)

chrisgreaves

Hi, I'm not familiar with 'AS ast' in the select statement. Try omitting it.
Chris.

ASKER CERTIFIED SOLUTION

chrisgreaves

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial