[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Adding an SQLParameter to my SQLCommand - Getting error

Posted on 2008-02-06
4
Medium Priority
?
616 Views
Last Modified: 2013-11-26
I am trying to make a .NET application that turns off the "Enforce password policy" and "Enforce password expiration" options for a SQL user in SQL Server 2005.  This script works in my application (where "tester" is the username):

USE master ALTER LOGIN tester WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

However, when I try to use a SQL Parameter instead of manually building the string, I get an error that says "Incorrect syntax near '@UserName'."  Here is my updated script:

USE master ALTER LOGIN @UserName WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

and here is the .NET code where I am adding my SQLParameter:

            cmd = New SqlCommand(RESETPASSWORDSCRIPT, conn.SqlConn)
            cmd.CommandType = CommandType.Text

            With cmd.Parameters
                .Add("@UserName", SqlDbType.NVarChar).Value = userName
            End With

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()

Can anybody see where I might be doing something wrong?  RESETPASSWORDSCRIPT is equal to the script I posted.
0
Comment
Question by:RAFUser
  • 2
4 Comments
 
LVL 6

Expert Comment

by:pollock_d
ID: 20832254
what does the cmd.commandtext look like just before cmd.ExecuteNonQuery() ??
0
 

Author Comment

by:RAFUser
ID: 20832278
I copied and pasted it right from the Watch window:
USE master ALTER LOGIN @UserName WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

If I look at the Parameters collection in my command I can see the one parameter successfully added.
0
 
LVL 21

Accepted Solution

by:
mastoo earned 1500 total points
ID: 20833135
I don't think you can parameterize username on that statement.  You can either build the string dynamically in sql from the parameter and then invoke it via Exec(), or...

Dim sSql as string = "USE master ALTER LOGIN '" + @UserName + "' WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF"

and then execute that.  If you're worried about injection you need to substitute for single quotes in @UserName.
0
 

Author Comment

by:RAFUser
ID: 20833206
Naw, I will just build the string dynamically.  I was trying to dabble with using parameters with command text instead of using Stored Procs like I usually do.

Now that I look at the statement closely I can see why I wouldn't be able to parameterize it - It would be like parameterizing a table name in a CRUD query.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month8 days, 11 hours left to enroll

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question