如何根据serial nr将值从一个表插入另一个表？ [英] How can I insert values from one table to another based on serial nr?
问题描述
您好，我目前正在尝试将一些数据从一个表（RegisterStudent）实时插入到另一个表中。这意味着我有一个读卡器，当学生在读卡器上刷卡时，一个看不见的TextBox具有焦点将从卡中取出序列号，用数据库检查值，如果卡在系统中，它将显示有关该学生的信息以及到达日期，出发日期（何时将刷卡第二次）记录和记录课程总时数的小时数。现在这些是我的2个表格：
Hello,I am currently trying to insert some data from one table (RegisterStudent) to another in real time.That means that I have a card reader and when the student swipes the card over the card reader,an invisible TextBox with focus will take the serial nr from the card,check the value with the database and,if the card is in the system,it will show the info regarding that student along with date of arrival,date of departure(when it will swipe the second time to log of) and hoursSpent which shows the total hours spent in the course.Now these are my 2 tables:
CREATE TABLE [dbo].[AttendanceList] (
[sNr] INT NOT NULL,
[SN] CHAR (10) NOT NULL,
[fName] NVARCHAR (MAX) NOT NULL,
[lName] NVARCHAR (MAX) NOT NULL,
[dateArrival] TIMESTAMP NOT NULL,
[dateDeparture] DATETIME NOT NULL,
[hoursSpent] FLOAT NOT NULL,
PRIMARY KEY CLUSTERED ([sNr] ASC),
CONSTRAINT [FK_AttendanceList_ToTable] FOREIGN KEY ([SN]) REFERENCES [dbo].[RegisterStudent] ([SN])
);
CREATE TABLE [dbo].[RegisterStudent] (
[SN] CHAR (10) NOT NULL,
[sNr] INT NOT NULL,
[fName] NVARCHAR (MAX) NOT NULL,
[lName] NVARCHAR (MAX) NOT NULL,
[semester] INT NULL,
PRIMARY KEY CLUSTERED ([SN] ASC)
);
现在，我想从RegisterStudent中取出没有学期的SN，sNr，fName，lName，并根据序列号nr将其插入AttendanceList（SN来自AttendanceList（dateArrival，dateDeparture和hoursSpent）的其他值将自动更新。我的问题是，当我刷卡以查看它是否有效时，我在cmd.ExecuteNonQuery（）收到错误，该错误表明该查询没有成功。我怎么能以正确的方式使它工作。这是插入数据的功能：
Now,I want to take the SN,sNr,fName,lName without semester from RegisterStudent and insert it into AttendanceList based on the serial nr(SN).The other values from the AttendanceList(dateArrival,dateDeparture and hoursSpent)will update automatically.My problem is that when I swipe the card to see if it works,I get an error at the cmd.ExecuteNonQuery() which states that the query was not successful.How can I make it work in a proper manner.This is the function to insert data:
public void GetStudentData(object param)
{
SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\user0909\Documents\AttendanceListStudents.mdf;Integrated Security=True;Connect Timeout=30");
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("insert into AttendanceList(sNr,SN,fName,lName,dateArrival,dateDeparture,hoursSpent) select sNr,SN,fName,lName from RegisterStudent where SN=SN", conn);
cmd.Parameters.AddWithValue("@SN", SqlDbType.Char).Value = SerialN;
cmd.Parameters.AddWithValue("@sNr", SqlDbType.Int).Value = studentNr;
cmd.Parameters.AddWithValue("@fName", SqlDbType.NVarChar).Value = firstName;
cmd.Parameters.AddWithValue("@lName", SqlDbType.NVarChar).Value = lastName;
cmd.Parameters.AddWithValue("@dateArrival", SqlDbType.DateTime).Value = dateT;
cmd.Parameters.AddWithValue("@dateDeparture", SqlDbType.DateTime).Value = departure;
cmd.Parameters.AddWithValue("@hoursSpent", SqlDbType.Float).Value = hoursSpent;
cmd.ExecuteNonQuery();//here I get the error
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
}
我尝试了什么：
我已经尝试了所有描述的内容以及内部联接和linq的不同查询。
VM：
What I have tried:
I have tried everything depicted up plus different query with inner join and linq.
VM:
private string SerialN;
public string SNr
{
get { return SerialN; }
set
{
if (SerialN != value)
{
SerialN = value;
NotifyPropertyChanged("SNr");
}
}
}
private int studentNr;
public int StudentNR
{
get { return studentNr; }
set
{
if (studentNr != value)
{
studentNr = value;
NotifyPropertyChanged("StudentNR");
}
}
}
private String firstName;
public String FName
{
get { return firstName; }
set
{
if (firstName != value)
{
firstName = value;
NotifyPropertyChanged("FName");
}
}
}
private String lastName;
public String LName
{
get { return lastName; }
set
{
if (lastName != value)
{
lastName = value;
NotifyPropertyChanged("LName");
}
}
}
public String FullName
{
get
{
return string.Format("{0} {1}", FName, LName);
}
}
private DateTime dateT = DateTime.Now;
public DateTime StartDate
{
get { return dateT; }
set
{
dateT = value;
NotifyPropertyChanged("StartDate");
}
}
private DateTime departure = DateTime.Now;
public DateTime DepartureTime
{
get { return departure; }
set
{
departure = value;
NotifyPropertyChanged("DepartureTime");
}
}
private double hoursSpent;
public double HoursSpent
{
get { return departure.Subtract(dateT).TotalHours; }
}
构造函数：
Constructor:
showStudents = new DelegateCommand(GetStudentData);
和委托命令：
and the delegate command:
private DelegateCommand showStudents;
public DelegateCommand ShowStudents
{
get { return showStudents; }
set
{
if (showStudents != value)
{
showStudents = value;
NotifyPropertyChanged("ShowStudents");
}
}
}
这是视图中的绑定：
This is the binding in the view:
<Window.DataContext>
<local:AttendanceListViewModel/>
</Window.DataContext>
<Grid Background="#2e3137">
<TextBox HorizontalAlignment="Left" Height="31" Margin="171,212,0,0" TextWrapping="Wrap" Text="{Binding FilterString}" VerticalAlignment="Top" Width="334"/>
<Button Content="Search" HorizontalAlignment="Left" Margin="569,197,0,0" VerticalAlignment="Top" Width="104" Height="47"/>
<DataGrid HorizontalAlignment="Left" ItemsSource="{Binding Items}" Name="grdStudents" Height="433" Margin="0,287,0,0" VerticalAlignment="Top" Width="792">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding SNr}" Width="100" Header="Serial no." />
<DataGridTextColumn Binding="{Binding StudentNR}" Width="100" Header="Student no."/>
<DataGridTextColumn Binding="{Binding FullName}" Width="100" Header="Name"/>
<DataGridTextColumn Binding="{Binding StartDate,StringFormat=\{0:dd.MM.yy HH:mm:ss\}}" Width="100" Header="Arrival time"/>
<DataGridTextColumn Binding="{Binding DepartureTime,StringFormat=\{0:dd.MM.yy HH:mm:ss\}}" Width="100" Header="Departure time"/>
<DataGridTextColumn Binding="{Binding HoursSpent,StringFormat=\{0:HH:mm\}}" Width="100" Header="Total hours"/>
<DataGridTemplateColumn Header="Edit Row">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Button Name="editBtn" Content="Edit" Click="editBtn_Click" />
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTemplateColumn Header="Delete Row">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Button Content="Delete" Name="deleteBtn" Command="{Binding Path=DeleteCommand}" Click="deleteBtn_Click" />
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
<TextBox HorizontalAlignment="Left" Text="{Binding Path=SNr}" Name="txtBoxFocus" ss:EnterKeyHelpers.EnterKeyCommand="{Binding ShowStudents}" Height="23" Margin="277,259,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
</Grid>
推荐答案
该查询看起来不对，请尝试
That query doesn't look right, try
SqlCommand cmd = new SqlCommand("insert into AttendanceList(sNr,SN,fName,lName,dateArrival,dateDeparture,hoursSpent) select sNr,SN,fName,lName from RegisterStudent where SN=@SN", conn);
其余的参数是没有意义的，你要么必须从你拥有的选择中填充表，然后更新行（在单独的操作中），更改选择或捕获信息并相应地设置参数。
这是一个选项（未经测试）
The rest of the parameters are pointless, you either have to populate the table from the select that you have and then update the row (in a separate action), change the select OR capture the information and set the parameters accordingly.
Here is one option (untested)
SqlCommand cmd = new SqlCommand("insert into AttendanceList(sNr,SN,fName,lName,dateArrival,dateDeparture,hoursSpent) select sNr,SN,fName,lName,
@dateArrival as dataArrival, @dateDeparture as dateDeparture, @hoursSpent as hoursSpent from RegisterStudent where SN=@SN", conn);
cmd.Parameters.AddWithValue("@SN", SqlDbType.Char).Value = SerialN;
cmd.Parameters.AddWithValue("@dateArrival", SqlDbType.DateTime).Value = dateT;
cmd.Parameters.AddWithValue("@dateDeparture", SqlDbType.DateTime).Value = departure;
cmd.Parameters.AddWithValue("@hoursSpent", SqlDbType.Float).Value = hoursSpent;
cmd.ExecuteNonQuery();
如果仍然无效，请告诉我们错误的确切措辞
尝试将参数的添加更改为
If this still doesn't work then give us the exact wording of the error
Try changing the adding of the parameters to
cmd.Parameters.AddWithValue("@SN", SerialN);
cmd.Parameters.AddWithValue("@dateArrival",dateT);
cmd.Parameters.AddWithValue("@dateDeparture",departure);
cmd.Parameters.AddWithValue("@hoursSpent", hoursSpent);
cmd.ExecuteNonQuery();
这篇关于如何根据serial nr将值从一个表插入另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
