Click here to Skip to main content
15,867,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the following database schema

column Datatype

appointment_id int (it is set as primary key and identity
specification true)
patient_name varchar(50)
patient_email varchar(50)
doctor_name varchar(50)
doctor_email varchar(50)
treat_for varchar(50)
date date
time time



Design part in asp.net

appointment_id TextBox1
patient_name TextBox2
patient_email TextBox3
doctor_name TextBox4
doctor_email TextBox5
treat_for TextBox6
date TextBox7
time Dropdown list



Here i want appointment id as auto generated ID ie it should get ID automatically generate by system.
I have written the code but i don'tknow how to automatically generate ID by the system.
My database is null so how to write query to get the appointment_ID .ie If the first user going insert value appointment id should be 1 then next 2 goes on

What I have tried:

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

public partial class appointment : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=USER Initial Catalog=Project;Integrated Security=True");
    string uname;
    protected void Page_Load(object sender, EventArgs e)
    {

        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
    }
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {

    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void Button1_Click1(object sender, EventArgs e)
    {

        uname = Session["email"].ToString();
        con.Open();
        string sql = "insert into appointmt values('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+TextBox6.Text+"','"+TextBox7.Text+"','" + DropDownList1.SelectedItem.Text + "')";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.ExecuteNonQuery();
        Response.Write("alert('Booked successfully')");
        con.close();
    }
}
Posted
Updated 13-Nov-22 0:50am
Comments
PIEBALDconsult 13-Nov-22 9:38am    
Yeah, what Griff said. And never use identity columns.

1 solution

When you create an IDENTITY column, you are telling the database system to manage it for you: every time you INSERT a new row, it will automatically assign a new value to it for you, you don't have to do anything to fill in its value - in fact, you can't give it a value yourself without the database complaining!

THe problem is that you don't specify which value is to go into which column, so SQL will assign them starting from the first column going on from there. Since the convention is to have ID values at the start, that means that you re trying to assign the content of textBox2 to the ID column, and you get an error.
Always specify column names: it makes your code more "future proof" and thus more reliable. The syntax is:
SQL
INSERT INTO MyTable (Column1Name, Column2Name) VALUES (ValueForColumn1, ValueForColumn2)


But ... don't do it like you are! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900