I tried to seek for help for this but couldn't get it. Hope to find it here. This is my code which need help:
sql = "SELECT ChristianID, CommonName FROM Christian ORDER BY CommonName "
rs_FollowUpBy = conn_FollowUpBy.Execute(SQL)
DO until rs_FollowUpBy.eof
sql = "SELECT ChristianID FROM FollowUp WHERE FriendID = " & rs("FriendID") & " " & _
" AND ChristianID = " & rs_FollowUpBy("ChristianID") & " "
set rs_FollowUpBy2 = conn_FollowUpBy.Execute(SQL)
if not rs.eof then
%><option value=<%=rs_FollowUpBy("ChristianID")%> selected><%=rs_FollowUpBy("commonname")%></option><%
else
%><option value=<%=rs_FollowUpBy("ChristianID")%>><%=rs_FollowUpBy("commonname")%></option><%
end if
rs_FollowUpBy.MoveNext
LOOP
what I'm trying to do it like this. I got 3 tables:
Christian { ChristianID, Common Name }
Record1 { 11, Steve Ngai }
Record2 { 32, Roger }
Friend { FriendID }
Record1 { 211 }
FollowUp { FollowUpID, ChristianID, FriendID }
Record1 { 2, 11, 211 }
Record2 { 3, 32, 211 }
In my friendadd.asp, I have many controls and one of them is listbox. This listbox will populate all the names (common name ) of Christians. I have no problem to do so in this add page. I also can save them into FollowUp table.
But, the problem now is friendedit.asp, where I have problem to do mapping (value from db map with listbox) for this listbox above.Not sure I understand completely what you are trying to do.
Where is the code with the <select> tag, you need to know the name of the <select> tag, and when you post the form, you can reference that in your other page.
Request.Form("#selectName#") will get you the value from the listbox, and then you can use that to generate the fields for editing.
~ mellamokbThanks for the reply, maybe you can take a lot of the screen shot first.
Add page - <!-- m --><a class="postlink" href="http://www.lifechangingstory.com/add.gif">http://www.lifechangingstory.com/add.gif</a><!-- m -->
Edit page - <!-- m --><a class="postlink" href="http://www.lifechangingstory.com/edit.gif">http://www.lifechangingstory.com/edit.gif</a><!-- m -->
The problem I'm facing is the edit page. The mapping of listbox of "Follow-Up By:".I think I kinda understand now. How are you storing the selected items in the listbox to the database?
Tell me if this is at all what you were thinking:
'sql = "SELECT ChristianID, CommonName FROM Christian ORDER BY CommonName "
sql = "SELECT ChristianID, CommonName, FollowUp.FriendID FROM Christian LEFT JOIN FollowUp ON FollowUp.ChristianID = Christian.ChristianID WHERE FollowUp.FriendID = " & rs("FriendID") & " ORDER BY CommonName"
rs_FollowUpBy = conn_FollowUpBy.Execute(SQL)
DO until rs_FollowUpBy.eof
'sql = "SELECT ChristianID FROM FollowUp WHERE FriendID = " & rs("FriendID") & " " & _
' " AND ChristianID = " & rs_FollowUpBy("ChristianID") & " "
'set rs_FollowUpBy2 = conn_FollowUpBy.Execute(SQL)
'if not rs.eof then
%><option value=<%=rs_FollowUpBy("ChristianID")%><% if len(rs_FollowUpBy("friendID")) > 0 Then %> selected <% end if %>><%=rs_FollowUpBy("commonname")%></option><%
'else
' %><option value=<%=rs_FollowUpBy("ChristianID")%>><%=rs_FollowUpBy("commonname")%></option><%
'end if
rs_FollowUpBy.MoveNext
LOOP
(changes in red)
I changed the query to pull in the FollowUpBy table with a left join, that way all of the Christians are pulled into the query, but whenever FriendID is null, therer is no corresponding record in FollowUpBy, so the <option> is not selected.
~ mellamokbIt looks like it works. I will give a try and let you know the result. Thanks.
Ya, for your question I use vbscript to split them and make a count using ubound and do an insert for each loop.mellamokb, I managed to see only selected common name and does not include other common name. But, I got the point, which is using LEFT JOIN. I will try to work on it, just some syntax thing.
Thanks.Hi,
I didn't see "Kok Meng" in any of the tables u posted online as examples. Is this what ur referring to when u say u see the common names that are selected but not the other ones? Is there another source of common names that i am missing?
~ mellamokbIt's ok. I didn't sync them as test data changes. Yah, using LEFT JOIN is correct way to get what I need. Thanks.ugly ASP. But that is because i use .net now. (off subject)
Please fix your code. if that is in the open someone can drop tables.if that is in the open someone can drop tables.
You mean someone can perform sql injection to me?You mean someone can perform sql injection to me?
Apparently the previous poster doesn't entirely understand SQL injection. SQL injection is only possible if you are placing code in the SQL based on user input or some other interface which the user can edit, such as the QueryString. You are insterting the variable rs("FriendID") into the SQL, but that comes from the database, not the user. If you pull information directly from the QueryString, the Form, or field on the page, you always want to replace single quote characters for strings, use IsNumeric for numbers, or use stored procedures with parameters to prevent SQL injection.
In the case of a number, use IsNumeric:
myValue = Request.Form("myField")
If IsNumeric(myValue) Then
mySQL = "SELECT * FROM myTable WHERE myField = " & myValue
Else
myErrorMessage = "Please enter a number!"
Response.Redirect ("myForm.asp?error=" & myErrorMessage)
End If
In the case of a string data type (varchar, nvarchar, char, etc.), replace single quotes with double single-quotes:
myValue = Request.Form("myField")
mySQL = "SELECT * FROM myTable WHERE myField = '" & Replace(myValue, "'", "''") & "'"
The safest is a stored procedure, because there is no ad-hoc SQL being produced, and the SQL engine will recognize bad parameters and generate error messages.
Hope this helps!
~ mellamokbI see. Okay, I will start with the above first exclude the stored procedure way. Even though mysql 5 has this feature already but I have no idea how to create one yet. Thanks.You can use dynamiclly generated scripts and then just use parameters, that also has protection against injection.How to go about it? Any website tutorial / sample as reference?
sql = "SELECT ChristianID, CommonName FROM Christian ORDER BY CommonName "
rs_FollowUpBy = conn_FollowUpBy.Execute(SQL)
DO until rs_FollowUpBy.eof
sql = "SELECT ChristianID FROM FollowUp WHERE FriendID = " & rs("FriendID") & " " & _
" AND ChristianID = " & rs_FollowUpBy("ChristianID") & " "
set rs_FollowUpBy2 = conn_FollowUpBy.Execute(SQL)
if not rs.eof then
%><option value=<%=rs_FollowUpBy("ChristianID")%> selected><%=rs_FollowUpBy("commonname")%></option><%
else
%><option value=<%=rs_FollowUpBy("ChristianID")%>><%=rs_FollowUpBy("commonname")%></option><%
end if
rs_FollowUpBy.MoveNext
LOOP
what I'm trying to do it like this. I got 3 tables:
Christian { ChristianID, Common Name }
Record1 { 11, Steve Ngai }
Record2 { 32, Roger }
Friend { FriendID }
Record1 { 211 }
FollowUp { FollowUpID, ChristianID, FriendID }
Record1 { 2, 11, 211 }
Record2 { 3, 32, 211 }
In my friendadd.asp, I have many controls and one of them is listbox. This listbox will populate all the names (common name ) of Christians. I have no problem to do so in this add page. I also can save them into FollowUp table.
But, the problem now is friendedit.asp, where I have problem to do mapping (value from db map with listbox) for this listbox above.Not sure I understand completely what you are trying to do.
Where is the code with the <select> tag, you need to know the name of the <select> tag, and when you post the form, you can reference that in your other page.
Request.Form("#selectName#") will get you the value from the listbox, and then you can use that to generate the fields for editing.
~ mellamokbThanks for the reply, maybe you can take a lot of the screen shot first.
Add page - <!-- m --><a class="postlink" href="http://www.lifechangingstory.com/add.gif">http://www.lifechangingstory.com/add.gif</a><!-- m -->
Edit page - <!-- m --><a class="postlink" href="http://www.lifechangingstory.com/edit.gif">http://www.lifechangingstory.com/edit.gif</a><!-- m -->
The problem I'm facing is the edit page. The mapping of listbox of "Follow-Up By:".I think I kinda understand now. How are you storing the selected items in the listbox to the database?
Tell me if this is at all what you were thinking:
'sql = "SELECT ChristianID, CommonName FROM Christian ORDER BY CommonName "
sql = "SELECT ChristianID, CommonName, FollowUp.FriendID FROM Christian LEFT JOIN FollowUp ON FollowUp.ChristianID = Christian.ChristianID WHERE FollowUp.FriendID = " & rs("FriendID") & " ORDER BY CommonName"
rs_FollowUpBy = conn_FollowUpBy.Execute(SQL)
DO until rs_FollowUpBy.eof
'sql = "SELECT ChristianID FROM FollowUp WHERE FriendID = " & rs("FriendID") & " " & _
' " AND ChristianID = " & rs_FollowUpBy("ChristianID") & " "
'set rs_FollowUpBy2 = conn_FollowUpBy.Execute(SQL)
'if not rs.eof then
%><option value=<%=rs_FollowUpBy("ChristianID")%><% if len(rs_FollowUpBy("friendID")) > 0 Then %> selected <% end if %>><%=rs_FollowUpBy("commonname")%></option><%
'else
' %><option value=<%=rs_FollowUpBy("ChristianID")%>><%=rs_FollowUpBy("commonname")%></option><%
'end if
rs_FollowUpBy.MoveNext
LOOP
(changes in red)
I changed the query to pull in the FollowUpBy table with a left join, that way all of the Christians are pulled into the query, but whenever FriendID is null, therer is no corresponding record in FollowUpBy, so the <option> is not selected.
~ mellamokbIt looks like it works. I will give a try and let you know the result. Thanks.
Ya, for your question I use vbscript to split them and make a count using ubound and do an insert for each loop.mellamokb, I managed to see only selected common name and does not include other common name. But, I got the point, which is using LEFT JOIN. I will try to work on it, just some syntax thing.
Thanks.Hi,
I didn't see "Kok Meng" in any of the tables u posted online as examples. Is this what ur referring to when u say u see the common names that are selected but not the other ones? Is there another source of common names that i am missing?
~ mellamokbIt's ok. I didn't sync them as test data changes. Yah, using LEFT JOIN is correct way to get what I need. Thanks.ugly ASP. But that is because i use .net now. (off subject)
Please fix your code. if that is in the open someone can drop tables.if that is in the open someone can drop tables.
You mean someone can perform sql injection to me?You mean someone can perform sql injection to me?
Apparently the previous poster doesn't entirely understand SQL injection. SQL injection is only possible if you are placing code in the SQL based on user input or some other interface which the user can edit, such as the QueryString. You are insterting the variable rs("FriendID") into the SQL, but that comes from the database, not the user. If you pull information directly from the QueryString, the Form, or field on the page, you always want to replace single quote characters for strings, use IsNumeric for numbers, or use stored procedures with parameters to prevent SQL injection.
In the case of a number, use IsNumeric:
myValue = Request.Form("myField")
If IsNumeric(myValue) Then
mySQL = "SELECT * FROM myTable WHERE myField = " & myValue
Else
myErrorMessage = "Please enter a number!"
Response.Redirect ("myForm.asp?error=" & myErrorMessage)
End If
In the case of a string data type (varchar, nvarchar, char, etc.), replace single quotes with double single-quotes:
myValue = Request.Form("myField")
mySQL = "SELECT * FROM myTable WHERE myField = '" & Replace(myValue, "'", "''") & "'"
The safest is a stored procedure, because there is no ad-hoc SQL being produced, and the SQL engine will recognize bad parameters and generate error messages.
Hope this helps!
~ mellamokbI see. Okay, I will start with the above first exclude the stored procedure way. Even though mysql 5 has this feature already but I have no idea how to create one yet. Thanks.You can use dynamiclly generated scripts and then just use parameters, that also has protection against injection.How to go about it? Any website tutorial / sample as reference?