|
Subject:
|
cant show data from my database
|
|
Posted By:
|
tjgrindsted
|
Post Date:
|
1/6/2007 12:47:41 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.
<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>
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/7/2007 5:41:51 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:<asp:QueryStringParameter DefaultValue="1" QueryStringField="SiteMainId" /> Otherwise, something like this should work:SqlDataSource:
<SelectParameters>
<asp:Parameter Name="SiteMainID" Type="Int32" />
</SelectParameters> 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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/7/2007 6:08:05 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.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
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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/7/2007 6:14:15 PM
|
im using the link as Default.aspx?Page=Page2 ans still no text from my DB.
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/8/2007 5:08:38 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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/8/2007 1:24:42 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.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
End Class
..::web.config::..
<connectionStrings>
<add name="ConnectionStringMain" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/8/2007 1:48:39 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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/8/2007 4:05:56 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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/8/2007 4:30:00 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 !?
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/9/2007 1:34:17 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.SqlDataSourceSelectingEventArgs) _
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.
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/9/2007 1:37:36 AM
|
quote:
but havent i forgot something like Eval("SiteMainText")
That's possible, but hard for me to judge as I don't know your pages. So, maybe the SqlDataSource does fire, but you don't do anything with it?
Try adding a data control (like a GridView) to the page and attach it to the SqlDataSource with its DataSourceID property and see if you get any results. Then you can see if the query returns data or not and work from there.
Cheers,
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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/9/2007 5:01:37 PM
|
nothing happens when i do like u write about the breakline. hmm !!?
if i want to use old asp code as i know like:
Dim strTitle, strText
strSQL = "SELECT [TextOverskrift], [TextMain] FROM index"
strSQL = strSQL & " WHERE [TextID] = 1"
Set objRS = objConn.Execute(strSQL)
If Not (objRS.BOF Or objRS.EOF) Then
Response.Write("<table width='100%' border='0' cellspacing='0' cellpadding='0'>")
Do While Not objRS.EOF
strTitle = objRS("TextOverskrift")
strText = objRS("TextMain")
Response.Write("<tr><td>")
If strTitle <> "" Then
Response.Write("<b>"&strTitle&"</b><br><br>")
Else
Response.Write("")
End If
Response.Write(strText)
Response.Write("</td></tr>")
objRS.MoveNext
Loop
Response.Write ("</table>")
else
Response.Write "Der findes intet i Databasen"
End If
and want to use the connection in web.config
<connectionStrings>
<add name="ConnectionStringMain" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aeronetDSNbase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
can u then help me making my old asp code work so i can see how i can make it, bc im new in asp.net but asp:datagrid an so on i thing is harder to use then the old code i know.
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/9/2007 5:13:04 PM
|
Did you add a GridView that uses the SqlDataSource?
Can you post the code for the ASPX page and its code behind?
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
While typing this post, I was listening to: Tempel by Opgezwolle (Track 4 from the album: Vloeistof) What's This?
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/9/2007 5:16:20 PM
|
the hole code is in this post, do u want the master page to !?
Posted - 01/08/2007 : 6:24:42 PM
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/9/2007 5:18:42 PM
|
Is it?? Then where is the GridView?
The SqlDataSource only retrieves data; you need other controls like the GridView to display it...
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
While typing this post, I was listening to: Tempel by Opgezwolle (Track 4 from the album: Vloeistof) What's This?
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/10/2007 3:07:37 PM
|
Imar
1000 Thx for ur time, i ende up with this and it work so i can show the data, im also changes it so i use access DB and not MSSQL bc my host can't handel a MSSQL db, now i just have to changes the code to use Request.QueryString("Page").ToString().
<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<%
Dim rdr As OleDbDataReader = Nothing
Dim con As OleDbConnection = Nothing
Dim cmd As OleDbCommand = Nothing
Try
Dim ConnectionString As String = ConfigurationManager.AppSettings("ConnStr")
con = New OleDbConnection(ConnectionString)
con.Open()
Dim CommandText As String = "SELECT [SiteMa], [SiteMai] FROM [Text] WHERE [SiteMa] = 1"
cmd = New OleDbCommand(CommandText)
cmd.Connection = con
rdr = cmd.ExecuteReader()
Dim strText As String = ""
While rdr.Read()
strText = rdr("SiteMainText").ToString()
Response.Write(strText)
End While
Finally
If Not (rdr Is Nothing) Then
rdr.Close()
End If
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
%>
</asp:Content>
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/10/2007 3:34:24 PM
|
While this code certainly works, I can strongly recommend investigating a little more time in the other solution. With your current code, you're not really making use of the new features in .NET. In fact, your code resembles classic ASP quite a lot.
Learning .NET may be hard at first, but once you get the hang of it, you can be really productive with it, without reverting to "old skool" spaghetti code.... ;-)
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.
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/10/2007 4:28:41 PM
|
i know it :) but work as a TV/Radio sellsman so i have only time for this after work. :'(
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/10/2007 4:31:29 PM
|
Well, if it works, it works... ;-)
Good luck,
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
While typing this post, I was listening to: Vraag & Antwoord by Opgezwolle (Track 6 from the album: Vloeistof) What's This?
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/10/2007 4:40:17 PM
|
Damn U hit me hard, aboud Posted - 01/10/2007 : 8:34:24 PM, and yes now i know how to get it to word, BUT.. :D (if we can look at it just one more time)
I have a content in this content i will only show one record from an Access database, and the record is a long text, how will this code then look like and what grid/repeater do i need to use in this code !?
<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain %>"
SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = @MainID)">
<SelectParameters>
<asp:Parameter Name="MainID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Content>
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/10/2007 4:48:43 PM
|
I didn't mean to hit you hard; I just wanted to give you advice about getting the most out of .NET.
Anyway, you can use a DetailsView or a FormView control for this; both are meant to display a single record at the time (although they support paging as well.
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/detailsview.aspx
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/formview.aspx
Cheers,
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
While typing this post, I was listening to: Rustug by Opgezwolle (Track 11 from the album: Vloeistof) What's This?
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/10/2007 4:51:32 PM
|
I know i diden't mean to hit me hard ;) just a joke.
do i need
<asp:AccessDataSource runat="server"></asp:AccessDataSource>
or
<asp:ObjectDataSource runat="server"></asp:ObjectDataSource>
fore my Access database !?
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/10/2007 4:59:02 PM
|
The ObjectDataSource works with .NET objects only; you need classes in your App_Code folder, Bin folder, or standard .NET classes for this control to work.
The AccessDataSource control is for quick access to an Access database, although you could also use the SqlDataSource control.
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
While typing this post, I was listening to: Vork by Opgezwolle (Track 12 from the album: Vloeistof) What's This?
|
|
Reply By:
|
tjgrindsted
|
Reply Date:
|
1/10/2007 5:07:01 PM
|
okay so i can still use this from my web.config
<appSettings>
<add key="ConnStr" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/WEB/webdesign/net/site/App_Data/DSNbase.mdb" />
</appSettings>
and then i can use something like:
<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStr %>"
SelectCommand="SELECT [MainID], [MainText] FROM [MainText] WHERE ([MainID] = @MainID)">
</asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSource="SqlDataSource1">
</asp:FormView>
</asp:Content>
or it that wrong !?
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/10/2007 5:12:07 PM
|
Why don't you try it out and see if it works?
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
While typing this post, I was listening to: I'm Not Okay (I Promise) by My Chemical Romance (Track 5 from the album: Three Cheers For Sweet Revenge) What's This?
|
