Basic(?) SQL question

Results 1 to 2 of 2

Thread: Basic(?) SQL question

  1. #1
    Chris Goldfarb Guest

    Default Basic(?) SQL question

    Greetings,<BR><BR>I am relatively new at SQL. I am running into the following error when attempting to execute this SQL statement:<BR><BR>select opponent, firstname, lastname, email from opponents where number=(select opponent from matches where gameNum=38)<BR><BR>Microsoft JET Database Engine error &#039 80040e10&#039 <BR><BR>No value given for one or more required parameters <BR><BR>/myDir/myFile.asp, line 75 <BR><BR>I am using Access 2000. I have two tables: One called opponents which contains user information (and a unique record number), and one called matches, which contains three fields: a unique record number, a game number, and an opponent number. The opponent number field in the matches table refers to the unique record number in the opponents table.<BR><BR>I have gone into Access and "joined" these tables appropriately (I think), where the opponent number field in the matches table links to the unique opponent number in the opponents table.<BR><BR>In a nutshell, I am trying to retrieve a recordset of opponents who are assigned to a specific game (in the case above, game #38). I can&#039t simply add a game number field in the opponents table, because each opponent can be involved in more than one game.<BR><BR>I appreciate any help - thanks in advance!<BR><BR>Chris Goldfarb<BR><BR>www.warplanner.c om<BR>

  2. #2
    David Highlander Guest

    Default RE: Basic(?) SQL question

    Well the reason for your error is really just like it says, You are not supplying parameters for the WHERE statement. <BR><BR>If I understand you correctly, You want to display the user information of the table "opponents" only if the value of the table "matches" is equal to 38. <BR><BR>And gameNum is a field in the table "matches" that stores that number. <BR><BR>So I think you want something like this:<BR><BR>Because you have two tables you need pull the data from both.<BR><BR>&#060;%<BR>Dim SQLstring<BR>SQLstring = "SELECT opponent.opponent, opponent.firstname, opponent.lastname,, " & _<BR> "matches.gameNum " & _<BR> "FROM opponent, matches " & _<BR> "WHERE matches.gameNum = " & 38 & " " & _<BR> "AND opponent.ID = matches.ID"<BR><BR>Dim conn<BR>Dim UserRecordset<BR>Set conn = Server.CreateObject("ADODB.Connection")<BR>conn.op en ("DSN-NAME")<BR>Set UserRecordset = Server.CreateObject("ADODB.Recordset")<BR>UserReco rdset.Open SQlstring, conn<BR><BR>If not UserRecordset2.EOF Then<BR><BR>-----Then you have to assign the OBJ to those variables-----<BR><BR>Set objfirstname = UserRecordset("firstname")<BR>Set objlastname = UserRecordset("lastname")<BR>Set objemail = UserRecordset("email")<BR>Set objopponent = UserRecordset("opponent")<BR><BR>----Then display those Variables ----<BR><BR>&#060;%=objfirstname%&#062;<BR>&#060;%=obj lastname%&#062;<BR>&#060;%=objemail%&#062;<BR>&#06 0;%=objopponent%&#062;<BR><BR>%&#062;<BR><BR>Ok this may or may not work depending on the syntax and the name of your DSN name. I doubt you can simply cut paste the code although you may want to give it a try.<BR><BR>I&#039m sure someone can come behind me and look at the syntax to make sure it is correct.<BR><BR>But with any luck this could be a good starying point to help you understand what you need to do to pull data from more than one table.

Posting Permissions

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