sql

dennishea · Jun 27, 2008

Should something like this work.

PHP:

    reader.New1
   cmd.CommandText = "SELECT * FROM WorkData Where WireSize ='"& txtWireSize.Text & "'"
   Reader.Value = cmd.ExecuteReader
   Do While reader.ReadNextRow = True 
       tblFilter.AddRow(reader.GetValue(0),reader.GetValue(1),reader.GetValue(2), _
      reader.GetValue(3),reader.GetValue(4),reader.GetValue(5), _
      reader.GetValue(6),reader.GetValue(7),reader.GetValue(8), _
      ,reader.GetValue(9),reader.GetValue(10))
   Loop
   Reader.Close

Erel · Jun 28, 2008

Yes. Why don't you use ExecuteTable instead? It will be faster and simpler.
You could also use a parameterized query:

B4X:

[COLOR=#000000][COLOR=Black]cmd[/COLOR][COLOR=Black].[/COLOR][COLOR=Black]CommandText [/COLOR][COLOR=Black]= [/COLOR][COLOR=Black]"SELECT * FROM WorkData Where WireSize = @value1[/COLOR][COLOR=#dd0000][COLOR=Black]"
cmd.AddParameter("value1")
cmd.SetParameter("value1",txtWireSize.Text)
...[/COLOR]
[/COLOR][/COLOR]

dennishea · Jun 28, 2008

Hi Erel.

After a few hrs this is what I came up with.

PHP:

Sub MySort
   If txtInternal.Text <> "" Then 
      MyFilter = "Internal"
      Load_tblFilter
   Else If txtColdOd.Text <> "" Then
      MyFilter = "ColdOd"
      Load_tblFilter
   Else If txtColdFp.Text <> "" Then
      MyFilter = "ColdFp"
      Load_tblFilter
   Else If txtHotOd.Text <> "" Then
      MyFilter = "HotOd"
      Load_tblFilter
   Else If txtHotFp.Text <> "" Then
      MyFilter = "HotFp"
      Load_tblFilter
   Else If txtAmtWire.Text <> "" Then
      MyFilter = "AmtWire"
      Load_tblFilter
   Else If txtPph.Text <> "" Then
      MyFilter = "Pph"
      Load_tblFilter
   Else If txtWireSize.Text <> "" Then
      MyFilter = "WireSize"
      Load_tblFilter
   Else If txtCycTime.Text <> "" Then
      MyFilter = "CycTime"
      Load_tblFilter
   Else If txtMach.Text <> "" Then
      MyFilter = "Mach"
      Load_tblFilter
   Else
      Msgbox("Nothing entered")
      frmFilter.Close
   End If
End Sub

Sub Load_tblFilter
   Select MyFilter
      Case "Internal"
         cmd.CommandText = "Select * from WorkData Where Internal = '" & txtInternal.Text & "'"
      Case "ColdOd"
         cmd.CommandText = "Select * from WorkData Where ColdOd = '" & txtColdOd.Text & "'"
      Case "ColdFp"
         cmd.CommandText = "Select * from WorkData Where ColdFp = '" & txtcoldfp.Text & "'"
      Case "HotOd"
         cmd.CommandText = "Select * from WorkData Where HotOd = '" & txthotod.Text & "'"
      Case "HotFp"
         cmd.CommandText = "Select * from WorkData Where HotFp = '" & txthotfp.Text & "'"
      Case "AmtWire"
         cmd.CommandText = "Select * from WorkData Where AmtWire = '" & txtamtwire.Text & "'"
      Case "Pph"
         cmd.CommandText = "Select * from WorkData Where Pph = '" & txtpph.Text & "'"
      Case "WireSize"
         cmd.CommandText = "Select * FROM WorkData Where  WireSize < '" & txtWireSize.Text & "'"
      Case "CycTime"
         cmd.CommandText = "Select * from WorkData Where CycTime = '" & txtCycTime.Text & "'"
      Case "Mach"
         cmd.CommandText = "Select * from WorkData Where Mach = '" & txtMach.Text & "'"
      End Select   
      cmd.ExecuteNonQuery
      cmd.ExecuteTable("tblFilter",500)
End Sub

I still am not sure how to use parameters. Another flaw in my thinking is this will only search for exact matches and I would rather be able to create a range in a single textbox (Internal, Mach, ect,...)or multiple textboxes ( Internal and ColdOd and ect ) to define a narrower search. I have tried a couple of things and have not been successful yet.

As always your help is greatly apreciated. :sign0188:

Denny

Erel · Jun 28, 2008

You could use the Control keyword to remove repetitive code:

B4X:

Sub Globals
 Dim filters(0)
End Sub

Sub App_Start
 filters() = Array("Internal","ColdOd","ColdFp",...)
...
cmd.AddParameter("value1")
End Sub

Sub MySort
 For i = 0 To ArrayLen(filters())-1
  if Control("txt" & filters(i)).Text <> "" Then
    Loadtbl_Filter(filter(i))
    Exit
 End If
 if i = ArrayLen(filters()) Then
  Msgbox("Nothing entered.")
  ...
 End If
End Sub

Sub Loadtbl_Filter(flt)
 if flt = "WireSize" Then
      [COLOR=#000000][COLOR=Black]cmd[/COLOR][COLOR=Black].[/COLOR][COLOR=Black]CommandText [/COLOR][COLOR=Black]= [/COLOR][COLOR=Black]"Select * FROM WorkData Where WireSize < @value1"
[/COLOR][COLOR=#dd0000][COLOR=Black]else
 cmd.CommandText = "Select * From WorkData Where " & flt & " = @value1"
end if
cmd.SetParameter("value1",Control("txt" & flt).Text)
cmd.ExecuteTable (...)
[/COLOR][/COLOR][/COLOR]

BTW, cmd.ExecuteNonQuery is not required and it just creates another query.

dennishea · Jun 28, 2008

Thanks erel, this will keep me busy for a while.

:sign0188:

Thanks Denny.

sql

dennishea

Active Member

Erel

B4X founder

dennishea

Active Member

Erel

B4X founder

dennishea

Active Member

sql

dennishea

Active Member

Erel

B4X founder

dennishea

Active Member

Erel

B4X founder

dennishea

Active Member

Privacy & Transparency

Privacy & Transparency