problem with execute.scalar()

Hello,

I've got the following code:

Dim Selected1
Dim cnnAs New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
Dim SqlCommandAs New Data.SqlClient.SqlCommand("SELECT [OKTipp1] FROM [ErsteSchritte] WHERE (User.Name = @.UserName)", cnn)
 SqlCommand.Parameters.Add("@.UserName", Data.SqlDbType.VarChar, 30)
 SqlCommand.Parameters("@.UserName").Value = User.Identity.Name
 cnn.Open()
 Selected1 = SqlCommand.ExecuteScalar()
 cnn.Close()
 MsgBox(Selected1)

Unfortunatly, I am given an error message. What did I do wrong?

Thanks for any suggestions

Regards

What is the exception you are recieving?

|||

Methods for nvarchar could not be called up.

(Methoden für nvarchar k?nnen nicht aufgerufen werden.)

|||

Yikes a German translation! This will be hard. Maybe one of the MSFT admins can chime in on this one. One thing you might want to do is declare your variable as a type. For example.

Dim ret as String

|||

If I do not use the WHERE-clause and the @.username-parameter, everything's fine:

1Dim Selected1As Integer2Dim cnnAs New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())3Dim SqlCommandAs New SqlCommand("SELECT [OKTipp1] FROM [ErsteSchritte]", cnn)45cnn.Open()6Selected1 = SqlCommand.ExecuteScalar()7cnn.Close()89MsgBox(Selected1)

But how can I restrict the data to those of the logged in user?|||

First ,make sure your User.Identity.Name actually have value (you can set a break point there);

and second, are you sure we can use SqlCommand.Parameters("@.UserName").Value and there is no compilation errors given? I think sqlcommand.Parameters is a collection and we are using an index here so we should use sqlcommand.parameters["@.UserName"] instead.But anyway,this should not be the point... Pay attention to the User.Identity.Name part, I'm afraid the issue resides there.

Hope my suggestion helps

|||

Hi Mr.MSFT

I'm new to this forum and want to get some help here, since that i've heard some of Microsoft guys are active on this forum.

But for the post above, Bo,

are you sure we can use SqlCommand.Parameters("@.UserName").Value

Are you asking us if we can use the statment above? Hey!!! You are MSFT, it is YOU who should tell us the answer.

I think sqlcommand.Parameters is a collection and we are using an index here so we should use sqlcommand.parameters["@.UserName"] instead

You are suggesting us to use ["@.UserName"] to replace ("@.Username")... oh...MYGOD... ("@.UserName") is in VB.Net... the poster's using VB.NET,not C#..... don't you even know the code in VB way...

Finally..the badest, your reply has been marked as an answer... OHHHHH.... HOW CAN IT BE AN ANSWER? really ... really... disappointed here.

Mr. MSFT, i hope some words like "are you sure we can use..." won't come out from you guys....if you don't know, at least, you should have a test for us since it's not a complicated issue... just setting a parameter agasint the SQLDATASOURCE....

Last, i hope you won't be angry , Mr. MSFT... For us , Microsoft users, we just want a correct and reliable answer.

|||

Hi squall44 and GroovesRojar,

Thanks for all your comments. We are still trying our best to improve the quality of our replies and answers.

First, for the parameters collection issue, we do can useSqlCommand.Parameters("@.UserName").Value in VB.Net although we are getting used to useSqlCommand.Parameters["@.UserName"].Value in C#.

You can refer the samples in following link:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Second, I think the cause of the issue is "User.Name" in your sql statements. If you want to check whether "User.Name" in ASP.Net equals to some user name stored in the table, you should create your sql in the following way:

SELECT [YourField] FROM [YourTable] WHERE ( [YourTable].[UserNameField] = @.UserName)

// Here, You can assign the @.UserName with the value of User.Name propery in ASP.NET

After you modify your sql statement, I suggest that you should run it with a hard-coded parameter in Query Analyzer first to see if it can work. If it can, then add a break point in your application and run it step by step in order to check if any parameters lost during the runtime. In this way, I think you can find the cause of the problem.

I hope that helps.

Thanks.