asp.net client page quieries remote ms sql server 2005 table..if row exists than play.wav file

I'm looking for a asp.net page that will be run on the client side....it will run a query against a ms sql server 2005 table. The table is created as a queue table to handle.wav files. If the client finds any .wav files on the table than play the .wav files on the client until the queue is exhausted.
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GlobaLevelProgrammerAuthor Commented:
any?
carlnorrbomCommented:
Hi,

Attached is a very simple example on how you can upload wave files to a sql server backend and get them displayed in a grid. Then simply have them played in mediaplayer when the "Play" button is clicked in the grid. Nothing on queuing up items though as that in my view is for more complex. You could probably do it by embedding a media control and adding items to a playlist?!? Anyway, hope this example helps you get going in the correct direction.

/Carl.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GlobalLevel_20101022.aspx.vb" Inherits="GlobalLevel_20101022" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="fu1" runat="server" />&nbsp;&nbsp;
        <asp:Label ID="lblResult" runat="server"></asp:Label>
        <br />
        <asp:Button ID="btnUpload" runat="server" Text="Upload File" />
        <br />
        <br />
        Uploaded wave files:<br />
    </div>
    <div>
        <asp:GridView ID="gvWaveFiles" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="ID" DataSourceID="dsWaveFiles" 
            ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <Columns>
                <asp:ButtonField Text="Play" CommandName="Play" />
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                    ReadOnly="True" SortExpression="ID" Visible="False" />
                <asp:BoundField DataField="FileName" HeaderText="FileName" 
                    SortExpression="FileName" />
                <asp:BoundField DataField="UploadDate" HeaderText="UploadDate" 
                    SortExpression="UploadDate" />
                <asp:BoundField DataField="UploadedBy" HeaderText="UploadedBy" 
                    SortExpression="UploadedBy" />
                <asp:BoundField DataField="Comments" HeaderText="Comments" 
                    SortExpression="Comments" />
            </Columns>
            <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>
        <asp:SqlDataSource ID="dsWaveFiles" runat="server" 
            ConnectionString="<%$ ConnectionStrings:dev %>" 
            SelectCommand="SELECT [ID], [FileName], [UploadDate], [UploadedBy], [Comments] FROM [tblWaveFiles]">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>


Imports System.Data.SqlClient
Imports System.IO

Partial Class GlobalLevel_20101022
    Inherits System.Web.UI.Page

    Private _conn As New SqlConnection(ConfigurationManager.ConnectionStrings("dev").ConnectionString)

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If fu1.HasFile Then
            Dim sqlUploadCommand As New SqlCommand("INSERT INTO tblWaveFiles ([FileName], [WaveFileData], [UploadDate], [UploadedBy], [Comments]) VALUES (@FileName, @WaveFileData, @UploadDate, @UploadedBy, @Comments)", _conn)
            sqlUploadCommand.Parameters.AddWithValue("@FileName", fu1.FileName.ToString())
            sqlUploadCommand.Parameters.AddWithValue("@WaveFileData", fu1.FileBytes)
            sqlUploadCommand.Parameters.AddWithValue("@UploadDate", DateTime.Now())
            sqlUploadCommand.Parameters.AddWithValue("@UploadedBy", "WebUser")
            sqlUploadCommand.Parameters.AddWithValue("@Comments", "NOT IMPLEMENTED")
            Try
                If Not _conn.State = Data.ConnectionState.Open Then
                    _conn.Open()
                End If
                sqlUploadCommand.ExecuteNonQuery()
                lblResult.Text = "Wave file successfully uploaded to database."
            Catch ex As Exception
                lblResult.Text = "Wave file was NOT uploaded to the database, reason: " & ex.ToString()
            Finally
                If Not _conn.State = Data.ConnectionState.Closed Then
                    _conn.Close()
                End If
            End Try
        End If
    End Sub


    Protected Sub gvWaveFiles_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvWaveFiles.RowCommand
        If e.CommandName = "Play" Then
            Dim waveId As Integer = gvWaveFiles.DataKeys(e.CommandArgument).Value
            Dim sqlPlayWaveFile As New SqlDataAdapter("SELECT [WaveFileData] FROM [tblWaveFiles] WHERE ([ID] = @ID)", _conn)
            sqlPlayWaveFile.SelectCommand.Parameters.AddWithValue("@ID", waveId)
            Try
                If Not _conn.State = Data.ConnectionState.Open Then
                    _conn.Open()
                End If
                Dim WaveFile As Byte() = sqlPlayWaveFile.SelectCommand.ExecuteScalar()
                Dim strFile As FileStream = File.OpenWrite(Server.MapPath("~/stream.wav"))
                Dim str As New MemoryStream()
                str.Write(WaveFile, 0, WaveFile.Length)
                str.WriteTo(strFile)
                strFile.Flush()
                strFile.Close()
                Response.ContentType = "audio/x-wav"
                Response.Redirect("~/stream.wav")
            Catch ex As Exception
                'TODO: Implement error handling logic.
            Finally
                If Not _conn.State = Data.ConnectionState.Closed Then
                    _conn.Close()
                End If
            End Try
        End If
    End Sub
End Class

Open in new window

Screenshot-GlobalLevel-20101022-.PNG
GlobaLevelProgrammerAuthor Commented:
really appreciate your help...so heres what is going on...basically its a survey to test how well the user knows their 80 songs..

on page_load songs snippets will be begin playing about 10 seconds long..one after another...in that time the user needs to enter which 80's band
 ..they think it is...before the next song plays..then another snippet plays and they enter the name of the and for that
..their answer will upload to a ms sql db...PLEASE note..
it cant be stored at the end of the session to be sent to sql as a bulk..
..it has to be sent whenever the loop calls it to send...pretty much immediately...

basically repeat this  over and over...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GlobalLevel_20101022.aspx.vb" Inherits="GlobalLevel_20101022" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Imports = " System.Data.SqlClient" %>
<%@ Imports = " System.IO "%>



<script runat="server">



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

    Dim strUnderwriter As String
    strUnderwriter = Request.QueryString("u")
    
    Dim strURL As String
    strURL = Request.QueryString("v")
    
    Dim strUTitle As String
    strUTitle = Request.QueryString("ut")
    
    Dim strVTitle As String
    strVTitle = Request.QueryString("vt")
    
' loop continuously to find new.wav files on the sql back end...then play as they enter the player...on client
' the .wav files are at most 20 seconds long per .wav
' so  play over and over...as they enter the client

For i = 1 to 1000

        Response.ContentType = "video/x-ms-asf"
        Response.Expires = 0
' query to get wav snippets from sql 
 gvWaveFiles_RowCommand()

    Response.Write("<ASX version=""3.0"">")
    Response.Write("<ENTRY CLIENTSKIP=""NO"">")
    Response.Write("<TITLE>" & (strUTitle) & "</TITLE>")
    Response.Write("<COPYRIGHT> 2008 </COPYRIGHT>")
    Response.Write("<ref href = """ & (strUnderwriter) & """ />")
    Response.Write("</ENTRY>")
    Response.Write("<ENTRY>")
    Response.Write("<TITLE>" & (strVTitle) & "</TITLE>")
    Response.Write("<ref href =""" & (strURL) & """ />")
    Response.Write("<COPYRIGHT> 2008 </COPYRIGHT>")
    Response.Write("</ENTRY>")
    Response.Write("</ASX>")
    
    send_reponse_to_server()
    
    

next

' source:http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23707335.html?sfQueryTermInfo=1+10+30+asp.net+playlist

Partial Class grab_wav_files
    Inherits System.Web.UI.Page



    Protected Sub gvWaveFiles_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvWaveFiles.RowCommand
       
            Dim waveId As Integer = gvWaveFiles.DataKeys(e.CommandArgument).Value
            Dim sqlPlayWaveFile As New SqlDataAdapter("SELECT [WaveFileData] FROM [tblWaveFiles] WHERE ([ID] = @ID)", _conn)
            sqlPlayWaveFile.SelectCommand.Parameters.AddWithValue("@ID", waveId)
            Try
                If Not _conn.State = Data.ConnectionState.Open Then
                    _conn.Open()
                End If
                Dim WaveFile As Byte() = sqlPlayWaveFile.SelectCommand.ExecuteScalar()
                Dim strFile As FileStream = File.OpenWrite(Server.MapPath("~/stream.wav"))
                Dim str As New MemoryStream()
                str.Write(WaveFile, 0, WaveFile.Length)
                str.WriteTo(strFile)
                strFile.Flush()
                strFile.Close()
                Response.ContentType = "audio/x-wav"
                Response.Redirect("~/stream.wav")
            Catch ex As Exception
                'TODO: Implement error handling logic.
            Finally
                If Not _conn.State = Data.ConnectionState.Closed Then
                    _conn.Close()
                End If
            End Try
   
    End Sub



public sub send_reponse_to_server( ByVal sender As Object, ByVal e as events)

  dim user_guess as string

  user_guess = textbox_user_guess.text

   Dim waveId As Integer = gvWaveFiles.DataKeys(e.CommandArgument).Value
            Dim sqlPlayWaveFile As New SqlDataAdapter("UPDATE [user_guess] FROM [user_guess_80_band] WHERE ([ID] = @ID)", _conn)
            sqlPlayWaveFile.SelectCommand.Parameters.AddWithValue("@ID", waveId)
            Try
                If Not _conn.State = Data.ConnectionState.Open Then
                    _conn.Open()
                End If
                Dim WaveFile As Byte() = sqlPlayWaveFile.SelectCommand.ExecuteScalar()
                Dim strFile As FileStream = File.OpenWrite(Server.MapPath("~/stream.wav"))
                Dim str As New MemoryStream()
                str.Write(WaveFile, 0, WaveFile.Length)
                str.WriteTo(strFile)
                strFile.Flush()
                strFile.Close()
                Response.ContentType = "audio/x-wav"
                Response.Redirect("~/stream.wav")
            Catch ex As Exception
                'TODO: Implement error handling logic.
            Finally
                If Not _conn.State = Data.ConnectionState.Closed Then
                    _conn.Close()
                End If
            End Try


end class

</script>


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="guess_result" text="Guess the name of the band of the 80 songs that are playing in rapid session, enter your guess below..you only have a few seconds before the next one begins..so be quick!!"runat="server"></asp:Label>
        <br />
        <asp:textbox="textbox_user_guess" runat="server" />&nbsp;&nbsp;
        
        <br />
        
        <br />
        <br />
        Guess the song snippet:<br />
    </div>
    <div>
        <object classid="CLSID:6BF52A52-394A-11d3-B153-00C04F79FAA6" id="VIDEO" type="video/x-ms-asf"
	            width="400" height="365" standby="Loading Microsoft Windows Media Player components..."
	            codebase="http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf.cab#Version=6,4,5,715">
	            <param name="StretchToFit" value="1" />
	            <param name="AutoStart" value="true" />
	            <param name="Balance" value="0" />
	            <param name="EnableContextMenu" value="1" />
	            <param name="Enabled" value="1" />
	            <param name="EnableErrorDialogs" value="1" />
	            <param name="WindowlessVideo" value="0" />
	            <param name="Rate" value="1.000" />
	            <param name="CurrentPosition" value="0.000" />
	            <param name="CurrentMarker" value="1" />
	            <param name="FullScreen" value="0" />
	            <param name="Mute" value="0" />
	            <param name="PlayCount" value="1" />
	            <param name="Uimode" value="Full" />
	            <param name="Volume" value="50" />
	            <param name="URL"  value ="<%Response.Write(strPlay) %> "/>
	            <embed id="mpFAB" type="video/x-ms-asf" src="<%Response.Write(strPlay) %>" width="400" height="345"
	                showcontrols="1" />
	          
	            
	  </object>


    </div>
    </form>
</body>
</html>

Open in new window

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

GlobaLevelProgrammerAuthor Commented:
I ran your code above that you provided and I get an error:

Error Creating Control - gvWaveFiles
Type: 'System.Web.UI.WebControls.Gridview' does not have a public property named 'SortedAscendingCellStyle'.
carlnorrbomCommented:
Hi,

For me it compiles and runs just fine, i never post code on EE which doesn't compile and run. Did you run it in a completely new project or inside an existing one? Which version of the framework are you targeting? (code is for 4.0). Other than that if you can not get it to run, try removing the style elements from the gridview markup (they are actually designer generated within Visual Studio so i find it hard to understand why they would break your code).

/Carl.
GlobaLevelProgrammerAuthor Commented:
this is great...your right your code is  (code is for 4.0).

I am runninn in 2.0...that why it doesnt see:

Type: 'System.Web.UI.WebControls.Gridview' does not have a public property named 'SortedAscendingCellStyle'.
carlnorrbomCommented:
Hi,

Well, nevertheless, just try removing the style elements from the gridview markup and it should run.

/Carl.
carlnorrbomCommented:
Hi,

Also responded in your other, follow up question:

Change the gridview markup to:

<asp:GridView ID="gvWaveFiles" runat="server" AutoGenerateColumns="False"
    DataKeyNames="ID" DataSourceID="dsWaveFiles" CellPadding="4"
    EnableModelValidation="True" ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:ButtonField Text="Play" CommandName="Play" />
        <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
            ReadOnly="True" SortExpression="ID" Visible="False" />
        <asp:BoundField DataField="FileName" HeaderText="FileName"
            SortExpression="FileName" />
        <asp:BoundField DataField="UploadDate" HeaderText="UploadDate"
            SortExpression="UploadDate" />
        <asp:BoundField DataField="UploadedBy" HeaderText="UploadedBy"
            SortExpression="UploadedBy" />
        <asp:BoundField DataField="Comments" HeaderText="Comments"
            SortExpression="Comments" />
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>

This will solve your issue!

/Carl.
GlobaLevelProgrammerAuthor Commented:
This is the only error I have left:object is set to a null reference...or something...

can I do this?


Private _conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Data Source=xx.xx.xx.xx;Initial Catalog=xx;Trusted_connection=true;").ConnectionString)
carlnorrbomCommented:
Hi,

No you can't. You need to specify the connectionstring in your web.config like this:

<configuration>
...
    <connectionStrings>
        <add name="NameOfConnection" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=xx;Trusted_connection=true;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
...
</configuration>

And then in code reference it like this:

Private _conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NameOfConnection").ConnectionString)

/Carl.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlnorrbomCommented:
Hi,

Did this help you out?

/Carl.
GlobaLevelProgrammerAuthor Commented:
Let me check and get Back tnite ....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.