locked
Problem passing parameter values to stored procedure RRS feed

  • Question

  • User-552178233 posted

    I have a file upload app that i'm working on and am able to send the parameters to the file upload stored procedure, but seem to be having trouble with the SP that updates a different table.  I'm still pretty new to asp.net and am not really sure where i'm going wrong.  I am posting the .aspx file, .aspx.vb file, and the file upload SP and the data updating SP.  I checked my data update sp with the query analyzer and it works.  Thanks for any input!

    griduploads.aspx.vb

     
    1    Imports System.Data.SqlClient
    2    
    3     
    4    
    5    Partial Class GridUploads
    6    
    7        Inherits System.Web.UI.Page
    8    
    9        Dim iBrief_ID As Integer
    10   
    11    
    12   
    13         Protected Sub Page_Load(ByVal s As Object, ByVal e As System.EventArgs) Handles Me.Load
    14   
    15    
    16   
    17               If CheckIFrameMode() Then Return
    18   
    19    
    20   
    21           If Not IsPostBack Then BindBriefsGrid()
    22   
    23    
    24   
    25    
    26   
    27           iBrief_ID = Request.QueryString("briefs")
    28   
    29           
    30   
    31         End Sub
    32   
    33    
    34   
    35    
    36   
    37       Protected Sub BindBriefsGrid()
    38   
    39           ' this routine gets data from the db and binds to the grid
    40   
    41    
    42   
    43           Dim conn As SqlConnection
    44   
    45           Dim cmd As SqlCommand
    46   
    47           Dim rdr As SqlDataReader
    48   
    49    
    50   
    51           conn = New SqlConnection("Server=XXXXXXXXXX;Database=XXXXXX;User ID=XXXXXX;Password=XXXX")
    52   
    53           conn.Open()
    54   
    55    
    56   
    57           cmd = New SqlCommand()
    58   
    59           cmd.Connection = conn
    60   
    61           cmd.CommandText = "SELECT * FROM briefs"
    62   
    63    
    64   
    65           rdr = cmd.ExecuteReader()
    66   
    67    
    68   
    69           gvUploads.DataSource = rdr
    70   
    71           gvUploads.DataBind()
    72   
    73    
    74   
    75           rdr.Close()
    76   
    77           conn.Close()
    78   
    79       End Sub
    80   
    81    
    82   
    83       Protected Function CheckIFrameMode() As Boolean
    84   
    85           ' this function checks if the page is to be displayed for iframe
    86   
    87           ' it is checked by querystring parameter named MODE
    88   
    89           ' when in iframe mode, all panels except for the upload panel should be hidden
    90   
    91    
    92   
    93           If Request.QueryString("MODE") = "UPLOAD" Then
    94   
    95    
    96   
    97               pnlUpload.Visible = True
    98   
    99               pnlMain.Visible = False
    100  
    101   
    102  
    103              ' need to set this to track brief_ID
    104  
    105              ViewState("brief_ID") = Request.QueryString("ID")
    106  
    107   
    108  
    109              Return True
    110  
    111   
    112  
    113          Else
    114  
    115   
    116  
    117              pnlUpload.Visible = False
    118  
    119              pnlMain.Visible = True
    120  
    121   
    122  
    123              Return False
    124  
    125   
    126  
    127          End If
    128  
    129      End Function
    130  
    131   
    132  
    133   
    134  
    135      Protected Sub btnUpload_Click(ByVal o As Object, ByVal e As EventArgs) _
    136  
    137         Handles btnUpload.Click
    138  
    139   
    140  
    141          Dim brief_Id As Integer = CInt(ViewState("brief_ID"))
    142  
    143          Dim iDoc_ID As Integer
    144  
    145          Dim iBrief_file_type As Integer
    146  
    147          Try
    148  
    149   
    150  
    151              'If upFile.HasFile Then
    152  
    153   
    154  
    155              ' save the uploaded file
    156  
    157              iBrief_file_type = 1
    158  
    159   
    160  
    161              'UPLOAD TO A STREAM obj THEN transform to BYTE ARRAY
    162  
    163              'fileContent property returns a STREAM obj
    164  
    165              Dim bytFile() As Byte
    166  
    167              Dim strFileName As String
    168  
    169              Dim strFileType As String
    170  
    171              Dim iFileLength As Integer
    172  
    173              Dim objStream As System.IO.Stream
    174  
    175   
    176  
    177              strFileName = upFile.FileName
    178  
    179              strFileType = upFile.PostedFile.ContentType
    180  
    181              iFileLength = upFile.PostedFile.ContentLength
    182  
    183   
    184  
    185              ReDim bytFile(iFileLength)
    186  
    187              objStream = upFile.PostedFile.InputStream
    188  
    189              objStream.Read(bytFile, 0, iFileLength)
    190  
    191   
    192  
    193              iDoc_ID = uploadToDB(bytFile, strFileType, iFileLength, strFileName)
    194  
    195              Call updateBriefwithFile(iBrief_ID, iBrief_file_type, iDoc_ID)
    196  
    197   
    198  
    199              'upFile.SaveAs(Server.MapPath(".") & upFile.FileName)
    200  
    201   
    202  
    203              lblMessage.Text = "File uploaded successfully."
    204  
    205   
    206  
    207              'Else
    208  
    209   
    210  
    211              ' there was no file to upload, just show message
    212  
    213   
    214  
    215              'lblMessage.Text = "No file selected for upload."
    216  
    217   
    218  
    219              'End If
    220  
    221   
    222  
    223          Catch ex As Exception
    224  
    225   
    226  
    227              ' show error if occurred
    228  
    229   
    230  
    231              lblMessage.Text = "Error: " & ex.Message
    232  
    233   
    234  
    235          End Try
    236  
    237   
    238  
    239          ' hide other controls in the panel
    240  
    241          imgLoader.Visible = False
    242  
    243          pnlUploadControl.Visible = False
    244  
    245   
    246  
    247          ' generate JS script for next file upload
    248  
    249          Dim s As String
    250  
    251          s = "parent.StartFilesUpload();"
    252  
    253   
    254  
    255          ClientScript.RegisterStartupScript(Me.GetType(), "", s, True)
    256  
    257   
    258  
    259      End Sub
    260  
    261   
    262  
    263      Function uploadToDB(ByVal bytFile() As Byte, ByVal strFileType As String, _
    264  
    265             ByVal iFileLength As Integer, ByVal strFileName As String)
    266  
    267   
    268  
    269          'CONNECT TO DB
    270  
    271          Dim oSQLConn As SqlConnection = New SqlConnection()
    272  
    273          oSQLConn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("SQLConn")
    274  
    275   
    276  
    277          'SET PARAMS FOR STORED PROCEDURE
    278  
    279          Dim cmdUpload As New SqlCommand("spUploadFile", oSQLConn)
    280  
    281          cmdUpload.CommandType = Data.CommandType.StoredProcedure
    282  
    283   
    284  
    285          cmdUpload.Parameters.Add(New SqlParameter("@doc_title", Data.SqlDbType.VarChar, 200))
    286  
    287          cmdUpload.Parameters("@doc_title").Value = strFileName
    288  
    289   
    290  
    291          cmdUpload.Parameters.Add(New SqlParameter("@doc", Data.SqlDbType.Image))
    292  
    293          cmdUpload.Parameters("@doc").Value = bytFile
    294  
    295   
    296  
    297          cmdUpload.Parameters.Add(New SqlParameter("@doc_type", Data.SqlDbType.VarChar, 50))
    298  
    299          cmdUpload.Parameters("@doc_type").Value = strFileType
    300  
    301   
    302  
    303          cmdUpload.Parameters.Add(New SqlParameter("@doc_length", Data.SqlDbType.Int))
    304  
    305          cmdUpload.Parameters("@doc_length").Value = iFileLength
    306  
    307   
    308  
    309          oSQLConn.Open()
    310  
    311          uploadToDB = cmdUpload.ExecuteScalar()
    312  
    313          oSQLConn.Close()
    314  
    315   
    316  
    317          cmdUpload.Dispose()
    318  
    319          oSQLConn.Dispose()
    320  
    321      End Function
    322  
    323   
    324  
    325      Sub updateBriefwithFile(ByVal iBrief, ByVal iFileType, ByVal iDoc)
    326  
    327          Dim oSQLConn As SqlConnection = New SqlConnection()
    328  
    329          oSQLConn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("SQLConn")
    330  
    331   
    332  
    333          Dim cmdUpdate As New SqlCommand("spUpdateBriefFile", oSQLConn)
    334  
    335          cmdUpdate.CommandType = Data.CommandType.StoredProcedure
    336  
    337   
    338  
    339          cmdUpdate.Parameters.Add(New SqlParameter("@brief_ID", Data.SqlDbType.Int))
    340  
    341          cmdUpdate.Parameters("@brief_ID").Value = iBrief
    342  
    343   
    344  
    345          cmdUpdate.Parameters.Add(New SqlParameter("@Brief_file_type", Data.SqlDbType.Int))
    346  
    347          cmdUpdate.Parameters("@Brief_file_type").Value = iFileType
    348  
    349   
    350  
    351          cmdUpdate.Parameters.Add(New SqlParameter("@doc_ID", Data.SqlDbType.Int))
    352  
    353          cmdUpdate.Parameters("@doc_ID").Value = iDoc
    354  
    355   
    356  
    357          oSQLConn.Open()
    358  
    359          cmdUpdate.ExecuteNonQuery()
    360  
    361          oSQLConn.Close()
    362  
    363   
    364  
    365          cmdUpdate.Dispose()
    366  
    367          oSQLConn.Dispose()
    368  
    369      End Sub
    370  
    371   
    372  
    373  End Class
    374  
    375  
    
     
     griduploads.aspx
     
     
    1    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridUploads.aspx.vb" Inherits="GridUploads" %>
    2    
    3     
    4    
    5    <html>
    6    
    7    <head>
    8    
    9    <title>Brief Upload</title>
    10   
    11    
    12   
    13   <style type="text/css">
    14   
    15   body, td, th { font-family:Verdana;font-size:8pt; }
    16   
    17   </style>
    18   
    19    
    20   
    21   <script type="text/javascript">
    22   
    23    
    24   
    25   function StartFilesUpload() {
    26   
    27         var i, frm, brief_Id;
    28   
    29         
    30   
    31         for (i = 0; i < window.frames.length; i++) {
    32   
    33               frm = window.frames[i];
    34   
    35               
    36   
    37               // skip other iframes that may be present in the document
    38   
    39               if (!frm.name) continue;
    40   
    41               if (!frm.name.indexOf("upfrm") == 0) continue;
    42   
    43               
    44   
    45               // skip iframes that are related to already processed
    46   
    47               if (frm.location.href == "about:blank") continue;
    48   
    49               
    50   
    51               // skip iframes that have already been processed
    52   
    53               if (!frm.document.getElementById("pnlUploadControl")) continue;
    54   
    55               
    56   
    57               //skip iframes that have no file selected
    58   
    59               if (frm.document.getElementById("upFile").value == "") continue;
    60   
    61               
    62   
    63               // get StudentID to which iframe is linked to
    64   
    65               brief_Id = frm.name.replace("upfrm", "");
    66   
    67               
    68   
    69               // hide the upload control, and display nice progress animation
    70   
    71               frm.document.getElementById("imgLoader").style.display = "block";
    72   
    73               frm.document.getElementById("pnlUploadControl").style.display = "none";
    74   
    75               
    76   
    77               // invoke upload in iframe
    78   
    79               frm.document.getElementById("btnUpload").click();
    80   
    81               
    82   
    83               // break the loop, to have only one upload at a time
    84   
    85               break;
    86   
    87         }
    88   
    89    
    90   
    91   }
    92   
    93    
    94   
    95   </script>
    96   
    97    
    98   
    99   </head>
    100  
    101  <body>
    102  
    103  <form id="frmMain" runat="server">
    104  
    105   
    106  
    107   
    108  
    109   
    110  
    111  <asp:Panel ID="pnlMain" runat="server">
    112  
    113   
    114  
    115        <asp:GridView ID="gvUploads" AutoGenerateColumns="false" 
    116  
    117              Width="350" RowStyle-Height="25"
    118  
    119              CellPadding="0" CellSpacing="0"
    120  
    121              runat="server">
    122  
    123        <Columns>
    124  
    125        
    126  
    127        <asp:BoundField 
    128  
    129              DataField="brief_ID" HeaderText="ID" 
    130  
    131              ItemStyle-Width="20" ItemStyle-HorizontalAlign="center" 
    132  
    133              />
    134  
    135        
    136  
    137        
    138  
    139        <asp:TemplateField ItemStyle-Width="250" ItemStyle-HorizontalAlign="center">
    140  
    141              <ItemTemplate>
    142  
    143                    
    144  
    145                    <iframe 
    146  
    147                          name="upfrm<%#DataBinder.Eval(Container, "DataItem.brief_ID")%>"
    148  
    149                          id="upfrm<%#DataBinder.Eval(Container, "DataItem.brief_ID")%>"
    150  
    151                          src="<%# IIf(CBool(DataBinder.Eval(Container, "DataItem.IsBriefUploaded")), "", Request.Url.ToString() & "?MODE=UPLOAD&ID=" & DataBinder.Eval(Container, "DataItem.brief_ID")) %>" 
    152  
    153                          style="width:240px;height:25px;<%#IIf(CBool(DataBinder.Eval(Container, "DataItem.IsBriefUploaded")), "display:none;", "")%>" 
    154  
    155                          scrolling="no" 
    156  
    157                          frameborder="0"
    158  
    159                          >
    160  
    161                          </iframe>
    162  
    163                          
    164  
    165                    <%#IIf(CBool(DataBinder.Eval(Container, "DataItem.IsBriefUploaded")), "File exists", "")%>
    166  
    167   
    168  
    169              </ItemTemplate>
    170  
    171        </asp:TemplateField>
    172  
    173        
    174  
    175        </Columns>
    176  
    177        </asp:GridView>
    178  
    179        
    180  
    181        <asp:Button 
    182  
    183              ID="btnStartUpload" 
    184  
    185              Text="Start Briefs Upload" 
    186  
    187              OnClientClick="StartFilesUpload();return false;" 
    188  
    189              runat="server" 
    190  
    191              />
    192  
    193   
    194  
    195  </asp:Panel>
    196  
    197   
    198  
    199  <asp:Panel ID="pnlUpload" 
    200  
    201        style=";top:0;left:0;" 
    202  
    203        runat="server">
    204  
    205   
    206  
    207        <asp:Image id="imgLoader" ImageUrl="uploading.gif" 
    208  
    209              style="display:none;margin:5px;" runat="server" />
    210  
    211        
    212  
    213        <asp:Panel id="pnlUploadControl" runat="server">
    214  
    215        
    216  
    217              <asp:FileUpload ID="upFile" runat="server" />
    218  
    219              
    220  
    221              <asp:Button ID="btnUpload" 
    222  
    223                    style="visibility:hidden;" runat="server" />
    224  
    225              
    226  
    227        </asp:Panel>
    228  
    229   
    230  
    231        <p style="margin:5px;">
    232  
    233              <asp:Label ID="lblMessage" runat="server" />
    234  
    235        </p>
    236  
    237        
    238  
    239  </asp:Panel>
    240  
    241   
    242  
    243   
    244  
    245   
    246  
    247   
    248  
    249  </form>
    250  
    251  </body>
    252  
    253  </html>
    254  
    255  
    
     
     

     Stored Procedures

          Upload File

    CREATE PROCEDURE dbo.spUploadFile
      @doc_title VARCHAR(200),
      @doc IMAGE,
      @doc_type VARCHAR(50),
      @doc_length INT
    AS
      INSERT uploaded_files(doc_title, doc, doc_type, doc_length)
      VALUES (@doc_title, @doc, @doc_type, @doc_length)
      SELECT SCOPE_IDENTITY()
    GO

         Update Table

    CREATE PROCEDURE [dbo].[spUpdateBriefFile]
      @brief_ID INT,
      @brief_file_type INT,
      @doc_ID INT
    AS
        UPDATE briefs SET IsBriefUploaded = 1
        WHERE brief_ID = @brief_ID
    GO

    Sunday, June 8, 2008 11:50 PM

All replies

  • User1233494541 posted

    Where exactly do you see the problem?

    Wednesday, June 11, 2008 9:24 PM