Click here to Skip to main content
15,916,941 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day buddies.

Please I have a problem and I hope you guys can help me out.

I am working on VB.NET 2012 application that connects to ms sql server. I want to allow users to select the installed sever instance of their chioce so I have a module that present installed server instances for the user to select from.

After a selection is made, the module will then create the necessary database objects. My code raise error when it tries to create ms sql login in for the current user. I found that the error is raised by connectionstring(firstSQL) but I do not know to resolve it.

Please find below details of my code.
VB


C#
connectionStr = "Data Source=" & lstLocalSevers.SelectedItem.ToString & ";DataBase=;Integrated Security=SSPI"

Dim firstSQL As New SqlConnection(connectionStr)


<pre>'create login for the current user
Dim sql As New SqlCommand("if not exists (select NAME FROM sys.login_token" & ")  CREATE LOGIN " & us, firstSQL)
            sql.ExecuteNonQuery()



The error: 'Incorrect syntax near '-'. The '-' is part of the computer name(name-PC) and the instance is name-PC\SQLEXPRESS and the onnection string generate from the pc is '
"Data Source=name-PC\SQLEXPRESS;DataBase=;Integrated Security=SSPI"
'


Thanks in advance for your help
Posted

1 solution

Two solutions:

1) (best): Parameterize your query to the command object and use AddParameterWithValue for the argument: ... LOGIN @us" ...

2) enclose your argument in single quotes: ... LOGIN '" & us "'" ...

The first will be immune from SQL injection, the second will not be!
 
Share this answer
 
Comments
noblepaulaziz 2-Sep-14 11:18am    
Thank you PhiLenoir for your reply. I am very sorry for the late reply. Your suggestion did not work. The 'us' is a string variable.

I found the solution. The problem was missing 'FROM WINDOWS'. The string should read:

Dim sql As New SqlCommand("if not exists (select NAME FROM sys.login_token" & ") CREATE LOGIN " & us & "FROM WINDOWS", firstSQL)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900