I have 2 tables in SQL.
https://www.dropbox.com/s/7f1uipr4v0trxte/table%20staff.png?dl=0[
^]
Table 1: "
Staff"
Staff_ID (PK, int, NOT NULL)
Meeting_With (there are 5 names in the table to select from)
https://www.dropbox.com/s/lznvcd06f8m7viy/Table%20visitor.png?dl=0[
^]
Table 2: "
Visitor"
Visitor_ID (PK, int, NOT NULL)
Name
Surname
Mobile
email
Date
Time
Meeting_Aim
Staff_ID (FK, int, NULL)
I also have a
Visitor Registration Form with a Listbox in C# Visual Studio.
https://www.dropbox.com/s/l9h0lthun0ioatj/FORM.png?dl=0[
^]
Visitor ID
Name
Surname
Mobile
Email
Date
Time
Meeting With
Meeting Aim
Staff ID
visitor_textbox = "Visitor_ID"
visitor_nametext = "Name"
visitor_surnametext = "Surname"
visitor_mobiletext = "Mobile"
visitor_emailtext = "Email"
visitor_timepick = "Date"
visitor_timepick = "Time"
visitor_meetingaimbutton = "Meeting_Aim"
visitor_meetingtext = "Meeting_With"
Staff_ID = "Staff_ID"
These are the assignment tasks that need to be performed:
1: Insert data into the Visitor registration form and see if the data has also been added to the "Visitor" table.
2: Edit an item from Listbox in the Visitor registration form and see if the data has also been edited in the "Visitor" table.
3: Save/Update the item in Listbox in the Visitor registration form and see if the data has also been Save/Updated in the "Visitor" table.
I have written what errors I am getting in the code section. I would really appreciate it if anyone could help me. Thank you.
What I have tried:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Project_Charlie
{
public partial class visitor_registration : Form
{
string connString = @"Data Source=PC;Initial Catalog=VisitorInfo;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
int Vist_ID = 0;
public static visitor_registration Self;
Aim aim = new Aim();
public visitor_registration()
{
InitializeComponent();
Self = this;
}
private void visitor_registration_Load(object sender, EventArgs e)
{
visitor_listbox_Data_Load();
Staff_Data_Load();
}
public void visitor_listbox_Data_Load()
{
SqlConnection conn = new SqlConnection(connString);
string sql_Query = "Select Visitor.Visitor_ID, Visitor.Name, Visitor.Surname, Visitor.Mobile, Visitor.Email, Visitor.Date, Visitor.Time, Visitor.Meeting_Aim, Staff.Meeting_With From Visitor, Staff Where Visitor.Staff_ID = Staff.Staff_ID";
conn.Open();
SqlCommand cmd = new SqlCommand(sql_Query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
visitor_listbox.Items.Add((reader["Visitor_ID"] + "_" + reader["Name"] + "_" + reader["Surname"] + "_" + reader["Mobile"] + "_" + reader["Email"] + "_" + reader["Date"] + "_" + reader["Time"] + "_" + reader["Meeting_Aim"] + "_" + reader["Meeting_With"]));
}
reader.Close();
conn.Close();
}
public void Staff_Data_Load()
{
SqlConnection conn = new SqlConnection(connString);
string sql_Query2 = "Select Staff_ID, Meeting_With From Staff";
SqlCommand cmd2 = new SqlCommand(sql_Query2, conn);
conn.Open();
SqlDataReader reader = cmd2.ExecuteReader();
while (reader.Read())
{
visitor_meetingtext.Items.Add(reader["Meeting_With"]);
}
reader.Close();
conn.Close();
}
// for "
Insert_Click_Click" I am getting this error: 'Conversion Failed when converting date and/or time from character string'. I did change the time and date format to YYYY-MM-DD for date and hh:mm:ss for time, but I am still getting the same error.
private void Insert_Click_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connString);
string sql_Query3 = "Insert into Visitor(Name, Surname, Mobile, Email, Date, Time, Meeting_Aim, Staff_ID) values('" + this.visitor_nametext.Text + "','" + this.visitor_surnametext.Text + "','" + this.visitor_mobiletext.Text + "','" + this.visitor_emailtext.Text + "','" + this.visitor_timepick.Text + "','" + this.visitor_timepick.Text + "','" + this.visitor_meetingaimbutton.Text + "','" + this.Staff_ID.Text + "');";
SqlCommand cmd4 = new SqlCommand(sql_Query3, conn);
conn.Open();
cmd4.ExecuteNonQuery();
MessageBox.Show("Data Saved");
conn.Close();
visitor_listbox_Data_Load();
}
// "Staff_ID_Function" is the SelectedValueChanged in the Events of the Listbox.
// for "
Staff_ID_Function" I am getting this error: 'Object reference not set to an instance of an object'. This error appears in the "String Staff_Query" section of the code.
private void Staff_ID_Function(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connString);
String Staff_Query = "Select Staff_ID From Staff Where Meeting_With='" + visitor_meetingtext.SelectedItem.ToString() + "'";
SqlCommand cmd3 = new SqlCommand(Staff_Query, conn);
conn.Open();
SqlDataReader reader = cmd3.ExecuteReader();
while (reader.Read())
{
Staff_ID.Text = reader["Staff_ID"].ToString();
}
conn.Close();
}
// "visitor_listbox_MouseClick" is the "Click" in the Events of the Listbox.
// for "
visitor_listbox_MouseClick" I am getting this error: 'Input string was not a correct format'. This error appears in the "Vist_ID" section of the code.
private void visitor_listbox_MouseClick(object sender, EventArgs e)
{
var selectedValue = visitor_listbox.SelectedItem;
if (selectedValue != null)
{
MessageBox.Show(selectedValue.ToString());
}
string StudentData = visitor_listbox.SelectedItem.ToString();
string[] Field_Data = StudentData.Split('-');
Vist_ID = Int16.Parse(Field_Data[0]);
SqlConnection conn = new SqlConnection(connString);
string sql_Query = "Select Visitor.Visitor_ID, Visitor.Name, Visitor.Surname, Visitor.Mobile, Visitor.Email, Visitor.Date, Visitor.Time, Visitor.Meeting_Aim, Staff.Staff_ID, Staff.Meeting_With From Visitor, Staff Where Visitor.Staff_ID = Staff.Staff_ID AND Visitor_ID =" + Vist_ID;
SqlCommand cmd = new SqlCommand(sql_Query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
visitor_textbox.Text = reader["Visitor_ID"].ToString();
visitor_nametext.Text = reader["Name"].ToString();
visitor_surnametext.Text = reader["Surname"].ToString();
visitor_mobiletext.Text = reader["Mobile"].ToString();
visitor_emailtext.Text = reader["Email"].ToString();
visitor_timepick.Text = reader["Date"].ToString();
visitor_timepick.Text = reader["Time"].ToString();
visitor_meetingaimbutton.Text = reader["Meeting_Aim"].ToString();
visitor_meetingtext.Text = reader["Meeting_With"].ToString();
Staff_ID.Text = reader["Staff_ID"].ToString();
}
reader.Close();
conn.Close();
}
// for "
Update_Click_Click" I am getting this error:
Unclosed quotation mark after the character string 'Where Visitor_ID=';'.
Incorrect syntax near 'Where Visitor_ID=';'.'
//these appear in the "string sql_Query4" section of the code.
private void Update_Click_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connString);
string sql_Query4 = "Update Visitor set Name ='" + this.visitor_nametext.Text + "',Surname ='" + this.visitor_surnametext.Text + "',Mobile ='" + this.visitor_mobiletext.Text + "',Email ='" + this.visitor_emailtext.Text + "',Date ='" + this.visitor_timepick.Text + "',Time ='" + this.visitor_timepick.Text + "',Meeting_Aim ='" + this.visitor_meetingaimbutton.Text + "',Staff_ID =" + this.Staff_ID.Text + "' Where Visitor_ID='" + visitor_textbox.Text + "';";
SqlCommand cmd5 = new SqlCommand(sql_Query4, conn);
conn.Open();
cmd5.ExecuteNonQuery();
MessageBox.Show("Data Updated");
conn.Close();
visitor_listbox_Data_Load();
}
private void Delete_Click_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connString);
string sql_Query = "Delete From Visitor Where Visitor_ID ='" + visitor_textbox.Text + "'";
SqlCommand cmd5 = new SqlCommand(sql_Query, conn);
conn.Open();
cmd5.ExecuteNonQuery();
MessageBox.Show("Data Deleted");
conn.Close();
visitor_listbox_Data_Load();
}
}