Jump to content

bsheridan

Members
  • Posts

    10
  • Joined

  • Last visited

bsheridan's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. The error wasn't in the SQL code after all, it was in the ASP code that i was using to diaplsy the record sets. I have 2 tables (staff and client) and in both tables i had a column called "firstName". So i just changed the columns to "staffFirstName" and "clientFirstName"... so now it works
  2. Ok,After reviewing the code again i amended it to this: strSQL = "SELECT * From (((SALE " & _ " Inner Join CLIENT On SALE.clientID = CLIENT.clientID)" & _ " Inner Join TREATMENT On SALE.treatmentID = TREATMENT.treatmentID)" & _ " Inner Join STAFF On SALE.staffID = STAFF.staffID)" & _ " WHERE (((CLIENT.clientID) = " & Request.QueryString("clientID") & "));" but it only lets me display the saleID when i try to return the record sets from the tables.Again, any help appreciated.Thanks,Bernard.
  3. Hello,I am trying to retrive data from 4 tables called the following: Client Staff Treatment Sale The Sale table holds foreign keys from the 3 other tables i.e: SALE.clientID, SALE.staffID and SALE.treatmentID What I want to do is be able to display data from tables Client, Staff and Treatment (which are obviously dependent on an entry in the Sale table) by using an SQL join on the SALE table. For example I want to be able to display a history of sales for a specific client which will show, the client's name, the treatment they had and the staff member who did the treatment.I reckon i should use Inner joins but the code below is not displaying anything: strSQL = "SELECT * From CLIENT INNER JOIN (SALE INNER JOIN (TREATMENT INNER JOIN STAFF" & _ "ON TREATMENT.treatmentID = SALE.treatmentID ) ON CLIENT.clientID = SALE.saleID " & _ "WHERE (( (CLIENT.clientID)="& Request.QueryString("clientID") & "));" Can you help?Thanks,Bernard.
  4. Hey,Got it fixed. I had some weird things going on with that form! Thanks for the help!Bernard.
  5. Hello Haibec,Wrox have a good starter auction website coded in ASP. You can download code from their book, Beginning ASP 3.0, at the following address: http://media.wiley.com/product_ancillary/3...AD/3382Ch15.zipHope this helps!
  6. Hi,I am trying to update a table with values from three other tables. This is what is happening;I have a Client, Treatment, Staff and Sale table. So when a Client buys a Treatment from a Staff member the Sale table is updated.My page that is displayed to the Client, shows the Treatment and Staff name in a drop down menu. The Staff member selects the Treatment and the Staff member who sold the treatment to the Client. However, when i click submit, only the first record from each table is added to the Sale table. Here is the code i am trying to use: <table border="0"><tr><td width="1000" class="bluenewshead"><b>Treatment</B></TD>x<td width="1000" class="bluenewshead"><b>Staff Member</B></TD><td width="1000" class="bluenewshead"><b>Action</B></TD><tr><form name="frm" action="insert_sale_script.asp?clientID=<%= rs3("clientID")%>&staffID=<%=rs2("staffID")%>&treatmentID=<%=rs("treatmentID")%>&clientID=<%=Session("clientID")%>" method="post" ><td width="1000" class="body"><tr><td width="1000" class="body"><select name="select" id="<%=rs2("firstName")%>" value="<%=rs2("staffID")%>" ><option value="<%=rs2("staffID")%>">Please Select</option><%'while not the end of our treatment record setdo while not rs.EOF%><option value="<%= rs("treatmentID")%>"> <%Response.Write rs("treatmentName")%></option><%' Move to the next recordrs.MoveNext' LoopLoop' move back to the first recordrs.MoveFirst%><td width="1000" class="body"><select name="select" id="<%=rs2("firstName")%>" value="<%=rs2("staffID")%>" ><option value="<%=rs2("staffID")%>">Please Select</option><%'while not the end of our staff member record setdo while not rs2.EOF%><option value="<%= rs2("staffID")%>"> <%Response.Write rs2("firstName")%></option><%' Move to the next recordrs2.MoveNext' LoopLoop' move back to the first recordrs2.MoveFirst%><td width="1000" class="body"><input type="submit" value="Add" id=submit1 name=submit1></td></tr></select></table><%'end of check of obs for displayend ifrs.Closers2.Close%> I have a Do While loop for the Treatment and the Staff member, but the update is only taking the first record set from each table. Can you help please
  7. Try... <tr> <td class="bodyText"><p>Use the form to search for a client by <b><font color=green>first name, second name or eMail</font></b>...</td> <% 'u_input is the varible for user input ' the trim function removes any blanks around the the use input u_input = trim(request.form("u_input")) 'the dropdown varible for user inputted search field u_field=request.form("u_field") ' If the user entered a value query the db if u_input <> "" then ' name of the access db accessdb="client_database" ' name of the table within the access db table_name="CLIENT" set rs = server.createobject("ADODB.Recordset") sql = "select * from "& table_name &" where " & u_field & " like '%%" & u_input & "%%' " rs.Open sql, objConn if rs.eof or rs.bof then response.write "<font size=4pt color=red>No results found...</font>" observations=0 else observations=1 'end check for observations end if end if 'end check for user input %> <% 'If there are observations then display them if observations > 0 then %> <table border="1" width=600> <tr> <TD width="1000" class="bluenewshead"><b>First Name</B></TD> <td width="1000" class="bluenewshead"><b>Last Name</B></TD> <td width="1500" class="bluenewshead"><b>E-Mail Address</B></TD> <td width="1000" class="bluenewshead"><b>Select</B></TD> <tr> <% 'Write the values do while not rs.eof %> </td> <td width="150" class="body"><%= rs("firstName") %></td> <td width="150" class="body"><%= rs("lastName") %></td> <td width="200" class="body"><%= rs("eMailAddress") %></td> </tr> <% rs.movenext loop %> <% 'end of check of obs for display end if %> <input type="text" name="u_input" value="<%= u_input %>"> <select name="u_field" size="1"> <option <% ' write out all the search fields and select if u_field = "firstName" or u_field = "" then response.write "selected " end if %>value="firstName">First Name</option> <option <% if u_field = "lastName" then response.write "selected " end if %>value="lastName">Second Name</option> <option <% if u_field = "eMailAddress" then response.write "selected " end if %>value="eMailAddress">eMail Address</option> </select> <input type="submit" value="Submit" id=submit1 name=submit1> </form>
  8. THanks,Will try that so...CHeers,
  9. Hey,HTML page that posts the data is: <INPUT TYPE="Text" NAME="firstName" VALUE="<%If Not blnNew = 1 Then Response.Write rsEdit("firstName") End If%>" ASP that requests data and inserts is: rsItem ("firstName") = Request.Form("firstName")rsItem.Update Thanks for the help!Bernard.
  10. Hi,When handling user registration details from an asp page to Access 2003 database, I want to be able to include hyphen (-) values and also apostrophe (') values in user names. For example,O'Loughlin or Oliver-PatrickAny suggestions welcome!Thanks,Bernard.
×
×
  • Create New...