如何将值与其他列一起插入指定列中，而不会单独将其显示在新的另一行中? [英] How can I insert a value in a specified column together with the other columns without appearing it into a new another row alone?

问题描述

我正在为我们的大学图书馆创建一个登录注销系统，该系统记录学生进入或退出图书馆的时间.

我的表单使用 VS 2010 Ultimate，我的数据库使用 SQL server 2008 r2.

在我的数据库中

我有 3 张桌子

tblCollegeinfo 包含:学生编号(主键)，学生姓名，和学生课程，

tblCollegeStudentLog 包含他们的记录studentNumber 再次作为外键，学生时间，学生超时，学生日期和登录号和

tblCollegeStudentPassword 包含他们的密码又是学生号和学生密码.

实际上我的登录按钮有效，只要他们输入用户名和密码并点击登录按钮，他们现在就登录了，他们的学号、姓名、课程登录时间(时间)和日期出现在 ListView 中.

我的问题是注销按钮.我不知道我应该使用什么正确的代码.我应该使用什么正确的 SQL 查询.

我有一个提示，我应该先加入表格，然后在表格tblCollegeStudentLog"的 studentTimeOut 中插入注销时间作为值"

但我不知道怎么做.每当我在 ListView 中单击带有 SelectedItems 的注销按钮时，插入到所选项目中的注销时间(超时)就会出现在另一行中.简而言之，如何在指定列中与其他列一起插入值而不出现在新的另一行中?请帮我解决这个问题:(我希望你明白我想说的话:(我希望插入的值与其他列一起出现，因为只会发生在我身上的是每当我点击注销按钮时该值单独出现在另一行中，它没有加入其他列:(谢谢大家！！！代码如下:

Private Sub btnLogoutNow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 处理 btnLogoutNow.Click连接()

<预> <代码> CMD =新的SqlCommand(SELECT tblCollegeStudentlog.LoginNo，tblCollegeStudentLog.studentNumber，tblCollegeInfo.studentName，tblCollegeInfo.studentCourse，tblCollegeStudentLog.studentTimeIn，tblCollegeStudentLog.studentTimeOut，tblCollegeStudentLog.studentDateIn FROM tblCollegeStudentLog LEFT OUTER JOIN tblCollegeInfo ON tblCollegeStudentLog.studentNumber= tblCollegeInfo.studentNumber WHERE tblCollegeStudentLog.studentNumber = '" + lvwLog.SelectedItems(0).Text + "'", con)cmd.ExecuteNonQuery()如果 Dr.HasRows 那么cmd = New SqlCommand("INSERT INTO tblCollegeStudentLog (studentTimeOut) VALUES ('" + lblTimeOut.Text + "') WHERE studentNumber = '" + lvwLog.SelectedItems(0).Text + "' ", con)cmd.ExecuteNonQuery()MsgBox("您现在已注销！")万一结束子

解决方案

您需要使用 UPDATE，而不是 INSERT.假设他们无法注销，除非他们已经登录(即 tblCollegeStudentLog 中有记录，其中 StudentTimeIn 是 NULL，你可以使用:

更新 tblCollegeStudentLogSET StudentTimeOut = @OutTimeWHERE StudentNumber = @StudentNumber并且 StudentTimeOut 为空；

然后您将使用 SqlParameters 来正确执行命令 并且不会让自己受到 SqlInjection 的影响，糟糕的 SQL 计划缓存和数据类型问题:

Dim Sql as String = "UPDATE tblCollegeStudentLog SET StudentTimeOut = @OutTime WHERE StudentNumber = @StudentNumber AND St​​udentTimeOut IS NULL;"使用 cmd = New SqlCommand(Sql, con)cmd.Parameters.AddWithValue("@OutTime", Convert.ToDateTime(lblTimeOutNya.Text))cmd.Parameters.AddWithValue("@StudentNumber", lvwLog.SelectedItems(0).Text)con.Open()如果 cmd.ExecuteNonQuery() = 0 那么'如果没有行受到影响，则没有有效的'记录更新，即学生未登录MsgBox("您还没有登录")别的MsgBox("您现在已注销！")万一结束使用

注意请原谅我的VB.NET，我已经有一段时间没有使用它了，所以可能存在一些语法错误.

I am creating a login logout system for our College Library that records the students' time in and time out whenever they enter or exit in our library.

I am using VS 2010 Ultimate for the form and SQL server 2008 r2 for my database.

In my database

i have 3 tables

tblCollegeinfo Contains : studentNumber(primary key), studentName, and studentCourse,

tblCollegeStudentLog contains their record with studentNumber again as the foreign key, studentTimeIn, studentTimeOut, studentDate and LoginNo and

tblCollegeStudentPassword contains their Passwords with studentNumber again and studentPassword.

Actually my Log In button works, that whenever they entered their Username and Password and hit the Log In button they are now Logged In and their Student Number, Name, Course Login time (Time In) and Date appears in the ListView.

My problem is the Log out button. I don't know what correct code should i use. What correct SQL query should I use.

I have a hint that I should join the tables first and Insert the Log Out Time as the 'value' in the studentTimeOut of the table "tblCollegeStudentLog"

but I don't know how. Whenever I click the Log Out button with the SelectedItems in my ListView, instead the Logout Time(Time Out) inserted in the selected item it appears in another row. In short, how can I insert a value in a specified column together with the other columns without appearing into a new another row? Please help me guys to solve this problem :( I hope you understand what I am trying to say :( I want that the inserted Value appears together with the other columns, because what only happens to me is that whenever i hit the Log Out button the value appears in another row alone it is not joining in the other columns :( thanks Guys!!! Heres the code:

Private Sub btnLogoutNow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogoutNow.Click Connect()

    cmd = New SqlCommand("SELECT tblCollegeStudentlog.LoginNo, tblCollegeStudentLog.studentNumber, tblCollegeInfo.studentName, tblCollegeInfo.studentCourse, tblCollegeStudentLog.studentTimeIn, tblCollegeStudentLog.studentTimeOut, tblCollegeStudentLog.studentDateIn FROM tblCollegeStudentLog LEFT OUTER JOIN tblCollegeInfo ON tblCollegeStudentLog.studentNumber = tblCollegeInfo.studentNumber WHERE  tblCollegeStudentLog.studentNumber = '" + lvwLog.SelectedItems(0).Text + "'", con)
    cmd.ExecuteNonQuery()


    If dr.HasRows Then
        cmd = New SqlCommand("INSERT INTO tblCollegeStudentLog (studentTimeOut) VALUES     ('" + lblTimeOut.Text + "') WHERE studentNumber = '" + lvwLog.SelectedItems(0).Text + "' ", con)
    cmd.ExecuteNonQuery()

        MsgBox("You are now logged out!")

    End If
End Sub

解决方案

You need to use UPDATE, not INSERT. Assuming they are not able to log out unless they have logged in (i.e. there is record in tblCollegeStudentLog where StudentTimeIn is NULL, you can just use:

UPDATE  tblCollegeStudentLog
SET     StudentTimeOut = @OutTime
WHERE   StudentNumber = @StudentNumber
AND     StudentTimeOut IS NULL;

Then you would use SqlParameters to properly execute the command and not leave yourself vunerable to SqlInjection, poor SQL Plan caching and data type issues:

Dim Sql as String = "UPDATE tblCollegeStudentLog SET StudentTimeOut = @OutTime WHERE StudentNumber = @StudentNumber AND StudentTimeOut IS NULL;"
Using cmd = New SqlCommand(Sql, con)
    cmd.Parameters.AddWithValue("@OutTime", Convert.ToDateTime(lblTimeOutNya.Text))
    cmd.Parameters.AddWithValue("@StudentNumber", lvwLog.SelectedItems(0).Text)
    con.Open()
    If cmd.ExecuteNonQuery() = 0 Then
        'If no rows are affected then there was not a valid
        'record to update, i.e. the student was not logged in
        MsgBox("You were not logged in")
    Else
        MsgBox("You are now logged out!")
    End If
End Using

N.B. Please excuse my VB.NET, I have not used it in quite some time so there may be some syntax errors.

这篇关于如何将值与其他列一起插入指定列中，而不会单独将其显示在新的另一行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！