无法使用我的存储过程. [英] Can't use my stored procedure.

问题描述

我已经在SQL Server 2008中创建了一个存储过程，但是，当我尝试在其中使用C#(Visual Studio)时，它不返回任何行(他不返回任何内容).

连接字符串正确，因为

这有效:

I''ve made a stored procedure in SQL server 2008, however, when i try to use in it C# (visual studio), it returns no rows (he doesn''t return anything).

The Connection string is correct becouse,

This works:

SqlConnection connection = new SqlConnection("Data Source=LT004512\\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=SSPI;");
SqlCommand command = new SqlCommand();
DataTable data = new DataTable();
command.CommandText = "SELECT * FROM item WHERE item_ID = @hoi";
command.Connection = connection;
command.Parameters.AddWithValue("@hoi", 1);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data.Load(reader);
connection.Close();
DeGridView.DataSource = data;

但这不是:

but THIS doesn''t:

SqlConnection connection = new SqlConnection("Data Source=LT004512\\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=SSPI;");

      SqlCommand command = new SqlCommand("usp_GetItemWithID",connection);
      command.CommandType = CommandType.StoredProcedure;
      connection.Open();
      command.Parameters.AddWithValue("@hetid", 1);
      DataTable InstellingTabel = new DataTable();
      SqlDataReader informatie = command.ExecuteReader();
      connection.Close();
      InstellingTabel.Load(informatie);
      DeGridView.DataSource = InstellingTabel;

我的存储过程如下所示:

My stored procedure looks like this:

USE [TestDatabase]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetItemWithID]    Script Date: 03/21/2011 11:31:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name:  usp_GetItemWithID
Description:  Gets the item with the requested ID
Author:  Amateurgrammer
Modification Log: None
Description                  Date         Changed By
Created procedure            3/21/201           None
*/
ALTER PROCEDURE [dbo].[usp_GetItemWithID]
@hetid int
AS
SELECT * FROM item
WHERE item_ID=@hetid

但是，当我在SQL Server中执行存储过程时，它工作得很好，所以我认为这不是问题.

很抱歉，我提供了大量信息，但我在这里是新手，不熟悉有关此问题的问题.

But when i execute the stored procedure in SQL server it works just fine, so i think that won''t be the problem.

Sorry for the S***load of information i gave, but i''m new here and unfamiliar with asking questions about this stuff.

推荐答案

代码，但是您是否尝试在加载数据表后移动

Just skimmed through the code, but have you tried moving the

connection.Close();

?

您必须先从SqlDataReader对象读取数据后才能关闭连接.如果尚未将代码放在try/catch/finally块中，然后在finally部分中关闭连接.

You can''t call connection close until after you''ve read the data from the SqlDataReader object. If you haven''t already, put your code in a try/catch/finally block, and close the connection in the finally portion.

SQL事件探查器可以帮助您跟踪对您的请求数据库.

SQL Profiler can help you to track requests to your database.

这篇关于无法使用我的存储过程.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！