creating a querystring

RPG SEARCH · April 11th, 2005, 03:21 AM

Hi there,

first time at attempting a querystring in the my page url. i have looked around and cant quite grasp it.

I call the database:
<code>'gets the portfolio
        Function getPortfolio() As System.Data.IDataReader
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwr"& _
    "oot\Davejenkins\admin\sitedb.mdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

            Dim queryString As String = "SELECT [tblDesigns].[DesignId], [tblDesigns].[SiteUrl], [tblDesigns].[SiteShortDe"& _
    "sc], [tblDesigns].[SiteThumb], [tblDesigns].[AltTag] FROM [tblDesigns]"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection

            dbConnection.Open
            Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavio r.CloseConnection)

            Return dataReader
        End Function
</code>

And then have a repeater here:
<code>
<asp:Repeater id="RptPortfolio" runat="server">
                        <ItemTemplate>
                            <img align="right" src='<%# Container.DataItem("SiteThumb") %>' alt='<%# Container.DataItem("AltTag") %>'>
                            Name: <a href="designs.aspx?DesignId='<%# Container.DataItem("DesignId") %>'"><%# Container.DataItem("SiteUrl") %></a>
                            <br />
                            Description: <%# Container.DataItem("SiteShortDesc") %> [<a href="designs.aspx?DesignId="& recordset=("DesignId") &">More...</a>]
                        </ItemTemplate>
                    </asp:Repeater>
</code>
The bit in red is where it goes wrong presently nothing in DesignId If it make it " " i still dont get anything.

I have not done the next page yet but does this it seem fine to put a select info from the Database WHERE Request.QueryString= 'DesignId'?

*Left scrathing my head like a chimp here:D*

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

RPG SEARCH · April 12th, 2005, 04:35 AM

Ok i have it now passing the the Design Id number but i dont know why its not reading. the error i get is .... BC30455: Argument not specified for parameter 'designId' of 'Public Function getPortfolio(designId As Integer) As System.Data.DataSet'.

<code>
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

     Sub Page_Page()
        If Not Page.IsPostBack
            viewSite.DataSource() = getPortfolio()
            viewSite.DataBind()
        End If
     End Sub

       'get the portfolio = design id that is passed in the url.
        Function getPortfolio(ByVal designId As Integer) As System.Data.DataSet
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\vhosts\davejenkins.co.uk\httpdoc s\admin\sitedb.mdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

            Dim queryString As String = "SELECT [tblDesigns].[DesignId], [tblDesigns].[SiteUrl], [tblDesigns].[SiteOwner],"& _
    " [tblDesigns].[SiteTech], [tblDesigns].[SiteLongDesc], [tblDesigns].[SiteCreated"& _
    "], [tblDesigns].[SiteImage], [tblDesigns].[AltTag] FROM [tblDesigns] WHERE "Request.Querystring("DesignId")" = designId)"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection

            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)

            Return dataSet
        End Function
</code>

Any ideas. I have had a play but cant get anywhere!

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

jbenson001 · April 12th, 2005, 12:58 PM

I only see one call to your function in this line of code:

viewSite.DataSource() = getPortfolio()

However, you function is defined this way:
Function getPortfolio(ByVal designId As Integer) As System.Data.DataSet

You need to call the function with and integer

RPG SEARCH · April 13th, 2005, 01:29 PM

Hi there,

I dont know if i understood you correctly. I have tried severl different things to try to do what you said. Have got this now...

Code:

get the portfolio = design id that is passed in the url.
    Function getPortfolio(ByVal designId As Integer) As System.Data.DataSet
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\vhosts\davejenkins.co.uk\httpdocs\admin\sitedb.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "SELECT [tblDesigns].[DesignId], [tblDesigns].[SiteUrl], [tblDesigns].[SiteOwner],"& _
" [tblDesigns].[SiteTech], [tblDesigns].[SiteLongDesc], [tblDesigns].[SiteCreated"& _
"], [tblDesigns].[SiteImage], [tblDesigns].[AltTag] FROM [tblDesigns] WHERE Request.Querystring = designId)" [u]<---- Is that bit correct? I think it is</u>
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)

        Return dataSet
    End Function

Sub Page_Page()
    If Not Page.IsPostBack
        viewSite.DataSource() = getPortfolio(ByVal designId As Integer) As System.Data.DataSet
        viewSite.DataBind()
    End If
 End Sub

cheers for help

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

planoie · April 13th, 2005, 09:38 PM

The WHERE clause was incorrect. Also, using a StringBuilder is a better and more efficient way of constucting long strings.

Dim queryString As New System.Text.StringBuilder
queryString.Append("SELECT [tblDesigns].[DesignId], [tblDesigns].[SiteUrl], [tblDesigns].[SiteOwner]")
queryString.Append(", [tblDesigns].[SiteTech], [tblDesigns].[SiteLongDesc], [tblDesigns].[SiteCreated]")
queryString.Append(", [tblDesigns].[SiteImage], [tblDesigns].[AltTag]")
queryString.AppendFormat(" FROM [tblDesigns] WHERE designId={0}", Request.Querystring("designId"))
...
dbCommand.CommandText = queryString.ToString

-Peter

RPG SEARCH · April 14th, 2005, 09:34 AM

Hi Peter,

Its just throwing the following error at me now. I have tried using ("") and a couple of
other things aswell but nothing seems to do the trick. I thought that this was going to
make life simple but its becoming more confusing!

Compiler Error Message: BC30201: Expression expected.

Sub Page_Page()
Line 6: If Not Page.IsPostBack
Line 7: viewSite.DataSource() = getPortfolio(ByVal designId As Integer) As System.Data.DataSet
Line 8: viewSite.DataBind()
Line 9: End If

C:\Inetpub\vhosts\davejenkins.co.uk\httpdocs\desig n\designs.aspx(7) : error BC30201: Expression expected.

viewSite.DataSource() = getPortfolio([u]ByVal</u> designId As Integer) As System.Data.DataSet

Thanks for the help. Its really appreciated.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

planoie · April 14th, 2005, 11:39 AM

viewSite.DataSource() = getPortfolio(ByVal designId As Integer) As System.Data.DataSet

should be

viewSite.DataSource() = getPortfolio(CType(Request.QueryString("designId") , Integer))

-Peter

RPG SEARCH · April 15th, 2005, 09:26 AM

Hi there,

MMM. The page now displays an end expression is expected with the above method. Why? I thought that was a complete expression.
I tried taking away the As Sytem.Data.DataSet which displays the page without errors but nothing is shown in the Repeaters
Container.DataItem field, yet web matrix is showing that it is bound.

Does anyone know of a better way to pick up the Id number, pull the relevent data for that id number and display it?
Or just the solution to this as its really bugging me. Everyone has this on their sites... i thought it would be easy
to do.

thanks for the help.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

planoie · April 15th, 2005, 10:08 AM

David,

My last post was incorrect, I fixed it.

If you still can't get this working, please repost the relevant parts of the code again.

-Peter

RPG SEARCH · April 15th, 2005, 10:25 AM

This is the the code part of the page...

Code:

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

    Sub Page_Page()
        If Not Page.IsPostBack
            viewSite.DataSource() = getPortfolio(CType(Request.QueryString("designId"), Integer))
            viewSite.DataBind()
        End If
     End Sub

       'get the portfolio = design id that is passed in the url.
        Function getPortfolio(ByVal designId As Integer) As System.Data.DataSet
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\vhosts\davejenkins.co.uk\httpdocs\admin\sitedb.mdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

            Dim queryString As New System.Text.StringBuilder
    queryString.Append("SELECT [tblDesigns].[DesignId], [tblDesigns].[SiteUrl], [tblDesigns].[SiteOwner]")
    queryString.Append(", [tblDesigns].[SiteTech], [tblDesigns].[SiteLongDesc], [tblDesigns].[SiteCreated]")
    queryString.Append(", [tblDesigns].[SiteImage], [tblDesigns].[AltTag]")
    queryString.AppendFormat(" FROM [tblDesigns] WHERE designId={0}", Request.Querystring("designId"))

            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString.ToString
            dbCommand.Connection = dbConnection

            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)

            Return dataSet
        End Function

</script>

And this is the repeater control on the page... is repeater the best thing to use? It only displays one recored of information...

Code:

  <asp:Repeater id="viewSite" runat="server">
                    <ItemTemplate>
                        <asp:Label text='<%# Container.DataItem("SiteUrl") %>' runat="server" /><br/>
                        <asp:LinkButton text='<%# Container.DataItem("SiteOwner") %>' id="test" runat="server" />
                    </ItemTemplate>
                </asp:Repeater>

Cheers for all the help peter its much appreciated!

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521