Hello again experts,
I just have ONE more problem and I will be done with this crazy project.
Since GridView / Repeater is dynamically adding rows, when a user completes one row, adds another row and completes that row, upon submitting to the database, only the last row gets inserted into the database.
For instance, if I enter AKIBN as BoatRegNum, and I enter BUIN as another value for BoatRegNum, when this is submitted to the database, only the last row which is BUIN gets saved to the database. The first value of AKIBN is ignored. Any ideas how to correct this?
I was able to finally get this to work. It turns out the stored proced saveMarineInfo was the problem. Since that part of web form was anchored by MarineRegno and vesselRegno, I had to add them to the WHERE predicates.
Now, when you insert into the database and load the page, all the rows, no matter how many, are inserted correctly and are loading correctly.
However, given the number of rows created in Repeater2 are determined by the number of rows created in Repeater1, I cannot get the values to populate correctly in Repeater2.
For instance, if I have two rows created for MarineRegNo and VesselRegNo, and assume that those values are:
For MarineRegNo, M12547 and M7984 and values for VesselRegNo are V2697 and V1234 respectivel, only the first row for M12547 and B2697 are inserted into the database with the records from Repeater2.
The second row is never inserted.
Do you want me to send you the SP that is supposed to insert those records for Repeater2 or do you prefer that I close this thread and open annother one?
This is the code I am using so far.
protected void btnContinue_Click(object sender, EventArgs e)
{
//Begin by posting to the Taxpayer table but first, get connection string to sql server database
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
try
{
// First get datatable information as defined at beginning of page
//SetRowData();
DataTable regtable = ViewState["CurrTable"] as DataTable;
////Now store data into the MarineScheduleE table
SetRowData2();
DataTable stable = ViewState["CurrentTable"] as DataTable;
if (regtable != null & stable != null)
{
//open connection
conn.Open();
SqlCommand cmd = new SqlCommand();
int tPayerID = 0;
cmd.CommandText = "usp_TaxPayer";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@taxPayerFullName", txtfname.Text);
cmd.Parameters.AddWithValue("@address", txtfaddress.Text);
cmd.Parameters.AddWithValue("@city", txtcity.Text);
cmd.Parameters.AddWithValue("@state", ddlstates.SelectedValue);
cmd.Parameters.AddWithValue("@zip", txtfzip.Text);
cmd.Parameters.AddWithValue("@pid", accountnumber.Text);
cmd.Parameters.AddWithValue("@phone", txtOwnerPhone.Text);
cmd.Connection = conn;
SqlParameter tID = cmd.Parameters.Add("@taxPayerID", SqlDbType.Int);
tID.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
tPayerID += (tID.Value == DBNull.Value) ? 0 : Convert.ToInt32(tID.Value);
//Display some feedback to the user to let them know it was processed
lblResult.ForeColor = System.Drawing.Color.Green;
lblResult.Text = "Thank you! <br>You have successfully saved this portion of your. Close the window but don't forget to come back to complete this form.";
//Now deal with Marine Info stuff (Registration #, tax return year, etc)
foreach (DataRow row in regtable.Rows)
{
string txBoatRegNum = row.ItemArray[1].ToString();
string txVesselRegNum = row.ItemArray[2].ToString();
string txTaxPayerRet = row.ItemArray[3].ToString();
string txVTaxPayerRet = row.ItemArray[4].ToString();
//prevent money data types from breaking. If user does not enter value, then enter 0 in its place
//This sub will store Marine and Vessel info to the database.
if (txTaxPayerRet != null & txVTaxPayerRet != null)
{
if (string.IsNullOrEmpty(txTaxPayerRet))
{
txTaxPayerRet = "0";
}
if (string.IsNullOrEmpty(txVTaxPayerRet))
{
txVTaxPayerRet = "0";
}
SqlCommand aircmd = new SqlCommand("sp_saveMarineInfo", conn);
aircmd.CommandType = CommandType.StoredProcedure;
aircmd.Parameters.AddWithValue("@pid", accountnumber.Text);
aircmd.Parameters.AddWithValue("@eID", Request["pageId"]);
aircmd.Parameters.AddWithValue("@tID", tPayerID);
aircmd.Parameters.AddWithValue("@txYr", txtTaxYr.Text);
aircmd.Parameters.AddWithValue("@marineRegno", txBoatRegNum);
aircmd.Parameters.AddWithValue("@vesselRegno", txVesselRegNum);
aircmd.Parameters.Add("@txretval", SqlDbType.Money).Value = decimal.Parse(txTaxPayerRet); //convert text back to money
aircmd.Parameters.Add("@vtxretval", SqlDbType.Money).Value = decimal.Parse(txVTaxPayerRet); //convert text back to money
aircmd.ExecuteNonQuery();
}
}
foreach (DataRow row in stable.Rows)
{
string boatcnty = row.ItemArray[1] as string;
string aRegNo = row.ItemArray[2] as string;
string aMake = row.ItemArray[3] as string;
string mMake = row.ItemArray[4] as string;
string ModelNoA = row.ItemArray[5] as string;
string ModelNoM = row.ItemArray[6] as string;
string boatYrBuilt = row.ItemArray[7] as string;
string motorYrBuilt = row.ItemArray[8] as string;
string blength = row.ItemArray[9] as string;
string bhullmateria = row.ItemArray[10] as string;
string mhorsepower = row.ItemArray[11] as string;
string rdlmhorsepType = row.ItemArray[12] as string;
string datePurchased = row.ItemArray[13] as string;
string mdatePurchased = row.ItemArray[14] as string;
string PurchaseType = row.ItemArray[15] as string;
string rblmPurchasedType = row.ItemArray[16] as string;
//Previous Onwer info
string PrevOnwerName = row.ItemArray[17] as string;
string prevAddr = row.ItemArray[18] as string;
string prevCity = row.ItemArray[19] as string;
string PrevState = row.ItemArray[20] as string;
string prevzip = row.ItemArray[21] as string;
string mPrevOnwerName = row.ItemArray[22] as string;
string mprevAddr = row.ItemArray[23] as string;
string mprevCity = row.ItemArray[24] as string;
string mPrevState = row.ItemArray[25] as string;
string mprevzip = row.ItemArray[26] as string;
//New buyer info
string buyername = row.ItemArray[27] as string;
string buyeraddress = row.ItemArray[28] as string;
string buyercity = row.ItemArray[29] as string;
string buyerstate = row.ItemArray[30] as string;
string buyerzip = row.ItemArray[31] as string;
string datesold = row.ItemArray[32] as string;
string salePrice = row.ItemArray[33] as string;
string Description = row.ItemArray[34] as string;
string mbuyername = row.ItemArray[35] as string;
string mbuyeraddress = row.ItemArray[36] as string;
string mbuyercity = row.ItemArray[37] as string;
string mbuyerstate = row.ItemArray[38] as string;
string mbuyerzip = row.ItemArray[39] as string;
string mdatesold = row.ItemArray[40] as string;
string msalePrice = row.ItemArray[41] as string;
string mDescription = row.ItemArray[42] as string;
//Additional Marine Schedule D info
string boatCost = row.ItemArray[43] as string;
string motorCost = row.ItemArray[44] as string;
string rblIssues = row.ItemArray[45] as string;
string functionalIsses = row.ItemArray[46] as string;
string mrblIssues = row.ItemArray[47] as string;
string mfunctionalIsses = row.ItemArray[48] as string;
string vesseltypeUse = row.ItemArray[49] as string;
string vesselname = row.ItemArray[50] as string;
string vessellength = row.ItemArray[51] as string;
string vesselYrBult = row.ItemArray[52] as string;
string VesselHullMateria = row.ItemArray[53] as string;
string vesselhorsepEngine = row.ItemArray[54] as string;
string coastGuardNumber = row.ItemArray[55] as string;
string VesselYrPurchased = row.ItemArray[56] as string;
string PurchasedNew = row.ItemArray[57] as string;
string VesselPurchaseAmt = row.ItemArray[58] as string;
string homePort = row.ItemArray[59] as string;
string whereDocked = row.ItemArray[60] as string;
string bmequipAccessoryList = row.ItemArray[61] as string;
if (boatCost != null & motorCost != null & salePrice != null & msalePrice != null)
{
if (string.IsNullOrEmpty(boatCost))
{
boatCost = "0";
}
if (string.IsNullOrEmpty(motorCost))
{
motorCost = "0";
}
if (string.IsNullOrEmpty(salePrice))
{
salePrice = "0";
}
if (string.IsNullOrEmpty(msalePrice))
{
msalePrice = "0";
}
SqlCommand cmd2 = new SqlCommand("sp_MarineScheduleD", conn);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@pid", accountnumber.Text);
cmd2.Parameters.AddWithValue("@txYr", txtTaxYr.Text);
cmd2.Parameters.AddWithValue("@cnty", boatcnty);
cmd2.Parameters.AddWithValue("@regno", aRegNo);
cmd2.Parameters.AddWithValue("@boatmfgMake", aMake);
cmd2.Parameters.AddWithValue("@motormfgMake", mMake);
cmd2.Parameters.AddWithValue("@boatmfgModel", ModelNoA);
cmd2.Parameters.AddWithValue("@motormfgModel", ModelNoM);
cmd2.Parameters.AddWithValue("@boatYrBuilt", boatYrBuilt);
cmd2.Parameters.AddWithValue("@motorYrBuilt", motorYrBuilt);
cmd2.Parameters.AddWithValue("@blength", blength);
cmd2.Parameters.AddWithValue("@hullmaterial", bhullmateria);
cmd2.Parameters.AddWithValue("@horsepower", mhorsepower);
cmd2.Parameters.AddWithValue("@horsepowertype", rdlmhorsepType);
cmd2.Parameters.AddWithValue("@boatDatePurchased", datePurchased);
cmd2.Parameters.AddWithValue("@motorDatePurchased", mdatePurchased);
cmd2.Parameters.AddWithValue("@boatPurchaseType", PurchaseType);
cmd2.Parameters.AddWithValue("@motorPurchaseType", rblmPurchasedType);
cmd2.Parameters.AddWithValue("@boatCost", boatCost.ToString());
cmd2.Parameters.AddWithValue("@motorCost", motorCost.ToString());
cmd2.Parameters.AddWithValue("@boatIssues", rblIssues);
cmd2.Parameters.AddWithValue("@boatIssuesDetails", functionalIsses);
cmd2.Parameters.AddWithValue("@motorIssues", mrblIssues);
cmd2.Parameters.AddWithValue("@motorIssuesDetails", mfunctionalIsses);
cmd2.Parameters.AddWithValue("@vesseltypeUse", vesseltypeUse);
cmd2.Parameters.AddWithValue("@vesselname", vesselname);
cmd2.Parameters.AddWithValue("@vessellength", vessellength);
cmd2.Parameters.AddWithValue("@vesselYrBult", vesselYrBult);
cmd2.Parameters.AddWithValue("@VeseelHullMateria", VesselHullMateria);
cmd2.Parameters.AddWithValue("@vessehorsepEngine", vesselhorsepEngine);
cmd2.Parameters.AddWithValue("@coastGuardNumber", coastGuardNumber);
cmd2.Parameters.AddWithValue("@VesselYrPurchased", VesselYrPurchased);
cmd2.Parameters.AddWithValue("@PurchasedNew", PurchasedNew);
cmd2.Parameters.AddWithValue("@VesselPurchaseAmt", VesselPurchaseAmt.ToString());
cmd2.Parameters.AddWithValue("@homePort", homePort);
cmd2.Parameters.AddWithValue("@whereDocked", whereDocked);
cmd2.Parameters.AddWithValue("@bmequipAccessoryList", bmequipAccessoryList);
cmd2.Parameters.AddWithValue("@fdate", DateTime.Now);
cmd2.Parameters.AddWithValue("@tID", tPayerID);
cmd2.ExecuteNonQuery();
string fullName = PrevOnwerName;
if (fullName != null)
{
if (string.IsNullOrEmpty(fullName))
{
fullName = "Not Available";
}
var names = fullName.Split(' ');
string firstName = names[0];
string lastName = names[1];
}
string buyernames = buyername;
if (buyernames != null)
{
if (string.IsNullOrEmpty(buyernames))
{
buyernames = "Not Available";
}
var flnames = buyernames.Split(' ');
string bFName = flnames[0];
string bLName = flnames[1];
}
if (salePrice != null)
{
if (string.IsNullOrEmpty(salePrice))
{
salePrice = "0";
}
// Insert into Marine Previous owner if user chooses "Used"
foreach (RepeaterItem item in Repeater2.Items)
{
// Checking the item is a data item
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
{
var rdbList = item.FindControl("rblPurchaseType") as RadioButtonList;
// Get the selected value
if (rdbList.SelectedValue == "Used")
{
SqlCommand cmd3 = new SqlCommand("sp_MarinePurchaserInfo", conn);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.AddWithValue("@pid", accountnumber.Text);
cmd3.Parameters.AddWithValue("@txYr", txtTaxYr.Text);
cmd3.Parameters.AddWithValue("@prevOwnerName", PrevOnwerName);
cmd3.Parameters.AddWithValue("@fname", firstName);
cmd3.Parameters.AddWithValue("@lname", lastName);
cmd3.Parameters.AddWithValue("@PrevOnwerAddr", prevAddr);
cmd3.Parameters.AddWithValue("@PrevOnwerCity", prevCity);
cmd3.Parameters.AddWithValue("@PrevOnwerState", PrevState);
cmd3.Parameters.AddWithValue("@PrevOnwerZip", prevzip);
cmd3.Parameters.AddWithValue("@mprevOwnerName", mPrevOnwerName);
cmd3.Parameters.AddWithValue("@mfname", bFName);
cmd3.Parameters.AddWithValue("@mlname", bLName);
cmd3.Parameters.AddWithValue("@mprevAddr", mprevAddr);
cmd3.Parameters.AddWithValue("@mPrevCity", mprevCity);
cmd3.Parameters.AddWithValue("@mPrevState", mPrevState);
cmd3.Parameters.AddWithValue("@mPrevZip", mprevzip);
cmd3.Parameters.AddWithValue("@sTitle", txtTitle.Text);
cmd3.Parameters.AddWithValue("@signDate", txtSignDate.Text);
cmd3.Parameters.AddWithValue("@dtesold", datesold);
cmd3.Parameters.AddWithValue("@saleprice", salePrice.ToString());
cmd3.Parameters.AddWithValue("@descr", Description);
cmd3.Parameters.AddWithValue("@buyerfullname", buyername);
cmd3.Parameters.AddWithValue("@buyerfname", firstName);
cmd3.Parameters.AddWithValue("@buyerlname", lastName);
cmd3.Parameters.AddWithValue("@buyeraddr", buyeraddress);
cmd3.Parameters.AddWithValue("@buyercity", buyercity);
cmd3.Parameters.AddWithValue("@buyerstate", buyerstate);
cmd3.Parameters.AddWithValue("@buyerzip", buyerzip);
cmd3.Parameters.AddWithValue("@mdatesold", datesold);
cmd3.Parameters.AddWithValue("@msaleprice", msalePrice.ToString());
cmd3.Parameters.AddWithValue("@mdescription", Description);
cmd3.Parameters.AddWithValue("@mbuyername", buyername);
cmd3.Parameters.AddWithValue("@mbuyerfname", firstName);
cmd3.Parameters.AddWithValue("@mbuyerlname", lastName);
cmd3.Parameters.AddWithValue("@mbuyeraddr", buyeraddress);
cmd3.Parameters.AddWithValue("@mbuyercity", buyercity);
cmd3.Parameters.AddWithValue("@mbuyerstate", buyerstate);
cmd3.Parameters.AddWithValue("@mbuyerzip", buyerzip);
cmd3.Parameters.AddWithValue("@citylist", citylist.SelectedValue);
cmd3.Parameters.AddWithValue("@tID", tPayerID);
cmd3.Parameters.AddWithValue("@preparerName", txtPreparerName.Text);
cmd3.Parameters.AddWithValue("@email", txtEmail.Text);
cmd3.ExecuteNonQuery();
}
else
{
SqlCommand cmdpurchaser = new SqlCommand("sp_MarinePurchaserInfo", conn);
cmdpurchaser.CommandType = CommandType.StoredProcedure;
cmdpurchaser.Parameters.AddWithValue("@pid", accountnumber.Text);
cmdpurchaser.Parameters.AddWithValue("@txYr", txtTaxYr.Text);
cmdpurchaser.Parameters.AddWithValue("@prevOwnerName", PrevOnwerName);
cmdpurchaser.Parameters.AddWithValue("@fname", firstName);
cmdpurchaser.Parameters.AddWithValue("@lname", lastName);
cmdpurchaser.Parameters.AddWithValue("@PrevOnwerAddr", prevAddr);
cmdpurchaser.Parameters.AddWithValue("@PrevOnwerCity", prevCity);
cmdpurchaser.Parameters.AddWithValue("@PrevOnwerState", prevState);
cmdpurchaser.Parameters.AddWithValue("@PrevOnwerZip", prevzip);
cmdpurchaser.Parameters.AddWithValue("@mprevOwnerName", mPrevOnwerName);
cmdpurchaser.Parameters.AddWithValue("@mfname", firstName);
cmdpurchaser.Parameters.AddWithValue("@mlname", lastName);
cmdpurchaser.Parameters.AddWithValue("@mprevAddr", mprevAddr);
cmdpurchaser.Parameters.AddWithValue("@mPrevCity", mprevCity);
cmdpurchaser.Parameters.AddWithValue("@mPrevState", mprevState);
cmdpurchaser.Parameters.AddWithValue("@mPrevZip", mprevzip);
cmdpurchaser.Parameters.AddWithValue("@dtesold", datesold);
cmdpurchaser.Parameters.AddWithValue("@saleprice", salePrice.ToString());
cmdpurchaser.Parameters.AddWithValue("@descr", Description);
cmdpurchaser.Parameters.AddWithValue("@buyerfullname", buyername);
cmdpurchaser.Parameters.AddWithValue("@buyerfname", bFName);
cmdpurchaser.Parameters.AddWithValue("@buyerlname", bLName);
cmdpurchaser.Parameters.AddWithValue("@buyeraddr", buyeraddress);
cmdpurchaser.Parameters.AddWithValue("@buyercity", buyercity);
cmdpurchaser.Parameters.AddWithValue("@buyerstate", buyerstate);
cmdpurchaser.Parameters.AddWithValue("@buyerzip", buyerzip);
cmdpurchaser.Parameters.AddWithValue("@sTitle", txtTitle.Text);
cmdpurchaser.Parameters.AddWithValue("@signDate", txtSignDate.Text);
cmdpurchaser.Parameters.AddWithValue("@mdatesold", datesold);
cmdpurchaser.Parameters.AddWithValue("@msaleprice", msalePrice.ToString());
cmdpurchaser.Parameters.AddWithValue("@mdescription", Description);
cmdpurchaser.Parameters.AddWithValue("@mbuyername", mbuyername);
cmdpurchaser.Parameters.AddWithValue("@mbuyerfname", bFName);
cmdpurchaser.Parameters.AddWithValue("@mbuyerlname", bLName);
cmdpurchaser.Parameters.AddWithValue("@mbuyeraddr", buyeraddress);
cmdpurchaser.Parameters.AddWithValue("@mbuyercity", buyercity);
cmdpurchaser.Parameters.AddWithValue("@mbuyerstate", buyerstate);
cmdpurchaser.Parameters.AddWithValue("@mbuyerzip", buyerzip);
cmdpurchaser.Parameters.AddWithValue("@citylist", citylist.SelectedValue);
cmdpurchaser.Parameters.AddWithValue("@tID", tPayerID);
cmdpurchaser.Parameters.AddWithValue("@preparerName", txtPreparerName.Text);
cmdpurchaser.Parameters.AddWithValue("@email", txtEmail.Text);
cmdpurchaser.ExecuteNonQuery();
}
}
}
}
}
}
}
//Display some feedback to the user to let them know it was processed
lblResult.ForeColor = System.Drawing.Color.Green;
lblResult.Text = "Thank you! <br>You have successfully saved this portion of your. Close the window but don't forget to come back to complete this form.";
}
catch (Exception ex)
{
// this will catch your second exception
lblResult.ForeColor = System.Drawing.Color.Red;
lblResult.Text = "Error with second operation! " + ex.Message;
}
finally
{
conn.Close();
}
}
As always, thanks so much for helping get through this project.