Dataset question from novice on a deadline

Results 1 to 2 of 2

Thread: Dataset question from novice on a deadline

  1. #1
    Join Date
    Dec 1969

    Default Dataset question from novice on a deadline

    I have a form in which the user has checkboxes to choose which surveys they would like to see descriptions as well as survey data if they choose. So, I get the results from the form submission and run my SQL to get back the data.<BR>***************************************** ******************<BR>bmCode = Request.Form("bmCode")<BR>bmCode = Replace( bmCode, ", ", "&#039;,&#039;" )<BR><BR>If requestData = "DATA" Then<BR> Set objRSGetSurveyData = Server.CreateObject("ADODB.RECORDSET")<BR> sql = "SELECT survey_data.survey, survey_data.DATA_EFF_DATE, survey_data.SURVEY_BM_CODE, survey_data.LEV, survey_data.BM_TITLE, survey_data.SCOPE1, survey_data.SCOPE2, survey_data.[#_OF_COS], survey_data.[#_OF_INCUMB], survey_data.[10TH_BASE], survey_data.[25TH_BASE], survey_data.[50TH_BASE], survey_data.[75TH_BASE], survey_data.[90TH_BASE], survey_data.[25TH_BONUS_TGT], survey_data.[50TH_BONUS_TGT], survey_data.[75TH_BONUS_TGT], survey_data.[PERC_ELIG_BONUS], survey_data.[25TH_BONUS_ACT], survey_data.[50TH_BONUS_ACT], survey_data.[75TH_BONUS_ACT], survey_data.[10TH_TCC], survey_data.[25TH_TCC], survey_data.[50TH_TCC], survey_data.[75TH_TCC], survey_data.[90TH_TCC], survey_data.PERC_ELIG_LTI, survey_data.[25TH_LTI], survey_data.[50TH_LTI], survey_data.[75TH_LTI], survey_data.[25TH_TDC], survey_data.[50TH_TDC], survey_data.[75TH_TDC] FROM survey_data WHERE (survey_data.DATA_CODE)IN (&#039;" & bmCode & "&#039;) ORDER BY survey_data.SURVEY, survey_data.SURVEY_BM_CODE, survey_data.LEV ;" <BR> Set objRSGetSurveyData = dbConn.Execute(sql)<BR>End If <BR><BR>If no data came back at all (EOF) then I write out a message accordingly. My issues is if only some of the selections do not have data. For example, if they chose to see three out of the five surveys and only two of those three have data associated in the database, I would like to display data available yet also write out a line that says "Survey123 does not yet have date loaded in the warehouse." So, based on my SQL (WHERE (survey_data.DATA_CODE)IN (&#039;" & bmCode & "&#039;)), how can I extract which value in the string "bmCode" did not have information to be returned?<BR><BR>Hopefully I have explained this correctly. Any and all information greatly appreciated.

  2. #2
    Join Date
    Dec 1969

    Default RE: Dataset question from novice on a deadline

    Run multiple SQL Statements in a loop for every value contained in &#039;bmCode&#039;.<BR><BR>Rather than using one single SQL Statement:<BR>"WHERE (survey_data.DATA_CODE) IN (&#039;" & bmCode & "&#039;) "<BR><BR>I suggest lopping the SQL Statement, Recordset and output:<BR>"WHERE survey_data.DATA_CODE =&#039;" & bmCode_parsed(i) & "&#039; " <BR>where: bmCode_parsed(i) represents EACH value in bmCode (0&#062;i&#062;n).<BR>where: n+1 representds the total number of bmCodes.<BR><BR>I hope this helps.

Posting Permissions

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