If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Re: VB Script will not remove modified data from Database

Originally Posted by DataMiser

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.

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.

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

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.

Re: VB Script will not remove modified data from Database

Originally Posted by DataMiser

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......

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>

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.

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.

Re: VB Script will not remove modified data from Database

Originally Posted by DataMiser

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?

Re: VB Script will not remove modified data from Database

Originally Posted by DataMiser

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...

Re: VB Script will not remove modified data from Database

Originally Posted by davida1956

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.

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.

Re: VB Script will not remove modified data from Database

Originally Posted by DataMiser

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.

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