如何使用SQL语句在VB.NET中传输某些值？ [英] How do I transfer certain values in VB.NET using an SQL statement?

问题描述

所以，我遇到了一个问题，我有一个登录表和一个访问过的表。我希望它如何工作是当一个人登录访问表时将记录用户名，用户ID，日期和时间。我有代码使用用户名和日期和时间，但它不会保持用户ID与登录表相同的数字。



我如何获得用户ID只是通过使用他们的登录名（用户名和密码）连接到该SQL数据库行？我没有使用gridview控件，所以我需要引用sql表。



所以如果我的登录表有3行，每行有自己的个人ID我想要我的访问表每次登录时显示相同数量的个人。

 Dim sql1 As String =insert into [Accessed]（ ID，用户名，标识，[日期]，[时间]）值（@ ID，@ Username，@ Identifate，@ Date，@ Time）
 cmd =新的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）
 

我尝试过：



我尝试使用与行ID相同的方法，即

 LoginDataSet.Tables（Login）。Rows.Count + I 

但我知道这是错误的。无论哪种方式我都试图使用参数化参考。

解决方案

为什么你假设刚刚登录的用户的id将是表中的行加一个随机数？因为有效，这就是你在做什么。



当用户登录时，你检查他的密码是否存储在你的数据库中的（哈希值为安全性）值登录表，并从该表中获取userID。您可以将其存储在应用程序中（在变量中，或在会话中或基于Web的应用程序的Cookie中），并在每次需要知道用户是谁时引用该值。你不要每次尝试解决，因为一旦你的登录表单或页面关闭，你再也无法真正访问登录信息了！



因此，当您存储访问信息时，使用存储的userID值作为表数据：

 cmd.Parameters.AddWithValue（@ Identification，userID） 

此外，您可能不希望 - 或者需要 - 将特定值设置为已访问表的ID列，因为它应该是IDENTITY或您的代码不应该关心的UNIQUEIDENTIFIER列（或者在多用户系统中维护）。

您访问的表不应该存储用户名 - 它有UserID所以它可以通过简单的方式获取用户名在需要时加入查询 - 存储重复信息是浪费和危险的。

您可能最好将日期和时间值存储为单个DATETIME（或DATETIME2）列 - 并存储在那里的UTC值，所以它是consi数据库中的支架，无论用户位于何处。

我的访问表版本将是：

 ID INT，IDENTITY ，主键，不是空的。 
 UserID INT或GUID，具体取决于您的Users表，Foreign Key to Users表，Not Null 
时间戳DATETIME（或DATETIME2），非空，默认值= GETUTCDATE（）（存储为UTC）
 Action INT（访问类型，因此您可以存储登录，错误登录尝试，更新等）

然后，当您插入值时，所有传递给它的是UserID和Action代码通过参数

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.

解决方案

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.

这篇关于如何使用SQL语句在VB.NET中传输某些值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！