How to use a query for 2 grids?

[WiseGuy]

Gooood morning everyone,

here's another question from me to break your heads at
well I gues it's not so hard.

Here's the situation:

I've got a beautiful SQL query that loads into a grid. That works fine.
But I want to use the same query to use it for another grid on the same form. Only this time it needs a little extra. here's the query:

Dim strSQL As String
  
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Data Source=spijsDB;User ID=spijs;Password=spijs;"
  cn.Open
  Set rs = New ADODB.Recordset
  grdDatagrid2.Refresh
  cn.CursorLocation = adUseServer
  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 " & _
            "           PO.isorder = 1 " & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "
  rs.Open strSQL, cn

And with the second grid it needs PO.isorder = 0 instead of = 1

Anyone has a solution for this? Or do I just have to make a new query?

[mendhak]

Suggestion:

create an integer.

public gridnumber as integer
 
'now, for grid 1, 
gridnumber=0
'for grid 2,
gridnumber = 2
 
'this above is a rough idea
 
 
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 " & _
            "           PO.isorder = " & gridnumber & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "

Since I do not know whether this is in some function... I would suggest you create this common integer, and depending upon the grid, just change gridnumber... for example


private sub grid1load()

gridnumber = 1
'call common function
end sub

private sub grid2load()
gridnumber = 0
'call common function
end sub


I hope I am haven't confused you, and what language is that?

[WiseGuy]

As you could have seen at 'location', I'm from the Netherlands so the language is Dutch


Thanks for the help, I'll try what you suggested and let you know if it works or not.


WiseGuy

[mendhak]

aah. You're welcome, and I hope it works...

[WiseGuy]

There is no such thing as Grid_load, so it has to be done at form_load or form_activate (I gues).
Here's what it looks like now:

Private Sub Form_Load()
  Dim strSQL As String
  Dim Gridnummer As Integer
     
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Data Source=spijsDB;User ID=spijs;Password=spijs;"
  cn.Open
  Set rs = New ADODB.Recordset
  grdDatagrid2.Refresh
  cn.CursorLocation = adUseServer
  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 and" & _
            "           PO.isorder = " & Gridnummer & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "
  rs.Open strSQL, cn
  'isorder wordt handmatig op 1 of op 0 gezet
  Gridnummer = 1
  Set grdDatagrid2.DataSource = rs
  Gridnummer = 0
  Set grdDataGrid1.DataSource = rs
  grdDataGrid1.Refresh
  grdDatagrid2.Refresh

And this is no succes. I don't get any errors or anything so I hope you can see what I did wrong!


WiseGuy

[mendhak]

Actually, those functions don't exist. I was merely giving you an idea, such as creating a function for that purpose. Try this: um... let me think first ....

Private Sub Form_Load()
  Dim strSQL As String
  Dim Gridnummer As Integer
     
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Data Source=spijsDB;User ID=spijs;Password=spijs;"
  cn.Open
  Set rs = New ADODB.Recordset
  grdDatagrid2.Refresh
  cn.CursorLocation = adUseServer
 
  rs.Open strSQL, cn
  'isorder wordt handmatig op 1 of op 0 gezet
 
  Set grdDatagrid2.DataSource = getdatastring(1)
 
  Set grdDataGrid1.DataSource = getdatastring(0)
  grdDataGrid1.Refresh
  grdDatagrid2.Refresh
 
end sub
 
Private function getdatastring(gridnumber as integer) as string
 
getdatastring = "   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 and" & _
            "           PO.isorder = " & Gridnumber & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "
 
end function

there. hope that helps. do tell me.. we'll work it out

[mendhak]

wait! mistake!!

gimme a couple minz

[mendhak]

OK, I apologize for misleading you earlier.

You're going to have to open TWO recordsets.

Don't get mad at me. I've already been criticized for being an ******* today. So... two recordsets. It's not possible to modify a recordset query without closing and reopening it. I suggest two recordsets

So, basically, whatever you said in the last sentence of your post was right. New query. Would u like me to help you there?

Sorry, and hth... damn. bad day today.

[WiseGuy]

So basicly I have to define the SQL query 2 times with the adjustment? And there is no (easy) way to do it with only one?


WiseGuy

B.T.W. I'm not mad at you, why should I? I'm glad someone takes the time to help me out!

[mendhak]

dim rs1 as adodb.recordset
dim rs2 as adodb.recordset
 
 
Private Sub Form_Load()
  Dim strSQL As String
  Dim Gridnummer As Integer
     
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Data Source=spijsDB;User ID=spijs;Password=spijs;"
  cn.Open
 
  Set rs1 = New ADODB.Recordset
  set rs2 = new adodb.recordset
 
  grdDatagrid2.Refresh
  cn.CursorLocation = adUseServer
 
  rs1.Open getdatastring(0), cn
rs2.open getdatastring(1),cn
  'isorder wordt handmatig op 1 of op 0 gezet
'knuut naarf!  :D
 
  Set grdDatagrid2.DataSource = rs1
 
  Set grdDataGrid1.DataSource = rs2
 
msgbox("KnuutZaarf!  Hahaha! ")
 
 
  grdDataGrid1.Refresh
  grdDatagrid2.Refresh
 
 
 
end sub
 
 
 
Private function getdatastring(gridnumber as integer) as string
 
getdatastring = "   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 and" & _
            "           PO.isorder = " & Gridnumber & _
            "  ORDER BY PO.Verstoring, PO.OpdrachtId "
 
end function

THERE!!!

that should be it!

[WiseGuy]

Thanks for the code, that did work! Sorry it took a while to figure that out but I am adjusting an exciting program so there where about 500 lines I had to go through!



Thanks,

WiseGuy

[mendhak]

NP