Insert, update, delete, display data in mysql and C# listbox using C#

Question

0.00/5 (No votes)

See more:

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();

}
}

Posted 27-May-22 20:38pm

Two Otaku A

Updated 27-May-22 22:24pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2022-05-27T22:24:00

Solution 2

The main problem is that you are using string concatenation in your SQL commands. This is dangerous and can lead to corruption, or even destruction, of your database tables. Learn to use proper parameterised queries.

Posted 27-May-22 22:24pm

Richard MacCutchan

Comments

Member 15627495 28-May-22 6:27am

one comment I add :

- write a Db class
*connect
*execute_query(string query) : imperative ::: update delete insert ( for query to the db )
*get_query_result(string query) : selective ::: "select...." ( to get result and rows from the db )
*commit()
*close()

you'll need this one everytime while coding DB

Richard MacCutchan 28-May-22 6:30am

What does this mean, and why are you telling me?

Member 15627495 · Answer 2 · 2022-05-27T22:17:00

Hello !

It's about few syntax error with queries building.

if your var is 'integer' , you don't have to enclose it between "integer" ( wrong ), put it as it is.

if it's 'string' type, enclose it "mystring",
if the query contains a dynamic 'string' , you have to close the query ( because the query is a String to ),
and to concatenate the dynamic var through the query string.
then due to the sql engine you need a ";" before the end of instruction ";"

[ vb syntax  "& " is "+" in cs]

integer ::: "select phone from USER where id=" & var & ";";
 
literals/string ::: select id from USER where phone =\"" & var_phone & "\";";   or
literals/string ::: select id from USER where phone =\'" & var_phone & "\';";

because String type is melt in another string.
' or " belong to your settings