My queries don't work after I upgraded to SQL Server Compact 3.5

[Star]

Hi all,

I have upgraded my project to VS2008 and for some reason my queries
stopped working. I have spent almost the whole day trying different
things, but I always get a
'Cannot convert from Float to DateTime.' exception when the query
is executed.

This is my table:
http://luisespinosa.com/temp/table.jpg

and this is one of the queries that fail:

try
{
SqlCeParameter p3 = new SqlCeParameter("Latitude", SqlDbType.Float);
p3.Value = 3.2133;
SqlCeParameter p4 = new SqlCeParameter("Longitude", SqlDbType.Float);
p4.Value = 5.2133;
SqlCeParameter p5 = new SqlCeParameter("Speed", SqlDbType.Float);
p5.Value = 3.1;
SqlCeParameter p6 = new SqlCeParameter("Altitude", SqlDbType.Float);
p6.Value = 1.2;
SqlCeParameter p7 = new SqlCeParameter("DateAdded",
SqlDbType.DateTime);
p7.Value = DateTime.Now;
SqlCeParameter p8 = new SqlCeParameter("DateOccurred",
SqlDbType.DateTime);
p8.Value = DateTime.Now;

cSQL = "Insert into Positions
(Latitude,Longitude,Speed,Altitude,DateAdded,DateOccurred) ";
cSQL += "VALUES (?,?,?,?,?,?)";

SqlCeCommand sqlCommand = m_DB.m_cConn.CreateCommand();

sqlCommand.CommandText = cSQL;
sqlCommand.Parameters.Add(p3);
sqlCommand.Parameters.Add(p4);
sqlCommand.Parameters.Add(p5);
sqlCommand.Parameters.Add(p6);
sqlCommand.Parameters.Add(p7);
sqlCommand.Parameters.Add(p8);


sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
String eeee = e.Message;
}
Please... I hope somebody can help me with this. I don't know what else
to try.

Thanks

 

[Ginny Caughey [MVP]]

Star,

I was able to reproduce the error you saw even on the desktop. I was also
able to work around it (on desktop and CF) using this type of coding:

string cSQL = "Insert into Positions
(Latitude,Longitude,Speed,Altitude,DateAdded,DateOccurred) ";
cSQL += "Values (@Latitude, @Longitude, @Speed, @Altitude, @DateAdded,
@DateOccurred) ";
SqlCeCommand sqlCommand = conn.CreateCommand();
sqlCommand.CommandText = cSQL;
sqlCommand.Parameters.AddWithValue("@Latitude", 3.2133);
sqlCommand.Parameters.AddWithValue("@Longitude", 5.2133);
sqlCommand.Parameters.AddWithValue("@Speed", 3.1);
sqlCommand.Parameters.AddWithValue("@Altitude", 1.2);
sqlCommand.Parameters.AddWithValue("@DateAdded", DateTime.Now);
sqlCommand.Parameters.AddWithValue("@DateOccurred", DateTime.Now);
sqlCommand.ExecuteNonQuery();

I have seen something similar with SQL Server so I don't believe it's
specific to SQL Compact 3.5 or Compact Framework. In general I have fewer
problems using Parameters.AddWithValue, and it's a little less code.

HTH,

 

[Star]

Thanks Ginny.

Yes, I got it to work using the @[parameter_name] instead of using just a ?
It's strange. It works ok with CF2.0 but it doesn't with CF3.5

Thanks for your help.