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)
);
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();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
}
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>