SQL problem?

I have a little problem with filling a grid.
When I fill my grid I get a value in a column wich is always 0 or 1.
But I want to show yes or no in the grid.
So 0=no and 1=yes.
Where and how should I define this? In the SQL statement or in VBcode?


WiseGuy

I would do it in the VB-code.

The problem is if you do it in SQL (if it is possible) you would do it for all records. In VB-code you do it only for the current record, it is faster.

Franky

Okey so I should do it in VB but how do I do that?
Could you give a small example something that could help me get started?

WiseGuy

B.T.W. I'm using the MSHFlexGrid with an Oracle DB

create on your form a CheckButton
Then write:

I don't think that it'll work when I write:





LOL :D



WiseGuy

create on your form a CheckButton

rsTest is the Recordset

Then write:


myForm.chkYesNo = rsTest!YesNoField


Because your field have 0 for No and 1 for yes.

Thats all

Franky

I don't really understand what you mean. I've tried the thing you said but it did't work.
Also I assumed that with Checkbutton you meant Checkbox.
Here is what I made of it:

VB Code:

Private Sub Check1_Click()
frmPlanningBrowse.Check1 = rs!blokkeren
End Sub

Here's the entire SQL code if its of any use:

VB Code:

strSQL = "   SELECT  PO.Verstoring, PO.OpdrachtId, PO.Bonnummer, " & _
            "          PO.ReceptId, A.ArtikelNaam, RT.TypeCode, " & _
            "           PO.RetourID, PO.RetourHoeveelheid, " & _
            "           PO.Plandatum, PO.ChargeGewicht, " & _
            "           PO.AantalCharges, PO.AantalGereed, " & _
            "           PO.StartDatumTijd, PO.LotnummerWas, " & _
            "           PO.Bestemming, R.Versie, PO.Status, " & _
            "           PO.Blokkeren, PO.Commentaar, " & _
            "           R.ChargeGewichtKg, R.Datum, PO.ProductCode, " & _
            "           PO.IsOrder, R.NieuwRecept, PO.IsVerwijderd, " & _
            "           PO.OperatorInvoer " & _
            "   From    ReceptType RT, Recepten R, Artikelen A, ProductieOrders PO " & _
            "   WHERE   RT.ReceptTypeId = R.ReceptTypeId (+) and " & _
            "           R.ReceptId = PO.ReceptId (+) and " & _
            "           PO.ReceptId = A.ArtikelId and " & _
            "           PO.IsVerwijderd = 0 " & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "

WiseGuy

Before i sent this to you i checked it and it worked.

So, there is a problem. But that is normaly not a big problem. Just write then for example:

if rs!blokkeren = 0 then
frmPlanningBrowse.Check1 = TRUE
else
frmPlanningBrowse.Check1 = FALSE
end if

The only problem can be the type of the field. if it is a numeric then you can use my code from before, if it is a textfield, then you can use the code from before and val(rs!blokkeren)

Franky


Your sqlCommand looks ok.

Well, I still can't get it work but If I understand your code then the checkbox indicates wether it's true or false but I want to show in the grid wether it is true or false. And preferably 'Ja' or 'Nee' (Dutch for yes or no).

WiseGuy

BTW it is a character field (Char)

With the first code I keep getting the runtim-error 3021 "BOF or EOF is true, or the current record has been removed.... etc."


WiseGuy

Then don't use a checkBox, use a textfield:

txtBlokkeren.Enabled = TRUE
if trim(rs!blokkeren) = "0" then
txtBlokkeren.Text = "Nee"
elseif trim(rs!blokkeren) = "1" then
txtBlokkeren.Text = "Ja"
else
txtBlokkeren.Text = " "
end if
txtBlokkeren.Enabled = False

Franky

hmm, still getting the same error.

VB Code:

If question = you_know_any_other_solutions 
Then answer = "I'd like to hear them from you"
else too_bad

LOL :D


WiseGuy

Attach please your DB with some records.

And your project. I will check it.

I am sure that my way works. there must be something with your DB.

Which result you get when you make before this IF-Then a msgbox with msgbox rs!blokkeren ?

Franky

I'm sorry I can't post the DB: for 1 it's Oracle and second it contains calassified information of recepies from a big multi national company called Zeelandia. But that's irrelevant. I can send you the entire project but I don't think that's of any use without a DB.

WiseGuy

Quote:

---

Which result you get when you make before this IF-Then a msgbox with msgbox rs!blokkeren ?

---

I'll try this

Of course you can send the DB. If it is classified then just send 2 Records in each table and if nec. change the text in the fields.
But then it will be no problem anymore
Oracle to load is for me no problem here.

Franky