How to solve this problem?

Question

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 13-Nov-22 0:16am

Member 15802819

Updated 13-Nov-22 0:50am

Add a Solution

Comments

PIEBALDconsult 13-Nov-22 9:38am

Yeah, what Griff said. And never use identity columns.

1 solution

Add a Solution

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

OriginalGriff · Answer 1 · 2022-11-13T00:50:00

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?

How to solve this problem?

1 solution

Solution 1

Add your solution here

Preview 0

How to solve this problem?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us