ODBCCommand & DateTime

Hi,

I have two problems with my code...
1. about DateTime, when I acquire a DateTime, it contains AM/PM in my native language, which MySQL cannot understands.  How can I solve this problem?
2. I try to add attributes into SQL commands, but I got this error message "invalid attributes".  How exactly I can pass the correct values in parameters?

Here is my code..
========================
OdbcCommand dc;
OdbcConnection conn;

using (conn = new OdbcConnection(@"Driver={MySQL Connector/ODBC v5 Driver};SEVER=localhost;UID=root;PWD=root;Database=reporter;"))
 {
     string user_id = obj.user_id;
     DateTime date = obj.date;

     dc = conn.CreateCommand();
     conn.Open();
     dc.CommandText = "use reporter;";
     dc.ExecuteNonQuery();


     dc = new OdbcCommand("insert into rpt_carrier_id (user_id, date) " +
         "values (?, ?)", conn);
     dc.Parameters.Add("", OdbcType.VarChar, 32).Value = user_id;
     dc.Parameters.Add("", OdbcType.DateTime).Value = date;

     dc.ExecuteNonQuery();
}
xenia27Asked:
Who is Participating?
 
Razzie_Connect With a Mentor Commented:
Well after searching the net for this, it seems this is a problem with MDAC / ODBC. Some few links with info about this:

http://groups.google.com/group/microsoft.public.sqlserver.connect/msg/b1b1c54950503bdf?hl=en&lr=&ie=UTF-8
http://www.webservertalk.com/message1201947.html

I really hope this helps. Try reinstalling ODBC / MDAC drivers.

Good luck,
Razzie
0
 
xenia27Author Commented:
Is there anyway that AM/PM won't be shown???
0
 
Razzie_Commented:
Hi xenia,

1. You could manually format the date to whichever format you want. For example, you can use date.ToString("MM-dd-yyyy") to get your date in a 11-22-2006 format, and add the date as a string parameter.

2. You should name the sql parameters:

dc = new OdbcCommand("insert into rpt_carrier_id (user_id, date) values (@id, @date)", conn);
dc.Parameters.Add("@id", OdbcType.VarChar, 32).Value = user_id;
dc.Parameters.Add("@date", OdbcType.DateTime).Value = date;

HTH,
Razzie
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
xenia27Author Commented:
I am still having this error code.."ERROR [HY092][MySQL][MyODBC 5.00.05] Invalid attribute/option identifier"
I am using ODBCcommand to set the parameters.  Do I still use the same way to set the parameters?
0
 
Razzie_Commented:
Hmm well I'm not sure about this. I always use the Mysql .NET driver instead of ODBC (http://dev.mysql.com/downloads/connector/net/1.0.html) since it is mysql specific. You can always give it a try if you want. It is easy. Just install it, reference the .dll in your VS.NET project, and you can use it.

Anyway, to answer your question, I'm not sure about the parameter syntax. Try:

dc = new OdbcCommand("insert into rpt_carrier_id (user_id, date) values (?, ?)", conn);
dc.Parameters.Add("@user_id", OdbcType.VarChar, 32);
dc.Parameters["@user_id"].Value = user_id;
dc.Parameters.Add("@date", OdbcType.DateTime);
dc.Parameters["@date"].Value = date;

You can give it a try. Sorry I can't test it for you, but do not have a mysql database with ODBC connector here to test it for you!
HTH,
Razzie
0
 
xenia27Author Commented:
I still have  that error message...What exactly it means "Invalid attribute" or "Invalid option identifier"??  I do believe all my attributes are correct...
0
 
xenia27Author Commented:
OK...that's why I cannot run the code...thank you~~~
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.