sql

[dennishea]

Should something like this work.

    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]

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

[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]

Hi Erel.

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

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]

You could use the Control keyword to remove repetitive code:

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]

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

:sign0188:

Thanks Denny.