Link to home
Start Free TrialLog in
Avatar of Alomiry
Alomiry

asked on

i got double value in database

Hello,
Please can u help me to review my code becuse when i insert data from my form, i got a different row for my image at HOTELS TABLE and double value in my ROOM_TYPE TABLE

I mean when i click the button to submit the form,the value it's saving in the database.
But the Image row in the HOTELS Table saving in different row, i mean the value of the HOTEL in one row and The image in another one even if the are in same TABLE (Hotels TABLE)

my code behind:

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

namespace BacholerProject
{
    public partial class AddHotel : System.Web.UI.Page
    {

        string AddType;
        string AddRates;
        string AddHotelName;
        string AddCity;
        string AddStreet;
        string AddZipCode;
        string AddCountry;
        string AddBuildingNo;

        string AddContactName;
        string AddPhone;
        string AddFax;
        string AddWebsite;
        string AddEmail;

        string AddHotelDescription;
        string AddCheckIn;
        string AddCheckOut;

        string AddDistanceCenter;
        string AddDistanceTrain;
        string AddDistanceBeach;

        string AddFacilities;
        string AddService;

        string AddRoomName;
        string AddRoomName2;
        string AddRoomName3;
        string AddRoomDescription;
        string AddRoomDescription2;
        string AddRoomDescription3;

        /// int AddPersonPerRoom;
        ///int AddPersonPrice;

        string AddTerms;
        string AddPolicy;


        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Addyourhotel_Click(object sender, EventArgs e)
        {
            AddType = id_type.SelectedItem.Value;
            AddRates = id_rate.SelectedItem.Value;
            AddHotelName = id_HotelName.Text;
            AddCity = id_city.Text;
            AddStreet = id_street.Text;
            AddZipCode = id_zipCode.Text;
            AddCountry = id_country_select.SelectedItem.Value;
            AddBuildingNo = id_buildingNo.Text;

            AddContactName = id_name.Text;
            AddPhone = id_phone.Text;
            AddFax = id_fax.Text;
            AddWebsite = id_web.Text;
            AddEmail = id_email.Text;

            AddHotelDescription = id_hotelDescription.Text;
            AddCheckIn = id_checkIn.Text;
            AddCheckOut = id_checkOut.Text;

            AddDistanceCenter = id_distance_center.Text;
            AddDistanceTrain = id_distance_train.Text;
            AddDistanceBeach = id_distance_beach.Text;

            AddFacilities = id_facilities.Text;
            AddService = id_service.Text;

            AddRoomName = id_room_name.Text;

            AddRoomName2 = id_room_name2.Text;

            AddRoomName3 = id_room_name3.Text;


            AddRoomDescription = id_room_description.Text;

            AddRoomDescription2 = id_room_description2.Text;

            AddRoomDescription3 = id_room_description3.Text;


            ///AddPersonPerRoom = Convert.ToInt32(id_room_person.Text);
            ///AddPersonPrice = Convert.ToInt32(id_room_price.Text);

            AddTerms = id_terms.Text;
            AddPolicy = id_policy.Text;


            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString =
            @"Data Source=localhost\SQLEXPRESS;
                   AttachDbFilename=C:\Users\Anas\Documents\Visual Studio 2010\Projects\BacholerProject\BacholerProject\App_Data\Booking.mdf;
                   Integrated Security=True;
                   User Instance=True";

            cnn.Open();


            SqlCommand query = cnn.CreateCommand();

            query.CommandText = "INSERT INTO [Hotels] ([Type],[Rates],[Name],[City],[Street],[ZipCode],[Country],[BulidingNumber],[ContactFullName],[PhoneNumber],[FaxNumber],[Website],[Email],[Hotel_description],[Check_in],[Check_out],[Distance_center],[Distance_train],[Distance_beach],[FacilitiesDescription],[ServiceDescription],[Terms_condition],[Policy]) VALUES ('" + AddType + "','" + AddRates + "','" + AddHotelName + "','" + AddCity + "','" + AddStreet + "','" + AddZipCode + "','" + AddCountry + "','" + AddBuildingNo + "','" + AddContactName + "','" + AddPhone + "','" + AddFax + "','" + AddWebsite + "','" + AddEmail + "','" + AddHotelDescription + "','" + AddCheckIn + "','" + AddCheckOut + "','" + AddDistanceCenter + "','" + AddDistanceTrain + "','" + AddDistanceBeach + "','" + AddFacilities + "','" + AddService + "','" + AddTerms + "','" + AddPolicy + "') SELECT SCOPE_IDENTITY();";

            decimal hotelid = (decimal)query.ExecuteScalar();
            //var hotelid = query.ExecuteScalar();


            query.CommandText = "INSERT INTO [Room_type] (Hotels_id, Name, Description)VALUES('" + hotelid.ToString() + "', '" + AddRoomName + "', '" + AddRoomDescription + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "','" + AddRoomDescription2 + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName3 + "','" + AddRoomDescription3 + "')";

            query.ExecuteNonQuery();

            if (Page.IsValid) //save the image
            {
                Stream imgStream = UploadFile.PostedFile.InputStream;
                int imgLen = UploadFile.PostedFile.ContentLength;
                string img_contenttype = UploadFile.PostedFile.ContentType;
                string img_name = txtImgName.Value;
                byte[] img_data = new byte[imgLen];
                int n = imgStream.Read(img_data, 0, imgLen);

                String idis = Request.QueryString["id"];
                int business_id = System.Convert.ToInt32(idis);

                int RowsAffected = SaveToDB(img_name, img_data, img_contenttype);
                if (RowsAffected > 0)
                {
                    TextBox TextBox2 = new TextBox();
                    TextBox2.Text = "The Image was Saved";
                    Response.Write("<BR>The Image was saved");
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }

        }




        private int SaveToDB(string img_name, byte[] img_data, string img_contenttype)
        {
            //use the web.config to store the connection string


            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString =
              @"Data Source=localhost\SQLEXPRESS;
                   AttachDbFilename=C:\Users\Anas\Documents\Visual Studio 2010\Projects\BacholerProject\BacholerProject\App_Data\Booking.mdf;
                   Integrated Security=True;
                   User Instance=True";
         
                cnn.Open();
         
            SqlCommand query = cnn.CreateCommand();

            query.CommandText = "INSERT INTO Hotels (Image_name,Image_data, Image_contenttype) VALUES ( @img_name, @img_data,@img_contenttype)";
           
           

            SqlParameter param0 = new SqlParameter("@img_name", System.Data.SqlDbType.VarChar, 50);
            param0.Value = img_name;
            query.Parameters.Add(param0);

            SqlParameter param1 = new SqlParameter("@img_data", System.Data.SqlDbType.Image, img_data.Length);
            param1.Value = img_data;
            query.Parameters.Add(param1);



            SqlParameter param2 = new SqlParameter("@img_contenttype", System.Data.SqlDbType.VarChar, 50);
            param2.Value = img_contenttype;
            query.Parameters.Add(param2);


            Label1.Text = "<b>Server Version:</b> " + cnn.ServerVersion;

            int numRowsAffected = query.ExecuteNonQuery();
            cnn.Close();

            return numRowsAffected;
             
        }





    }

}
Avatar of officedog
officedog

<sarcasm>
Please give me the production URL for the website so I can extract all your information and then delete it
</sarcasm>

In all seriousness, this code is very messy (read poor). Use proper stored procedures, sanitise the input and use sql profiler if still not working.
Avatar of Alomiry

ASKER

i don't have production URL.
I know my code is very messy but I'm still beginner and i don't know from where i have to start?
Can you Please guide me what i have to do.

Thanks for help.
Avatar of srikanthreddyn143
You are inserting two times.


            query.CommandText = "INSERT INTO [Hotels] ([Type],[Rates],[Name],[City],[Street],[ZipCode],[Country],[BulidingNumber],[ContactFullName],[PhoneNumber],[FaxNumber],[Website],[Email],[Hotel_description],[Check_in],[Check_out],[Distance_center],[Distance_train],[Distance_beach],[FacilitiesDescription],[ServiceDescription],[Terms_condition],[Policy]) VALUES ('" + AddType + "','" + AddRates + "','" + AddHotelName + "','" + AddCity + "','" + AddStreet + "','" + AddZipCode + "','" + AddCountry + "','" + AddBuildingNo + "','" + AddContactName + "','" + AddPhone + "','" + AddFax + "','" + AddWebsite + "','" + AddEmail + "','" + AddHotelDescription + "','" + AddCheckIn + "','" + AddCheckOut + "','" + AddDistanceCenter + "','" + AddDistanceTrain + "','" + AddDistanceBeach + "','" + AddFacilities + "','" + AddService + "','" + AddTerms + "','" + AddPolicy + "') SELECT SCOPE_IDENTITY();";

  query.CommandText = "INSERT INTO Hotels (Image_name,Image_data, Image_contenttype) VALUES ( @img_name, @img_data,@img_contenttype)";

So you see two rows. It should be one insert statement.



And also for room type you have 3 insertions.

query.CommandText = "INSERT INTO [Room_type] (Hotels_id, Name, Description)VALUES('" + hotelid.ToString() + "', '" + AddRoomName + "', '" + AddRoomDescription + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "','" + AddRoomDescription2 + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName3 + "','" + AddRoomDescription3 + "')";

If they are of diff values you should be good otherwise it looks like a duplicate row.
Avatar of Alomiry

ASKER

The first one I'm inserting the value of hotel Table but i did not find any solution then to use:
query.CommandText = "INSERT INTO Hotels (Image_name,Image_data, Image_contenttype) VALUES ( @img_name, @img_data,@img_contenttype)";

Please provide me with solution .

And for room_table
what the the solution if i have different value, is it the right way to make the Insert command in this way :

query.CommandText = "INSERT INTO [Room_type] (Hotels_id, Name, Description)VALUES('" + hotelid.ToString() + "', '" + AddRoomName + "', '" + AddRoomDescription + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName2 + "','" + AddRoomDescription2 + "')";

            query.CommandText += "INSERT INTO [Room_type] ([Hotels_id],[Name],[Description]) VALUES ('" + hotelid.ToString() + "','" + AddRoomName3 + "','" + AddRoomDescription3 + "')";
What is your HOTELS table structure?

In your first insert into HOTELS, I see that you get back a HotelID value. When you insert the image (assuming the Image is part of HOTELS table), you should *update* the table, not *insert* a new row.

So you would do something like
update HOTELS
set image = ....
where hotelid =

instead of your second insert.
ASKER CERTIFIED SOLUTION
Avatar of srikanthreddyn143
srikanthreddyn143

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alomiry

ASKER

The Image inside the HOTELS TABLE:

HOTELS TABLE
Id
Name
Image
Image_Type ...............

and the solution should be :

query.CommandText = "Update INTO Hotels (Image_name,Image_data, Image_contenttype) VALUES ( @img_name, @img_data,@img_contenttype)";
 

and for room_table why i got double value?
Avatar of Alomiry

ASKER

For the room_table, Thats the right way of doing. You need to validate, if there are duplicates.

Please Can you provide me with example how can i validate my table for duplicates.


regards
Are those values entered in textbox or selected from dropdown?
Avatar of Alomiry

ASKER

it\s in textbox,
You need to use Java script or any custom validators to do that. And also if you are using text box, user has the ability to enter random text which doesn't necessarily be some specific room type.

Can we have the snapshot of how your page looks.
Avatar of Alomiry

ASKER

It's a Form to ADD you Hotel to my system, so i can control the value.

Thanks for Help me, it's very useful information for someone like me (in beginning of ASP:NET)

BEST REGARDS
Avatar of Alomiry

ASKER

Tak.