I'm trying to create a paramerterized query for an odbc connection but I can't get it to work I'm must be missing something or it is not possible with a reader.
Heres is the code I have so far:
public T GetSingleEntity(string id)
{
T temp = new T();
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
connection.Open();
OdbcCommand command = new OdbcCommand(null, connection);
//using (OdbcCommand command = connection.CreateCommand())
{
//command.CommandText = GetBarCommandTextForSingle(id);
//command.Parameters.Add("?", OdbcType.NVarChar).Value = id;
command.CommandText = "Select * from Table where theID = '@id'";//this query does not work.
//command.CommandText = "Select * from BPRAttorneyInfo where zk_idnum_p = '" + id + "'"; //this query does.
command.Parameters.AddWithValue("@id", id);
command.Prepare();
//OdbcParameter idParam = new OdbcParameter("@id",OdbcType.NVarChar,200);
//idParam.Value = id;
//command.Parameters.Add(idParam);
//command.Prepare();
//command.Connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
object tempEntity = (object)temp;
if (reader.Read())
{
//Do Something
}
return (T)(object)tempEntity;
}
}
}
}
This might not be a problem as long as mvc paramertized action input. If it doesn't I need to figure out how to paramertize my query properly.
MikesDotNettings advices you quite right？Is there anything feedback？
My addition is that I've noticed you want to write a generic class type T to make the function gneric，so it would be better to limit your "T" with "new。Something like this：
public class XXX<T>where T:new()
{
}
This would be easy for you to do something like reflector or use non-parameter constructor for T to avoid constructor with parameters……
Unfortunatley filemaker is not having it. I get an error in query syntax. It has to be have the ' ' on it because the unique id for the record is unfortunatley a string. Any work arounds would be much appreciated.
Please stay on topic if your going to post. I'm trying to solve a problem with parameterizing a query not generics.
I see，but that's just a tip——to tell you that since I saw new T()，because not every generic T has default parameter，and in order to avoid this，I used this limiting way for T……:D
So have u tried Mikesdotnetting's way？Any exceptions or syntax error？Would you mind showing them out？
Sorry - overlooked the fact that you are using ODBC. Change the parameter marker to a question mark:
command.CommandText = "Select * from Table where theID = ?"
That should work for most databases, although I have no experience with FileMaker. MySql sometimes needs the name of the parameter afte the question mark:
command.CommandText="Select * from Table where theID = ?id"
Gonna give that a try. I think filemaker either doesn't have variable or parameter syntax or It is not the same as SQL Server's or other databases I would imagine. Tried to do a select while creating a parameter in my server explorer query pane and kept
getting the same error.
viperassasin
Member
35 Points
49 Posts
Odbc parameterized query.
Jun 18, 2012 01:48 AM|LINK
I'm trying to create a paramerterized query for an odbc connection but I can't get it to work I'm must be missing something or it is not possible with a reader.
Heres is the code I have so far:
public T GetSingleEntity(string id) { T temp = new T(); using (OdbcConnection connection = new OdbcConnection(connectionString)) { connection.Open(); OdbcCommand command = new OdbcCommand(null, connection); //using (OdbcCommand command = connection.CreateCommand()) { //command.CommandText = GetBarCommandTextForSingle(id); //command.Parameters.Add("?", OdbcType.NVarChar).Value = id; command.CommandText = "Select * from Table where theID = '@id'";//this query does not work. //command.CommandText = "Select * from BPRAttorneyInfo where zk_idnum_p = '" + id + "'"; //this query does. command.Parameters.AddWithValue("@id", id); command.Prepare(); //OdbcParameter idParam = new OdbcParameter("@id",OdbcType.NVarChar,200); //idParam.Value = id; //command.Parameters.Add(idParam); //command.Prepare(); //command.Connection.Open(); using (OdbcDataReader reader = command.ExecuteReader()) { object tempEntity = (object)temp; if (reader.Read()) { //Do Something } return (T)(object)tempEntity; } } } }This might not be a problem as long as mvc paramertized action input. If it doesn't I need to figure out how to paramertize my query properly.
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: Odbc parameterized query.
Jun 18, 2012 06:24 AM|LINK
Remove the quotes around the parameter otherwise it is seen a s a literal string:
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Odbc parameterized query.
Jun 19, 2012 01:40 AM|LINK
MikesDotNettings advices you quite right？Is there anything feedback？
My addition is that I've noticed you want to write a generic class type T to make the function gneric，so it would be better to limit your "T" with "new。Something like this：
public class XXX<T>where T:new() { }This would be easy for you to do something like reflector or use non-parameter constructor for T to avoid constructor with parameters……
Reguards！
viperassasin
Member
35 Points
49 Posts
Re: Odbc parameterized query.
Jun 19, 2012 04:20 AM|LINK
Dong,
Please stay on topic if your going to post. I'm trying to solve a problem with parameterizing a query not generics.
viperassasin
Member
35 Points
49 Posts
Re: Odbc parameterized query.
Jun 19, 2012 04:23 AM|LINK
Unfortunatley filemaker is not having it. I get an error in query syntax. It has to be have the ' ' on it because the unique id for the record is unfortunatley a string. Any work arounds would be much appreciated.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Odbc parameterized query.
Jun 19, 2012 04:27 AM|LINK
I see，but that's just a tip——to tell you that since I saw new T()，because not every generic T has default parameter，and in order to avoid this，I used this limiting way for T……:D
So have u tried Mikesdotnetting's way？Any exceptions or syntax error？Would you mind showing them out？
Reguards！
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Odbc parameterized query.
Jun 19, 2012 04:28 AM|LINK
How did you write now？Mikes's way should work……
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: Odbc parameterized query.
Jun 19, 2012 06:30 AM|LINK
Sorry - overlooked the fact that you are using ODBC. Change the parameter marker to a question mark:
That should work for most databases, although I have no experience with FileMaker. MySql sometimes needs the name of the parameter afte the question mark:
command.CommandText = "Select * from Table where theID = ?id"
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
viperassasin
Member
35 Points
49 Posts
Re: Odbc parameterized query.
Jun 19, 2012 12:53 PM|LINK
Gonna give that a try. I think filemaker either doesn't have variable or parameter syntax or It is not the same as SQL Server's or other databases I would imagine. Tried to do a select while creating a parameter in my server explorer query pane and kept getting the same error.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Odbc parameterized query.
Jun 20, 2012 01:27 AM|LINK
Do you mean you've tried Mike's second method with parameter "?" but still fails？Any exceptions？
http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.aspx