problems with sqlreader

Hi everyone,

I am writing a simple login application in asp 2.0. I have spent quite a little bit of time of this error so any help would be greatly appreciated.

Here is the code i am using.

Dim cnAs SqlConnection

Dim cmdAs SqlCommand

Dim sqlAsString ="select password from attendant where " & _

" ((username = @.username) and (password = @.password)"

cn =

New SqlConnection("Data Source=z-davis\sqlexpress;Initial Catalog=Trainingdb;Integrated Security=True")' cn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("TrainingdbConnectionString").ToString)

'I have tried both connection strings and get the same error.

cmd =New SqlCommand(sql, cn)

cmd.Parameters.Add(

"@.username", SqlDbType.VarChar, 50)

cmd.Parameters(

"@.username").Value = txtuser.Text

cmd.Parameters.Add(

"@.password", SqlDbType.VarChar, 50)

cmd.Parameters(

"@.password").Value = txtpass.Text

cn.Open()

Dim myreaderAs SqlDataReader

myreader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'The line above is where i get the error.

If myreader.Read()Then

FormsAuthentication.RedirectFromLoginPage(txtuser.Text,

False)Else

Response.Write(

"Try again")

After stepping through the code the error stops on this line:

myreader = cmd.executereader(commandbehavior.closeconnection)

The error that is generated is: Incorrect syntax near ')'

Any help would be greatly appreciated or any other code snippets

Thanks,

notrosh

In this line you have 3 open parentheses and only 2 close parentheses.

Dim sqlAsString ="select password from attendant where " & _

" ((username = @.username) and (password = @.password)"

Remove the first open parenthesis and you will have better luck.

|||

Thanks,

Sometimes you need an outside eye to see an error that simple.

zach davis

|||Or you need to have made that same sort of error a few dozen times

I've learrned to count my parentheses using my fingers. Working left to right in my SQL statement, I put up a finger each time I open one, and put down a finger each time I close one. When I get to the last character, if I have any fingers sticking up, I know I have a problem. Pretty silly approach, but it works for me.

Problems with sp_droplogin in SQL 2005

I have a script that must run in both SQL 2005 and SQL 2000. So beforehand,I want to tell
that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
I'm currently logon on the SQL server (DEVDSL1) using the local administrator account.
When I attempt to run:
EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
I receive the following error message:
Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
'DEVDSL1\LCL ADS Service' is not a valid login or you do not have permission.
However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no problem.
What could possibly be the rason why "sp_droplogin" do not work?
> What could possibly be the rason why "sp_droplogin" do not work?
sp_drop_login is intended to be use only be used for SQL-authenticated
logins, although SQL 2000 apparently didn't enforce the requirement. Use
sp_revokelogin to remove Windows-authenticated logins. That should work in
both versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaetan Simard" <gaetan.simard@.eds.com> wrote in message
news:ms1fv19mkrc8m9ri54st01bv3crc8901n8@.4ax.com...
>I have a script that must run in both SQL 2005 and SQL 2000. So
>beforehand,I want to tell
> that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
> The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
> I'm currently logon on the SQL server (DEVDSL1) using the local
> administrator account.
> When I attempt to run:
> EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
> I receive the following error message:
> Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
> 'DEVDSL1\LCL ADS Service' is not a valid login or you do not have
> permission.
> However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no
> problem.
>

Problems with sp_droplogin in SQL 2005

I have a script that must run in both SQL 2005 and SQL 2000. So beforehand,I
want to tell
that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
I'm currently logon on the SQL server (DEVDSL1) using the local administrato
r account.
When I attempt to run:
EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
I receive the following error message:
Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
'DEVDSL1\LCL ADS Service' is not a valid login or you do not have permission
.
However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no pr
oblem.
What could possibly be the rason why "sp_droplogin" do not work?> What could possibly be the rason why "sp_droplogin" do not work?
sp_drop_login is intended to be use only be used for SQL-authenticated
logins, although SQL 2000 apparently didn't enforce the requirement. Use
sp_revokelogin to remove Windows-authenticated logins. That should work in
both versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaetan Simard" <gaetan.simard@.eds.com> wrote in message
news:ms1fv19mkrc8m9ri54st01bv3crc8901n8@.
4ax.com...
>I have a script that must run in both SQL 2005 and SQL 2000. So
>beforehand,I want to tell
> that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
> The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
> I'm currently logon on the SQL server (DEVDSL1) using the local
> administrator account.
> When I attempt to run:
> EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
> I receive the following error message:
> Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
> 'DEVDSL1\LCL ADS Service' is not a valid login or you do not have
> permission.
> However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no
> problem.
>

Problems with sp_droplogin in SQL 2005

I have a script that must run in both SQL 2005 and SQL 2000. So beforehand,I want to tell
that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
I'm currently logon on the SQL server (DEVDSL1) using the local administrator account.
When I attempt to run:
EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
I receive the following error message:
Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
'DEVDSL1\LCL ADS Service' is not a valid login or you do not have permission.
However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no problem.
What could possibly be the rason why "sp_droplogin" do not work?> What could possibly be the rason why "sp_droplogin" do not work?
sp_drop_login is intended to be use only be used for SQL-authenticated
logins, although SQL 2000 apparently didn't enforce the requirement. Use
sp_revokelogin to remove Windows-authenticated logins. That should work in
both versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaetan Simard" <gaetan.simard@.eds.com> wrote in message
news:ms1fv19mkrc8m9ri54st01bv3crc8901n8@.4ax.com...
>I have a script that must run in both SQL 2005 and SQL 2000. So
>beforehand,I want to tell
> that I cannot use "DROP LOGIN" since SQL 2000 does not support it.
> The SQL 2005 documentation states that "sp_droplogin" calls "DROP LOGIN"
> I'm currently logon on the SQL server (DEVDSL1) using the local
> administrator account.
> When I attempt to run:
> EXEC sp_droplogin [DEVDSL1\LCL ADS Service]
> I receive the following error message:
> Msg 15007, Level 16, State 1, Procedure sp_droplogin, Line 26
> 'DEVDSL1\LCL ADS Service' is not a valid login or you do not have
> permission.
> However the statement "DROP LOGIN [DEVDSL1\LCL ADS Service]" works no
> problem.
>

Problems with login to Web Data Administrator

Hi..

1. I installed MSDE with this string: Setup SAPWD=mypassword DISABLENETWORKPROTOCOLS=1 SecurityMode=SQL

2. And then i installed Web Data Administrator.

3. I try to login to my MSDE, but when i press the Login button, NOTHING is happening, I´ve been waiting for half an hour or so and it just freezes??.

4. I have tried Both SQL login and Windows, NONE of them work, i get no error messages at all, it just freezes?.

5. And the funny thing is that i can login with a connectionstring and a ordinary SqlCommand, that is no problem but the Web Data Administrator just freezes?I'm afraid that I don't have an answer for you. I just wanted to throw in that I have just started experiencing a similar problem. I used to have Web Data Admin working very nicely with MSDE. Then I uninstalled Visual Basic .NET Standard 2003 and installed Visual Studio .NET 2003 Professional. After doing so, suddenly the Web Data Admin just hangs after I press "Login" ... and VS.NET cannot access the MSDE database at all, whereas I used to be able to access the database with VB.NET Std just fine. The database itself still seems to be fine ... or at least it still works with a local ASP.NET website I have been working on.

At this point I'm thinking that my uninstalling VB.NET Standard took something with it that MSDE needed to be accessed by these other programs. If so, the problem is that I have no idea what that is (not to mention that I would have expected any such thing to be restored by the VS.NET Pro install, which to my thinking should have been a superset of the VB.NET Standard functionality.

Any ideas on what might have happened here would be most appreciated.|||After installing the MSSQLSERVER service isn't started automatically. If you start it manually with "net start MSSQLSERVER" (without quotes) in your run box it will work. Alternativly you could restart and it will start at startup.|||I did restart the computer...

Problems with login

I have a few proggramers in my firm. They connect to server with login 'prog' (not 'sa'). I don't want to give them sysadmin rights, because I don't want to allow them to CREATE, ALTER, DROP, DETACH, ATACH, BACKUP, RESTORE ... DATABASE, and some other things (sp_addlogin, sp_password, ...).
They have db_owner role for one of few database we have, with all permissions on the objects of that database. But, they can not use SQL-Profiler wich is a poweful for them to manage theirs applications, because they are not member of sysadmin role.
How can I avoid that.
Can I give them sysadmin rights, but to exclude some privileges.
Or, what to add, to be able to use SQL Profiler, without sysadmin rights

Thanks for any helpYou can create a proxy account for your developer

1. Open Enterprise manager.
2. Click on the server name -> Management
3. Right Click on SQL Server Agent
4. Choose the Job System tab
5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.

Verify/Change permissions

1. Open Administrative tools (You will need admin rights on the server to do this)
2. Go to Local Security Policy (this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
3. Click on "user rights Assignment" in the left hand pane.
4. In the right hand pane, scroll down until you find "replace a process level token".
5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.

And since your developers are not in the sysadmin group you as the person that is located in the sysadmin group will need to create the proxy account.

You may need to investiagate what rights you can give to the proxy account.

I hope this is helpful.

Lystra

Problems with email alerts

Hi,

I hope I am posting this in the correct forum. I want to have emails sent to me when ever there is a declined login. I have set this up but it is not working. I will go through the procedure I used, and please tell me what I am missing. I am using SQL Server 2005.

First, under Management --> Database Mail in the object explorer, I run the Database Mail Configuration Wizard. I gave the account a name, an email address, a reply address, a server name (I used the IP address) and port number 25.

Second, under Management --> Database Mail in the object explorer, I selected Send Test Email. I choose the profile I just created, my own email address in the To section and left the Subject and Body sections as the default. This email arrives in my inbox, so I believe the mail account is configured correctly.

Third, under Notification Services --> Operators in the object explorer, I add a new operator. I give it a name, select enabled and give it an email address.

The last step, under Notification Services --> Alerts in the object explorer, I add a new alert. I give it a name, select for "<all databases>", and severity I select "014 - Insufficient Permission". Under the response tab I select "Notify Operators" and select the new operator and click the email checkbox for the operator.

Then I test the process by trying to login to my server with the wrong password. I never recieve an email alerting me.

If I go to the properties of the alert, under the history tab, I can see the date of the last alert is current, and for every attempt I login with the wrong password the number of occurances goes up.

If I go to the properties of the operator, under the history tab, it says "(Never e-mailed)"

Please tell me what I am missing?

Are the SQL Server and SQL Server Agent accounts set up as users, or are they using the LocalSystem or NetworkSystem accounts? You'll want to make sure both are normal user accounts, so that they can access the SMTP Server you're using.

Also, you need to configure the mail to use stored procedures...check out this link:

http://www.databasejournal.com/features/mssql/article.php/3626056

Buck Woody

|||

Hi,

I followed the article and I was missing one step, that of creating a profile. I had only created an account. However the alert is still not sending me email.

I then checked the SQL Server and SQL Server Agent services and both are using Local System accounts to log on. I change this to my domain user account and I get an error message when I try to start the service. "The service did not strat due to a logon failure". I then created a new user account on my pc, and gave it administrator rights. This also gives me the same error when I try to start the service using this local account.

It seems I can only use Local System, Local Service and Network Service to start these services.

|||

It seems like you're missing a permission or right on that account, although if it is an administrator that wouldn't be true. Try your domain account again, and make sure your account has all the rights needed to start the service as described in Books Online.

Buck Woody

Problems with email alerts

Hi,

I hope I am posting this in the correct forum. I want to have emails sent to me when ever there is a declined login. I have set this up but it is not working. I will go through the procedure I used, and please tell me what I am missing. I am using SQL Server 2005.

First, under Management --> Database Mail in the object explorer, I run the Database Mail Configuration Wizard. I gave the account a name, an email address, a reply address, a server name (I used the IP address) and port number 25.

Second, under Management --> Database Mail in the object explorer, I selected Send Test Email. I choose the profile I just created, my own email address in the To section and left the Subject and Body sections as the default. This email arrives in my inbox, so I believe the mail account is configured correctly.

Third, under Notification Services --> Operators in the object explorer, I add a new operator. I give it a name, select enabled and give it an email address.

The last step, under Notification Services --> Alerts in the object explorer, I add a new alert. I give it a name, select for "<all databases>", and severity I select "014 - Insufficient Permission". Under the response tab I select "Notify Operators" and select the new operator and click the email checkbox for the operator.

Then I test the process by trying to login to my server with the wrong password. I never recieve an email alerting me.

If I go to the properties of the alert, under the history tab, I can see the date of the last alert is current, and for every attempt I login with the wrong password the number of occurances goes up.

If I go to the properties of the operator, under the history tab, it says "(Never e-mailed)"

Please tell me what I am missing?

Are the SQL Server and SQL Server Agent accounts set up as users, or are they using the LocalSystem or NetworkSystem accounts? You'll want to make sure both are normal user accounts, so that they can access the SMTP Server you're using.

Also, you need to configure the mail to use stored procedures...check out this link:

http://www.databasejournal.com/features/mssql/article.php/3626056

Buck Woody

|||

Hi,

I followed the article and I was missing one step, that of creating a profile. I had only created an account. However the alert is still not sending me email.

I then checked the SQL Server and SQL Server Agent services and both are using Local System accounts to log on. I change this to my domain user account and I get an error message when I try to start the service. "The service did not strat due to a logon failure". I then created a new user account on my pc, and gave it administrator rights. This also gives me the same error when I try to start the service using this local account.

It seems I can only use Local System, Local Service and Network Service to start these services.

|||

It seems like you're missing a permission or right on that account, although if it is an administrator that wouldn't be true. Try your domain account again, and make sure your account has all the rights needed to start the service as described in Books Online.

Buck Woody