Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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)
Posted 2-Jan-12 21:04pm
Edited 2-Jan-12 21:09pm
Mehdi Gholam253.6K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Kornfeld Eliyahu Peter 275
1 Maciej Los 214
2 Sergey Alexandrovich Kryukov 200
3 King Fisher 185
4 OriginalGriff 163
0 OriginalGriff 6,223
1 DamithSL 4,724
2 Maciej Los 4,196
3 Kornfeld Eliyahu Peter 3,894
4 Sergey Alexandrovich Kryukov 3,496


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 3 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100