[RESOLVED] Compare sql table totext input

I am using Visual Web Developer 2010 to create a "project" for school. It is pretty simple.



The idea is for a patient self-check-in kiosk for a medical facility.



I already have my sql database created with the following:



    Table: Doctors

    Fields: Doctor_ID (PK), Doctor_F_Name, Doctor_L_Name

    

    Table: Patients

    Fields: Patient_ID (PK), Patient_F_Name, Patient_L_Name

    

    Table: Appointments

    Fields: Appointment_ID (PK), Appt_Day, Appt_Mo, Appt_Yr, Patient_ID (SK), Doctor_ID (SK), Appt_Time, Appt_Reason, Checked_In



So the first page of the code has a simple layout with 3 text entry boxes asking for the patients first name, last name, and last 4 of SSN (`Patient_ID`) and a submit button.




I would like for the data from the text boxes to be used to compare to the database and ask the question "are you here to check-in for your appointment today at (`Appt_Time`) with (Dr. `Doctor_L_Name`) for (`Appt_Reason`)?) and then a check box or drop down
for yes or no, if yes is chosen then it inserts a "1" (`binary`) into checked_in field




I have gotten the text boxes and button created:







   

    

   

First Name

   

   

Last Name

   

   

Last 4 of SSN

   

   

   



Below this I have a few debug checks to make sure communication with the sql server is working, and I can pull table information into a gridview without issue using the following connection settings:



   
    selectcommand="select * from [Patients]" />



Then running a gridview:



   

   

   

   

   

   

   



This successfully returns the "patients" already entered into the database.



Any help creating/finishing this would be great. I am getting very tired of searching for tips, trying, failing searching, trying, failing.....




Thanks,

Eric

Hi Ducky

    

If you have any question of technology please let me known, according to your description I find you just describe how to implement the function, and does not mention any technical problem.

i understand ur problem

use onblur javascript method of textbox and check through webservice and return the value

please feel free to ask for further clarification

Hi ducky

According to you description, my understanding is that patient enter the "patientID" then website compare to the database and confirm to user, if user choose the ok, then operation the database. My understand is correctly? Here is a solution according to
my understanding:

1. Add a file compare.ashx in the project, it used to be called by ajax and compare patientID with database.
   
2. Add a button it used to be called by javascript, you can process your business in it click event.
   
3. Add some javascript in the head tag, it can called behind by ajax, and process business by user's feedback.
   

The aspx file code

<%@ Page Title="主?" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CompareSQLTable._Default" %>

    


    


    
 First Name 
    
    
 Last Name 
    
    
 Last 4 of SSN 
    
    
    
    
        SelectCommand="select * from [Patients]" />
    
    
    

The compare.ashx.cs file code

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace CompareSQLTable
{
    /// 

    /// Compare 的摘要?明
    /// 
    public class Compare : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string patientID = context.Request.QueryString["id"];//receive then patientID from client
            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Demo072401;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("select count(*) from Appointments where Patient_ID = @patientID", conn);
            //string patientID = Textbox3.Text;
            SqlParameter para = new SqlParameter("@patientID", patientID);
            cmd.Parameters.Add(para);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int count = (int)cmd.ExecuteScalar();//return the number of matches
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            if (count > 0)
            {
                context.Response.Write("1");
            }
            else
                context.Response.Write("0");
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

note:The result of ajax called, it is only for testing, if you want more, you can do it in the same way. If you have any question, please let me known.

I hope it can help you.