parameter for column name

parameter for column name

Postby oonce » Fri 10 Mar 2006 05:58

mysqldataadapter query as follows:
SELECT
client_master.CCM_ID AS ID,
client_master.CCM_Name AS `Company Name`,
client_master.CCM_Address AS Address,
client_master.CCM_Suburb AS Suburb,
client_master.CCM_State AS State,
client_master.PCode AS PCode,
client_master.CCM_Country AS Country,
client_master.CCM_Phone AS Phone,
client_master.CCM_Fax AS Fax,
client_master.CCM_Email AS Email,
client_master.CCM_www AS Web,
client_master.CCM_Notes AS Notes,
client_master.CCM_Address1_PO AS `Postal Address`,
client_master.CCM_Suburb_PO AS `Postal Suburb`,
client_master.CCM_State_PO AS `Postal State`,
client_master.PCode_PO AS `Postal Pcode`,
client_master.CCM_Country_PO AS `Postal Country`,
client_master.CCM_Phone2 AS `Phone 2`,
client_master.CCM_Phone3 AS `Phone 3`,
client_master.CCM_Type AS `company Type`,
client_master.CCM_cust_cat AS Category,
client_master.CCM_area AS `NAV Area`,
client_master.CCM_visited AS `Visited Status`,
client_master.CCM_active AS `Active Staus`,
client_master.CCM_dt_create AS `Date Created`,
client_master.CCM_dt_modify AS `Date Modified`
FROM
newagev1.client_master
WHERE
newagev1.client_master.CCM_area = ? AND
newagev1.client_master.CCM_active = ? AND
newagev1.client_master.CCM_visited = ? AND
? LIKE ?

ORDER BY
`Company Name`,
PCode

All good till I put in the ? to replace the column name before the like. Can't invoke the last parameter properly which is a text string. Can i do this?
oonce
 
Posts: 1
Joined: Sun 05 Mar 2006 08:58

Postby LostPassword » Fri 10 Mar 2006 14:14

I think this driver is more cool then MS ODBC hard-usable driver.
You are trying to use it in old manner, better use for parameter
naming something like :first_parameter, :second_parameter.
If You use parameter named "?" or let call me it ":p" (because I am not sure it will accept the "?" simbole) multiple times in query
You will gote Query sended by driver like this :
Code: Select all
// SELECT * FROM somewhere WHERE some1 = 'some' AND some2='some';
//-----------------------------
this.cmdSel_miscellaneus.Parameters.Clear();
this.cmdSel_miscellaneus.Parameters.Add(new MySqlParameter("p", MySqlType.VarChar));
this.cmdSel_miscellaneus.Parameters["p"].Value = "some";
this.cmdSel_miscellaneus.CommandText =
" SELECT * FROM somewhere WHERE some1 = :p AND some2=:p ";

instead of this better use
Code: Select all
// SELECT * FROM somewhere WHERE some1 = 'some one' AND some2='some second';
//-----------------------------
this.cmdSel_miscellaneus.Parameters.Clear();
this.cmdSel_miscellaneus.Parameters.Add(new MySqlParameter("p1", MySqlType.VarChar));
this.cmdSel_miscellaneus.Parameters["p1"].Value = "some one";
this.cmdSel_miscellaneus.Parameters.Add(new MySqlParameter("p2", MySqlType.VarChar));
this.cmdSel_miscellaneus.Parameters["p2"].Value = "some second";
this.cmdSel_miscellaneus.CommandText =
" SELECT * FROM somewhere WHERE some1 = :p1 AND some2=:p2 ";

Also about second part of Your question - I am not sure it is good idea to use field_name as parameter.
Where is a problem to just change string with query???
For example when I use sinchroniztion of grid sorting with real DB sorting I just append to the query string grid's sorting value.
string fromSomewhere = " ORDER BY some_one ASC ";
string basicQuery = " SELECT * FROM somewhere WHERE some1 = :p1 AND some2=:p2 ";
this.cmdSel_miscellaneus.CommandText = fromSomewhere + basicQuery;

Hope it helps You ;)
LostPassword
 

Postby Serious » Mon 13 Mar 2006 16:11

Command parameters in MySQLDirect .NET are designed to handle data values, not names.
In MySQL Server string values must be quoted. When you create string parameter its value is quoted with ' quotes instead of ` quotes.
Serious
 


Return to dotConnect for MySQL