none
create login with SqlCommand RRS feed

  • Question

  • Hi,

     

    I am trying to create login like following

     

                    SqlCommand cmd = new SqlCommand();

                    cmd.Connection = connection;

                    connection.Open();

     

                    cmd.CommandText = "CREATE LOGIN [@loginname] FROM WINDOWS";

                    cmd.Parameters.AddWithValue("@loginname", “NT AUTHORITY\NETWORK SERVICE”);

                    cmd.ExecuteNonQuery();

     

    However, it fails with

    '@loginname' is not a valid Windows NT name. Give the complete name: <domain\username>.

     

    Why the parameter is not substituted with the value in this case?

     

    Thanks,

    Zhisheng

    Saturday, June 14, 2008 10:57 AM

Answers

  • Parameters only valid for DML (Data Manipulation Language) statements and you cannot use them with DDL (Data Definition Language) statements. All CREATE stements belong to DDL. You would need to concatenate your statement if you need to do it from the code. Better way would be to use SQL Server SMO library (.NET based) that allows creating different objects in SQL Server database. You could install SMO as part of SQL Server SDK. Than you will see it in a list of available assemblies of Visual Studio .NET

    Tuesday, June 17, 2008 2:17 AM
    Moderator

All replies

  • hello,

     

    parameters are only allowed on stored procs...

    hehehe... this made me think a lot...

     

    try this.

     

    dim X$="manila/joeydj"

    cmd.commandtext= "create login "+X$+" from windows"

    cmd.executequery

     

     

    sorry that's VB. hehehe

     

    cheersSmile

    joey

     

     

    Monday, June 16, 2008 9:58 AM
  • Only sproc? I don't know what is the different between CREATE LOGIN statement and SELECT statement makes the difference that the later can have parameter.

     

    USE AdventureWorks;
    GO
    DECLARE @state char(25);
    SET @state = N'Oregon';
    SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
    FROM HumanResources.vEmployee
    WHERE StateProvinceName = @state;

     

     

    private static void UpdateDemographics(Int32 customerID,
        string demoXml, string connectionString)
    {
        // Update the demographics for a store, which is stored
        // in an xml column.
        string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
            + "WHERE CustomerID = @ID;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@ID", SqlDbType.Int);
            command.Parameters["@ID"].Value = customerID;

            // Use AddWithValue to assign Demographics.
            // SQL Server will implicitly convert strings into XML.
            command.Parameters.AddWithValue("@demographics", demoXml);

            try
            {
                connection.Open();
                Int32 rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("RowsAffected: {0}", rowsAffected);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }

    Monday, June 16, 2008 2:25 PM
  • Parameters only valid for DML (Data Manipulation Language) statements and you cannot use them with DDL (Data Definition Language) statements. All CREATE stements belong to DDL. You would need to concatenate your statement if you need to do it from the code. Better way would be to use SQL Server SMO library (.NET based) that allows creating different objects in SQL Server database. You could install SMO as part of SQL Server SDK. Than you will see it in a list of available assemblies of Visual Studio .NET

    Tuesday, June 17, 2008 2:17 AM
    Moderator
  •  

    Thanks a lot! This really helps.
    Tuesday, June 17, 2008 1:37 PM