1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development ASP/SQL/ADO help...

Discussion in 'Software' started by rupbert, 9 Sep 2003.

  1. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Hey guys the basic premise is thus: I have an admin section for a Library Board's contacts, and one of the final pages I am creating is a 'deleteSection'.

    The admin can search for the desired section, and once found they can click on it and pass the unique sectionID through the url. On the final page I have currently been able to retrieve the sectionID from the url, and display a list of contact's that have been assigned the sectionID that has been passed.

    I want to create a SQL statement that checks the recordset that holds the above contacts, and something like

    IF rsSection.recordcount > 0 THEN
    "You cannot delete this section, as users have been assigned etc..."
    ELSE
    "DELETE from tblsection WHERE sectionID LIKE 'varSectionID'
    END IF

    I have not done manual coding for a while now, and I am not sure of the order the code should be placed, and some of the syntax seems alien....

    Here is what I currently have, a mixture of manual and Dreamweaver code:

    #####

    <%
    'Retrieve the sectionID from the URL and place it in a variable
    Dim sectionID
    sectionID = Request.Querystring("sectionID")

    Dim rsSection

    'Retrieve the list of contacts, if they have been assigned the section

    Set rsSection = Server.CreateObject("ADODB.Recordset")
    rsSection.ActiveConnection = MM_contactsDSN_STRING
    rsSection.Source = "SELECT contactName FROM tblcontacts WHERE sectionID LIKE '" & sectionID & "'"
    rsSection.CursorType = 0
    rsSection.CursorLocation = 2
    rsSection.LockType = 1
    rsSection.Open()%>

    'Check if the number of records is more than 0, if so alert the admin that the section cannot be deleted; if 0 then delete

    IF rsSection.recordcount > 0 THEN
    "You cannot delete this section, as users have been assigned etc..."
    ELSE
    "DELETE from tblsection WHERE sectionID LIKE 'varSectionID'
    END IF

    #####
     
  2. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    doesnt it work? Cos that looks like it should work.. As long as you include the <% around the if else endif bits?

    eg this should work.

    Code:
    <%
      IF rsSection.recordcount > 0 THEN %>
        "You cannot delete this section, as users have been assigned etc..."
    <%
      ELSE
    
        set oCmd = Server.CreateObject("ADODB.Command")
        oCmd.ActiveConnection = MM_contactsDSN_STRING
        oCmd.CommandType = 1
        delSQL = "DELETE from tblsection WHERE sectionID LIKE 'varSectionID'
        oCmd.CommandText = delSQL
        oCmd.Execute
    
    END IF
    %>
    
    
     
  3. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Thanks for the reply LaughingJon, I think there is a problem with the recordcount as it still deletes the section even with your cmd...

    I get no errors, it just deletes it!?!

    Here is the current code now:

    <%
    Dim sectionID, rsSection, oCmd, delSQL

    sectionID = Request.Querystring("sectionID")

    Set rsSection = Server.CreateObject("ADODB.Recordset")
    rsSection.ActiveConnection = MM_contactsDSN_STRING
    rsSection.Source = "SELECT contactName FROM tblcontacts WHERE sectionID LIKE '" & sectionID & "'"
    rsSection.CursorType = 0
    rsSection.CursorLocation = 2
    rsSection.LockType = 1
    rsSection.Open()
    %>

    <%
    IF rsSection.recordcount > 0 THEN %>
    <%Response.Write("You cannot delete this section, as users have been assigned etc...")
    %>

    <%
    ELSE

    set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    delSQL = "DELETE from tblsection_copy WHERE sectionID LIKE '" & sectionID & "'"
    oCmd.CommandText = delSQL
    oCmd.Execute
    END IF
    %>
     
  4. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    hummmm confusing.. you could try...

    Code:
    if(NOT rsSection.EOF)THEN
    
    <%Response.Write("You cannot delete this section, as users have been assigned etc...")
    %>
    
    <%
    ELSE
    
    set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    delSQL = "DELETE from tblsection_copy WHERE sectionID LIKE '" & sectionID & "'"
    oCmd.CommandText = delSQL
    oCmd.Execute
    END IF
    
    
     
  5. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Many thanks mate, I always thought there was a problem with the recordcount because it always returned -1 (a problem with the locktype?)...

    Its working great now, however it checks for deletion onload, I would prefer to have a 'delete' button which then ran your 0Cmd...

    Easy enough?

    I assume its something like Type = Submit onclick = "oCmd"?
     
  6. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    hey there

    yeah all you need to do is create a form

    a very simple eg could be..

    Code:
    <% if(Request.Form("subbed")) then
    
      Dim sectionID, rsSection, oCmd, delSQL
    
      sectionID = Request.Querystring("sectionID")
    
      Set rsSection = Server.CreateObject("ADODB.Recordset")
      rsSection.ActiveConnection = MM_contactsDSN_STRING
      rsSection.Source = "SELECT contactName FROM tblcontacts WHERE sectionID LIKE '" & sectionID & "'"
      rsSection.CursorType = 0
      rsSection.CursorLocation = 2
      rsSection.LockType = 1
      rsSection.Open()
    
      if(NOT rsSection.EOF)THEN
      %>
    
      Response.Write("You cannot delete this section, as users have been assigned etc...")
      <%
      ELSE
    
      set oCmd = Server.CreateObject("ADODB.Command")
      oCmd.ActiveConnection = MM_contactsDSN_STRING
      oCmd.CommandType = 1
      delSQL = "DELETE from tblsection_copy WHERE sectionID LIKE '" & sectionID & "'"
      oCmd.CommandText = delSQL
      oCmd.Execute
      END IF
    
    else %>
      <form method="post" name="aform" action="your.asp">
      <input type="hidden" name="subbed" value="true">
      <input type="submit" value="Del This">
      </form>
    <% endif %>
    
    Laters
    Jon
     
  7. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Many thanks mate, much appreciated :)
     
  8. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    i take it by some fluke that code worked of sorts then LOL :)

    NPs bud

    Laters
    Jon
     
  9. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Hey mate just wondering, as I sit here tweaking the finished pages, I was thinking it might be a good idea to apply a similar IF condition to the Add new Section/Department pages...

    Obviously it would be careless to allow the admin the add a new Section that already exists!

    Just wondering whats the easiest way to go about checking if the sectionID passed through the URL already exists in tblsection_copy?

    IF rsSection.Fields.Item("sectionID) == sectionID THEN
    Response.Write("This Section already exists etc...")
    ELSE
    set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    delSQL = "INSERT INTO tblsection_copy ([section]) VALUES ("section")"
    oCmd.CommandText = delSQL
    oCmd.Execute
    END IF

    The SQL syntax is wrong yes?
     
  10. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    is this what you mean?

    Code:
    //grab sectionID from URL
    sectionID = Request.Querystring("sectionID")
    
    //need to check that there is no section with this ID already created.  thus.
    
    rsSection.ActiveConnection = MM_contactsDSN_STRING
      rsSection.Source = "SELECT * FROM tblsection_copy WHERE sectionID LIKE '" & sectionID & "'"
      rsSection.CursorType = 0
      rsSection.CursorLocation = 2
      rsSection.LockType = 1
      rsSection.Open()
    
    
    
    IF not(rsSection.EOF) then
    //section already exists as recordset isnt null
    Response.Write("This Section already exists etc...")
    ELSE
    set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    delSQL = "INSERT INTO tblsection_copy VALUES (" & section & ")"
    oCmd.CommandText = delSQL
    oCmd.Execute
    END IF
    
    Cheers
    Jon
     
  11. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Yeah cheers mate, knew there was a simpler way to check if the section already existed, thanks :)

    Steve
     
  12. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    cool... nps matey :D
     
  13. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    me again :)

    I have this code for the Add New Section page, its working fine. I have three if statements, the first checks that the inputbox is not empty; the second checks if the section already exists and the third runs the SQL statement if no section already exists...

    However, when I go to Add a new section having left the inpubox blank, it returns both the repsonse.write for the first IF statement ("Please enter a Section") and ALSO the response.write for the second IF statement ("The Section you tried to add already exists...")

    Obviously I only want each relevant response.write to display, is there a way round this?

    In Short, if the admin trys to add a blank section, I want to display "Please enter a section"; but I don't want to check if the section exists

    %IF varSection = "" THEN
    Response.Write("Please enter a Section")
    END IF

    IF NOT (rsSection.EOF) THEN
    Response.Write("<font color=##990000>The Section </font><font color=#666666>'" & varSection & "'</font><font color=#990000> you tried to add, already exists...</font>")Response.Write("<br><br>You can try to add another Section, or click <a href=http://belbweb1/_demo/admin_contacts/contactsDatabaseAdmin.asp>here</a> to go back to the main admin page")
    END IF

    IF (rsSection.EOF) THEN
    Response.Write("<font color=##990000>You have successfully added the new Section </font>'<font color=#666666>" & varSection & "</font>'")
    Response.Write("<br><br>Click <a href=http://belbweb1/_demo/admin_contacts/contactsDatabaseAdmin.asp>here</a> to go back to the admin page...")

    set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    delSQL = "INSERT INTO tblsection_copy ([section]) VALUES ('" & varSection & "')"
    oCmd.CommandText = delSQL
    oCmd.Execute
    END IF
     
  14. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    :D

    well you could either use..

    Code:
    response.end
    
    to terminate the processing of the page..

    or use:

    Code:
    if  (expression)  then
    
    elseif(expression2) then
    
    elseif(expression3) then
    
    end if
    
    
    Either would work..

    Cheers
    Jon
     
  15. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Scratch that, got it working :)

    Changed this:

    IF (rsSection.EOF) THEN
    Response.Write("<font color=##990000>You have successfully added the new Section </font>'<font color=#666666>" & varSection & "</font>'")
    Response.Write("<br><br>Click <a href=http://belbweb1/_demo/admin_contacts/contactsDatabaseAdmin.asp>here</a> to go back to the admin page...")

    To this:

    IF (rsSection.EOF) AND varSection <> "" THEN
    Response.Write("<font color=##990000>You have successfully added the new Section </font>'<font color=#666666>" & varSection & "</font>'")
    Response.Write("<br><br>Click <a href=http://belbweb1/_demo/admin_contacts/contactsDatabaseAdmin.asp>here</a> to go back to the admin page...")
     
  16. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    hehehe.. cool :)
     
  17. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Always used Dreamweaver to do most of the work, you learn a lot more when you are forced to think it through...

    I learned the basic of ASP/SQL around eight months ago, and this is the first time I have had to apply it :)

    Need some books me thinks, got any recommendations?
     
  18. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    hummmmm.. books.. unfortunately i havent really used books for ASP or SQL... i mainly use online sites for these.

    http://www.devguru.com

    is good for ASP/VBScript

    Then for SQL i have sql2000 installed and i use the "books online" help which comes with that as it includes examples and searching which helps out lots tbh.

    I am very much self taught both sql and asp as when i started my job i didnt have much choice but to learn.. They tried to make me use dreamweaver etc, but tbh i really dont like those gui interfaces, i would prefer to use editplus2 :D

    But i agree, you need to get down and dirty with the code to really get a better understanding for it. :D

    Laters
    Jon
     
  19. rupbert

    rupbert What's a Dremel?

    Joined:
    28 Jul 2002
    Posts:
    800
    Likes Received:
    0
    Okay mate, I am trying to manually code Dreamweavers bloated version of the Edit Contact page...

    Here is what I have:

    <%
    Dim varContactID, contactName, designation, departmentID, sectionID, telephone, email, groups, surname, forename, oCmd, varSql

    varContactID = Request.QueryString("contactID") 'Passed through URL

    contactName = Request.QueryString("contactName")
    designation = Request.QueryString("designation")
    departmentID = Request.QueryString("department")
    sectionID = Request.QueryString("section")
    telephone = Request.QueryString("telephone")
    email = Request.QueryString("email")
    groups = Request.QueryString("group")
    surname = Request.QueryString("surname")
    forename = Request.QueryString("forename")
    %>

    Then I have this after numerous standard recordsets:

    <%Set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = MM_contactsDSN_STRING
    oCmd.CommandType = 1
    varSql = "UPDATE tblcontacts SET contactName = '" & contactName & "', forename = '" & forename & "', surname = '" & surname & "', departmentID = '" & department & "', sectionID = '" & sectionID & "', designation = '" & designation "', telephone = '" & telephone & "', email = '" & email & "', [group] = '" & groups & "' WHERE contactID = '" & varContactID & "'"
    oCmd.CommandText = varSql
    oCmd.Execute
    %>

    I keep getting 'Expected end of statement, line 312'. Line 312 is where varSql resides. Is there anything wrong with the sql statement mate? I am puzzled...
     
  20. LaughingJon

    LaughingJon What's a Dremel?

    Joined:
    12 Feb 2003
    Posts:
    282
    Likes Received:
    0
    Code:
    departmentID = Request.QueryString("department")
    
    should be
    Code:
    department = Request.QueryString("department")
    
    or change your SQL statement... :)

    Cheers
    Jon
     

Share This Page