Oledb Insert statement using parameters

1
votes

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;
3
c#ms-accessoledboledbcommand
The first thing I've noticed is that you have an extra comma at the end of your columns list. The second thing is that OLEDB doesn't let me use VALUES((SELECT)) doesn't really tell us much. Do you get an error message? If so, what is it? What database are you working with? - Zohar Peled
I was editing that one xD sorry. I'm using an access database. It says: System.Data.OleDb.OleDbException: 'Query input must contain at least one table or query.' - Jake
It's been forever since I've last worked with ms-access. Are you sure that syntax is valid? Did you try hard-coding values into the parameter placeholders and run it directly in access? - Zohar Peled

3 Answers

1
votes

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).

0
votes

I am not sure about the syntax. I always use it like (this is from VB, but C# should be similar) :

cmd.Parameters.Addwithvalue("crateID", txtCrate.Text)

0
votes

I have tried this and it sort of works, but for some reason works only with values for the items table as ID's. If I have an ID (they're ints) that crateTbl has but itemTbl doesn't, it doesn't insert.

cmd.Dispose();
cmd.CommandText = @"INSERT INTO contentTbl(itemID,crateID,qty) SELECT itemTbl.itemID,(SELECT crateTbl.crateID FROM crateTbl WHERE crateTbl.crateID = ?), ? FROM itemTbl WHERE itemTbl.itemID=?";

cmd.Connection = con;

cmd.Parameters.Add(new OleDbParameter("itemID", txtItem.Text));
cmd.Parameters.Add(new OleDbParameter("qty", txtQty.Text));
cmd.Parameters.Add(new OleDbParameter("crateID", txtCrate.Text));

con.Open();
cmd.ExecuteNonQuery();
con.Close();

MessageBox.Show("Done!");

dt2.Clear();