如何使用ASP.NET执行存储过程？ [英] How to execute stored procedure using ASP.NET?

问题描述

嗨

我有问题我无法在asp.net中执行MSsql存储过程



工作正常并给出了在Mssql服务器控制台中执行时返回值。

我在asp.net页面中的代码

Hi
I have issue that I could not execute MSsql stored procedure in asp.net

It is working fine and gives the return value when executing in Mssql server console.
My code in asp.net page

Dim strConnString As String = ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "usp_Z_SO_Details"
cmd.Parameters.AddWithValue("@DATEFROM", Me.txtDateFrom.Text)
cmd.Parameters.AddWithValue("@DATETO", Me.txtDateTo.Text)
cmd.Parameters.AddWithValue("@CATEGORY ", Me.cboCategory.SelectedValue)
cmd.Connection = con
con.Open()
cmd.ExecuteReader()
con.Close()
con.Dispose()

我存储的程序

My stored Procedure

INSERT INTO [dbo].[RPT_SO_Details](SoNo, SDate, SDay, DealerCode, Dealer, DistCode, DistName, Area, 
		                                   RouteCode, [Route], CatCode, Category, ZoneCode, ZoneDesc, NoStand, 
										   Nocopies)
		SELECT SoNo, SDate, SDay, DealerCode, Dealer, DistCode, DistName, Area, 
		                                   RouteCode, [Route], CatCode, Category, ZoneCode, ZoneDesc, NoStand, 
										   Nocopies
		FROM SUNMedia.dbo.StandingOrder
		
		WHERE SDATE >= @DATEFROM AND SDATE <= @DATETO AND CATEGORY = @CATEGORY 
		ORDER BY CATEGORY

		SELECT * FROM [dbo].[RPT_SO_Details] ORDER BY ROUTECODE,sono

请告诉我我错在哪里



谢谢



Maideen



我尝试过：

Pls advice me where i did wrong

Thank you

Maideen

What I have tried:

Dim strConnString As String = ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "usp_Z_SO_Details"
        cmd.Parameters.AddWithValue("@DATEFROM", Me.txtDateFrom.Text)
        cmd.Parameters.AddWithValue("@DATETO", Me.txtDateTo.Text)
        cmd.Parameters.AddWithValue("@CATEGORY ", Me.cboCategory.SelectedValue)
        cmd.Connection = con
        con.Open()
        cmd.ExecuteReader()
        con.Close()
        con.Dispose()

推荐答案

从做三件事开始：

1）不要使用ExecuteReader - 你根本就没有使用结果，因为您丢弃它返回的SqlDataReader。为什么SP代码完全执行最终选择，因为这是一个INSERT INTO SELECT语句 - 您的代码将返回所有行...

2）检查您的用户输入：发送文本日期SQL是一个坏主意。使用 DateTime.TryParse [ ^ ]将用户的intoputs转换为DateTiume值 - 向用户报告问题 - 并将DateTime值作为参数直接传递给SQL。传递字符串意味着SQL必须猜测日期的格式，并且它并不总是与用户共享相同的区域设置。

3）我知道这很明显，但检查SDATE列的列数据类型 - 如果它是字符串而不是DATE，DATETIME或DATETIME2那么这将导致日期比较的主要问题。你会惊讶于有多少人认为NVARCHAR是一个很好的数据类型来存储日期，它总是会在以后给它们带来真正的问题。

Start by doing three things:
1) Don't use ExecuteReader - you aren't using the results at all, because you discard the SqlDataReader that it returns. Why does you SP code do the final select at all, given that this is an INSERT INTO SELECT statement - your code would return all the rows...
2) Check your user inputs: Sending text dates to SQL is a bad idea. Use DateTime.TryParse[^] to convert teh user intoputs to DateTiume values - reporting problems to the user - and pass the DateTime values directly to SQL as parameters. Passing strings means that SQL has to "guess" what format the date is in, and it doesn't always share the same locale as your user.
3) I know this is damn obvious, but check the column datatype of your SDATE column - if it's a string rather than a DATE, DATETIME, or DATETIME2 then that will cause major problems with date comparisons. You'd be surprised how many people think that NVARCHAR is a good datatype to store dates in and it always gives them real problems later on.

请参考使用ASP.NET存储过程 [ ^ ]

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