.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> VB.Net >> Post New QuestionBookmark and Share Subscribe to Forum

If condition not working

Posted By: Catherine     Posted Date: April 12, 2011    Points:5   Category :VB.Net
Hi i have this stored procedure. this works well in sql. when i call this stored procedure in vb.net does not work properly. when i give "encashment" it shows both result. and when i give "sales" it returns no data in datasource.

ALTER PROCEDURE [dbo].[sp_Encash]

(
@strbranchname varchar(40),
@type varchar(10),
@fromdate datetime,
@todate datetime
)

AS
BEGIN
SET NOCOUNT ON;
if @type='Encashment'

BEGIN

select b.strBranchName as Branch_Name, e.intrecordid as Record_Id,convert(varchar,e.datTransDate,103) as Transcation_Date,
case when f.strInstrumentType ='TC' then sum(f.intQty*f.intDenomination*f.intTransactionRate) else sum(f.intQty*f.intDenomination*f.intReferenceRate) end as Amount
from tblfxrows f inner join tblencashments e on f.intencashmentid=e.intRecordID
inner join tblbranch b on e.intBranchCode=b.intBranchCode
where b.strbranchname=@strbranchname
and e.dattransdate between @fromdate and @todate
group by b.strBranchName,e.intrecordid, e.dattransdate,f.strInstrumentType

END

if @type='Sales'

BEGIN

select b.strBranchName as Branch_Name, s.intrecordid as Record_Id,convert(varchar,s.datTransDate,103) as Transcation_Date,
case when f.strInstrumentType ='TC' then sum(f.intQty*f.intDenomination*f.intTransactionRate) else sum(f.intQty*f.intDenomination*f.intTransactionRate) end as Amount
from tblfxrows f inner join tblsales s on f.intsalesid= s.intRecordID
inner join tblbranch b on s.intBranchCode=b.intBranchCode where b.strbranchname=@strbranchname
and s.dattransdate between @fromdate and @todate
group by b.strBranchName,s.intrecordid,s.dattransdate,f.strInstrumentType
END

END


--exec [sp_Encash] 'Jaipur','Encashment','02-03-2008', '02-12-2008'
--exec [sp_Encash] 'Jaipur','sales','05-04-2010', '05-10-2010'


Here is my vb.net code:



Protected Sub btngenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btngenerate.Click
connectionString = ""
Dim connection As OleDbConnection = New OleDbConnection(connectionString)

Dim strtodate As String
Dim strfromdate As String
Dim strbrname As String
Dim strtype As String

strbrname = ddlbranch.SelectedItem.Text
strtype = ddltype.SelectedItem.Text
strfromdate = txtfdate.Text
strtodate = txttdate.Text
Dim cmd As New OleDbCommand("sp_Encash", connection)
connection.Open()
' cmd.CommandType = CommandType.Text
cmd.CommandType = CommandType.StoredProcedure
Dim pstrbranchname As New OleDbParameter("@strbranchname", OleDbType.VarChar, 20)
ddlbranch.DataValueField = Convert.ToString(pstrbranchname)
Dim pfromdate As New OleDbParameter("@fromdate", OleDbType.VarChar, 20)

' txtfdate.Text = Convert.ToString(pfromdate)
Dim ptodate As New OleDbParameter("@todate", OleDbType.VarChar, 20)
' txttdate.Text = Convert.ToString(ptodate)
Dim ptype As New OleDbParameter("@type", OleDbType.VarChar, 10)
ddltype.DataValueField = Convert.ToString(ptype)
' DateAndTime.Now.ToString("dd\mm\yyyy")

pstrbranchname.Value = strbrname
ptype.Value = strtype
pfromdate.Value = strfromdate
ptodate.Value = strtodate

cmd.Parameters.Add(pstrbranchname)
cmd.Parameters.Add(ptype)
cmd.Parameters.Add(pfromdate)
cmd.Parameters.Add(ptodate)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
cmd.CommandTimeout = 0
Dim ds As New DataSet()
da.Fill(ds)

GridView1.DataSource = ds

GridView1.DataBind()
GridView1.EmptyDataText = "No Records found"
connection.Close()

End Sub

Responses
Author: Mahesh             
Posted Date: April 13, 2011     Points: 20   

hi,
i just wants to know when you execute this in sql,

exec [sp_Encash] 'Jaipur','sales','05-04-2010', '05-10-2010'

weather it returns result...?

and also check that you are providing varchar type in vb code for from date and to date parameters you should use datetime there.

Regards
Mahesh




Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend