SQL query woes

[isotope235]

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:

<%

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:

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!

[DaveMaxwell]

Try changing the SQL statement to:

oCommand.CommandText = "select * from main_table, description_table " & _
		     " where main_table.Field.Name=description_table.fldName"

[isotope235]

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

[bbolte]

Originally posted by isotope235

<%

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:

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???

[isotope235]

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.

[bbolte]

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

[isotope235]

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.

[DaveMaxwell]

Is the code you posted the EXACT code or did you re-write it for some reason before posting it?

[isotope235]

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.

[DaveMaxwell]

Ohhh!! I see what you're trying to do now! <<slaps head a couple dozen times>>

Try something like this:

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!!!!!

[isotope235]

Thanks, that looks like what I've been looking for. I'll give it a whirl & let you know how it turns out.

[isotope235]

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.