Passing value through parametised query

Question

Hi there,

I've created a parametised query to get article/page content as follows;

<%
        article = cint(request("article"))

        Set conn = Server.CreateObject("ADODB.connection")
        conn.Open Application("database")
        Set cmd = Server.CreateObject("ADODB.command")
        With cmd
                .ActiveConnection = conn
                .CommandType = adCmdStoredProc
                .CommandText = "prc_getArticle"
                .Parameters.Append .CreateParameter("@ArticleID", adInteger, adParamInput,, article)
                Set rsArticle = .Execute
        End With
        While Not rsArticle.EOF

        article = rsArticle.fields("art_id")
        arttitle  = rsArticle.fields("art_title")
        artcontent  = rsArticle.fields("art_content")

        rsArticle.MoveNext
        Wend
        rsArticle.Close()
        Set rsArticle = Nothing
%>

The above query is placed in an include file, which requests the "article" from the page to get the article content, but i'm not sure how to then pass this through to the query.

I have the following line in the query;

.Parameters.Append .CreateParameter("@ArticleID", adInteger, adParamInput,, article)

Notice the 'article' at the end, but obviously this is not the correct way to reference the requested article from this line;

article = cint(request("article"))

In my actual page, I include a file with the above code and then also have the following in the page;

<%
article = 22
%>

<h1><%=arttitle%></h1>

Obviously at the moment, this is not grabbing the content to output into the page. Wondered if anyone might be able to help after my rather verbose question?

Thank you. :D

Answer 1

You need to create a Class to define the aquired output from the DB and a Function to create an appropriate instance of that class filled in with the article details.

Modify your include file to this:-

Public Class Article
    public ID
    public Title
    public Content
End Class

Function GetArticle(article)

    Dim conn: Set conn = Server.CreateObject("ADODB.connection") 
    conn.Open Application("database") 

    Dim cmd: Set cmd = Server.CreateObject("ADODB.command") 
    Dim rsArticle
    cmd.ActiveConnection = conn 
    cmd.CommandType = adCmdStoredProc 
    cmd.CommandText = "prc_getArticle" 
    cmd.Parameters.Append cmd.CreateParameter("@ArticleID", adInteger, adParamInput,, article) 
    Dim rsArticle: Set rsArticle = cmd.Execute 

    IF Not rsArticle.EOF Then
       Set GetArticle = new Article;

       GetArticle.ID = rsArticle.fields("art_id") 
       GetArticle.Title  = rsArticle.fields("art_title") 
       GetArticle.Content  = rsArticle.fields("art_content")  
    Else
       Set GetArticle = Nothing
    End If

    rsArticle.Close() 
End Function

With this include in place you can do this:-

 <%
    Dim article : Set article = GetArticle(22)
 %>

 <h1><%=article.Title%></h1>
 <div><%=article.Content%></div>

Answer 2

Just after the "Set rsArticle = Nothing" try this and let us know what happens:

    <h1><%= arttitle %></h1>
    <%= article %> - <%= artcontent %>