Correct syntax in stored procedure and method using MsSqlProvider.ExecProcedure? [migrated]
- by Dudi
I have problem with ASP.net and database prcedure
My procedure in mssql base
USE [dbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[top1000]
@Published datetime output,
@Title nvarchar(100) output,
@Url nvarchar(1000) output,
@Count INT output
AS
SET @Published = (SELECT TOP 1000 dbo.vst_download_files.dfl_date_public FROM dbo.vst_download_files
ORDER BY dbo.vst_download_files.dfl_download_count DESC )
SET @Title = (SELECT TOP 1000 dbo.vst_download_files.dfl_name FROM dbo.vst_download_files
ORDER BY dbo.vst_download_files.dfl_download_count DESC)
SET @Url = (SELECT TOP 1000 dbo.vst_download_files.dfl_source_url FROM dbo.vst_download_files
ORDER BY dbo.vst_download_files.dfl_download_count DESC)
SET @Count = (SELECT TOP 1000 dbo.vst_download_files.dfl_download_count FROM dbo.vst_download_files
ORDER BY dbo.vst_download_files.dfl_download_count DESC)
And my proceduer in website project
public static void Top1000()
{
List<DownloadFile> List = new List<DownloadFile>();
SqlDataReader dbReader;
SqlParameter published = new SqlParameter("@Published", SqlDbType.DateTime2);
published.Direction = ParameterDirection.Output;
SqlParameter title = new SqlParameter("@Title", SqlDbType.NVarChar);
title.Direction = ParameterDirection.Output;
SqlParameter url = new SqlParameter("@Url", SqlDbType.NVarChar);
url.Direction = ParameterDirection.Output;
SqlParameter count = new SqlParameter("@Count", SqlDbType.Int);
count.Direction = ParameterDirection.Output;
SqlParameter[] parm = {published, title, count};
dbReader = MsSqlProvider.ExecProcedure("top1000", parm);
try
{
while (dbReader.Read())
{
DownloadFile df = new DownloadFile();
//df.AddDate = dbReader["dfl_date_public"];
df.Name = dbReader["dlf_name"].ToString();
df.SourceUrl = dbReader["dlf_source_url"].ToString();
df.DownloadCount = Convert.ToInt32(dbReader["dlf_download_count"]);
List.Add(df);
}
XmlDocument top1000Xml = new XmlDocument();
XmlNode XMLNode = top1000Xml.CreateElement("products");
foreach (DownloadFile df in List)
{
XmlNode productNode = top1000Xml.CreateElement("product");
XmlNode publishedNode = top1000Xml.CreateElement("published");
publishedNode.InnerText = "data dodania";
XMLNode.AppendChild(publishedNode);
XmlNode titleNode = top1000Xml.CreateElement("title");
titleNode.InnerText = df.Name;
XMLNode.AppendChild(titleNode);
}
top1000Xml.AppendChild(XMLNode);
top1000Xml.Save("\\pages\\test.xml");
}
catch
{
}
finally
{
dbReader.Close();
}
}
And if I made to MsSqlProvider.ExecProcedure("top1000", parm); I got String[1]: property Size has invalid size of 0.
Where I shoudl look for solution? Procedure or method?
