0

My Problem has Been Fixed, My main problem was getting the information from the textbox in the xaml which got erased after that window was closed and another opened. Though the answers did fix my other problems and have made my code much simpler and easier to read. So thank you very much!

So I am Currently working on building a Calendar for a personal project and working on adding events to a Database, this table for Events stores two varchars, and an int (name, description, userid), the userid is a foreign key and is linked to the User Table. When I use the code below to try and pull the userid for the username that the person entered, it tells me that there is no existing value.

using (SqlConnection connection = new SqlConnection())
        {

            connection.ConnectionString =
                    "Data Source=calenderserver.database.windows.net;" +
                    "Initial Catalog=Calender;" +
                    "User id=*******;" +
                    "Password=*******;" +
                    "MultipleActiveResultSets = true";
            connection.Open();

            SqlCommand com = new SqlCommand("Select UserId from Users Where UserName = @user", connection);
            com.Parameters.AddWithValue("@user", UsernameTextBox.Text);

            SqlDataReader reader = com.ExecuteReader();
            reader.Read();
            int userid = reader.GetInt32(1);

            messages.Text = "Event Added";
            SqlCommand command = new SqlCommand("INSERT INTO [Events] VALUES (@eventname, @eventdesc)", connection);
            command.Parameters.AddWithValue("@eventname", name);
            command.Parameters.AddWithValue("@eventdesc", description);
            command.Parameters.AddWithValue("@userid", userid);
            command.ExecuteNonQuery();

            reader.Close();
            connection.Close();

        }

Even though when I run the same command in an actual SQL Query it returns a proper value.

SQL Command

I am completely lost on this and have checked multiple sources and solutions and would really appreciate the help.

Improve this question
4
  • 3
    You might want to remove your database password from the connection string in this question..... Commented Aug 30, 2017 at 21:58
  • You describe your Calendar project and events table in great detail, but your problem only references a users table which you have not described... Commented Aug 30, 2017 at 22:21
  • @mikeTheLiar Don't worry it wasn't the actual account info but someone changed it to *'s which is even better. Commented Aug 30, 2017 at 23:28
  • @Toastrackenigma The User Table contains UserId (nvarchar), Username (nvarchar), and Password (nvarchar). The problem does involve the Users table, yes, but the only thing I am using from it is the Userid. Sorry for the haziness! Commented Aug 30, 2017 at 23:30

4 Answers 4

Reset to default
2

You are doing int userid = reader.GetInt32(1); the indexes for the get function are 0 based so you actually need int userid = reader.GetInt32(0); so you get the first column.

That being said, because you are using the first result of the first column you can simplify your code by switching from a data reader to using ExecuteScalar()

        SqlCommand com = new SqlCommand("Select UserId from Users Where UserName = @user", connection);
        com.Parameters.AddWithValue("@user", UsernameTextBox.Text);

        int userid = (int)com.ExecuteScalar();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you! It helped a lot! I did solve my problem only after running into another but it was yours and the others who commented similar if not the same solution that fixed this problem!
1

Try using ExecuteScalar function. Execute scalar returns a single value and I see you only need the user ID. Take a look at this link .

int userid = (Int32)com.ExecuteScalar();

I Hope it helps!

Improve this answer

1 Comment

Thank you! It did help and I also do appreciate the link, that also helped!
1

Indices in GetInt32 are 0-based as per doc, therefore your call should read:

int userid = reader.GetInt32(0);
Improve this answer

Comments

1

Change these lines:

        SqlDataReader reader = com.ExecuteReader();
        reader.Read();
        int userid = reader.GetInt32(1);

to:

var userID = com.ExecuteScalar();

Why:

Execute Scalar should be used when your query returns a single value.

Execute Reader returns a collection of data in the form of a DataReader. DataReaders are fast, and you can quickly iterate over them to get the data you need from the database. The connection remains open as long as the datareader is open.

Because you were only getting a single value back from the database, it makes sense to use ExecuteScalar. It's more efficient and too the point.

If you were getting a list of UserID's, then I'd recommend you use a DataReader to iterate through the UserIDs.

Improve this answer

5 Comments

Good that you provide the solution, but it would be nice if you included the why switching to execute scalar would fix the problem.
Because fixing his visible Username / password in his question was a higher priority ;) But true, I should elaborate
If the UserId in the SQL table was set to auto-increment would it still be able to be called?
Of course. The UserID is still a field in the database, and can be queried and filtered on like any other field.
Thank you for all the useful information! It was all very helpful!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.