0

hi all i am having a problem with returning a value of a sql server scalar valued function in a specific datagridview column, the code is below:-

1- sql function:-

CREATE FUNCTION [dbo].[ToLocationID](@ID int) 

RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @LocationName varchar(50)

    -- Add the T-SQL statements to compute the return value here
    SELECT @LocationName=dbo.Location.LocationName FROM Location WHERE LocationID=@ID

    -- Return the result of the function
    RETURN @LocationName

END

2-C# Method to execute the function:-

public static string MyMethod(int ID) {
            string SqlConnStr = globals.ServerConnStr;
            SqlConnection SqlConn = new SqlConnection(SqlConnStr);
            try
            {
                SqlConn.Open();
                SqlCommand cmd = new SqlCommand("SELECT dbo.ToLocationID(@ID)", SqlConn);
                cmd.Parameters.Add(new SqlParameter("@ID", ID));
                return  cmd.ExecuteScalar().ToString();
            }
            finally
            {
                SqlConn.Close();
            }
        }

and here is the gridview code:-

//GetAllTransfers is a method that executes a stored procedure to return most of the //columns like the column["Date"] below
    dataGridViewList.DataSource = Data.GetAllTransfers();
    dataGridViewList.Columns["Date"].HeaderText = "Date";
    dataGridViewList.Columns.Add("ToLocationID", "To Location");
    dataGridViewList.Columns["ToLocationID"].DisplayIndex = 5;
    dataGridViewList.Columns["ToLocationID"].Tag = Data.MyMethod(1);

I just want to fill the column "ToLocationID" with the result of the scalar function from the method "MyMethod", what is wrong with this code?

user3804193
  • 113
  • 4
  • 11
  • What error do you get? – DatRid Sep 03 '14 at 13:26
  • Scalar functions are notoriously slow. Why not just add this additional piece of data to your GetAllTransfers method? Or change this to an inline table valued function. – Sean Lange Sep 03 '14 at 13:27
  • @DatRid I'm not getting any results although the functions works well i am not sure if i am calling it the right way. – user3804193 Sep 03 '14 at 13:29
  • try putting a breakpoint @ return cmd.ExecuteScalar().ToString(); and see if you are getting data back from the database. – CharlesMighty Sep 03 '14 at 13:29
  • @SeanLange how can i add it to GetAllTransfers method? – user3804193 Sep 03 '14 at 13:31
  • @CharlesMighty I tried that and the condition is (no condition) and the hit count is (break always currently 0) – user3804193 Sep 03 '14 at 13:34
  • so that means you are not calling the function correctly, I would try putting the function in stored procedure and then call the stored procedure – CharlesMighty Sep 03 '14 at 13:36
  • Well presumably that method does something to retrieve data from the database? Add another property to your object and add another column to your procedure that populates your Transfer objects. – Sean Lange Sep 03 '14 at 13:36
  • I would drop that function like a hot potato. At the very least turn it into an iTVF instead of a scalar. – Sean Lange Sep 03 '14 at 13:37
  • please if you can fix the code i will be very thankful cuz i am really new to C# – user3804193 Sep 03 '14 at 13:39

0 Answers0