Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringSQL Statements & Display

Author : JJenson
Subject : SQL Statements & Display
Posted : 17 September 2007, 21:02
Message :

Ok I am so close on this and I just don't know how to finish this off.
Lets start if you goto www.vmcplugins.com/forum/file_library.asp

If you see depending on if the file is free or paid based on a value in the database called F_ISFREE is shows in the bottom section or top section.
Now if you click on one of the links it goes into list all the files within that section. The problem is it shows both free and paid files. I need this to only display paid or free depending on the link they click.
Here is a .txt version of my file_library.asp:
www.vmcplugins.com/downloads/file_library.asp.txt

On lines 825 - 830 I have my strsql statement to try and display the list on the order to be paid or free using a querystring.
That is being passed on lines 415 and also 479.
I am stuck at this point on how to display this the way I need to can anyone help me from here? I would be greatly appreciated.
Thanks

<

Reply Author: Shaggy
Replied on: 18 September 2007, 04:39
Message:

Originally posted by JJenson goto www.vmcplugins.com/forum/file_library.asp

Got a test account for us?
<

Reply Author: JJenson
Replied on: 18 September 2007, 08:43
Message:

Oh yeah sorry

user: snitz
pass: test<

Reply Author: cripto9t
Replied on: 18 September 2007, 09:09
Message:

cLng(Request.QueryString("IsFree")) Have you tried that?<

Reply Author: JJenson
Replied on: 18 September 2007, 09:15
Message:

Ok that did the same as the other. This is how I made the line look.
strSql = strSql & "WHERE F_ISFREE = " & cLng(Request.QueryString("IsFree"))

Also you can view it now without being logged in. SOrry about that didn't even know you had to be logged in to see it.

<

Reply Author: JJenson
Replied on: 18 September 2007, 13:40
Message:

Ok trying to go a different route here I tried passing a variable like so:

Code:


Dim IsFree : IsFree = Request.QueryString("IsFree")

Then putting this in my strsql statement.

Code:


strSql = "SELECT FC.F_CAT, FC.ID, COUNT(FF.ID) AS COUNTOFID "
   strSql = strSql & "FROM " & strTablePrefix & "FILECAT FC "
   strSql = strSql & "LEFT JOIN " & strTablePrefix & "FILEMANAGER FF "
   strSql = strSql & "ON FC.ID = FF.F_CAT "
   strSql = "WHERE F_ISFREE = " & IsFree & ""
   strSql = strSql & " GROUP BY FC.F_CAT, FC.ID "
   Set rsX2 = my_Conn.Execute (strSql)

But this did not work either what am I missing cause I am lost

as usual<

Reply Author: modifichicci
Replied on: 18 September 2007, 13:59
Message:

what kind of field is F_ISFREE? int or charvar?<

Reply Author: JJenson
Replied on: 18 September 2007, 14:05
Message:

int

The value is 0=Paid and 1=Free just so know what the values are in the column<

Reply Author: modifichicci
Replied on: 18 September 2007, 17:12
Message:

mmmm not FF.F_ISFREE in the query?<

Reply Author: JJenson
Replied on: 18 September 2007, 21:33
Message:

just tried that in the where claus and it didn't work.<

Reply Author: Shaggy
Replied on: 19 September 2007, 05:16
Message:

Have you tried a response.write on the IsFree & strSql variables to see if they are both set correctly?
<

Reply Author: JJenson
Replied on: 19 September 2007, 08:29
Message:

I did it on the strsql and that shows it correct but I have not done it on the IsFree I will give that a try.<

Reply Author: JJenson
Replied on: 19 September 2007, 08:39
Message:

Ok I did a response.write on the strsql I just want to make sure I did it correct.
Response.Write (IsFree)
Response.End

This showed nothing at all. Did I do this correct?<

Reply Author: gary b
Replied on: 19 September 2007, 09:36
Message:

Line 827-828

strSql = strSql & "INNER JOIN " & strTablePrefix & "FILEMANAGER FF "
strSql = strSql & "ON FC.ID = FF.F_CAT "

This correct?<

Reply Author: JJenson
Replied on: 19 September 2007, 09:49
Message:

I believe so this is basically how the original works but just adding in a where clause to make it only for the certain field of F_ISFREE. So everything else is basically the same with the where clause changing is all. Does that make sense?
<

Reply Author: JJenson
Replied on: 19 September 2007, 10:20
Message:

Ok I got the right response.write statement and it comes back with the correct values.

When I click the free ones it displays a 1
When I click the paid ones it displays a 0

Not sure where to go from here though?<

Reply Author: JJenson
Replied on: 19 September 2007, 11:32
Message:

Ok so I believe the strsql is good I will try and see if I can track down the code that displays it and see if that needs tweaking. I also response.write the strsql and ran the query that it outputed and that displayed correct info as well. Must be further down then.
Thanks will post back I am sure when I can't find it

<

Reply Author: JJenson
Replied on: 19 September 2007, 11:57
Message:

[edited]remove wrong information.[/edit]
OK I need t figure out how to get the where claus from the above statement into the below statment and I am not sure how to do it.

Code:


   strSql = "SELECT FC.F_CAT, FC.ID, COUNT(FF.ID) AS COUNTOFID "
   strSql = strSql & "FROM " & strTablePrefix & "FILECAT FC "
   strSql = strSql & "LEFT JOIN " & strTablePrefix & "FILEMANAGER FF "
   strSql = strSql & "ON FC.ID = FF.F_CAT "
   strSql = strSql & "WHERE F_ISFREE = " & Request.QueryString("IsFree")
   strSql = strSql & " GROUP BY FC.F_CAT, FC.ID "
   Set rsX2 = my_Conn.Execute (strSql)	
   
   strSql = "SELECT *"
   strSql = strSql & " FROM " & strTablePrefix & "FILEMANAGER "
   strSql = strSql & "WHERE F_CAT = " & showmethecat & " "
   strSql = strSql & "ORDER BY " & Sortme
   Set rs = Server.CreateObject("ADODB.Recordset")

So the rsX2 needs to be implemented into the rs statement anyone able to give me an idea on how to do this?
Thakns<

Reply Author: JJenson
Replied on: 19 September 2007, 13:41
Message:

Ok this is what I got to change the where claus can someone tell me where I went wrong?

Code:


strSql = strSql & "WHERE F_CAT = " & showmethecat & " AND F_ISFREE = " & Request.QueryString("IsFree")

<

Reply Author: ruirib
Replied on: 19 September 2007, 13:52
Message:

Are you getting errors? If so, Response.Write the sql statement to see where the error is.
You should not use values from the querystring without sanitizing them.<

Reply Author: JJenson
Replied on: 19 September 2007, 13:55
Message:

Alright I found out my problem was that I didn't remove one quote also I needed to put a space a the beggining of the group by claus. Its all working now the way it should

www.vmcplugins.com/forum/file_library.asp

They get grouped the whole way down as they should

<

Reply Author: Shaggy
Replied on: 20 September 2007, 04:40
Message:

Originally posted by JJenson Alright I found out my problem was that I didn't remove one quote also I needed to put a space a the beggining of the group by claus.

It's always the simple things, isn't it?!

<

Reply Author: JJenson
Replied on: 20 September 2007, 08:50
Message:

Yeah especially for me right now I am trying to grow and get better and better at all this but these things keep coming up that i overlook. I am hoping the more I do this I can just spot these things. We will see.<

Reply Author: Shaggy
Replied on: 20 September 2007, 09:30
Message:

Originally posted by JJenson I am hoping the more I do this I can just spot these things.

Believe me, the better you get the less easily you spot simple mistakes like this!
<

Reply Author: JJenson
Replied on: 20 September 2007, 09:51
Message:

Don't scar me like that cause I am missing these dumb things all over the place

<

SQL Statements & Display

Topic

Replies ...