datagridview

[diya45]

hi

In room table the roomtype and roomtypeid i created columns then the problem is that when i run the form and add data in database then in roomtypeID the show is double or single 

i want that in roomtypeID they show as 1 or 2 in roomtypeid column which is not  shown 

 in sql roomtype table i also fill the data like that

 RoomtypeID   RoomType
 1             DOUBLE
 2             sINGLE



the room table is like that

RooMid    Roomno    RoomTypeID
9          345       Double

then how is shown 1 or 2 in roomtypeid instead of double or single

[diya45]

namespace hotelmanagmentsystem
{
    class RoomInfo
    {
        SqlDataAdapter da = new SqlDataAdapter();
        string room_id;

        public string Room_id
        {
            get { return room_id; }
            set { room_id = value; }
        }
        private string room_type;

        public string Room_type
        {
            get { return room_type; }
            set { room_type = value; }
        }
        private int room_no;

        public int Room_no
        {
            get { return room_no; }
            set { room_no = value; }
        }



        public DataTable displaydata(string room_id)
        {
            DataSet ds = new DataSet();
            string ConString = @"Data source = .\SqlExpress ; Database=htm ;Integrated security=true";
            SqlConnection con = new SqlConnection(ConString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"SELECT     dbo.Room.Room_ID,dbo.Room.Room_no,dbo.RoomType.RoomTypeID,dbo.RoomType.RoomType
FROM         dbo.Room INNER JOIn


                      dbo.RoomType ON dbo.Room.RoomtypeID = dbo.RoomType.RoomTypeID 
='" + room_id + "'", con);
            da.Fill(ds);
           // con.Open();
            return ds.Tables[0];
        }


        public DataTable displaydata()
        {
            DataSet ds = new DataSet();


            string ConString = @"Data source = .\SqlExpress ; Database=htm ;Integrated security=true";

            SqlConnection con = new SqlConnection(ConString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"SELECT     dbo.Room.Room_ID,dbo.Room.Room_no,dbo.RoomType.RoomTypeID,dbo.RoomType.RoomType
FROM         dbo.Room Inner JOIn


                      dbo.RoomType ON dbo.Room.RoomtypeID = dbo.RoomType.RoomTypeID 
", con);
            da.Fill(ds);
            return ds.Tables[0];



        }
        public void add(int Room_no, string RoomTypeID)
        {

            string ConString = @"Data Source=USER-PC\SQLEXPRESS;Initial Catalog=htm;Integrated Security=True";
            SqlConnection con = new SqlConnection(ConString);
            string query = "Insert into Room(room_no,room_type) values (" + room_id + "," + Room_no + "')";
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            SqlCommand com = new SqlCommand("insertroominfo", con);


            com.CommandType = CommandType.StoredProcedure;
            //com.Parameters.Add(new SqlParameter("@guest_ID", guest_id));
            com.Parameters.Add(new SqlParameter("@room_no", Room_no));
            com.Parameters.Add(new SqlParameter("@RoomTypeID", RoomTypeID));
            com.ExecuteNonQuery();
            con.Close();

[diya45]

and the room type coding is

namespace hotelmanagmentsystem
{
    class RoomType
    {
        private string RoomTypeID;

        public string RoomTypeID1
        {
            get { return RoomTypeID; }
            set { RoomTypeID = value; }
        }
        private string Roomtype;

        public string Roomtype1
        {
            get { return Roomtype; }
            set { Roomtype = value; }
        }

        public DataTable Getroom()
        {
            DataSet ds = new DataSet();
            string ConString = @"Data source = .\SqlExpress ; Database=htm ;Integrated security=true";
            SqlConnection con = new SqlConnection(ConString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand("Select * from RoomType", con);
            da.Fill(ds);
            return ds.Tables[0];

        }

[diya45]

and the form coding is 
namespace hotelmanagmentsystem
{
    public partial class Room : Form
    {
    namespace hotelmanagmentsystem
{
    public partial class Room : Form
    {
        RoomInfo rooms = new RoomInfo();
        DataSet ds = new DataSet();
        BindingSource bs = new BindingSource();
        public void clearfield1()
        {
            room_id.Text = "";
            room_no.Text = "";
            room_type.SelectedIndex = 0;
        }

        public Room()
        {
            InitializeComponent();
        }

        public DataTable displaydata(string room_id)
        {
            DataSet ds = new DataSet();
            string ConString = @"Data source = .\SqlExpress ; Database=htm ;Integrated security=true";         
            SqlConnection con = new SqlConnection(ConString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"SELECT     dbo.Room.Room_ID,dbo.Room.Room_no,dbo.RoomType.RoomTypeID,dbo.RoomType.RoomType FROM         dbo.Room INNER JOIn  dbo.RoomType ON dbo.Room.RoomTypeID = dbo.RoomType.RoomTypeID '" + room_id + "'", con);


            ds.Clear();
            try
             {

               // da.SelectCommand = new SqlCommand(query, con);
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                bs.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            //return "done";
            return ds.Tables[0];
        }


        public DataTable displaydata()
        {
            DataSet ds = new DataSet();


            string ConString = @"Data source = .\SqlExpress ; Database=htm ;Integrated security=true";

            SqlConnection con = new SqlConnection(ConString);
            SqlDataAdapter da = new SqlDataAdapter();
            string query = "SELECT     dbo.Room.[Room_ID],dbo.Room.Room_no,dbo.RoomType.RoomTypeID,dbo.[RoomType].[RoomType] FROM dbo.Room INNER JOIn  dbo.[RoomType] ON dbo.Room.[RoomtypeID] = dbo.[RoomType].RoomTypeID";

            //da.Fill(ds);
            ds.Clear();
            try
            {

                da.SelectCommand = new SqlCommand(query, con);
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                bs.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

           return ds.Tables[0];



        }


        private void Add_Click(object sender, EventArgs e)
        {
            try
            {


                rooms = new RoomInfo();
                rooms.Room_id = room_id.Text;
                rooms.Room_type = Convert.ToString(room_type.SelectedIndex);
                rooms.Room_no = Convert.ToInt32(room_no.Text);
                dataGridView1.DataSource = rooms.displaydata();
                rooms.add(Convert.ToInt32(room_id.Text), room_type.Text);
                MessageBox.Show("Room Added Successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearfield1();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

[diya45]

can anyone tell me

[ChrisHunter]

I take it you want the user to select the word "double" or "Single from a combo box but the value 1 or 2 to be shown in the room table when the selection is saved, depending on which room type was chosen ?

If that is the case you need to:

• Make 'roomtypeid' in the room table a foreign key that references the primary key of the roomtype table

• Create a stored procedure with a select statement like below to select the descriptive field of a room type dependent on the roomtype of the room selected (return "double" instead of 1. @roomID is a parameters which will be the roomID for the room requested.

  CREATE PROCEDURE RoomSelect
  @roomID int
  AS
  SELECT room.RoomID, room.RoomNumber, roomtype.RoomType
  FROM room Left Join roomtype on room.RoomTypeID = roomtype.RoomTypeID
  WHERE room.RoomID = @roomID;

Try not to post ALL of your code, summerise your problem instead other wise you will put people off wanting to help because theres too much to bother reading.

[a1brains]

Thanks for share this great script for the data gread view. I can use this script code for my project.

[diya45]

can i use this select statement of store procedure in also 36,57,31,62

which i warite inner join instead of left join???

[diya45]

but i show in grid view also roomtypeID whcih i s 1 0r 2

[ChrisHunter]

Yes you can use the SQL if you like just try and understand it first. If you want to show the roomTypeID too just selected from your Room table during the select part of the statement.

[diya45]

i can do but not show roomtypeid as 1 or 2

[ChrisHunter]

So do you want to show both the roomTypeID AND roomType?

You can display everything returned by the SQL statement or just what you need.

[ChrisHunter]

I think I didn't understand you correctly yesterday.

• When you select a room from the database will you be entering the roomID OR roomNo
• Do you want to show the roomID and the roomTypeID in your datagrid view?