locked
SELECT JOIN SQL error - GUID format RRS feed

  • Question

  • User-1697400506 posted

    My database tables have NO GUID field defined but varchar(36) as primary key.

    In some time I got this error in SQL JOIN: Unrecognized Guid format.

    Can you shed me some lights what's wrong is that?

    Thanks

    ********************************************************************************

    Exception Details: System.FormatException: Unrecognized Guid format.

    Source Error:

    Line 121:        catch
    Line 122: {
    Line 123: throw;
    Line 124: }
    Line 125: finally


    Source File: C:\WebClasses\WebClass\CommonClasses\DataServiceBase.cs    Line: 123

    Stack Trace:

    [FormatException: Unrecognized Guid format.]
    System.GuidResult.SetFailure(ParseFailureKind failure, String failureMessageID, Object failureMessageFormatArgument,
    String failureArgumentName, Exception innerException) +3875935
    System.Guid.TryParseGuid(String g, GuidStyles flags, GuidResult& result) +9665624
    System.Guid..ctor(String g) +107
    MySql.Data.Types.MySqlGuid.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
    +319
    MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
    +242
    MySql.Data.MySqlClient.Driver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue value)
    +52
    MySql.Data.MySqlClient.ResultSet.get_Item(Int32 index) +351
    MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull) +121
    MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i) +172
    MySql.Data.MySqlClient.MySqlDataReader.GetValues(Object[] values) +111
    System.Data.ProviderBase.CommonLanguageSubsetDataReader.GetValues(Object[] values) +13
    System.Data.ProviderBase.SchemaMapping.LoadDataRow() +46
    System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +96
    System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable,
    DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords,
    DataColumn parentChapterColumn, Object parentChapterValue) +164
    System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
    +424
    System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords,
    String srcTable, IDbCommand command, CommandBehavior behavior) +204
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
    CommandBehavior behavior) +319
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
    DataServiceBase.ExecuteDataSet(String query, MySqlParameter procParams) in C:\WebClasses\WebClass\CommonClasses\DataServiceBase.cs:123
    ELogDataService.GetELogByAnnouncerID(String announcerID, DateTime eLogDate) in C:\WebClasses\WebClass\CommonClasses\ELogDataService.cs:176
    ELogBusinessCollection.GetELogByAnnouncerID(String announcerID, DateTime eLogDate) in C:\WebClasses\WebClass\CommonClasses\ELogBusinessCollection.cs:46
    ELogGenerator.GenerateAnnoucerELogs() in C:\WebLog\WebApplication1\WebApplication1\Help_Classes\ELogGenerator.cs:73
    WebApplication1.Main.PopulateAllAnnouncerLogs() in C:\WebLog\WebApplication1\WebApplication1\Main.aspx.cs:102
    WebApplication1.Main.InitialiseAnnouncerLogs() in C:\WebLog\WebApplication1\WebApplication1\Main.aspx.cs:94
    WebApplication1.Main.Page_Load(Object sender, EventArgs e) in C:\WebLog\WebApplication1\WebApplication1\Main.aspx.cs:44
    System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
    System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
    System.Web.UI.Control.OnLoad(EventArgs e) +91
    System.Web.UI.Control.LoadRecursive() +74
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
    Thursday, June 30, 2011 1:49 AM

All replies

  • User-269404413 posted

    Alan,

    GUID will be created from Front -end.

    check your lenght of the column, you said its size is varchar(36) if u exceed this length then it may happen.

    for more info read this thread

     

    Thursday, June 30, 2011 2:05 AM
  • User-1697400506 posted

    GUID will be created from Front -end.

    I just not sure if I understand.

    I am just executing a SELECT SQL, why there are GUID be created?

    check your lenght of the column, you said its size is varchar(36) if u exceed this length then it may happen.

    Yes, the table column width is varchar(36), how do I check where the GUID is created from front-end?

    Thursday, June 30, 2011 2:54 AM
  • User-269404413 posted

    if you are doing only select statetmtn then chcek what is going on the where clause.

    also if possible post your exact error code.

    Yes, the table column width is varchar(36), how do I check where the GUID is created from front-end?

    just press Ctrl+Shit+F and type GUID then select entire project and click Find

     

    Thursday, June 30, 2011 4:12 AM
  • User-1697400506 posted

    if you are doing only select statetmtn then chcek what is going on the where clause.

    It happened before in similar situation but different JOIN with different tables:

    The same exact SQL JOIN without problem:

    SELECT ......

    FROM .....

    LEFT JOIN ....

    LEFT JOIN ....

    WHERE .......

    AND .......

    AND .....

    AND ss.EmployeeID = blah blah

    ORDER BY ....

    BUT this HAD problem if no employee filter:

    SELECT ......

    FROM .....

    LEFT JOIN ....

    LEFT JOIN ....

    WHERE .......

    AND .......

    AND .....

    //AND ss.EmployeeID = blah blah

    ORDER BY ....

    Thursday, June 30, 2011 8:39 PM
  • User-269404413 posted

    Alan,

    If one chooses more than one object at the same time before first adding at least one entry previously, this issue does not happen, so this can be used as a workaround (choose everything needed at one time).

    This issue will be fixed in a future hot-fix version of ChangeAuditor version 5.5.x. This issue still exists as of ChangeAuditor version 5.5.108.

    Please refer to the "Resolved Issues" section of the Product Release Notes when new releases are available to identify if a fix for this defect has been implemented. Product Release Notes are available in the "Product Documentation" area of our Support Website at support.quest.com.

    refer this page and this page

    also can you post that table script?

    Friday, July 1, 2011 5:56 AM
  • User-1697400506 posted

    If one chooses more than one object at the same time before first adding at least one entry previously, this issue does not happen, so this can be used as a workaround (choose everything needed at one time).

    Sorry, could you explain a bit what my SQL problem was? When there is no 'WHERE' part in the employee filter?

    This issue will be fixed in a future hot-fix version of ChangeAuditor version 5.5.x. This issue still exists as of ChangeAuditor version 5.5.108.

    What is ChangeAuditor?

    Sunday, July 3, 2011 7:53 PM
  • User-1694870838 posted

    Hi,

    From your details, we guess that  the issue is about your sql clause ,could you please show us more codes so that we can help you as soon as possible.

    Best Regards,

    Damon

    Tuesday, July 5, 2011 1:57 AM
  • User-1697400506 posted

    From your details, we guess that  the issue is about your sql clause ,could you please show us more codes so that we can help you as soon as possible.

    Hi, what I did seems get rid of the error but strange, I added some IF in the SELECT part:

    SELECT

    e.EmpID, a.Address,

    IF(m.ManagerName IS NULL, "",  m.ManagerName)  AS ManagerName,

    IF(.... IS NULL, "",  ....)  AS ....,

    ....

    FROM employee e

    LEFT JOIN manager m ON m.ManagerID = e.ManagerID

    LEFT JOIN ....

    LEFT JOIN ....

    WHERE ....

    That is if some employee have NO manager, the manager details will be NULL, not sure if that caused the problem.

    Tuesday, July 5, 2011 3:09 AM