How do I insert into parameters from different tables using OleDB?
I have 3 tables: 1. itemTbl 2. crateTbl 3. contentTbl
itemTbl has: itemID, itemName, itemDesc crateTbl has: crateID, crateName contentTbl has: crateID, itemID, qty
contentTbl is the contents of the crate and the qty of each
I need it to select values in different tables I use WHERE. I have tried a similar code using a local db and service based db and they allow me, but OleDB doesn't let me use VALUES((SELECT))....
Error message:
System.Data.OleDb.OleDbException: 'Query input must contain at least one table or query.
My code:
cmd.Dispose();
cmd.CommandText = @"INSERT INTO contentTbl(crateID,itemID,qty) VALUES((SELECT crateTbl.crateID FROM crateTbl WHERE crateTbl.crateID=?),(SELECT itemTbl.itemID FROM itemTbl WHERE itemTbl.itemID = ?), ?)";
cmd.Connection = con;
cmd.Parameters.Add(new OleDbParameter("crateID", txtCrate.Text));
cmd.Parameters.Add(new OleDbParameter("itemID", txtItem.Text));
cmd.Parameters.Add(new OleDbParameter("qty", txtQty.Text));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Done!");
da.SelectCommand = new OleDbCommand("SELECT * FROM contentTbl", con);
da.Fill(dt);
dgvContent.DataSource = dt;
The error message is quite descriptive. Access doesn't support subqueries without a main query, so change the syntax round to use one of the subqueries as your main query:
INSERT INTO contentTbl(crateID,itemID,qty)
SELECT crateTbl.crateID,(SELECT itemTbl.itemID FROM itemTbl WHERE itemTbl.itemID = ?), ?
FROM crateTbl WHERE crateTbl.crateID=?
Note that parameters are passed by position, and rewriting this query does require you to re-order parameters:
cmd.Parameters.Add(new OleDbParameter("itemID", txtItem.Text));
cmd.Parameters.Add(new OleDbParameter("qty", txtQty.Text));
cmd.Parameters.Add(new OleDbParameter("crateID", txtCrate.Text));
If you don't like the main query/subquery syntax, you can go for a cross join too:
INSERT INTO contentTbl(crateID,itemID,qty)
SELECT crateTbl.crateID, itemTbl.itemID, ?
FROM crateTbl,itemTbl
WHERE crateTbl.crateID=? AND itemTbl.itemID = ?
(Parameter order needs to be adjusted again but you can figure that out).