Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, December 23, 2015 9:42 PM
Hi
I am facing error with connection , i have tried to use SqlCommand addadscmd = new SqlCommand(INSERT ... ); instead "string twosqlstatment = @"INSERT..." but is the same. So can some one give me a real solution for this problem
protected void adNewdadsbtn_Click(object sender, EventArgs e)
{
string FileExtentio = System.IO.Path.GetExtension(FileUploadImg1.FileName);
string FileExtentio2 = System.IO.Path.GetExtension(FileUploadImg2.FileName);
string FileExtentio3 = System.IO.Path.GetExtension(FileUploadImg3.FileName);
string FileExtentio4 = System.IO.Path.GetExtension(FileUploadImg4.FileName);
string FileExtentio5 = System.IO.Path.GetExtension(FileUploadImg5.FileName);
string makervalue = string.Empty;
string Yearvalue = string.Empty;
string Gearvalue = string.Empty;
string RoomNum = string.Empty;
string BathNum = string.Empty;
string garageNum = string.Empty;
string areasize = string.Empty;
string NyAdsDesc = TextBox2.Text;
HttpCookie cookie = Request.Cookies.Get("Location");
string Location = string.Empty;
var user = Session["UsrNme"];
Location = cookie.Value;
SqlCommand addadscmd = new SqlCommand();
var UsrNme = Session["UsrNme"];
if (Session["UsrNme"] != null && cookie != null)
{
switch (Catedrdoads.SelectedItem.Text)
{
case "Cars":
// Set your values
makervalue = barndcardrlst1.SelectedValue;
Yearvalue = CarYearfrmDrDw.SelectedValue;
Gearvalue = DropDownList4.SelectedValue;
break;
case "Trucks":
// Set your values
makervalue = DropDownList9.SelectedValue;
Yearvalue = DropDownList12.SelectedValue;
Gearvalue = DropDownList10.SelectedValue;
break;
case "Tractor":
// Set your values
makervalue = DropDownList1.SelectedValue;
Yearvalue = DropDownList5.SelectedValue;
Gearvalue = DropDownList7.SelectedValue;
break;
case "Airplane":
// Set your values
makervalue = DropDownList8.SelectedValue;
Yearvalue = DropDownList6.SelectedValue;
// Should you be setting Gearvalue here?
break;
case "Apartment":
case "Villa":
case "Office":
// Set your values
RoomNum = AddAdsRomDDL.SelectedValue;
BathNum = showersNumDDL.SelectedValue;
garageNum = garageNumddl.SelectedValue;
areasize = AreaSizeDDL.SelectedValue;
break;
default:
// Consider throwing an error here, none of your options were selected
break;
}
using (var nySqlCon = new SqlConnection(sc))
{
nySqlCon.Open();
if (FileUploadImg1.HasFile || FileUploadImg2.HasFile || FileUploadImg3.HasFile || FileUploadImg4.HasFile || FileUploadImg5.HasFile)
{
var filess = new[] { FileExtentio, FileExtentio2, FileExtentio3, FileExtentio4, FileExtentio5 };
filess = filess.Where(s => !string.IsNullOrEmpty(s)).ToArray();
var extensions = new[] { ".jpg", ".JPEG" };
if ((filess.Except(extensions).Count()) <= 0)
{
if (DropDownList3.SelectedValue == "no")
{
AdsWrngPanel.Visible = true;
adsstutslbel.Text = "- Please select ads status";
}
else
{
}
if (DropDownList2.SelectedValue == "no")
{
AdsWrngPanel.Visible = true;
adscondlbel.Text = "- Please select ads condition";
}
else
{
}
string onesqlstatment = @"INSERT INTO ads (Section, Category, UID, AdsTit, AdsDesc, Country, State,AdsDate,
City, AdsPrice, Img1, img2, img3, img4,img5, Wtags, Address,Condition, Status,Maker,Year,Gear,RoomNo,
Space,Shower,Garage,Currency,extlink)
VALUES (@Section, @Category, @UID, @AdsTit, @AdsDesc, @Country, @State,@adsDate, @City, @AdsPrice, @Img1, @img2, @img3,
@img4, @img5, @Wtags, @Address,@Condition, @Status,@Maker,@Year,@Gear,@RoomNo,@Space,@Shower,@Garage,@Currency, @extlink)";
addadscmd.Connection = nySqlCon;
addadscmd.CommandType = CommandType.Text;
addadscmd.CommandText = onesqlstatment;
addadscmd.Parameters.AddWithValue("@Section", Secdrdoads.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@Maker", makervalue);
addadscmd.Parameters.AddWithValue("@Year", Yearvalue);
addadscmd.Parameters.AddWithValue("@Gear", Gearvalue);
addadscmd.Parameters.AddWithValue("@RoomNo", RoomNum);
addadscmd.Parameters.AddWithValue("@Space", areasize);
addadscmd.Parameters.AddWithValue("@Category", Catedrdoads.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@UID", user);
addadscmd.Parameters.AddWithValue("@AdsTit", addadstittxtbx.Text);
addadscmd.Parameters.AddWithValue("@AdsDesc", NyAdsDesc);
addadscmd.Parameters.AddWithValue("@Country", cookie.Value);
addadscmd.Parameters.AddWithValue("@State", statedrdolst.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@City", citiesdrdolst.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@AdsPrice", adsaddpristxtbx.Text);
addadscmd.Parameters.AddWithValue("@Address", addadstxtbox.Text);
addadscmd.Parameters.AddWithValue("@Shower", BathNum);
addadscmd.Parameters.AddWithValue("@Garage", garageNum);
addadscmd.Parameters.AddWithValue("@Wtags", addadswtagtxtbtn.Text);
addadscmd.Parameters.AddWithValue("@Condition", DropDownList2.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@Status", DropDownList3.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@adsDate", DateTime.Now);
addadscmd.Parameters.AddWithValue("@Currency", AddAdsCurencyLBL.Text);
addadscmd.Parameters.AddWithValue("@extlink", extlinktxtbox.Text);
string imgnouser = "/images/general/nouser.jpg";
if (FileUploadImg1.HasFile)
{
addadscmd.Parameters.AddWithValue("@Img1", FileUploadImg1.FileName);
FileUploadImg1.SaveAs(Server.MapPath("~/images/AdsImgs/" + FileUploadImg1.FileName));
}
else
{
addadscmd.Parameters.AddWithValue("@Img1", imgnouser);
}
if (FileUploadImg2.HasFile)
{
addadscmd.Parameters.AddWithValue("@Img2", FileUploadImg2.FileName);
FileUploadImg2.SaveAs(Server.MapPath("~/images/AdsImgs/" + FileUploadImg2.FileName));
}
else
{
addadscmd.Parameters.AddWithValue("@Img2", imgnouser);
}
if (FileUploadImg3.HasFile)
{
addadscmd.Parameters.AddWithValue("@Img3", FileUploadImg3.FileName);
FileUploadImg3.SaveAs(Server.MapPath("~/images/AdsImgs/" + FileUploadImg3.FileName));
}
else
{
addadscmd.Parameters.AddWithValue("@Img3", imgnouser);
}
if (FileUploadImg4.HasFile)
{
addadscmd.Parameters.AddWithValue("@Img4", FileUploadImg4.FileName);
FileUploadImg4.SaveAs(Server.MapPath("~/images/AdsImgs/" + FileUploadImg4.FileName));
}
else
{
addadscmd.Parameters.AddWithValue("@Img4", imgnouser);
}
if (FileUploadImg5.HasFile)
{
addadscmd.Parameters.AddWithValue("@Img5", FileUploadImg5.FileName);
FileUploadImg5.SaveAs(Server.MapPath("~/images/AdsImgs/" + FileUploadImg5.FileName));
}
else
{
addadscmd.Parameters.AddWithValue("@Img5", imgnouser);
}
}
else
{
AddNwAddsWrngFrmtLbl.Text = "Error: The file should have .png or .jpg format only";
AddNwAddsWrngFrmtLbl.ForeColor = System.Drawing.Color.Red;
}
}
else
{
string twosqlstatment = @"INSERT INTO ads (Section, Category, UID, AdsTit, AdsDesc, Country, State,AdsDate,
City, AdsPrice, Wtags, Address,Condition, Status,Maker,Year,Gear,RoomNo, Space,Shower,Garage,extlink)
VALUES (@Section, @Category, @UID, @AdsTit, @AdsDesc, @Country, @State,@adsDate, @City, @AdsPrice, @Wtags, @Address,@Condition,
@Status,@Maker,@Year,@Gear,@RoomNo,@Space,@Shower,@Garage, @extlink)";
addadscmd.Connection = nySqlCon;
addadscmd.CommandType = CommandType.Text;
addadscmd.CommandText = twosqlstatment;
addadscmd.Parameters.AddWithValue("@Section", Secdrdoads.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@Maker", makervalue);
addadscmd.Parameters.AddWithValue("@Year", Yearvalue);
addadscmd.Parameters.AddWithValue("@Gear", Gearvalue);
addadscmd.Parameters.AddWithValue("@RoomNo", RoomNum);
addadscmd.Parameters.AddWithValue("@Space", areasize);
addadscmd.Parameters.AddWithValue("@Category", Catedrdoads.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@UID", user);
addadscmd.Parameters.AddWithValue("@AdsTit", addadstittxtbx.Text);
addadscmd.Parameters.AddWithValue("@AdsDesc", NyAdsDesc);
addadscmd.Parameters.AddWithValue("@Country", cookie.Value);
addadscmd.Parameters.AddWithValue("@State", statedrdolst.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@City", citiesdrdolst.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@AdsPrice", adsaddpristxtbx.Text);
addadscmd.Parameters.AddWithValue("@Address", addadstxtbox.Text);
addadscmd.Parameters.AddWithValue("@Shower", BathNum);
addadscmd.Parameters.AddWithValue("@Garage", garageNum);
addadscmd.Parameters.AddWithValue("@Wtags", addadswtagtxtbtn.Text);
addadscmd.Parameters.AddWithValue("@Condition", DropDownList2.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@Status", DropDownList3.SelectedItem.Text);
addadscmd.Parameters.AddWithValue("@adsDate", DateTime.Now);
addadscmd.Parameters.AddWithValue("@extlink", extlinktxtbox.Text);
}
SqlDataAdapter addadsSQLADP = new SqlDataAdapter(addadscmd);
DataSet addadsDS = new DataSet();
addadsSQLADP.SelectCommand = addadscmd;
nySqlCon.Close();
addadsSQLADP.Fill(addadsDS);
MultiView1.ActiveViewIndex = 3;
ViwMyAdsPanel.Visible = true;
CheckUsrAds();
}
}
}
All replies (9)
Friday, December 25, 2015 1:09 AM ✅Answered | 1 vote
SO.
at least. use string's ToLower() method convert all extension string lower before Except method invoked.
and introduce a toggle variable which control the flow before Fill action
if IF connection assigning branch is arrived , set the toggle variable as TRUE,
finally, the Fill action would be invoke only in toggle TRUE case.
DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.
Friday, December 25, 2015 5:54 AM ✅Answered | 1 vote
Here's another issue I see with your code. Unless I missed something, your SqlCommand is an "INSERT INTO ....", no matter which way the code flows (the "if" or the "else"). And yet, you're trying to .Fill() a DataSet?!?! There is nothing SELECTed, so there's nothing to put into that DataSet. When you UPDATE, INSERT or DELETE, you should be using a SqlCommand.ExecuteNonQuery() ... not a SqlDataAdapter.Fill().
~~Bonnie DeWitt [C# MVP]
http://geek-goddess-bonnie.blogspot.com
Wednesday, December 23, 2015 9:56 PM
SqlDataAdapter addadsSQLADP = new SqlDataAdapter(addadscmd);
DataSet addadsDS = new DataSet();
addadsSQLADP.SelectCommand = addadscmd;
nySqlCon.Close();
addadsSQLADP.Fill(addadsDS);
The error message is exactly correct. You close the connection, and then ask the SqlDataAdapter to fill a data set. Did you really expect that to work?
Tim Roberts, Driver MVP Providenza & Boekelheide, Inc.
Wednesday, December 23, 2015 10:13 PM
Hi Tim
Thats really a big mistake from me to close the connection before SqlDataAdapter , but i have correct the mistake and the problem still exist i dont know why.
Thursday, December 24, 2015 6:04 AM
take a look at below code segment
if (FileUploadImg1.HasFile || FileUploadImg2.HasFile || FileUploadImg3.HasFile || FileUploadImg4.HasFile || FileUploadImg5.HasFile)
{
var filess = new[] { FileExtentio, FileExtentio2, FileExtentio3, FileExtentio4, FileExtentio5 };
filess = filess.Where(s => !string.IsNullOrEmpty(s)).ToArray();
var extensions = new[] { ".jpg", ".JPEG" };
if ((filess.Except(extensions).Count()) <= 0)
{ ..........
if code flow go into this blanch, but you upload files has file extension name over ".jpg" and "JPEG", it would not go into "if ((filess.Except(extensions).Count()) <= 0)" TRUE branch.
so , the SqlCommand would not get the connection object which be assigned inside the IF-TURE branch
Remember that, IEnumerable.Except Method do string comparing casesensitive
I guess Case sensitive causes the problem
DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.
Thursday, December 24, 2015 3:36 PM
thanks a lot its really may the problem from this section, but what i suppose to do to fix it. As i cant remove checking file extention as my some of user upload .exe file and thats is not good at all.
Thursday, December 24, 2015 4:33 PM
SqlDataAdapter addadsSQLADP = new SqlDataAdapter(addadscmd); DataSet addadsDS = new DataSet(); addadsSQLADP.SelectCommand = addadscmd; nySqlCon.Close(); addadsSQLADP.Fill(addadsDS);The error message is exactly correct. You close the connection, and then ask the SqlDataAdapter to fill a data set. Did you really expect that to work?
Tim Roberts, Driver MVP Providenza & Boekelheide, Inc.
Tim, that doesn't matter in this case. The DataAdapter.Fill() will open a connection prior to Filling the DataSet if the connection is not already open. In addition to that, it will leave the connection in the same state it started with. So, if the connection was already open prior to the .Fill(), it will remain open after. If the connection was closed prior to the .Fill(), it will automatically close it again when it's done.
~~Bonnie DeWitt [C# MVP]
http://geek-goddess-bonnie.blogspot.com
Thursday, December 24, 2015 4:40 PM
thanks a lot its really may the problem from this section, but what i suppose to do to fix it. As i cant remove checking file extention as my some of user upload .exe file and thats is not good at all.
Try setting the command's connection here:
using (var nySqlCon = new SqlConnection(sc))
{
nySqlCon.Open();
// add this:
addadscmd.Connection = nySqlCon;
~~Bonnie DeWitt [C# MVP]
http://geek-goddess-bonnie.blogspot.com
Thursday, December 24, 2015 5:22 PM
Hi BonnieB
Thanks for your reply, I tried to do as you mention but its doesnt work. But i nearly sure the problem is as Matthew LEAN . D said becuase i tried to use the code without add images and its works fine