SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: SQL query woes

  1. #1
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query woes

    Its me again,

    This is a follow up to another post. Hopefully I can explain my probs a little better.

    I'm trying to join the field names in one table with descriptive text in another table.

    Say I have main_table with the fields cat, dog, cow.
    In another table (description_table)there are 2 fields fldName,fldDescription:

    fldName | fldDescription
    dog | woof
    cat | meow
    cow | moo

    I want to join the field names in main_table with the values in description_table fldName and return the fldDescription to the recordset so I can use the fldDescription as a header row:

    woof | meow | moo

    what I've got:
    Code:
    <%
    
    Dim oCommand, oRS
    
    Set oCommand = Server.CreateObject("ADODB.Command")
      'strConnect passed from Include File "DataConnection.asp"
      oCommand.ActiveConnection = strConnect
      oCommand.CommandText = "select * from main_table, description_table _
    		where main_table.Field.Name=description_table.fldName"
      oCommand.CommandType = adCmdText
    
    'Create the recordset based on the oCommand SQL query
    Set oRS = oCommand.Execute           'this is the line returning the error
    
    'oCommand has done its job so close it & clean up
    Set oCommand  = Nothing
    'html table markup omitted 
    While not oRS.EOF
      Response.Write "<th>" & oRS("fldDescription") & "</th>"
      oRS.MoveNext
    Wend
    
    'oRS has done its job so close it & claen up
    oRS.Close
    Set oRS = Nothing
    
    %>
    The asp error statement:
    Code:
    Microsoft JET Database Engine error '80040e10' 
    
    No value given for one or more required parameters. 
    
    /wpcf_v2/aspTest/svcReqNames.asp, line 24
    I've been struggling with this for a few days now and am getting quite frustrated & discouraged as this seems to be a simple thing to do.

    Any help would be much appreciated!

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)

    Re: SQL query woes

    Try changing the SQL statement to:
    Code:
    oCommand.CommandText = "select * from main_table, description_table " & _
    		     " where main_table.Field.Name=description_table.fldName"
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help.

    I gave it a shot, but get the same error.

    I think I've been goin at this the wrong way & that using select/case or if/then/else to compare the values & write the text might be the way to go.

    Onward to the next error...

  4. #4
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: SQL query woes

    Originally posted by isotope235

    Code:
    <%
    
    Dim oCommand, oRS
    
    Set oCommand = Server.CreateObject("ADODB.Command")
      'strConnect passed from Include File "DataConnection.asp"
      oCommand.ActiveConnection = strConnect
      oCommand.CommandText = "select * from main_table, description_table _
    		where main_table.Field.Name=description_table.fldName"
      oCommand.CommandType = adCmdText
    
    'Create the recordset 
    based on the oCommand SQL query
    Set oRS = oCommand.Execute           'this is the line returning the error
    
    'oCommand has done its job so close it & clean up
    Set oCommand  = Nothing
    'html table markup omitted 
    While not oRS.EOF
      Response.Write "<th>" & oRS("fldDescription") & "</th>"
      oRS.MoveNext
    Wend
    
    'oRS has done its job so close it & claen up
    oRS.Close
    Set oRS = Nothing
    
    %>
    The asp error statement:
    Code:
    Microsoft JET Database Engine error '80040e10' 
    
    No value given for one or more required parameters. 
    
    /wpcf_v2/aspTest/svcReqNames.asp, line 24
    shouldn't it be: oCommand.CommandText = "select * from main_table, description_table _
    where main_table.Field.fldName=description_table.fldName"

    and correct me if I'm wrong, but it seems I've read somewhere that you shouldn't use underscores???

  5. #5
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bill,

    I'm not sure I'm following you. There is no fldName field in main_table.

    I used underscores in the thread for illustrative purposes only. I'm not using underscores in the "real" code though.

  6. #6
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    oops

    missunderstood your example! it appears to me that your telling the sql to select everything from the 2 table where the main-table and description-table are the same, correct? try the operator "Like" instead of "=". I have gotten that to work when "=" wouldn't...

    otherwise, I'm at a loss, but I'm still fairly new at this...

  7. #7
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm at a loss too

    I was trying to join the field names in the main-table with the records in a field in a the second table. I'm thinking now that it can't be done.

    Thanks for the help though.

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Is the code you posted the EXACT code or did you re-write it for some reason before posting it?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  9. #9
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code is exactly the same with the exception of the table names which where changed in an attempt to simplify the example at the beginning.

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Ohhh!! I see what you're trying to do now! <<slaps head a couple dozen times>>

    Try something like this:

    Code:
    dim fieldsarray
    fieldsarray = ""
    
    strSQL = "SELECT * FROM MAIN_TABLE"
    rs.Open strSQL, my_conn
    
    if rs.EOF then
       ' No records
    else
       for i = 0 to (rs.fields.count - 1)
           if i > 0 then
              fieldsarray = fieldsarray & "," 
           end if
           fieldsarray = fieldsarray & "'" & rs.fields(i).name & "'"
       next
       strSQL = "SELECT DESCRIPTION " & _
                "  FROM DESCRIPTION_TABLE " & _
                " WHERE FIELD_NAME in (" & fieldsarray & ")"
       ' Note:  You may need to play with this SQL statement to get the order you want.  I did it with the array to only have one hit on the database.  You could do it field by field.
       rs2.Open strSQL, my_conn
       Do until rs2.EOF
          Response.Write "<th>" & rs2("DESCRIPTION") & "</th>"
          rs2.movenext
       loop
       rs2.Close
       do until rs.EOF
          ' Do the rest of your coding here.....
    <disclaimer> This code has NOT been tested in anyway. It's just something to get you on your way....</disclaimer>

    Hope this helps a little!!!!!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  11. #11
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that looks like what I've been looking for. I'll give it a whirl & let you know how it turns out.

  12. #12
    .net install meltdown
    Join Date
    May 2001
    Location
    View Ridge
    Posts
    692
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works like a charm!

    Thanks again Dave, words cannot express my gratitude. I've been struggling with this for almost a week now. Your code also showed me a few new things I had'nt thought about yet.

    Your a lifesaver.
    Last edited by isotope235; Dec 13, 2001 at 14:01.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •