cant show data from my database

tjgrindsted · January 6th, 2007, 01:47 PM

Hi

I have this code, but i can't get it to work, and if i delete <% %> then the if statsment is not working, how do i get this code to work.

Code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
    SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @SiteMainID)">
<%    Dim pageString As String = "Def"
      If Not (Request.QueryString("Page") Is Nothing) Then
        pageString = Request.QueryString("Page").ToString
      End If
      If pageString = "Def" Then
%>
        <SelectParameters>
            <asp:Parameter DefaultValue="1" Name="SiteMainID" Type="Int32" />
        </SelectParameters>
<%
      Else
        If pageString = "Page1" Then
%>
        <SelectParameters>
            <asp:Parameter DefaultValue="2" Name="SiteMainID" Type="Int32" />
        </SelectParameters>
<%
        Else
          If pageString = "Page2" Then
%>
        <SelectParameters>
            <asp:Parameter DefaultValue="3" Name="SiteMainID" Type="Int32" />
        </SelectParameters>
<%

        End If
      End If
%>
</asp:SqlDataSource>

Imar · January 7th, 2007, 06:41 AM

Hi there,

You can no longer use these kind of classic ASP tricks to build up a control. The control definition is needed at compile time, where the data for variables like pageString etc is not available.

Instead, you need to define the whole control in markup, then handle the OnSelecting event of the datasource (which fires before it tries to get to the database) to fill in the parameter based on the QueryString value. If your querystring values map one on one to the values you want to send to the database, you could even use QueryStringParameters:

Code:

<asp:QueryStringParameter DefaultValue="1" QueryStringField="SiteMainId" />

Otherwise, something like this should work:

Code:

SqlDataSource:
<SelectParameters>
  <asp:Parameter Name="SiteMainID" Type="Int32" />
</SelectParameters>

Code:

Code Behind:
  Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
          ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) _
          Handles SqlDataSource1.Selecting

    Select Case Request.QueryString("Page").ToString()
      Case "Def"
        e.Command.Parameters(0).Value = 1
      Case "Page1"
        e.Command.Parameters(0).Value = 2
      Case "Page2"
        e.Command.Parameters(0).Value = 3
    End Select

  End Sub

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

tjgrindsted · January 7th, 2007, 07:08 PM

Hi Imar

Thx for replay :)

<b>If i use this on my default.aspx site</b>

<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
    SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @SiteMainID)">
    <SelectParameters>
      <asp:Parameter Name="SiteMainID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

</asp:Content>

<b>And this in kode behind</b>
Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
            ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEv entArgs) _
            Handles SqlDataSource1.Selecting

        Select Case Request.QueryString("Page").ToString()
            Case "Def"
                e.Command.Parameters(0).Value = 1
            Case "Page1"
                e.Command.Parameters(0).Value = 2
            Case "Page2"
                e.Command.Parameters(0).Value = 3
        End Select

    End Sub

End Class

It run the site but it is not showing anything from the database and i know thats i have id 1 to 3 so
it can show some text from my db if the link is www.site.dk/default.aspx?page=1 or 2 or 3.
but nothing is showing.

if im in VWD2005EE make a mouse over in the codebehind Handles SqlDataSource1.Selecting on the word SqlDataSource1
it say "Handels clause requires a WhitEvents variable defined in the containing type or one of its base types."

im a little new in .Net so hope u can help me with this showing error.

tjgrindsted · January 7th, 2007, 07:14 PM

im using the link as Default.aspx?Page=Page2 ans still no text from my DB.

Imar · January 8th, 2007, 06:08 AM

What happens when you set a break point at the beginning of SqlDataSource1_Selecting (by pressing F9) and then hit F5 to debug the application?

Does your breakpoint get hit? The error (Handles clause requires a WhitEvents....) seems to suggest that your SqlDataSource is named differently from SqlDataSource1

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

tjgrindsted · January 8th, 2007, 02:24 PM

I get no error right now, so my code is lige this:

..::default.aspx::..
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<asp:Content ID="Menu" ContentPlaceHolderID="ContentPlaceHolderMenu" Runat="Server">
<%
Response.Write("Menu")
%>
</asp:Content>
<asp:Content ID="Div" ContentPlaceHolderID="ContentPlaceHolderDiv" Runat="Server">
<form id="Form1" runat="server">
<asp:calendar
    id="calDate"
    cssclass="calendar"
    Font-Names="tahoma"
    Font-Size="9px"
    selectionmode="Day"
    runat="server"
    visible="True"
    nextprevformat="ShortMonth"
    dayheaderstyle-cssclass="DayHeader"
    daystyle-cssclass="Day"
    nextprevstyle-cssclass="NextPrev"
    othermonthdaystyle-cssclass="OtherMonthDay"
    selecteddaystyle-cssclass="SelectedDay"
    selectorstyle-cssclass="Selector"
    titlestyle-cssclass="Title"
    todaydaystyle-cssclass="TodayDay"
    weekenddaystyle-cssclass="WeekendDay">
</asp:calendar>
</form>

</asp:Content>
<asp:Content ID="Hotkeys" ContentPlaceHolderID="ContentPlaceHolderHotkeys" Runat="Server">
<%
Response.Write("Hotkeys")
%>
</asp:Content>
<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
    SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @SiteMainID)">
    <SelectParameters>
      <asp:Parameter Name="SiteMainID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

</asp:Content>

..::default.aspx.vb::..
Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
            ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEv entArgs) _
            Handles SqlDataSource1.Selecting

        Select Case Request.QueryString("Page").ToString()
            Case "Def"
                e.Command.Parameters(0).Value = 1
            Case "Page1"
                e.Command.Parameters(0).Value = 2
            Case "Page2"
                e.Command.Parameters(0).Value = 3
        End Select

    End Sub

End Class

..::web.config::..
<connectionStrings>
  <add name="ConnectionStringMain" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\database.mdf;Integrated Security=True;User Instance=True"
   providerName="System.Data.SqlClient" />
</connectionStrings>

Imar · January 8th, 2007, 02:48 PM

But did you debug the page like I suggested? That can give you some very useful information. For example, string comparison is case sensitive, so the QueryString needs to contain Page1, not page1. Without proper debugging info, you can't tell what's going on. Debugging will show you wether the handler is hit, and if so, if the right Case block in the Select block is executed...

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

tjgrindsted · January 8th, 2007, 05:05 PM

I have tryed but if i click before the < tag in <asp:Sql..... and hit F9 nothing happens if i click infront of the <asp:Sql tag in the gray line then VWD2005EE say "This is not a valid location for a breakpoint" so cant run the debug with a breakpoint, But if i just hit F5 then it run debugging but it shows no error, it just show the site as before, without the text from the DB.

Cant i make a SQL code like old ASP 3.0 !? for me it's easier to make the sql statsment.

But hope u can help me anyway.

tjgrindsted · January 8th, 2007, 05:30 PM

dumm question but havent i forgot something like

Eval("SiteMainText") or <%# DataBinder.Eval(Container.DataItem, "SiteMainText") %> so the code know that now i have get my data from the database, and now i will write it in my content !?

Imar · January 9th, 2007, 02:34 AM

Don't set a break point in the ASPX, but in the handler in the code behind as I suggested earlier:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEv entArgs) _
Handles SqlDataSource1.Selecting

Select Case Request.QueryString("Page").ToString()

Set a breakpoint on the bold line and try again....

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.