current show from database

[reven]

im trying to get the current tv show from the database using this code

Code:

OleDbConnection conn = new OleDbConnection(GBPVRDB_CONNECTION_STRING); OleDbCommand cmd = new OleDbCommand("SELECT name, subtitle FROM PROGRAMME WHERE start_time >= @Start AND end_time < @End",conn); cmd.Parameters.Add("@Start", Sy{tem.DateTime.Now); command.Parameters.Add("@End", System.DateTime.Now); conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); string title = ""; string subtitle = ""; while(reader.Read()) { title = reader.GetString(0); subtitle = reader.GetString(1); } conn.Close();

but i keep getting this error

Code:

2/05/2005 12:53:22 a.m..782 ERROR [2] Error: msg=0x100 (WM_KEYDOWN) hwnd=0x370254 wparam=0x71 lparam=0x1 result=0x0 : No value given for one or more required parameters. : at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at LCDPlugin.Database.GetShow(String oid) in c:\xmltv\my lcd\database.cs:line 59

the problem is to do with the @Start and @End datetimes, im not sure how im meant to set this, anyone know where im going wrong?

TIA
reven.

[reven]

oh i forgot to add where oid=@oid to the command string, but that hasnt got anything to do with the error

[sub]

sub_title not subtitle

[reven]

ok that solved that problem, now im getting a datatype mismatch

Code:

2/05/2005 8:58:28 a.m..766 ERROR [2] Error: msg=0x100 (WM_KEYDOWN) hwnd=0x57021a wparam=0x71 lparam=0x1 result=0x0 : Data type mismatch in criteria expression. : at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at LCDPlugin.Database.GetShow(String oid) in c:\xmltv\my lcd\database.cs:line 42

im pretty sure its with the dates (well thats the only thing to be mismatched ), theres just so many different date types, but i think datetime is the correct one to pass.

[sub]

Whats on c:\xmltv\my lcd\database.cs:line 42?

[reven]

just the cmd.executereader(); which doesnt help any..

i removed the end_time < @end and it worked, so i think thats whats wrong, ill look in the db again for the column name../

[reven]

nope end_time is the correct column name

heres the complete code im working with now

Code:

public Show GetShow(int oid) { if(oid < 0) return new Show("",""); OleDbConnection conn = new OleDbConnection(GBPVRDB_CONNECTION_STRING); OleDbCommand cmd = new OleDbCommand("SELECT name, sub_title FROM PROGRAMME WHERE start_time >= @Start AND end_time < @End AND oid=@oid",conn); cmd.Parameters.Add("@Start", System.DateTime.Now); cmd.Parameters.Add("@End", System.DateTime.Now); cmd.Parameters.Add("@oid", oid); conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); string title = ""; string subtitle = ""; while(reader.Read()) { title = reader.GetString(0); subtitle = reader.GetString(1); } conn.Close(); return new Show(title, subtitle); }

oh and does a event get fired on a channel change and show change (ie when a show finishes and a new one starts, otherwise i thinking i might be wasting my time...)

[reven]

problem solved with:
System.DateTime.Now.ToString();

for some reason it didnt like the datetime object, but fine with the string dd/mm/yyyy hh:mms am/pm



channel change updates the display, now just got to wait for a end of a show to see if it gets updated