Return string from SQL Procedure to C#

Question

I have a SQL create procedure query like this

    USE KOST
    GO
    --Cabang means Branch in Bahasa.

CREATE PROCEDURE login_pro
        --received parameter
        @NoIDStaff VARCHAR(5),
        @Password VARCHAR (50)


    AS

    --sent parameter
    DECLARE @status INT
    DECLARE @IDCabang VARCHAR(5)

    --owner login
    IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('B%'))
            SET @status=1

    --staff login
    ELSE IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            BEGIN
            SELECT @IDCabang=IDCabang FROM MsStaff
            SET @status=2
            SET @IDCabang = (select IDCabang FROM Msstaff where @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            END

    --neither of them login
    ELSE
            set @status=0

    select @status, @IDCabang
    GO

The query returns value of the status but not the IDCabang. What should I do so the value of the IDCabang will be returned?

this is my C# Code:

 private void submit_Click(object sender, EventArgs e)
    {
        NoIDStaff = new SqlParameter();
        Password = new SqlParameter();
        SqlConnection con = new SqlConnection(strCon);

        com = new SqlCommand();
        com.Connection = con;
        con.Open();

        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "login_pro";

        NoIDStaff.SqlDbType = SqlDbType.VarChar;
        NoIDStaff.Size = 50;
        NoIDStaff.ParameterName = "@NoIDStaff";
        NoIDStaff.Value = username.Text.ToString();
        NoIDStaff.Direction = ParameterDirection.Input;

        Password.SqlDbType = SqlDbType.VarChar;
        Password.Size = 50;
        Password.ParameterName = "@Password";
        Password.Value = password.Text.ToString();
        Password.Direction = ParameterDirection.Input;

        com.Parameters.Add(NoIDStaff);
        com.Parameters.Add(Password);



        int status;
        string IDCabang;
        status = Convert.ToInt16(com.ExecuteScalar());
        IDCabang = Convert.ToString(com.ExecuteScalar());


        //owner login
        if (status == 1)
        {
            this.Hide();
            frm = new Form2();
            frm.Show();
        }

        //staff login
        else if (status == 2)
        {
            this.Hide();
            frm4 = new Form4(IDCabang);
            frm4.Show();
        }


        else if (username.Text.Equals(""))
        {
            MessageBox.Show("Username Must be Filled!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        else if (password.Text.Equals(""))
        {
            MessageBox.Show("Password Must be Filled!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        else
        {
            MessageBox.Show("Invalid Username or Password", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        con.Close();
    }

I want to receive IDCabang as string, so I can send it to Form4. But it seems like, IDCabang doesn't return any value.

What does "does not return" mean? How are you referencing these columns in your C# code, since you didn't give them aliases? Why aren't you using proper output parameters? — Aaron Bertrand, Apr 11 '13 at 16:33
There are three paths your code can take. If, Else if, Else. You are only setting the value of @IDCabang in the Else If path, so if it does not go through this path it will not get set. — Andy Nichols, Apr 11 '13 at 16:34
In two of your conditions, you don't attempt to set `@IDCabang` to anything which means that it's going to return as a DBNull value if it returns at all, depending on how your C# code is set up. What does your C# code look like when you attempt to access your returned values? — Jason Whitish, Apr 11 '13 at 16:35
@jasonwhisman I've posted my C# code. Can you please take a look? — Adyana Permatasari, Apr 11 '13 at 17:19

score 1 · Accepted Answer · answered Apr 12 '13 at 06:17

1

Try this

SqlDataReader reader = com.ExecuteReader();

while (reader.Read())
{
  status = reader["Status"] == DBNull.Value ? 0 : (int)reader["Status"];
  IDCabang = reader["IDCabang"] == DBNull.Value ? null : (string)reader["IDCabang"];
}

answered Apr 12 '13 at 06:17

noobprogrammer

1,140
7
22
35

Dilshod · Answer 2 · 2013-04-11T17:04:35.323

0

Did you try like this:

    USE KOST
    GO
    --Cabang means Branch in Bahasa.

CREATE PROCEDURE login_pro(@IDCabang VARCHAR(5))
        --received parameter
        @NoIDStaff VARCHAR(5),
        @Password VARCHAR (50)


    AS

    --sent parameter
    DECLARE @status INT


    --owner login
    IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('B%'))
            SET @status=1

    --staff login
    ELSE IF EXISTS(SELECT IDCabang, [Password]
                FROM MsStaff 
                WHERE @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            BEGIN
            SELECT @IDCabang=IDCabang FROM MsStaff
            SET @status=2
            SET @IDCabang = (select IDCabang FROM Msstaff where @NoIDStaff=NoIDStaff AND @Password=[Password]
                AND NoIDStaff LIKE('A%'))
            END

    --neither of them login
    ELSE
            set @status=0

    select @status, @IDCabang
    GO

Also SEE THIS

edited Apr 11 '13 at 17:04

answered Apr 11 '13 at 16:31

Dilshod

3,189
3
36
67

i've tried your code, but it said "Incorrect syntax near 'DECLARE'. Expecting a variable" – Adyana Permatasari Apr 11 '13 at 17:03
@AdyanaPermatasari I fixed it. No keyword DECLARE needed. – Dilshod Apr 11 '13 at 17:05
@AdyanaPermatasari also refer to the link on my post. There explained how to do SQL and C#. – Dilshod Apr 11 '13 at 17:06
It still won't work. I've seen your link. But it only tells how to return int value. Not string value – Adyana Permatasari Apr 11 '13 at 17:15
@AdyanaPermatasari so if it shows how to return int I am sure you can easily change it to string. – Dilshod Apr 11 '13 at 17:27

score 0 · Answer 3 · edited May 23 '17 at 12:12

You're using ExecuteScalar, which is only ever going to look at the first row and first column of your return set: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

You really want to use ExecuteReader or something similar instead. In addition, I would alias your variables in your select so that you can reference them easier when you look for them (i.e., SELECT @status as Status, @IDCabang as IDCabang).

SqlDataReader reader = com.ExecuteReader();

while (reader.Read())
{
  status = reader["Status"] == DBNull.Value ? 0 : (int)reader["Status"];
  IDCabang = reader["IDCabang"] == DBNull.Value ? null : (string)reader["IDCabang"];
}

EDIT: In reference to Aaron Bertrand's comment, you could also set up an output parameter on your stored proc and read that. There's more detail on how to do that here: Get output parameter value in ADO.NET

Return string from SQL Procedure to C#

3 Answers3