CODEWITHSUNDEEP
c#sql
eugene
eugene

Reputation: 550

Variable text in sql to C#

I have this:

public DataSet HopDongTheoNhaCungCap(string MaNCC)
        {
            return db.MyExcuteSQL("SELECT MaHD, TenHD, ThoiHan, NCC from HopDongCungCap as c where c.NCC= 'NCC_01'", CommandType.Text, null);                    
        }

And now I want to change to: 

public DataSet HopDongTheoNhaCungCap(string MaNCC)
        {
            return db.MyExcuteSQL("SELECT MaHD, TenHD, ThoiHan, NCC from HopDongCungCap as c where c.NCC= " + MaNCC, CommandType.Text, null);                    
        }

but when I update value for MaNCC example MaNCC = "NCC_01", it turn 

SELECT MaHD, TenHD, ThoiHan, NCC from HopDongCungCap as c where c.NCC= NCC_01"

NCC_01 here is a column not a text please help me

Upvotes: 1

Views: 59

Answers (2)

eugene
eugene

Reputation: 550

I've use 

return db.MyExcuteSQL("spHopDongTheoNhaCungCap", CommandType.StoredProcedure, new SqlParameter("@MaNCC",MaNCC));

this my sp:

ALTER PROCEDURE [dbo].[spHopDongTheoNhaCungCap]
    -- Add the parameters for the stored procedure here
    (@MaNCC NVARCHAR(50) )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT MaHD, TenHD, ThoiHan, NCC from HopDongCungCap as c where c.NCC=@MaNCC
END

and this's my problem 

Procedure or function 'spHopDongTheoNhaCungCap' expects parameter '@MaNCC', which was not supplied.

Note that im using threelayer so i dont know how to do something like this:

cmd.Parameters.AddWithValue(...);

Upvotes: 0

CindyH
CindyH

Reputation: 3026

Yes, parameterized is the way to go. However, in answer to your direct question, the problem is that in your second set of code you don't put quotes around the string variable. It should be

        return db.MyExcuteSQL("SELECT MaHD, TenHD, ThoiHan, NCC from HopDongCungCap as c where c.NCC= '" + MaNCC + "'", CommandType.Text, null);

with quotes around the variable you're adding.

Upvotes: 1

Related Questions