[RESOLVED] VB Script will not remove modified data from Database - Page 4
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 4 of 8 FirstFirst 1234567 ... LastLast
Results 46 to 60 of 114

Thread: [RESOLVED] VB Script will not remove modified data from Database

  1. #46
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    I had not saw any more questions. Am not sure what the question is now. Assuming that the value you are wanting to display is in the field called ColorName then what i showed you should work see post #35 and #37

    Hi DM.. I wondered why I hadn't heard from you as you always answer very quickly :-) I did try what you suggested but it gave m no end of problems. In one instance it removed my ability to add new categories and I couldn't edit the ones that were there. You suggested that providing the db was designed correctly that I should e able to extract the details I needed from the LinkColor table. My understanding is that 'LinkColor' contains only all of the current combinations of colors available. It is when you create a new category that this information is displayed. Once you create this information it is stored in the 'Type' table as three values....'Id' ..ColorName and ColorCode. I want to extract the color name associated with the new category and display it alongside the message advising me that the new category has been created. The code I showed in my last post is where I think it should be but I am not sure how to access it.

  2. #47
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    I am confused you have to get the value from where the value is stored. If it is not in the LinkColor table then you can not get it from there. If the type table has the code and the value you want to display then you may need to do a lookup and get it from there. This is very easy to do Just a simple Select where statement

    "Select ColorName From [Type] Where ColorCode='" & VarColorCode &"'"

    Assuming of course that colorcode is a text field [would not need the ' characters if it is a numeric field type
    Also assumign that VarColorCode is the name of the var holding the color code in question.
    Always use [code][/code] tags when posting code.

  3. #48
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Wow... fast .... caught me out :-) I tried that but I don't think it works. The value in the Type table 'TypeColor' is a hex value associated with a given value held in LinkColor table 'ColorCode' which has the text value in the ColorName field. So when creating a new category there is a drop down table with all of the available colours I can choose from. So I see the word 'BLUE' and it is displayed in the colour blue. I don't know how to capture that information at the point where this information is passed onto the next routine. Does this make sense ??
    Last edited by davida1956; July 31st, 2013 at 05:36 PM. Reason: Info Update

  4. #49
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    No not really. You said

    Once you create this information it is stored in the 'Type' table as three values....'Id' ..ColorName and ColorCode.
    If these three fields are there then you have all you need.
    Also you keep talking about being stored as Hex value but that is not a data type. either it is stored as a numeric type or a string/text type. The SQL string will be different for one type from the other.

    if I understand you correctly at the point where you want to display the color name you have the colorcode and also there is a table that holds both the color code and the colorname that is associated to that code if this is true then it is as I said very simple. You just do a select on the table like I showed you and the resulting RS should have 1 record with just one field in it and that field will be the colorname you want.

    It doesn't really matter what you have on your web page, clouds the issue more than it helps. If you tried the select method and had an issue then you should show us what you tried as you must have did something wrong.

    The other options are to pass the value to the script either using a hidden field or as part of the querystring or setting it in a Session var.

    Of the various options the Select statement is probably the most simple.
    Always use [code][/code] tags when posting code.

  5. #50
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    No not really. You said


    If these three fields are there then you have all you need.
    Also you keep talking about being stored as Hex value but that is not a data type. either it is stored as a numeric type or a string/text type. The SQL string will be different for one type from the other.

    if I understand you correctly at the point where you want to display the color name you have the colorcode and also there is a table that holds both the color code and the colorname that is associated to that code if this is true then it is as I said very simple. You just do a select on the table like I showed you and the resulting RS should have 1 record with just one field in it and that field will be the colorname you want.

    It doesn't really matter what you have on your web page, clouds the issue more than it helps. If you tried the select method and had an issue then you should show us what you tried as you must have did something wrong.

    The other options are to pass the value to the script either using a hidden field or as part of the querystring or setting it in a Session var.

    Of the various options the Select statement is probably the most simple.

    Hi and thanks again.... That has totally confused me other than to say the value I to display is obviously textual. You give many options but I must be having a bad day because I can't figure things out. I know where the code is and where I want display the new information but I don't know how to perform the select statement. Is this snip of code where I should apply the select statement ? I have shown where it should be... just after the first message advising that the new category has been added to the database....... WITH THE ASSOCIATED COLOR......

    [code]

    Dim Msg
    Dim CategoryRS
    Set CategoryRS = Server.CreateObject("ADODB.Recordset")
    CategoryRS.Open "SELECT * FROM Type", Connect, 2, 3
    If not Trim(Request("TypeName"))=""Then
    CategoryRS.Addnew
    CategoryRS("TypeName").Value=Request("TypeName")
    CategoryRS("TypeColor").Value=Request("LinkColor")
    CategoryRS.Update
    CategoryRS.Close


    Msg="<p class=""textEvent"">A New Category---> " & Request("TypeName") & " <--- Has Now Been Added To The Database." ......WITH THE ASSOCIATED COLOUR 'ColorName'
    Else
    Msg="<p class=""textEvent"">You Must Enter A Valid Category Name ! ... This Field Cannot Be Left Blank !</p>"
    End If
    %>
    <table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%">
    <tr>

    <td align="left" valign="top">
    <table width="100%" border="0" cellspacing="2" cellpadding="2">
    <tr>
    <td align="center" valign="middle"><p>&nbsp;</p>
    <% Response.Write(Msg) %>

    </p>

    [code/]

    where do I put the select statement and how does it access the TYPE in my db... A lot confused...

  6. #51
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    I misread something in your previous post as to where the data was stored but still not an issue just a slightly different select statement
    Code:
    Dim Msg
    Dim CategoryRS
    Set CategoryRS = Server.CreateObject("ADODB.Recordset")
    CategoryRS.Open "SELECT * FROM Type", Connect, 2, 3
    If not Trim(Request("TypeName"))=""Then
    CategoryRS.Addnew
    CategoryRS("TypeName").Value=Request("TypeName")
    CategoryRS("TypeColor").Value=Request("LinkColor")
    CategoryRS.Update
    CategoryRS.Close
    Dim ColorName
    rs.open "select ColorName from LinkColor where colorcode='" & Request("LinkColor") &"'" , Connect, 2, 3
    If not rs.eof then
        ColorName=rs(0).Value
    else
        ColorName="Not Found"
    End If
    rs.close
    Then of course you would be using this colorname var in your output to display the actual color name on down in the script.

    I am assuming that the hex value you keep referring to is stored as text. Also assuming that linkcolor is the name of the form field that holds this hex value.
    Last edited by DataMiser; July 31st, 2013 at 10:01 PM.
    Always use [code][/code] tags when posting code.

  7. #52
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    I misread something in your previous post as to where the data was stored but still not an issue just a slightly different select statement
    Code:
    Dim Msg
    Dim CategoryRS
    Set CategoryRS = Server.CreateObject("ADODB.Recordset")
    CategoryRS.Open "SELECT * FROM Type", Connect, 2, 3
    If not Trim(Request("TypeName"))=""Then
    CategoryRS.Addnew
    CategoryRS("TypeName").Value=Request("TypeName")
    CategoryRS("TypeColor").Value=Request("LinkColor")
    CategoryRS.Update
    CategoryRS.Close
    Dim ColorName
    rs.open "select ColorName from LinkColor where colorcode='" & Request("LinkColor") &"'" , Connect, 2, 3
    If not rs.eof then
        ColorName=rs(0).Value
    else
        ColorName="Not Found"
    End If
    rs.close
    Then of course you would be using this colorname var in your output to display the actual color name on down in the script.

    I am assuming that the hex value you keep referring to is stored as text. Also assuming that linkcolor is the name of the form field that holds this hex value.


    Hi DM,

    Thanks a lot for giving me the correct syntax to access that section of the database and for demonstrating how it should be done. I noticed that you left out some details but I think you did it intentionally to make me think about things. It worked and the proverbial penny dropped though it does lead me to ask another related question. Still, here is the completed code for anybody else following my ‘tutorial lessons’ from a… very patient teacher!!

    Code:
    <%
    ' *** Insert new category, name and link color to the database and display message. If the category name is left blank then flag up with an error message:
    
    Dim Msg
    Dim CategoryRS
    Set CategoryRS = Server.CreateObject("ADODB.Recordset")
    	CategoryRS.Open "SELECT * FROM Type", Connect, 2, 3
    If not Trim(Request("TypeName"))=""Then
        CategoryRS.Addnew
        CategoryRS("TypeName").Value=Request("TypeName")
    	CategoryRS("TypeColor").Value=Request("LinkColor")
    	CategoryRS.Update
    	CategoryRS.Close
    Dim ColorName
    CategoryRS.open "select ColorName from LinkColor where colorcode='" & Request("LinkColor") &"'" , Connect, 2, 3
    If not CategoryRS.eof then
        ColorName=CategoryRS(0).Value
    else
        ColorName="The Colour You have Selected Has Not Been Found Please Re-Select"
    End If
    CategoryRS.close	 	
    	
    Msg="<p class=""textEvent"">A new category---> " &  Request("TypeName") & " <--- has been added to the database. The colour associated with this category is --->  "
    Else
        Msg="<p class=""textEvent"">You Must Enter A Valid Category Name ! ... This Field Cannot Be Left Blank !</p>" 
    End If
    %>     
                  <table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%">
                    <tr> 
                      
                      <td align="left" valign="top"> 
                        <table width="100%" border="0" cellspacing="2" cellpadding="2">
                          <tr> 
                            <td align="center" valign="middle"><p>&nbsp;</p>
                              <% Response.Write(Msg) %>
                              <% Response.Write(ColorName) %>
                                  </p>
    When I want to modify an existing category there are only two fields to worry about and these are CategoryName and CategoryColor. This task is performed in the category_edit.asp script and it works well. Given that I already have the record details on the screen then would it be possible to take the new values as they are written to the database and store them in a variable called ‘change’ for instance. Could I then response.redirect the code to a new screen that just displays or confirms the ‘change’ details?? I think this is the code that does the job… but it currently sends me back to the main page and not to a page that I would call category_change.asp which I have bodged together from other code.

    Code:
    <%
    ' If the form has been submitted (Flag = True), and the Category Name field isn't empty, update the record.
    Else
    
    Dim TypeRS
    	Query = "SELECT * FROM Type WHERE TypeId=" & Request("TypeId")
    	Set TypeRS = Server.CreateObject("ADODB.Recordset")
    	TypeRS.Open Query, Connect, 2, 3
    	TypeRS("TypeName") = Request("TypeName")
    	TypeRS("TypeColor") = Request("LinkColor")
    	TypeRS.Update
    	TypeRS.Close()
    	Response.Redirect("category.asp") **I would change this to (category_change.asp)
    End If
    						%>

    Regards and thanks again :-)

  8. #53
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    Well if you call another script it will not have access to that data unless you either pass the data to the script, use a session var or do a lookup in the other script.
    Remember these scripts are like totally separate things and do not have access to any part of the other scripts.

    Another thing you should be aware of is using the recordset object to make inserts and possibly updates can lead to issues in a multi user environment. I have ran into this before even on a lan where the .addnew failed from time to time due to another user doing an add at the same time. I changed to using SQL Insert and Update queries and had no issues.
    Always use [code][/code] tags when posting code.

  9. #54
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    Well if you call another script it will not have access to that data unless you either pass the data to the script, use a session var or do a lookup in the other script.
    Remember these scripts are like totally separate things and do not have access to any part of the other scripts.

    Another thing you should be aware of is using the recordset object to make inserts and possibly updates can lead to issues in a multi user environment. I have ran into this before even on a lan where the .addnew failed from time to time due to another user doing an add at the same time. I changed to using SQL Insert and Update queries and had no issues.

    Hi DM and thanks again for your reply, as always packed with information This software is for our own use in terms of data input. It can however be accessed by multiple users for the purpose of viewing details so there will only be 1 administrator using the system at any given time. I think perhaps the simplest solution would be to display confirmation of the changes within that script rather than complicate the function by passing variable backwards and forwards. What is the best way to do this and is it possible to clear the screen before displaying this information ??

    Note fields within the software indicate where this function is performed but it is a little confusing at what point the information should be displayed, I presume it is before the RS is closed going by what I have learnt so far. I only have one other issue that I need to resolve but that will be another thread and I guess it will be the most difficult one I am going to try and tackle. At least I can now determine what is doing what so can hopefully home in on the part of that script that I guess I will still need help with I will try to store information from the code into two variable and then use a response.write function to display the information which I guess is the correct thing to do ?? I don't however have a clue as to whether or not the screen can be cleared of information prior to confirmation of the changes. In a DOS batch file (yes I can remember that far back !!LOL) I would simply have used have used the CLS command... any advice my friend?

  10. #55
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    The short answer is no you can not clear the screen. You need to redirect to a new script to do that.
    Always use [code][/code] tags when posting code.

  11. #56
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    The short answer is no you can not clear the screen. You need to redirect to a new script to do that.


    Hmmm.... then I guess the optimum solution would be to create a session var and call it from the new script which could in effect be a modified copy of the category_add.asp file which I can create and call category_change.asp. I will try to implement the advice you gave me earlier in our discussions. Thanks again and watch this space...

  12. #57
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by davida1956 View Post
    Hmmm.... then I guess the optimum solution would be to create a session var and call it from the new script which could in effect be a modified copy of the category_add.asp file which I can create and call category_change.asp. I will try to implement the advice you gave me earlier in our discussions. Thanks again and watch this space...

    Hi DM…. well… I tried to implement what you showed me a while back to create a session variable but with no joy. I’ve upgraded to Windows 8 so was offline for a bit longer than I expected. I’m still in the situation where I am trying to create two session variables called ColChange and CatChange. I’m inserting them just before the TypeRS.Update section of code. The function itself still updates the DB with correct information but It does not however seem to pass the variable into the category_change.asp script that I created by modifying the category_add.asp script.

    I have a feeling that IType comes into this equation somewhere but not sure why it might. Maybe I am looking in totally the wrong area… I don’t know… Do you have any advice please my friend… also, is it possible to have more than 1 thread open at a time or is it not a wise thing to do?? …. Hmmm I can’t cope with one thread so that answers the question for someone with my basic skills I think…LOL…Here the code I am looking at with a bit of it commented out.

    Code:
    <input type="hidden" name="Flag" value="True">
                              <input type="hidden" name="TypeId" value="<%=Request("iType")%>">
                              </form>
                              <%
    ' …If the form has been submitted (Flag = True), and the Category Name field isn't empty, update the record.
    '….Sample
    '……If (TypeRS("TypeName") = LinkColor("ColorCode")) Then ………Session("ColChange")=LinkColor("ColorName") similar code for  the Category Change.
    Else
    
    Dim TypeRS
    Dim ColChange
    Dim CatChange
    	Query = "SELECT * FROM Type WHERE TypeId=" & Request("TypeId")
    	Set TypeRS = Server.CreateObject("ADODB.Recordset")
    	TypeRS.Open Query, Connect, 2, 3
    	TypeRS("TypeName") = Request("TypeName")
    	TypeRS("TypeColor") = Request("LinkColor")
    	TypeRS.Update
    	TypeRS.Close()
    	Response.Redirect("category_change.asp")
    End If
    						%></td>


  13. #58
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    You should use a new thread for a new question. You can have as many as you need.

    As for what you posted there is no code there that uses session vars so I can't tell you what you did wrong. I would need to see the code you are using in both places and you would have to tell me what result you are seeing.
    Always use [code][/code] tags when posting code.

  14. #59
    Join Date
    Jun 2013
    Posts
    102

    Re: VB Script will not remove modified data from Database

    Quote Originally Posted by DataMiser View Post
    You should use a new thread for a new question. You can have as many as you need.

    As for what you posted there is no code there that uses session vars so I can't tell you what you did wrong. I would need to see the code you are using in both places and you would have to tell me what result you are seeing.
    I’m sorry…. I’m totally lost ….. Nothing I have done seems to work but then it doesn’t crash out and all I see is the actual text of the message but without the variables being displayed.so it seems I am doing nothing in effect. I have put the guts of the code, less all the frills below. I now think that this code uses to queries in separate parts of the code to do the job of updating the DB. This being the case then I simply don’t know how to handle that so am confused again. Here’s the code, some of it has no functionality within the product as far as I can see.

    Code:
    <%
    ' If the Category Name field is empty, and the form has been submitted 			
    Dim Flag, Query
    If trim(Request("TypeName"))="" Then
    	If Flag Then
    		Response.Write "<p class='subtitle'>You must enter a category name.</p>"
    	' If Flag isn't true, show the edit form 
    	Else
    	Dim CategoryRS
    	Set CategoryRS = Server.CreateObject("ADODB.Recordset")
    		CategoryRS.Open "SELECT * FROM Type WHERE TypeId =" & Request("iType"), Connect, 2, 3
    	End If
    %>
                  <table width="100%" border="0" cellspacing="0" cellpadding="0" height="100%">
                    <tr>      
                      <td align="left" valign="top"> 
                        <table width="100%" border="0" cellspacing="2" cellpadding="2">
                          <tr> 
                            <td height="205" align="left" valign="middle"><p>&nbsp;</p><form method="POST" action="<%= Request.ServerVariables("URL") %>" name="ADD">
                              <table align="center" class="textBold">
                                <tr valign="baseline"> 
                                  <td width="170" height="21" align="right" valign="middle" nowrap><span class="textEvent">Edit Category Name :</span>&nbsp;&nbsp;</td>
                                  <td width="313"> <input name="TypeName" type="text" class="form" value="<%=CategoryRS("TypeName")%>" size="50" maxlength="50"> 
                                    </td>
                                  </tr>
                                <tr valign="baseline">
                                  <td height="42" align="right" valign="middle" nowrap>&nbsp;</td>
                                  <td>&nbsp;</td>
                                </tr>
                                <tr valign="baseline"> 
                                  <td height="24" align="right" valign="middle" nowrap><span class="textEvent">Category Link Colour :</span>&nbsp;&nbsp;</td>
                                  <td> <select name="LinkColor">
                                    <%
    Dim LinkColorRS
    Set LinkColorRS = Server.CreateObject("ADODB.Recordset")
    	LinkColorRS.Open "SELECT *  FROM LinkColor", Connect, 3, 3
    While (Not LinkColorRS.EOF)
        Response.Write "<option STYLE='color: " & LinkColorRS("ColorCode") & " !important' value='" & LinkColorRS("ColorCode") & "'"
    If (CategoryRS("TypeColor") = LinkColorRS("ColorCode")) Then Response.Write "selected"
    	Response.Write">" & LinkColorRS("ColorName") & "</option>"
        LinkColorRS.MoveNext
    Wend
    	LinkColorRS.Close()
    	CategoryRS.Close()
    								%>
                                    </select> 
                                    </td>
                                  </tr>
                                <tr valign="baseline"> 
                                  <td height="24" align="right" nowrap>&nbsp;</td>
                                  <td> <p>&nbsp;
                                    </p>
                                    <p>
                                      <input name="Submit" type="submit" class="form" id="Submit" value="Submit">
                                    </p></td>
                                  </tr>
                                </table>
                              <input type="hidden" name="Flag" value="True">
                              <input type="hidden" name="TypeId" value="<%=Request("iType")%>">
                              </form>
                              <%
    ' If the form has been submitted (Flag = True), and the Category Name field isn't empty, update the record.
    'Sample
    'If (TypeRS("TypeName") = ("TypeName")) Then Session("TypeName")=("Catchange")
    Else
    Dim TypeRS
    Dim ColChange
    Dim CatChange
    	Query = "SELECT * FROM Type WHERE TypeId=" & Request("TypeId")
    	Set TypeRS = Server.CreateObject("ADODB.Recordset")
    	TypeRS.Open Query, Connect, 2, 3
    	TypeRS("TypeName") = Request("TypeName")
    	TypeRS("TypeColor") = Request("LinkColor")
    	TypeRS.Update
    	TypeRS.Close()
    	Response.Redirect("category_change.asp")
    End If
    						%>
    This is the code I have put into an almost blank script which just contains the menu links and a table in which I call for the two session variables. There are no DB queries or updates at all.

    Code:
    <p class="textEvent">The category name has now been changed to " 
                              <% Response.Write(Session("CatChange"))%> 
                            " with the associated colour of </p>
                            <% Response.write(Session("ColChange"))%>
    Thanks again for your patience with me DM. things are going wrong here at home now and the darn fridge freezer motor has just blown up !! all fun n games huh

  15. #60
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: VB Script will not remove modified data from Database

    It looks like you are not setting any value for either of the session vars you are trying to display

    In the first part of the script there is one line that would set one of the session vars if the If test succeeds but that line is commented out so nothing will be assigned and your result will be blank
    Always use [code][/code] tags when posting code.

Page 4 of 8 FirstFirst 1234567 ... LastLast

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center