i got double value in database

Alomiry
Alomiry used Ask the Experts™
on
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;
             
        }





    }

}
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<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.

Author

Commented:
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.
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.



Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

Author

Commented:
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 + "')";

Commented:
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.
Please add the Zone to C#.Net. That helps you much.

And coming to the solution.

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

For Hotels, Why dont u do like this.

The regular process is to save every thing at one shot

i.e.,

  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();";

Above query should have your image information.

If it is not possible, as you are getting the identity value, you can use this value and update that record with image using UPDATE statement in SavetoDB function.

UPDATE Hotels set Image_name=@img_name,Image_data=@img_data, Image_contenttype=@img_contenttype where Hotel_id = XXX
You need to pass Hotel Id XXX to the DavetoDB function.



Author

Commented:
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?

Author

Commented:
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?

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
Tak.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial