how to call scalar function sqlserver in asp.net.cs

Anil Sharma

6y788.NET

hi expert

please guide me how i can call the function result in asp.net program in a text field

i try as below but result is null

SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.check_farmer_loan(@farmer_cd)", connect);

// cmd.CommandType=CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@farmer_cd",party_code.Text);

SqlDataAdapter db = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

db.Fill(dt);

loan_amt.Text = dt.Rows[0][0].ToString();

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Accepted answer
Hi Anil,

Please use below code,
SqlCommand cmd = new SqlCommand("SELECT dbo.check_farmer_loan(@farmer_cd)", connect);
cmd.CommandType=CommandType.Text;
cmd.Parameters.AddWithValue("@farmer_cd",party_code.Text);
SqlDataAdapter db = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
db.Fill(dt);
loan_amt.Text = dt.Rows[0][0].ToString();
Anil Sharma6y ago
Jignesh ji thanks a lot for help, it work fine
Anil Sharma6y ago
sir my function is
ALTER FUNCTION [dbo].[check_farmer_loan]
(
-- Add the parameters for the function here
@farmer_cd nvarchar(6)
)
RETURNS numeric AS
BEGIN
-- Declare the return variable here
DECLARE @total_loan numeric;
DECLARE @vloan_deduct numeric;
-- Add the T-SQL statements to compute the return value here
SELECT @total_loan = (SELECT SUM(isnull(DEBIT_AMOUNT,0)-isnull(CREDIT_AMOUNT,0))
total_loan
FROM FA_BALANCE_MASTER
WHERE ACCOUNT_CODE IN (211068,211069) AND PARTY_CODE = @farmer_cd AND YYYYMM >= (Select year(start_date) from fa_company_detail ));
-- Return the result of the function

if @total_loan is null
begin
SET @total_loan = 0;
end;
if @total_loan > 0
begin
select @vloan_deduct = ( SELECT sum(ADJUSTED_AMOUNT)
loan_deduct
FROM CA_LOAN_DEDUCTION
WHERE ( isnull(voc_auth_flag,'N')= 'N'
AND PARTY_CODE = @farmer_cd) )
set @total_loan = @total_loan - @vloan_deduct;
end
RETURN @total_loan;

END
now i try as per code given by ur self
string sqlQuery = "SELECT * FROM dbo.check_farmer_loan where farmer_cd='" + party_code.Text + "';";
using (SqlConnection connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["nahar_canemgmnt"].ConnectionString.ToString()))
{
SqlCommand command =
new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
loan_amt.Text = reader[0].ToString();
}
reader.Close();
}

also try with select total_loan but result is same cursor not went in while (reader.Read()) at time of debug my be i wtrite any thing wrong, pls see and suggest me what change to need
Navaneeth Krishnan6y ago
Please use SqlDataReader
string sqlQuery= "SELECT column1,column2 FROM dbo.check_farmer_loan where farmer_cd='" +party_code.Text +"';";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
loan_amt.Text = reader[0].ToString();
}
reader.Close();
}