ASP.NET

samflex wrote:

Input string was not in a correct format.

samflex wrote:

Column 'boatYrBuilt' does not belong to table Table1

samflex wrote:

where does VesselRegNo come from?

ivate void PopulateRecord(DataTable table1)
{
    DataTable table2 = LoadTable2(true);
    table2.Rows.Clear();
    
    foreach (DataRow row in table1.Rows)
    {

        DataRow dr = table2.NewRow();
        dr["RowNumber"] = table2.Rows.Count + 1;
        dr["aRegNo"] = row["MarineRegNo"];
        dr["coastGuardNumber"] = row["VesselRegNo"];
        dr["boatcnty"] = string.Empty;
        dr["aMake"] = string.Empty;
        dr["mMake"] = string.Empty;
        dr["ModelNoA"] = string.Empty;
        dr["ModelNoM"] = string.Empty;
        dr["boatYrBuilt"] = string.Empty;
        dr["motorYrBuilt"] = string.Empty;
        dr["blength"] = string.Empty;
        dr["hullmaterial"] = string.Empty;
        dr["mhorsepower"] = string.Empty;
        dr["rdlmhorsepType"] = string.Empty;
        dr["datePurchased"] = string.Empty;
        dr["mdatePurchased"] = string.Empty;
        dr["PurchaseType"] = string.Empty;
        dr["rblmPurchasedType"] = string.Empty;
        dr["PrevOnwerName"] = string.Empty;
        dr["prevAddr"] = string.Empty;
        dr["prevCity"] = string.Empty;
        dr["PrevState"] = string.Empty;
        dr["prevzip"] = string.Empty;
        dr["mPrevOnwerName"] = string.Empty;
        dr["mprevAddr"] = string.Empty;
        dr["mprevCity"] = string.Empty;
        dr["mPrevState"] = string.Empty;
        dr["mprevzip"] = string.Empty;
        dr["buyername"] = string.Empty;
        dr["buyeraddress"] = string.Empty;
        dr["buyercity"] = string.Empty;
        dr["buyerstate"] = string.Empty;
        dr["buyerzip"] = string.Empty;
        dr["datesold"] = string.Empty;
        dr["salePrice"] = string.Empty;
        dr["Description"] = string.Empty;
        dr["mbuyername"] = string.Empty;
        dr["mbuyeraddress"] = string.Empty;
        dr["mbuyercity"] = string.Empty;
        dr["mbuyerstate"] = string.Empty;
        dr["mbuyerzip"] = string.Empty;
        dr["mdatesold"] = string.Empty;
        dr["msalePrice"] = string.Empty;
        dr["mDescription"] = string.Empty;
        dr["boatCost"] = string.Empty;
        dr["motorCost"] = string.Empty;
        dr["rblIssues"] = string.Empty;
        dr["functionalIsses"] = string.Empty;
        dr["mrblIssues"] = string.Empty;
        dr["mfunctionalIsses"] = string.Empty;
        dr["vesseltypeUse"] = string.Empty;
        dr["vesselname"] = string.Empty;
        dr["vessellength"] = string.Empty;
        dr["vesselYrBult"] = string.Empty;
        dr["VesselHullMateria"] = string.Empty;
        dr["vesselhorsepEngine"] = string.Empty;
        dr["VesselYrPurchased"] = string.Empty;
        dr["PurchasedNew"] = string.Empty;
        dr["VesselPurchaseAmt"] = string.Empty;
        dr["homePort"] = string.Empty;
        dr["whereDocked"] = string.Empty;
        dr["bmequipAccessoryList"] = string.Empty;
        table2.Rows.Add(dr);
    }
    
    Repeater2.DataSource = table2;
    Repeater2.DataBind();
    
    foreach (RepeaterItem item in Repeater2.Items)
    {
        ((Button)item.FindControl("BtnAdd2")).Visible = table2.Rows.Count > 1;
        ((Button)item.FindControl("btnDelete2")).Visible = table2.Rows.Count > 1;
    }
}

<td style="white-space: nowrap;">
     Year Built:<asp:TextBox ID="boatYrBuilt" Text='<%#Eval("boatYrBuilt")%>' Style="width: 270px; margin-left: 250px;" runat="server"></asp:TextBox>
  </td>

<ItemTemplate>
                  <tr>
                    <td>
                     <asp:TextBox ID="txtboatregNum" Text='<%# Eval("MarineRegNo") %>' runat="server" placeholder="Enter registration #..." Style="width: 450px;" class="form-control"></asp:TextBox>
                    </td>
                    <td>
                     <asp:TextBox ID="txtPayerret" Text='<%# Eval("TaxPyrRetdVal") %>' runat="server" placeholder="Enter Taxpayer returned value as of Jan. 1..." Style="width: 400px;" class="form-control txtPayerret"></asp:TextBox>
                    </td>
                  </tr>
                  <tr>
                    <td>
                     <asp:TextBox ID="cgaurdNumber" Text='<%# Eval("VesselRegNo") %>' runat="server" placeholder="Enter Coast Guard #..." Style="width: 450px;" class="form-control"></asp:TextBox>
                    </td>
                    <td>
                     <asp:TextBox ID="cguardreturnedval" Text='<%# Eval("VesselTaxPyrRetdVal") %>' runat="server" placeholder="Enter Taxpayer returned value as of Jan. 1..." Style="width: 400px;" class="form-control txtPayerret"></asp:TextBox>
                    </td>
                      <td>
                          <asp:Button ID="BtnAdd" Style="width: 150px;" runat="server" Text="Next Boat"
                              OnClick="BtnAdd_Click" CssClass="btnclass" />
                          <asp:Button ID="btnDelete" Style="width: 120px;" runat="server" Text="Remove a row"
                              OnClick="btnDelete_Click" CommandArgument='<%# Container.ItemIndex %>' CssClass="btnclass" />
                      </td>
                  </tr>
              </ItemTemplate>

TER PROCEDURE [dbo].[sp_AllRecs]
@pin int
AS
BEGIN
SET NOCOUNT ON;

SELECT ROW_Number() OVER (ORDER BY(SELECT 0)) RowNumber, mp.[DateSold] as datesold
      ,mp.[SalePrice] as salePrice
      ,mp.[Description] as Description
      ,mp.[BuyerFullName] as buyername
      ,mp.[BuyerAddress] as buyeraddress
      ,mp.[BuyerCity] as buyercity
      ,mp.[BuyerState] as buyerstate
      ,mp.[BuyerZipCode] as buyerzip
      ,mp.[mDateSold] as mdatesold
      ,mp.[mSalePrice] as msalePrice
      ,mp.[mDescription] as mDescription
      ,mp.[mBuyerFullName] as mbuyername
      ,mp.[mBuyerAddress] as mbuyeraddress
      ,mp.[mBuyerCity] as mbuyercity
      ,mp.[mBuyerState] as mbuyerstate
      ,mp.[mBuyerZipCode] as mbuyerzip
      ,mp.[PreviousOwnerName] as PrevOnwerName
      ,mp.[PreviousOwnerAddress] as prevAddr
      ,mp.[PreviousOwnerCity] as prevCity
      ,mp.[PreviousOwnerState] as PrevState
      ,mp.[PreviousOwnerZipCode] as prevzip
      ,mp.[mPreviousOwnerName] as mprevOnwerName
      ,mp.[mPreviousOwnerAddress] as mprevAddr
      ,mp.[mPreviousOwnerCity] as mprevCity
      ,mp.[mPreviousOwnerState] as mPrevState
      ,mp.[mPreviousOwnerZipCode] as mprevzip
      ,mp.[signDate]
      ,mp.[sTitle]
	  ,ms.County As boatcnty
      ,ms.Registration_No As aRegNo
      ,ms.Motor_MFG_Make AS aMake
      ,ms.Boat_MFG_Make as mMake
      ,ms.Motor_MFG_Model as ModelNoA
      ,ms.Boat_MFG_Model as ModelNoM
      ,ms.Boat_Year_Built as boatYrBuilt
      ,ms.Motor_Year_Built as motorYrBuilt
      ,ms.[Length] as blength
      ,ms.HullMaterial as hullmaterial
      ,ms.HorsePower as mhorsepower
      ,ISNULL(ms.HorsePowerType,'NA') as rdlmhorsepType
      ,ms.Boat_DatePurchased as datePurchased
      ,ms.Motor_DatePurchased as mdatePurchased
      ,ISNULL(ms.Boat_PurchaseType,'NA') as PurchaseType
      ,ms.Motor_PurchaseType as rblmPurchasedType
      ,ms.MotorCost as motorCost
      ,ms.BoatCost as boatCost
      ,ms.FileDate
      ,ms.boatIssues as rblIssues
      ,ms.motorIssues as mrblIssues
      ,ms.boatIssuesDetails as functionalIsses
      ,ms.motorIssuesDetails as mfunctionalIsses
	  ,mf.TypeOfVessel as vesseltypeUse
      ,mf.VesselName as vesselname
      ,mf.HorspwrTypOfEngine as vesselhorsepEngine
      ,mf.CoastGuardNo AS coastGuardNumber
      ,mf.YearPurchased as VesselYrPurchased
      ,mf.PurchaseType as PurchasedNew
      ,mf.AmtOfPurchase as VesselPurchaseAmt
      ,mf.HomePort as homePort
      ,mf.WhereDocked as whereDocked
      ,mf.Boat_Motor_Accsry_Equip as bmequipAccessoryList
      ,mf.vesselLength as vessellength
      ,mf.YrVesselBuilt as vesselYrBult
      ,mf.VHullMaterial as VesselHullMateria
  FROM MarineInfo m 
  INNER JOIN BoatMarincePurchaserInfo mp ON m.pid = mp.pid
  INNER JOIN MarineScheduleD ms ON m.pid = ms.pid
  INNER JOIN MarineFedVesselInfo mf ON m.pid = mf.pid
  WHERE m.pid = @pin
 END

C#
    private void getRecs(int pin)
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = new SqlCommand("sp_AllRecs", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter p1 = new SqlParameter("@pin", pin);
        cmd.Parameters.Add(p1);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataTable dtPrevRecords = new DataTable();
        sda.Fill(dtPrevRecords);
        if (dtPrevRecords.Rows.Count > 0)
        {
            Repeater2.DataSource = dtPrevRecords;
            Repeater2.DataBind();
            ViewState["CurrentTable"] = dtPrevRecords;
        }
    }
    
    pageLoad() event
    
    if (!Page.IsPostBack)
    {
    
     this.getRecs(pid);
   }

private void getRecs(int pin)
  {
      SqlConnection conn = new SqlConnection(connStr);
      SqlCommand cmd = new SqlCommand("sp_AllRecs", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlParameter p1 = new SqlParameter("@pin", pin);
      cmd.Parameters.Add(p1);
      SqlDataAdapter sda = new SqlDataAdapter(cmd);
      DataTable dtPrevRecords = new DataTable();
      sda.Fill(dtPrevRecords);
      if (dtPrevRecords.Rows.Count > 0)
      {

          Repeater2.DataSource = dtPrevRecords;
          Repeater2.DataBind();
          ViewState["CurrentTable"] = dtPrevRecords;
      }
  }

protected void OnClickNext(object sender, EventArgs e)
{
    DataTable dt = LoadTable1(false);
    if (dt != null)
    {
        ...
        
        myMultiView.ActiveViewIndex += 1;
        
        if (myMultiView.ActiveViewIndex != 3)
        {
            PopulateRecord(dt);
        }
    }
}

1. The first table (CurrTable) exists - ie: something has called LoadTable1(true) before OnClickNext is called; and
2. myMultiView.ActiveViewIndex is not equal to 2 at the start of the method.

strConnection

Member 12863453 wrote:

string excelConnectionString = @"Microsoft.Jet.OLEDB.4.0;Data Source=" + path +";Extended Properties=Excel 8.0;HDR =YES;Persist Security Info=False";

Member 12863453 wrote:

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

<pre>using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;
using System.Timers;
using System.Configuration;
using System.Data.OleDb;


namespace MoveToDest
{
    public partial class Service1 : ServiceBase
    {
        long delay = 5000;
        protected string LogPath = ConfigurationManager.AppSettings["LogPath"];
        protected string MoveFilePathPath = ConfigurationManager.AppSettings["MovePath"];
        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            WriteLog("Service started");


            if (!Directory.Exists(LogPath))
            {
                Directory.CreateDirectory(LogPath);
            }

            try
            {

                delay = Int32.Parse(ConfigurationManager.AppSettings["IntervalInSeconds"]) * 1000;
            }
            catch
            {
                WriteLog("IntervalInSeconds key/value incorrect.");
            }


            if (delay < 5000)
            {
                WriteLog("Sleep time too short: Changed to default(5 secs).");
                delay = 5000;
            }

            Timer timer1 = new Timer();
            timer1.Elapsed += new ElapsedEventHandler(OnElapsedTime);
            timer1.Interval = delay;
            timer1.Enabled = true;
        }

        private void OnElapsedTime(object source, ElapsedEventArgs e)
        {
            write();
            Upload();
        }

        private void write()
        {

            string sourcepath = ConfigurationManager.AppSettings["sourcepath"];
            WriteLog("Set source path");
            string[] sourcefiles = Directory.GetFiles(sourcepath);
            WriteLog("Get soutce path file");

            foreach (string childfile in sourcefiles)
            {
                WriteLog("Start to find file from loop");
                string sourceFileName = new FileInfo(childfile).Name;
                string destinationPath = ConfigurationManager.AppSettings["destinationPath"];

                string destinationFileName = sourceFileName;
                string sourceFile = Path.Combine(sourcepath, sourceFileName);
                WriteLog("Get file from source to destination");
                string destinationFile = Path.Combine(destinationPath, destinationFileName);
                WriteLog("Ready to copy");
                File.Copy(sourceFile, destinationFile, true);
                WriteLog("File copied");
                File.Delete(sourceFile);
                WriteLog("File deleted");
            }
        }
        protected void Upload()
        {
            string excelPath = ConfigurationManager.AppSettings["ExcelPath"];
            DirectoryInfo d = new DirectoryInfo(excelPath);
            FileInfo[] Files = d.GetFiles("*.xlsx");



            string str = "";
            WriteLog("ready to enter into loop");
            foreach (FileInfo file in Files)
            {
                str = file.Name;
                string sourceFilePath = ConfigurationManager.AppSettings["SourceFilePath"];
                string SourceFileName = Path.Combine(sourceFilePath, str);

                string destinationFilePath = ConfigurationManager.AppSettings["MovePath"];

                string destinationFileName = destinationFilePath + str;




                WriteLog("get file :" + str);
                String strConnection = ConfigurationManager.AppSettings["constr"];
                WriteLog("declare database connection");
                string path = excelPath + str;

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                WriteLog("declare excel oledb connection");
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                OleDbCommand cmd = new OleDbCommand("Select [ID],[Name] from [Sheet1$]", excelConnection);

                excelConnection.Open();
                WriteLog("oledb open");
                OleDbDataReader dReader;

                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

                sqlBulk.DestinationTableName = "shrabon";
                WriteLog("ready to insert into database");
                sqlBulk.WriteToServer(dReader);
                WriteLog("data inserted");
                excelConnection.Close();
                WriteLog("Process completed");
                File.Move(SourceFileName, destinationFileName);
                WriteLog("File moved to bak folder");
                excelConnection.Close();
                cmd.Dispose();
            }
        }

        protected void WriteLog(string Msg)
        {
            FileStream fs = new FileStream(LogPath + "\\" + System.DateTime.Today.ToString("yyyyMMdd") + ".log", FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);
            sw.BaseStream.Seek(0, SeekOrigin.End);
            sw.WriteLine(System.DateTime.Now.ToString() + ": " + Msg);
            sw.Close();
            sw.Dispose();
            fs.Close();
            fs.Dispose();
        }


        protected override void OnStop()
        {
            WriteLog("service stopped");
        }
    }
}

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
  <appSettings>
    <add key="IntervalInSeconds" value="5"/>
    <add key="sourcepath" value="F:\shrabon"/>
    <add key="destinationPath" value="E:\Test"/>
    <add key="ExcelPath" value="E:\Test\"/>
    <add key="LogPath" value="E:\Floralog"/>
    <add key="MovePath" value="E:\Test\bak\"/>
    <add key="SourceFilePath" value="E:\Test"/>
    <add key="constr" value="Data Source=.;Initial Catalog=zam;User ID=eftuser;Password=eftuser321"/>
  </appSettings>
</configuration>

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);