-
July 31st, 2013, 04:21 PM
#46
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.
-
July 31st, 2013, 04:44 PM
#47
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.
-
July 31st, 2013, 05:06 PM
#48
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
-
July 31st, 2013, 05:58 PM
#49
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.
-
July 31st, 2013, 06:45 PM
#50
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......
[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> </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...
-
July 31st, 2013, 09:57 PM
#51
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.
-
August 1st, 2013, 08:36 PM
#52
Re: VB Script will not remove modified data from Database
Originally Posted by DataMiser
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> </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 :-)
-
August 1st, 2013, 10:40 PM
#53
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.
-
August 2nd, 2013, 05:09 PM
#54
Re: VB Script will not remove modified data from Database
-
August 2nd, 2013, 06:12 PM
#55
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.
-
August 2nd, 2013, 08:18 PM
#56
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...
-
August 5th, 2013, 01:09 PM
#57
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.
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>
-
August 5th, 2013, 01:17 PM
#58
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.
-
August 5th, 2013, 06:31 PM
#59
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.
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> </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> </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> </td>
<td> </td>
</tr>
<tr valign="baseline">
<td height="24" align="right" valign="middle" nowrap><span class="textEvent">Category Link Colour :</span> </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> </td>
<td> <p>
</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
-
August 5th, 2013, 07:13 PM
#60
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|