How do I transfer certain values in VB.NET using an SQL statement?

Question

0.00/5 (No votes)

See more:

So, I have ran into a certain problem, I have a login table and an accessed table. How I want it to work is when a person logs on the access table will record the username, user ID, date and time. I have the code working with username and date and time, but it will not keep the user ID the same number as the login table.

How can I get the User ID just by using their login in (username and password) connected to that sql database row? I am not using a gridview control so I need to reference the sql table.

so if my login table has 3 rows each with their own personal ID's I want to have my access table show that same number of the individual each time that they login.

Dim sql1 As String = "insert into [Accessed](ID, Username, Identification, [Date], [Time]) values(@ID, @Username, @Identification, @Date, @Time)"
           cmd = New SqlClient.SqlCommand(sql1, sqlcon)

           cmd.Parameters.AddWithValue("@ID", LoginDataSet.Tables("Accessed").Rows.Count + I)
           cmd.Parameters.AddWithValue("@Username", TextBox1.Text)
           cmd.Parameters.AddWithValue("@Identification", LoginDataSet.Tables("Login").Rows.Count + I)
           cmd.Parameters.AddWithValue("@Date", registerdate)
           cmd.Parameters.AddWithValue("@Time", registertime)

What I have tried:

I tried using the same method I did with row ID, which was

LoginDataSet.Tables("Login").Rows.Count + I

but I know that's wrong. Either way I am trying to use a parameterized reference.

Posted 5-Jul-17 21:33pm

Member 11856456

Updated 5-Jul-17 21:54pm

Add a Solution

Comments

Prifti Constantine 6-Jul-17 3:53am

What value does ,LoginDataSet.Tables("Login").Rows.Count + I , return?

Member 11856456 6-Jul-17 4:10am

the I+=1 but that's not exactly what I wanted it to do. I just used the same format for ID, so I could test the code. but I could not come up with a creative way to express UserID which should stay the same as long as its the same user of course.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2017-07-05T21:54:00

Why are you assuming that the id of the "just logged in user" is going to be the number of rows in a table plus a random number? Because effectively, that is what you are doing.

When a user logs in, you check his password against a (hashed for security) value stored in the DB in your login table, and fetch the userID from that table. You store this in your app (in a variable, or in the Session or a Cookie for web based apps) and you reference that value every time you need to know who the user is. You don;t try to "work it out" each time, because once your login form or page is closed, you don't have any real access to the login information again!

So when you store your access info, you use the stored userID value as the table data:

cmd.Parameters.AddWithValue("@Identification", userID)

In addition, you probably don't want - or need - to set a specific value to the ID column of your "accessed" table as that should be an IDENTITY or UNIQUEIDENTIFIER column that your code shouldn't care about (or be maintaining in a multiuser system).
You accessed table should not store the Username - it's got the UserID so it can fetch the username with a simple JOIN query when you need it - it's wasteful and dangerous to store duplicate information.
You'd probably also be better off storing the date and time values as a single DATETIME (or DATETIME2) column - and store a UTC value in there so it's consistent across the database regardless of where the the user is located.
My version of the Accessed table would be:

ID         INT, IDENTITY, Primary Key, Not Null.
UserID     INT or GUID depending on your Users table, Foreign Key to Users table, Not Null
Timestamp  DATETIME (or DATETIME2), Not Null, Default Value = GETUTCDATE() (stored as UTC)
Action     INT (type of access, so you can store login, bad login attempt, update, etc.)

Then all you pass to it when you insert a value is the UserID and the Action code via parameters.