C# sql wcf parametres exception

Question

This is probably my first C# program, so i kindly ask for you patience. Im trying to create an app that adds/deletes/updates student data from a local MS Sql database by following an online tutorial.

Database- local windows authentication called with table Registration: primary key- UserId int UserName varchar (100) Password varchar (20) Country varchar (100) email varchar (20)

The program uses a WCF service and it starts correctly, however i keep getting the error when i try to invoke the InsertUserDetails().

Error text: The parameterized query '(@UserID int,@UserName nvarchar(4000),@Password nvarchar(4000),@' expects the parameter '@UserName', which was not supplied.

C# code for Service1.cs:

public class Service1 : IService1
{

    public DataSet SelectUserDetails()
    {

        SqlConnection con = new SqlConnection(@"Server=Robert;Database=Trying;Trusted_Connection=True;");

        con.Open();

        SqlCommand cmd = new SqlCommand("Select * from Registration", con);

        SqlDataAdapter sda = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        sda.Fill(ds);

        cmd.ExecuteNonQuery();

        con.Close();

        return ds;

    }



    public void UpdateRegistrationTable(UserDetails userInfo)
    {

        SqlConnection con = new SqlConnection(@"Server=Robert;Database=Trying;Trusted_Connection=True;");

        con.Open();

        SqlCommand cmd = new SqlCommand("update Registration set UserName=@UserName,Password=@Password,Country=@Country, Email=@Email where UserID=@UserID", con);



        cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);

        cmd.Parameters.AddWithValue("@Password", userInfo.Password);

        cmd.Parameters.AddWithValue("@Country", userInfo.Country);

        cmd.Parameters.AddWithValue("@Email", userInfo.Email);

        cmd.ExecuteNonQuery();

        con.Close();

    }



    public bool DeleteUserDetails(UserDetails userInfo)
    {

        SqlConnection con = new SqlConnection(@"Server=Robert;Database=Trying;Trusted_Connection=True;");

        con.Open();

        SqlCommand cmd = new SqlCommand("delete from Registration where UserID=@UserID", con);

        cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);

        cmd.ExecuteNonQuery();

        con.Close();

        return true;

    }



    public string InsertUserDetails(UserDetails userInfo)
    {

        string Message;

        SqlConnection con = new SqlConnection(@"Server=Robert;Database=Trying;Trusted_Connection=True;");

        con.Open();

        SqlCommand cmd = new SqlCommand("insert into Registration(UserID,UserName,Password,Country,Email) values(@UserID,@UserName,@Password,@Country,@Email)", con);

        cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
        cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);           
        cmd.Parameters.AddWithValue("@Password", userInfo.Password);
        cmd.Parameters.AddWithValue("@Country", userInfo.Country);
        cmd.Parameters.AddWithValue("@Email", userInfo.Email);

        int result = cmd.ExecuteNonQuery();

        if (result == 1)
        {

            Message = userInfo.UserName + " Details inserted successfully";

        }

        else
        {

            Message = userInfo.UserName + " Details not inserted successfully";

        }

        con.Close();

        return Message;

    }

}

}

C# code for IService1.cs

 [ServiceContract]

public interface IService1
{

    [OperationContract]

    string InsertUserDetails(UserDetails userInfo);



    [OperationContract]

    DataSet SelectUserDetails();



    [OperationContract]

    bool DeleteUserDetails(UserDetails userInfo);



    [OperationContract]

    void UpdateRegistrationTable(UserDetails userInfo);

}



// Use a data contract as illustrated in the sample below to add composite types to service operations.

[DataContract]

public class UserDetails
{

    int userid;

    string username;

    string password;

    string country;

    string email;



    [DataMember]

    public int UserID
    {

        get { return userid; }

        set { userid = value; }

    }



    [DataMember]

    public string UserName
    {

        get { return username; }

        set { username = value; }

    }

    [DataMember]

    public string Password
    {

        get { return password; }

        set { password = value; }

    }

    [DataMember]

    public string Country
    {

        get { return country; }

        set { country = value; }

    }

    [DataMember]

    public string Email
    {

        get { return email; }

        set { email = value; }

    }

}

}

Answer 1

That means that the UserName Property of your UserDetails class seems to be null. Check if it has a value.

One way to mitigate such errors is to check the validity of the UserDetails instance in advance by specifiying the contract of your insert method.

public string InsertUserDetails(UserDetails userInfo)
{

  if(null==userInfo)
    throw new Exception("userInfo is null");

  if(String.IsNullOrEmpty(userInfo.UserName))
    throw new Exception("UserName is null or empty");

  // and only after this check succeeds do the insert

}

If you want to allow null values you would have to translate the .NET NULL to the ado.NET DBNull.Value.

cmd.Parameters.AddWithValue("@UserName", userInfo.UserName ?? DBNull.Value);

Another possible solution would be to wrap your SQL in procedures and make the parameter nullable by providing a default parameter.

create procedure usp_InserProc @UserName varchar(32) = NULL
begin
   ... sql code
end