Hi i have a problem in binding DateTimePicker value from one form named SearchDoctor to existing form named Doctor.
Here is the code of my Doctor form and it works:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
namespace DRRS_in_Csharp
{
public partial class Doctor : Form
{
long doctorID;
long pUserID;
string mDoctorID;
private string conString = "Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Tuscan46;Integrated Security=true";
private DataSet dataset;
public Doctor()
{
InitializeComponent();
SqlConnection conn = new SqlConnection(conString);
conn.Open();
string selectString = ("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name as doctor_fname, n.doctor_middle_name as doctor_mname,n.doctor_last_name as doctor_lname,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where n.doctor_id=p.doctor_id and p.doctor_id=@doctorId");
SqlDataAdapter dap = new SqlDataAdapter(selectString, conn);
dap.SelectCommand.Parameters.Add("@doctorId", SqlDbType.VarChar, 50);
dap.SelectCommand.Parameters["@doctorId"].Value = mDoctorID;
}
public void loadDoctor(string mDoctorID)
{
button1.Text="Save";
SqlConnection conn = new SqlConnection(conString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string selectString = ("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name as doctor_fname, n.doctor_middle_name as doctor_mname,n.doctor_last_name as doctor_lname,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where n.doctor_id=p.doctor_id and p.doctor_id=@doctorId");
SqlDataAdapter dap = new SqlDataAdapter(selectString, conn);
dap.SelectCommand.Parameters.Add("@doctorId", SqlDbType.VarChar, 50);
dap.SelectCommand.Parameters["@doctorId"].Value = mDoctorID;
dataset = new DataSet();
dap.Fill(dataset, "DOCTORDETAIL");
dap.Fill(dataset, "DOCTOR3");
txtFname.DataBindings.Add("Text", dataset, "DOCTORDETAIL.doctor_fname");
txtMname.DataBindings.Add("Text", dataset, "DOCTORDETAIL.doctor_mname");
txtLname.DataBindings.Add("Text", dataset, "DOCTORDETAIL.doctor_lname");
cboSex.DataBindings.Add("Text", dataset, "DOCTOR3.doctor_sex");
dtDOB.DataBindings.Add("Text", dataset, "DOCTOR3.doctor_dob");
}
private void button1_Click(object sender, EventArgs e)
{
string dFieldName = "";
Boolean vEmptyB = false;
if (txtFname.Text.ToString().Trim() == "")
{
vEmptyB = true;
dFieldName = "First name should not be empty";
}
else if (cboSex.SelectedIndex == -1)
{
vEmptyB = true;
dFieldName = "Sex of doctor should not be empty";
}
if (vEmptyB==true)
{
MessageBox.Show(dFieldName + "should not be empty");
return;
}
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Tuscan46;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
if (doctorID == 0)
{
cmd = new SqlCommand("Insert into DOCTOR3(doctor_sex,doctor_dob,created_date,user_id)" + "Values(@doctor_sex,@doctor_dob,GetDate(),@user_id)", conn);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@doctor_sex", SqlDbType.VarChar).Value = "M";
}
else
{
cmd.Parameters.AddWithValue("@doctor_sex", SqlDbType.VarChar).Value = "F";
}
cmd.Parameters.AddWithValue("@doctor_dob", dtDOB.Value);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
}
if (doctorID == 0)
{
cmd = new SqlCommand("Update DOCTORDETAIL set status=false where doctor_id=" + doctorID);
}
cmd = new SqlCommand("Insert into DOCTORDETAIL(doctor_first_name,doctor_middle_name,doctor_last_name,status,row_upd_date,user_id)" + "Values(@doctor_first_name,@doctor_middle_name,@doctor_last_name,@status,GetDate(),@user_id)", conn);
cmd.Parameters.AddWithValue("@doctor_first_name", txtFname.Text);
cmd.Parameters.AddWithValue("@doctor_middle_name", txtMname.Text);
cmd.Parameters.AddWithValue("@doctor_last_name", txtLname.Text);
cmd.Parameters.AddWithValue("@status", true);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
((MDIParent5)this.MdiParent).updateUserActivities(pUserID, 2, txtFname.Text + "doctor detail was added successfully");
MessageBox.Show("Doctor Detail was added successfully", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
private void Doctor_Load(object sender, EventArgs e)
{
dtDOB.MaxDate = dtDOB.Value;
}
private void cboSex_SelectedIndexChanged(object sender, EventArgs e)
{
int selecteditem = cboSex.SelectedIndex;
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
Here is code of my SearchDoctor form using listview named lstSearch.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace DRRS_in_Csharp
{
public partial class SearchDoctor : Form
{
private System.Windows.Forms.FormWindowState normal;
public SearchDoctor()
{
InitializeComponent();
this.lstSearch.DoubleClick += new System.EventHandler(this.lstSearch_DoubleClick);
}
private void lstSearch_DoubleClick(object sender, EventArgs e)
{
string vFieldNameStr;
vFieldNameStr = lstSearch.SelectedItems[0].SubItems[0].Text;
Doctor d = new Doctor();
d.loadDoctor(vFieldNameStr);
d.Show();
d.WindowState = normal;
}
private void btnsearch_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Tuscan46;Integrated Security=true");
lstSearch.Items.Clear();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
DataSet ds;
SqlDataAdapter adp = new SqlDataAdapter();
lstSearch.Columns.Add("ID", 110, HorizontalAlignment.Left);
lstSearch.Columns.Add("DOB", 100, HorizontalAlignment.Center);
lstSearch.Columns.Add("NAME", 180, HorizontalAlignment.Center);
lstSearch.Columns.Add("SEX", 65, HorizontalAlignment.Right);
lstSearch.Visible = true;
lstSearch.View = View.Details;
lstSearch.GridLines = true;
if (txtDoctorID.Text.Trim() != "")
{
cmd = new SqlCommand("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name+' '+n.doctor_middle_name+' '+n.doctor_last_name as doctor_name,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where p.doctor_id=n.doctor_id and n.doctor_id=" + txtDoctorID.Text.Trim() + "", conn);
adp = new SqlDataAdapter(cmd);
ds = new DataSet();
adp.Fill(ds, "DOCTORDETAIL" + "DOCTOR3");
dt = ds.Tables["DOCTORDETAIL" + "DOCTOR3"];
}
else if (txtName.Text.Trim() != "")
{
cmd = new SqlCommand("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name+' '+n.doctor_middle_name+' '+n.doctor_last_name as doctor_name,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where p.doctor_id=n.doctor_id and ((n.doctor_first_name like '%" + (txtName.Text.Trim()) + "%') or (n.doctor_middle_name like '%" + (txtName.Text.Trim()) + "%') or (n.doctor_last_name like '%" + (txtName.Text.Trim()) + "%'))", conn);
adp = new SqlDataAdapter(cmd);
ds = new DataSet();
adp.Fill(ds, "DOCTORDETAIL" + "DOCTOR3");
dt = ds.Tables["DOCTORDETAIL" + "DOCTOR3"];
}
else if (dtDOB.Value.ToLongDateString() != "")
{
cmd = new SqlCommand("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name+' '+n.doctor_middle_name+' '+n.doctor_last_name as doctor_name,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where n.doctor_id=p.doctor_id and p.doctor_dob=" + dtDOB.Value + "", conn);
adp = new SqlDataAdapter(cmd);
ds = new DataSet();
adp.Fill(ds, "DOCTORDETAIL" + "DOCTOR3");
dt = ds.Tables["DOCTORDETAIL" + "DOCTOR3"];
}
for (int i = 0; i < dt.Rows.Count; i++)
{
lstSearch.Items.Add(dt.Rows[i].ItemArray[0].ToString());
lstSearch.Items[i].SubItems.Add(dt.Rows[i].ItemArray[1].ToString());
lstSearch.Items[i].SubItems.Add(dt.Rows[i].ItemArray[2].ToString());
lstSearch.Items[i].SubItems.Add(dt.Rows[i].ItemArray[3].ToString());
}
}
private void btncancel_Click(object sender, EventArgs e)
{
this.Close();
}
private void SearchDoctor_Load(object sender, EventArgs e)
{
}
private void lstSearch_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
}
The above code works for binding ID named txtDoctorID and Name named txtName(both are textboxes) except for DOB named dtDOB. I have enabled FullRowSelect property in my listview named lstSearch.
Given below is my DateTimePicker name and it's properties that i have used in my Doctor form:
DateTimePicker name: dtDOB
AccessibleRole: Default
AllowDrop: false
CausesValidation: true
Checked: false
CustomFormat: dd/MM/yyyy
enabled: true
Format: Custom
GenerateMember: true
ImeMode: NoControl
MaxDate:31-Dec-98
MinDate:01-Jan-53
Value: 22-Nov-03(2003) 2:03PM
visible:true
Given below is my DateTimePicker name and it's properties that i have used in my SearchDoctor form:
DateTimePicker name: dtDOB
AccessibleRole: Default
AllowDrop: false
CausesValidation: true
Checked: false
CustomFormat: dd-MMM-yyyy
enabled:true
Format: Custom
GenerateMember: true
ImeMode: NoControl
MaxDate:31-Dec-98
MinDate:01-Jan-53
Value: 30-Jan-14(2014) 11:14AM
visible:true
Now i have tried the below code for dtDOB in my SearchDoctor form :
else if (dtDOB.Value.ToLongDateString() != "")
{
cmd = new SqlCommand("Select p.doctor_id as doctor_id,p.doctor_dob as doctor_dob,n.doctor_first_name+' '+n.doctor_middle_name+' '+n.doctor_last_name as doctor_name,p.doctor_sex as doctor_sex from DOCTORDETAIL n,DOCTOR3 p where n.doctor_id=p.doctor_id and p.doctor_dob=" + dtDOB.Value + "", conn);
adp = new SqlDataAdapter(cmd);
ds = new DataSet();
adp.Fill(ds, "DOCTORDETAIL" + "DOCTOR3");
dt = ds.Tables["DOCTORDETAIL" + "DOCTOR3"];
}
But i get error SqlException occured:
Incorrect syntax near '10'.
Can anyone help me on how to bind dtDOB value using sql select query in SearchDoctor form to existing form Doctor. Any help would be greatly appreciated.
