title: VB.Net SQL Stored Procedure cache / Parameter Count Value?
body: <p>I'm pretty new to Web Development so this (hopefully) may have an easy answer.</p>
  
  <p>Basically, I have a stored procedure that originally took two parameters. I changed it to only require one. Now I send a request from VB.Net to use the procedure with the one parameter required.</p>
  
  <blockquote>
    <p>Parameter count does not match Parameter Value count.</p>
  </blockquote>
  
  <p>So I add the old second parameter back to the request, even though it's not part of the actual stored procedure anymore.</p>
  
  <blockquote>
    <p>Procedure or function xw_GetProducts_by_Brand has too many arguments specified.</p>
  </blockquote>
  
  <p>So I declare the old parameter in SQL again, even though the query doesn't use it. Sending both parameters from VB.Net</p>
  
  <blockquote>
    <p>Procedure or function 'xw_GetProducts_by_Brand' expects parameter '@AccountNo', which was not supplied.</p>
  </blockquote>
  
  <p>So lastly, I leave both parameters (String/nvarchar and Int) declared in SQL but remove the unneeded int from VB.Net, just in case.</p>
  
  <blockquote>
    <p>Parameter count does not match Parameter Value count.</p>
  </blockquote>
  
  <p>So... I have a query that was working fine until I removed the unused int parameter and now it won't work no matter what parameter set up I use. Is there a cache time on stored procedures (either in VB/ASP.Net or SQL Server) that would be screwing up my page?</p>
  
  <p>I don't think the actual query itself is a problem because everything worked until I removed the line <code>@AccountNo int</code> from the declarations.</p>
  
  <p><strong>VB.Net</strong>:</p>
  
  <pre><code>Dim data1 As DataTable = SqlHelper.ExecuteDataset(
      System.Configuration.ConfigurationManager.AppSettings("dbConn"), 
      "xw_GetStockCodesInBrand", brand.Value
      ).Tables(0)
  
  Dim stockString As String = "'"
  
  For Each row As DataRow In data1.Rows
      stockString += row("StockCode") + "','"
  Next
  
  Dim stockStringFinal As String = 
  stockString.Substring(0, stockString.Length - 2)
  
  Dim dt As DataTable = SqlHelper.ExecuteDataset(
      System.Configuration.ConfigurationManager.AppSettings("dbConn"), 
      "xw_GetProducts_by_Brand", stockStringFinal
      ).Tables(0)
  </code></pre>
  
  <p>Data one gets the string of stockcode values, they get trimmed by the substring and submitted as a parameter for DT(datatable).</p>
  
  <p>The second <code>SqlHelper</code> request is the issue, it doesn't accept an int parameter and won't work without 2 parameters.</p>
  
  <p><strong>SQL query</strong>:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER  PROCEDURE [dbo].[xw_GetProducts_by_Brand3] 
      @ProductID nvarchar(max)
  AS
      SELECT 
          stock_items.Stockcode, STOCK_GROUPS.XW_URL as stockgroup,
          STOCK_GROUP2S.XW_URL as stockgroup2, 
          STOCK_MAINGROUP.XW_URL as stockmaingroup, 
          stock_items.Stockcode as pID, stock_items.description as pName, 
          stock_web.sales_html as pdescription, 
          stock_web.picture_url as pImage, 
          stock_web.picture_url as pLargeimage, 
          stock_items.sellprice1 as pPrice, 
          stock_items.SELLPRICE1, stock_items.SELLPRICE2, 
          stock_items.SELLPRICE3, stock_items.SELLPRICE4, 
          stock_items.SELLPRICE5, stock_items.SELLPRICE6, 
          stock_items.SELLPRICE7, stock_items.SELLPRICE8, 
          stock_items.SELLPRICE9, stock_items.status as itemtype, 
          stock_items.SELLPRICE10 as pListPrice, 
          stock_items.x_totalstock as pInStock, 
          stock_items.x_webhits as pHits, 
          stock_items.ISACTIVE, stock_items.WEB_SHOW, 
          stock_items.X_WebBlub as X_WebBlurb, 
          stock_items.x_webpromo as X_PROMOPAGE, 
          stock_items.last_updated as lastupdated, 
          stock_items.x_stockleveloverride, 
          isnull(stock_items.Restricted_item,'N') as Restricted_item 
      FROM 
          stock_items 
      LEFT OUTER JOIN
          STOCK_WEB ON (stock_items.Stockcode = STOCK_WEB.Stockcode) 
      LEFT OUTER JOIN 
          STOCK_GROUPS ON (STOCK_GROUPS.GROUPNO = STOCK_ITEMS.STOCKGROUP)  
      LEFT OUTER JOIN 
          STOCK_GROUP2S ON (STOCK_GROUP2S.GROUPNO = STOCK_ITEMS.STOCKGROUP2) 
      LEFT OUTER JOIN 
          STOCK_MAINGROUP ON (STOCK_MAINGROUP.GROUPNO = STOCK_GROUPS.XW_MAINGROUP)
      WHERE 
          stock_items.ISACTIVE = 'Y'  
          AND stock_web.picture_url IS NOT NULL 
          AND stock_items.Stockcode IN (@ProductID)
  </code></pre>
  
  <p><strong>Fix</strong></p>
  
  <p>I got around this issue by renaming my stored procedure and pretending it's a whole new procedure. If anyone knows the actual cause behind this issue I'd still like to get it fixed.</p>