Steven Huang Steven Huang - 4 months ago 18
SQL Question

Insert a value from a select query

string ProductType = comboBoxProductType.Text;
string ProductID = "Select ProductID from Productinformation where Name = '" + ProductType + "'";
string query = "Insert into CustStoreProd (ProductID) VALUES (?ProductID)";
MySqlCommand cmd = new MySqlCommand(query, mySQLconnection);
cmd.Parameters.AddWithValue("?ProductID", "ProductID");
cmd.ExecuteNonQuery();


I want to insert the value from a Selection query "ProductID", I test that string
ProductID
works, and if I just insert a
cmd.Parameters.AddWithValue("?ProductID", "1");
, it works too, but when I put
productID
into the
parameters.addwithvalue
, it doesn't work. It is just one of the function for the whole project so I create the mysql database connection outside.

Answer

Where are you running the query ProductID. You aren't running the query ProductID to get ProductId and use it in the subsequent query.

First run ProductID query to get the product id from your table and then use the product id in the insert query. See sample code below.

string ProductType = comboBoxProductType.Text;
        string ProductID = "Select ProductID from Productinformation where Name = '" + ProductType + "'";
        string query = "Insert into CustStoreProd (ProductID) VALUES (?ProductID)";
        MySqlCommand cmd = new MySqlCommand(ProductID, mySQLconnection);
int pid = Convert.ToInt32(cmd.ExecuteScalar()); //assuming product id is integer
        cmd.CommandText = query;
        cmd.Parameters.AddWithValue("?ProductID", pid);
        cmd.ExecuteNonQuery();
Comments