sql

Discussion in 'Questions (Windows Mobile)' started by dennishea, Jun 27, 2008.

  1. dennishea

    dennishea Active Member Licensed User

    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   
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Yes. Why don't you use ExecuteTable instead? It will be faster and simpler.
    You could also use a parameterized query:
    Code:
    [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]
     
  3. dennishea

    dennishea Active Member Licensed User

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    You could use the Control keyword to remove repetitive code:
    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.
     
  5. dennishea

    dennishea Active Member Licensed User

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

    :sign0188:

    Thanks Denny.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice