If like me you earn your keep writing code to interrogate databases, you’re probably familiar with parameterised queries. If not, take a look here — they offer a way of passing parameters into queries without all that mucking about with formatting for different data types and so on. They also offer (so I’m led to believe) performance gains from the database server. And OLEDB makes it pretty easy to do it, too. So throw away those horrible old queries and switch to parameter queries today!
One thing not often mentioned however is that the parameters have to be in order. Because in the SQL you identify them only as question marks:
SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 = ? AND CRITERIA2 = ?
they have to be added in the correct order. If you add CRITERIA2 then CRITERIA1 to your parameters, then you’ll get unexpected results. The parameter names don’t actually match up to your SQL by name, only by position. This is kinda logical, because if you wanted:
SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 > ? AND CRITERIA1 < ?
both your parameters couldn’t be named CRITERIA1.
For some of my code, I build an INSERT or an UPDATE depending on whether I have a new or updated record to write. To avoid two sets of code, I’ll build the parameter list with the key field last, and though it may seem counter-intuitive, I build the INSERT statement to match. Bad semi-VB pseudocode follows:
If (new record) Then
SQL = "INSERT INTO TABLENAME (FIELD1, FIELD2, KEYFIELD) VALUES (?, ?, ?)"
Else (updating record)
SQL = "UPDATE TABLENAME SET FIELD1 = ?, FIELD2 = ? WHERE KEYFIELD = ?"
End If
…then add my parameters FIELD1, FIELD2 and KEYFIELD in that order. Easy.
That is all well and good… more or less.
But how does OLEDB process parameters in nested queries?
Take for example:
SELECT SubjectID, BusinessName, City, StateCode
FROM Subjects
WHERE Zip in ( SELECT Zip FROM ZipCodes WHERE ? > 3959 * ATN( SQR(1 – (SIN(?/ 57.3) * SIN(LAT/ 57.3) + COS(?/ 57.3) * COS(LAT/ 57.3) * COS((LNG/ 57.3) – (?/ 57.3))) ^2 ) / (SIN(?/ 57.3) * SIN(LAT/ 57.3) + COS(?/ 57.3) * COS(LAT/ 57.3) * COS((LNG/ 57.3) – (?/ 57.3))) ) )
I know that adding the 7 parameters in the order they appear in the SQL string will NOT place those parameters properly. However, the reason I’ve happened upon your post today is because I can’t figure out how OLEDB does order these parameters in a nested query. I have been trying to figure this out for the past several hours and I’m just fried.
Ooh, nasty stuff Michael. Let us know if you find a solution to that one!
Oh, for God’s sake Michael, if you’re going to do crazy shit like that just use a stored procedure!
public void DGProducts_Update(Object sender, DataGridCommandEventArgs e){
String sProductName, sUnitsInStock, sUnitsOnOrder, sProductID;
sProductName = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
sUnitsInStock = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
sUnitsOnOrder = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
sProductID = e.Item.Cells [5].Text;
StringBuilder uOleDbCm = new StringBuilder();
uOleDbCm.Append(“UPDATE Products “);
uOleDbCm.Append(“SET ProductName = @ProductName,”);
uOleDbCm.Append(“UnitsInStock = @UnitsOnStock,”);
uOleDbCm.Append(“UnitsOnOrder = @UnitsOnOrder “);
uOleDbCm.Append(“WHERE ProductID = @ProductID”);
OleDbConnection objConnection = new OleDbConnection(olDbCon);
OleDbCommand oleDbCm = new OleDbCommand(uOleDbCm.ToString(), objConnection);
oleDbCm.Parameters.Add(new OleDbParameter (“@ProductName”,OleDbType.VarChar,40,”@ProductName”));
oleDbCm.Parameters[“@ProductName”].Value = sProductName;
oleDbCm.Parameters.Add(new OleDbParameter(“@UnitsInStock”,OleDbType.SmallInt,0,”@UnitsInStock”));
oleDbCm.Parameters[“@UnitsInStock”].Value = sUnitsInStock;
oleDbCm.Parameters.Add(new OleDbParameter(“@UnitsOnOrder”,OleDbType.SmallInt,0,”@UnitsOnOrder”));
oleDbCm.Parameters[“@UnitsOnOrder”].Value = sUnitsOnOrder;
oleDbCm.Parameters.Add(new OleDbParameter(“@ProductID”,OleDbType.SmallInt,0,”@ProductID”));
oleDbCm.Parameters[“@ProductID”].Value = sProductID;
objConnection.Open();
oleDbCm.ExecuteNonQuery();
objConnection.Close();
DGProducts.EditItemIndex = -1;
Bind();
}
im not sure where i went wrong but it just don update the value…pls help…ty