iOS Question Using Resultset to return a "table.column"

[thughesimsuk]

I am in the middle of converting an application from b4a into b4i. I am using a resultset and I get an error if I try to return data using the table and column name.

For example I have an inner join on some tables and there is a column called question, in the question table i.e. getstring("question.question") there is also a column called question in some other tables, I understand the ambigious nature of the column names but I was not responsible for the design.

Is there a way of referencing the table name and column that I am after?

1. I have tried "select question as Q..." > getstring("question.Q").
2. I have also tried getstring("[question].question").
3. I tried to target the colum too using getstring(5) '5 being an example of where question column is in the sql select.

I am using resultset and a db3 database.

Thanks

Tom

 

[imbault]

Hi, can we see your code, between the ExecQuery and your resultset.GetString please, cause like this, it's hard to help you...

 

[thughesimsuk]

Hi, can we see your code, between the ExecQuery and your resultset.GetString please, cause like this, it's hard to help you...

Sure thing,

    Dim GetAuditInfoSQL As String
    Dim Cursor1 As ResultSet
   
    GetAuditInfoSQL = "Select AssessmentSummary.AssessmentSummaryRef,AssessmentSummary.Location, QuestionHeading.QuestionHeadingref, QuestionHeading.Type, QuestionHeading.InOut, QuestionHeading.FixedScore, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.QuestionHeadingNr, QuestionHeading.QuestionHeading as audit_name, QuestionHeading.QuestionHeadingDescription, QuestionHeading.ComboReference, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.Live, QuestionHeading.FromLow, QuestionHeading.ToLow, QuestionHeading.FromMedium, QuestionHeading.ToMedium, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.FromHigh, QuestionHeading.ToHigh, QuestionHeading.AssessedScore, QuestionHeading.AssessedRisk, QuestionHeading.HeadingField, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.Display, QuestionHeading.Mechanism, QuestionHeading.Increase_improve, QuestionHeading.COMPANYREF, QuestionHeading.SUBJECTIVE, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.NONA, QuestionHeading.CHECKLIST, QuestionHeading.IMPACTS, QuestionHeading.SPECIAL, QuestionHeading.Create_Percent, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.link_ref, QuestionHeading.Pass1, QuestionHeading.AuditGroupRef, QuestionHeading.Include, QuestionHeading.RWARef, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.RWASystem, QuestionHeading.FTOSet, QuestionHeading.ActivePeriod, QuestionHeading.GroupPriority, QuestionHeading.ReportOrderPriority, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.BaseScore, QuestionHeading.ConcactenateSolutions, QuestionHeading.LockedFromEdit, QuestionHeading.QuestionHeadingNumOriginator, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.CorporationOrigin, QuestionHeading.sharing, QuestionHeading.Resolution, QuestionHeading.ReportTitle, QuestionHeading.ReportNarrative, "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading.CalculatedConsultant "
    GetAuditInfoSQL = GetAuditInfoSQL & "FROM AssessmentSummary INNER JOIN "
    GetAuditInfoSQL = GetAuditInfoSQL & "QuestionHeading ON AssessmentSummary.QuestionHeadingRef = QuestionHeading.QuestionHeadingref "
    GetAuditInfoSQL = GetAuditInfoSQL & "WHERE (AssessmentSummary.AssessmentSummaryRef = "&CurrentAuditAssRef&") "

    Cursor1 = Main.SQL1.ExecQuery(GetAuditInfoSQL)




AuditHeading.Text = Cursor1.GetString("audit_name")

You can see my last attempt was to call the column "audit_name"

 

[imbault]

You need a cursor iteration like

Do While Cursor1.NextRow
AuditHeading.Text = Cursor1.GetString("audit_name")
Loop

 

[thughesimsuk]

You need a cursor iteration like

Do While Cursor1.NextRow
AuditHeading.Text = Cursor1.GetString("audit_name")
Loop

Thanks buddy that's done the trick, I must of forgotten to put that in. Can you say noob! Thanks again