Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Can't get my query to work

    I have plenty of DB experience, but not with MSSQL ...

    I'm working in Sitefinity. I'm changing a vb script that retrieves an mp3 by the date it was posted and it works fine as it is. Here is the code for that.

    Partial Class Foundry_UserControls_GetSegmentByDate
    Inherits System.Web.UI.UserControl

    Private _ProgramID As Integer
    Public Property ProgramID() As Integer
    Get
    Return _ProgramID
    End Get
    Set(ByVal value As Integer)
    _ProgramID = value
    End Set
    End Property

    Private _RunDate As Date
    Public Property RunDate() As Date
    Get
    Return _RunDate
    End Get
    Set(ByVal value As Date)
    _RunDate = value
    End Set
    End Property


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim db As New DAL.AllstarRadioDataContext
    Dim Content = (From c In db.fs_Contents _
    Where c.ProgramID = _ProgramID _
    And c.DisabledBy Is Nothing _
    And c.fs_ContentFiles.Count > 0 _
    And c.fs_ContentFiles.Where(Function(f) f.FileMIMEType = "audio/mpeg").Count > 0 _
    And c****nDate = _RunDate _
    Order by c****nDate Descending).Take(1).FirstOrDefault

    If Content IsNot Nothing Then
    'load player

    The table has a column named Title (varchar 200) and I want to retrieve by Title instead of the post date.

    Here's the new code after I changed it.

    Partial Class Foundry_UserControls_GetSegmentByTitle
    Inherits System.Web.UI.UserControl

    Private _ProgramID As Integer
    Public Property ProgramID() As Integer
    Get
    Return _ProgramID
    End Get
    Set(ByVal value As Integer)
    _ProgramID = value
    End Set
    End Property

    Private _Title As String
    Public Property Title() As String
    Get
    Return _Title
    End Get
    Set(ByVal value As String)
    _Title = value
    End Set
    End Property


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim db As New DAL.AllstarRadioDataContext
    Dim Content = (From c In db.fs_Contents _
    Where c.ProgramID = _ProgramID _
    And c.DisabledBy Is Nothing _
    And c.fs_ContentFiles.Count > 0 _
    And c.fs_ContentFiles.Where(Function(f) f.FileMIMEType = "audio/mpeg").Count > 0 _
    And c.Title = _Title _
    Order by c****nDate Descending).Take(1).FirstOrDefault

    If Content IsNot Nothing Then
    'load player

    As you can see, I left the rest of the query alone and only replaced the date match with a Title match. The _Title used for the match is a String. The query doesn't come back with the mp3.

    I suspect I need quotes, different data type, something that I'm not familiar with.

    Any help would be really appreciated.

    Ken
    Last edited by kwc; 07-17-11 at 12:19.

  2. #2
    Join Date
    Jul 2011
    Posts
    14
    I don't know VB.NET or LINQ all that well, but I'm not seeing anything wrong jumping out at me.

    You've confirmed that at the time the query is run the _Title variable contains a valid title that matches exactly the Title field of a row in the database?

    Also, what is Content set to after the query is run? Is it Nothing or just the wrong MP3 record?

    As a note for the future, you can enclose code in CODE tags, which makes it a lot more readable (there's a button in the post editor for that).

  3. #3
    Join Date
    Jul 2011
    Posts
    6

    Thanks

    Brian, thanks for taking the time to look at my code and thanks for the <CODE> tag tip.

    As it turned out the code is good. I had both Controls in the same page, which shouldn't be a problem, but it was. When I removed the GetSegmentByDate Control from the page, the ByTitle one started working fine.

    Go figure ...

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •