我的数据集未返回我的表值 [英] my dataset does not return my table value

问题描述

先生，
任何人都可以帮助我.

使用sql参数传递和使用存储过程搜索条件时，MY Dataset不返回值.当存储过程运行时，值将返回，但是当它通过asp.net页时，它将不返回值.

''''''''''''''''''''''''''''''''''''''''这是带有VB的ASP.NET代码.NET''''''''''''''''''''''''''

Sir,
Any one please help me.

MY Dataset does not return value while using sql parameter passing and using stored procedure for searching criteria. When stored procedure is run then value will return but when it will pass through asp.net page then it will not return the value.

''''''''''''''''''''''''''''''''''''''''''THIS IS ASP.NET CODE WITH VB.NET''''''''''''''''''''''''''

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim MyDs2 As New DataSet
        MyDs2.Clear()
        MyCon = New SqlConnection("Persist Security Info=False;User ID=sa;Pwd=123456;Initial Catalog=LMS;")
        MyCmd = New SqlCommand("LibSp_Search_Bill", MyCon)
        MyCmd.CommandType = CommandType.StoredProcedure
        MyCon.Open()
        MyCmd.Parameters.Add("@BBookLet", SqlDbType.NVarChar).Value = Trim(txtBookLet.Text).ToString
        MyCmd.Parameters.Add("@BReceiptNo", SqlDbType.NVarChar).Value = Trim(txtRecptNo.Text).ToString
        MyCmd.Parameters.Add("@BRegdDate", SqlDbType.NVarChar).Value = Trim(BDPRegdDate.SelectedDate).ToString
        MyCmd.Parameters.Add("@BStudentName", SqlDbType.NVarChar).Value = Trim(txtStudentName.Text).ToString
        MyCmd.Parameters.Add("@BUniversity", SqlDbType.NVarChar).Value = Trim(txtUniversity.Text).ToString
        MyCmd.Parameters.Add("@BCourses", SqlDbType.NVarChar).Value = Trim(txtCourses.Text).ToString
        MyCmd.Parameters.Add("@BSemester", SqlDbType.NVarChar).Value = Trim(txtSemester.Text).ToString
        MyCmd.Parameters.Add("@BPayMode", SqlDbType.NVarChar).Value = Trim(txtPayMode.Text).ToString
        MyCmd.ExecuteNonQuery()
        MyAdp.SelectCommand = MyCmd
        MyAdp.Fill(MyDs2)
        If MyDs2.Tables(0).Rows.Count > 0 Then
            CountRowData2 = MyDs2.Tables(0).Rows.Count
            dgDocProdDetals.DataSource = MyDs2
            dgDocProdDetals.DataBind()
        Else
        End If
        lblMsgCount.Text = "Total " & CountRowData2.ToString & " Records Found !"
        MyCon.Close
        'BindGrid()
    End Sub

''''''''''''''''''''此存储过程''''''''''''''''''''''''''

''''''''''''''''''''THIS IS STORED PROCEDURE''''''''''''''''''''''''''

create proc LibSp_Search_Bill 
(
	@BBookLet NVARCHAR(50)='',
	@BReceiptNo NVARCHAR(50)='',
	@BRegdDate NVARCHAR(50)='',
	@BStudentName NVARCHAR(200)='',
	@BUniversity NVARCHAR(100)='',
	@BCourses NVARCHAR(100)='',
	@BSemester NVARCHAR(50)='',
	@BPayMode NVARCHAR(50)=''
) 
as 
declare @sqlstr as varchar(max) 
SET @sqlstr='' 
Create Table #MstSearch 
(
	CID bigint Identity(1,1) Primary Key,
	BookLet NVARCHAR(50),
	ReceiptNo NVARCHAR(50),
	RegdDate NVARCHAR(50),
	StudentName NVARCHAR(200),
	University NVARCHAR(100),
	Courses NVARCHAR(100),
	Semester NVARCHAR(50),
	PayMode NVARCHAR(50)
)
set @sqlstr='Insert into #MstSearch
(BookLet,ReceiptNo,RegdDate,StudentName,University,Courses,Semester,PayMode)
SELECT BB.BOOKLET,BB.RECEIPTNO,BB.RDATE,BB.STUDENTNAME,BB.UNIVERSITY,BB.COURSES,BB.SEMESTER,BB.PAYMENTMODE
FROM BILL_BOOK BB 
WHERE
('''+@BBookLet+''' = '''' OR ( '''+@BBookLet+''' <> '''' AND isnull(BB.BOOKLET,'''') = '''+@BBookLet+'''))    
AND ('''+@BReceiptNo+''' = '''' OR ( '''+@BReceiptNo+''' <> '''' AND isnull(BB.RECEIPTNO,'''') = '''+@BReceiptNo+'''))  
AND ('''+@BRegdDate+''' = '''' OR ( '''+@BRegdDate+''' <> '''' AND isnull(BB.RDATE,'''') = '''+@BRegdDate+'''))  
AND ('''+@BStudentName+''' = '''' OR ( '''+@BStudentName+''' <> '''' AND isnull(BB.STUDENTNAME,'''') = '''+@BStudentName+'''))  
AND ('''+@BUniversity+''' = '''' OR ( '''+@BUniversity+''' <> '''' AND isnull(BB.UNIVERSITY,'''') = '''+@BUniversity+'''))  
AND ('''+@BCourses+''' = '''' OR ( '''+@BCourses+''' <> '''' AND isnull(BB.COURSES,'''') = '''+@BCourses+'''))  
AND ('''+@BSemester+''' = '''' OR ( '''+@BSemester+''' <> '''' AND isnull(BB.SEMESTER,'''') = '''+@BSemester+'''))  
AND ('''+@BPayMode+''' = '''' OR ( '''+@BPayMode+''' <> '''' AND isnull(BB.PAYMENTMODE,'''') = '''+@BPayMode+''')) 
'
exec (@sqlstr)
print @sqlstr
SELECT * FROM #MstSearch
--DROP TABLE #MstSearch
IF @@Error > 0
BEGIN
 Drop table #MstSearch
 Return
END
ELSE
BEGIN
 Drop table #MstSearch
END

表格结构如下''''''''''''''''''''''

''''''''''''''''''''''''''''''''TABLE STRUCTURE IS BELOW''''''''''''''''''''''''''''''''''''''

SNO	       bigint	
BOOKLET	       nvarchar(50)	
RECEIPTNO      nvarchar(50)	
RDATE	       nvarchar(50)	
STUDENTNAME    nvarchar(200)	
UNIVERSITY     nvarchar(100)	
COURSES	       nvarchar(100)	
BATCH	       nvarchar(50)	
SEMESTER       nvarchar(50)
PAYMENTMODE    nvarchar(50)

推荐答案

首先猜测是条件或调用中使用的值有问题.

将SQL语句带到SSMS，然后将您已使用的值添​​加到其中，然后尝试执行.这可能有助于您缩小问题范围.如果未返回任何行，请尝试注释掉条件并排除潜在的问题来源.

First guess would be that there is something wrong with either conditions or the values used in the call.

Take the SQL statement to SSMS and add the values you have used into it and try to execute. This probably helps you to narrow the problem. If no rows are returned, try commenting out conditions and to exclude potential problem sources.

这篇关于我的数据集未返回我的表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！