Insert into c# sql

Avuya Mxoli

I'm trying to insert records in a table in access and also in a grid thats in my program. Now my problem is tht I want to Insert columns that are from 2 seperate tables in my grid but insert only some of them in the actual access database. here is the general feel of this: Access database table name is Appointment the fields are PatientID, DoctorID,AppointmentNumber, AppointmentDate. All are text data types except the date. Then I have my grid which should display the PatientID,PatientSurname,PatientName,DoctorID,AppointmentDate. Now I am having trouble reading from these 2 tables in a single Insert Into...can anyone help
I am using c# and a 2007 access database
Thanks

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Avuya Mxoli15y ago
i am using text boxes to add new appointments for the patients and these appointments are shown on the grid once i press the add button and the new appointment is added to the appointmnt table in access. I think i need to use an INSERT INTO statement to insert new records correct? the code u just gave me, what does it update and what happens to the parameters?
Suthish Nair15y ago
like this..

select a.[Patient ID], a.[Doctor ID]
        , a.[Appointment Date], a.[Appointment Number]
        , p.PatientSurname, p.PatientName
from Appointment a inner join Patient p
       ON a.[Patient ID] = p.[Patient ID]
Avuya Mxoli15y ago
I cant insert the surname and first name in the appointmnt table bcoz those columns dont exist like iv bn saying. Can anyone tell me how to achieve this? I only need to display the xtra fields in the grid
Suthish Nair15y ago
You need to join both tables using PK and fetch those columns to display records in grid.
Avuya Mxoli15y ago
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
if (dbConn.State == ConnectionState.Closed)
dbConn.Open();
string sql = " INSERT INTO Appointment ([Patient ID], [Doctor ID], [Appointment Date], [Appointment Number] ) VALUES (?,?,?,?)";
dbCmd = new OleDbCommand(sql, dbConn);
//these are inserted in my access database table which is fine but then I want to
// insert them together with other columns i.e First Name, Surname from the patient table into the grid
//using the same button click event
dbCmd.Parameters.AddWithValue("[Patient ID]", txtID.Text);
dbCmd.Parameters.AddWithValue("[Doctor ID]", txtDoc.Text);
dbCmd.Parameters.AddWithValue("[Appointment Date]", txtDate.Text);
dbCmd.Parameters.AddWithValue("[Appointment Number]", txtNum.Text);
try
{
int x = dbCmd.ExecuteNonQuery();
if (x > 0)
{
dsAppointments.Tables["NextAppts"].Rows.Add(new object[]{txtID.Text,
txtDoc.Text, txtDate.Text,txtNum.Text});
}
}
catch
{

}
txtID.Clear();
txtDoc.Clear();
txtDate.Clear();
txtID.Focus();
}
Mittal Sejpal15y ago
attach the page or give the code!!!

it can be achieved by applying join!