Member Avatar for nav07

cmd = new OleDbCommand("select Convert(varchar(50),Fault_Time),[Level],[Trouble_Source],[Solution],[Ticket_Status],[Close_Date],convert(varchar,Close_Time),[Actual_Cause],[Solution],[Description] from [TT_Record$]", conn);


its showing error at runtime :- Undefined function 'Convert' in expression


pls suggest........

  • 3 Contributors
  • 5 Replies
  • 3K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nav07

Dani AI

Generated

Good catch by : the tell here is [TT_Record$], which means you are querying an Excel worksheet through the Jet/ACE engine via OleDb, not SQL Server. The CONVERT(...) syntax you used is T‑SQL and is not recognized by Jet/ACE. In this dialect you should rely on Access/Jet functions instead. For example, use formatting and type-conversion functions provided by Jet/ACE rather than SQL Server’s CONVERT. CAST and CONVERT (Transact‑SQL). Also note you’re hitting the Excel driver behind the scenes, not the SQL Server engine. Initializing the Microsoft Excel driver.

If your goal is a readable string for dates/times (rather than just coercing to string), prefer Format() and guard against Nulls with Nz() to avoid the follow‑up error you saw. In Access/Jet, minutes are formatted with n (or nn) and hours with h/hh. Example pattern you can adapt (and consider aliasing duplicate column names such as Solution/Description so your DataTable has unique names):

SELECT
  Format([Fault_Date], 'yyyy-mm-dd') AS Fault_DateText,
  Format([Fault_Time], 'hh:nn:ss') AS Fault_TimeText,
  [Level], [Trouble_Source], [Ticket_Status],
  Format([Close_Date], 'yyyy-mm-dd') AS Close_DateText,
  IIf(IsNull([Close_Time]), '', Format([Close_Time], 'hh:nn:ss')) AS Close_TimeText,
  Nz([Actual_Cause], '') AS Actual_CauseText,
  [Solution] AS Solution_Primary,
  [Description] AS Description_Primary
FROM [TT_Record$];

Format() and its date/time tokens are documented here, and Nz() prevents Nulls from propagating into string expressions. Format function (Access). Date/Time custom format tokens (n for minutes). Nz function.

If you still see odd Nulls or type errors, remember the Excel driver guesses column types by scanning only the first few rows (TypeGuessRows) and may coerce mixed columns; using IMEX=1 in the Excel connection string can help treat mixed types as text. Excel driver TypeGuessRows/ImportMixedTypes. Example Excel OleDb connection string and [Sheet$] syntax.

Recommended Answers

All 5 Replies

Member Avatar for Narue

Try CStr() instead of Convert():

string query = "select CStr(Fault_Time),[Level],[Trouble_Source],[Solution]," +
    "[Ticket_Status],[Close_Date],CStr(Close_Time),[Actual_Cause],[Solution]," +
    "[Description] from [TT_Record$]";

cmd = new OleDbCommand(query, conn);
Member Avatar for prit005

string query = "select CAST(Fault_Time AS varchar(50)) then remaining query "

hope this work.

Member Avatar for Narue

string query = "select CAST(Fault_Time AS varchar(50)) then remaining query "

hope this work.

That's another alternative to try, but given that CONVERT() doesn't exist, I'd wager he's connected to a JET-SQL database. CAST() probably won't work any better.

Member Avatar for nav07

Try CStr() instead of Convert():

string query = "select CStr(Fault_Time),[Level],[Trouble_Source],[Solution]," +
    "[Ticket_Status],[Close_Date],CStr(Close_Time),[Actual_Cause],[Solution]," +
    "[Description] from [TT_Record$]";

cmd = new OleDbCommand(query, conn);

its showing me thi error.....
Syntax error (missing operator) in query expression 'CStr[Fault_Time]'.

Member Avatar for nav07

cmd = new OleDbCommand("select [uid],[Ticket_ID],[Name],[Title],CStr(Fault_Date),CStr(Fault_Time),[Level],[Trouble_Source],[Probable_Cause],[Affected_Site],[Description],[Solution],[Ticket_Status],CStr(Close_Date),Cstr(Close_Time),[Actual_Cause],[Solution],[Description],[SLA_Time],[SLA_Status] from [TT_Record$]", conn);


Error:-
The provider could not determine the String value. For example, the row was just created, the default for the String column was not available, and the consumer had not yet set a new String value

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.