2019年11月30日 星期六

在IIS上存取(Access)使用Office COM元件

https://dotblogs.com.tw/v6610688/2015/02/19/iis_office_access_word_excel_com_interop_api_configuration

https://dotblogs.com.tw/gelis/archive/2010/12/25/20381.aspx

如果安裝的是32位元
mmc comexp.msc /32

進去看才對


擷取元件 (CLSID 為 {000209FF-0000-0000-C000-000000000046}) 的 COM Class Factory 失敗,因為發生下列錯誤: 80070005 存取被拒。 (發生例外狀況於 HRESULT: 0x80070005 (E_ACCESSDENIED))。


我根據下列改了




但會出現找不到物件


如果用vs直接跑專案會是正常的

https://dotblogs.com.tw/v6610688/2015/02/19/iis_office_access_word_excel_com_interop_api_configuration

要設office資料匣權限開啟



如果安裝的是32位元
mmc comexp.msc /32
進去看才對
webconfig不用設








npoi 讀取excel 空值 出錯

if (row != null)
                {
                    for (int j = 0; j < 9; j++)  //對工作表每一列  //最好寫數字,不然會出錯                       
                                                 // for (int j = 0; j < row.LastCellNum; j++)  //對工作表每一列             
                    {                       
                        if (row.GetCell(0).ToString() == null)
                        {
                            continue;
                        }
                        //解決讀到空值或出錯的問題
                        if (row.GetCell(j) == null || Convert.IsDBNull(row.GetCell(j)))
                        {
                            dr[j] = null;
                        }
                        else
                        {
                            string cellValue = row.GetCell(j).ToString(); //獲取i行j列資料 
                            dr[j] = cellValue;
                        }                                               
                    }
                }

SQL UPDATE 指令

UPDATE AFM_UNIT SET ParentName = '',ParentNoteId = '' WHERE UnitPathId = '00202A'

linq的join語法

https://blog.miniasp.com/post/2010/10/14/LINQ-to-SQL-Query-Tips-INNER-JOIN-and-LEFT-JOIN


from c in Categories
join o in Products on c.CategoryID equals o.CategoryID into ps
from o in ps.DefaultIfEmpty()
select new { c.CategoryName, o.ProductName }

left語法確保products資料完整出現

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////



select * from [dbo].[AFM_CheckList] c RIGHT join [dbo].[AFM_Product] p
on c.ProductID = p.ProductID


2019年11月2日 星期六

在C#中有沒有將時間轉換成純數位類型的方法

DateTime dt = DateTime.Now;
            string ds = dt.ToString("yyyyMMddHHmmss");
            double dd = Convert.ToDouble(ds);
            Label1.Text = dd.ToString();

2019年11月1日 星期五

linq字串轉數字及數字轉字串


var products =db.AFM_Product.AsEnumerable()
                .Where(m=>m.ProductName.Contains(str_Product))
                .OrderBy(m => int.Parse(m.ProductID)).ToList();


//需要把productID轉成int來排序時

                var query = from Parts in db.AFM_Product.AsEnumerable()
                            where Parts.UnitPathId==str_UnitPathId
                            where Parts.ProductName.Contains(str_ProductName)
                            orderby int.Parse(Parts.ProductID)
                            select Parts;   

2019年10月29日 星期二

sql語法int轉varchar及varchar轉int

/****** SSMS 中 SelectTopNRows 命令的指令碼  ******/
SELECT TOP 1000 [RowId]
      ,[ProductID]
      ,[ProductName]
      ,[UnitPathId]
      ,[Item]
      ,[Amount]
      ,[Size]
      ,[Kind]
      ,[OriginalHolder]
      ,[Worth]
      ,[Material]
      ,[ObjectTime]
      ,[Maker]
      ,[Status]
      ,[Place]
      ,[ImageUrl]
      ,[Remark]
      ,[Remark2]
      ,[CreateDate]
      ,[CreateUserId]
      ,[UpdateDate]
      ,[UpdateUserId]
      ,[Cancel],
  CONVERT(varchar,RowId) as string
  FROM [AFM].[dbo].[AFM_Product]
  order by CONVERT(int,ProductID) asc

2019年10月27日 星期日

GridView RowCommand與PageIndexChanging事件

https://bbs.csdn.net/topics/350100104

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {

        if (e.CommandName == "edit_admin")
        {
            GridView1.EditIndex = Convert.ToInt32(e.CommandArgument);
            db_common();
        }
        else
        {
            if (e.CommandName == "Page")
            {

            }
            else
            {
                SqlCommand cmd = new SqlCommand();
                string sql = "";
                fun fun = new fun();
                fun.Page = this.Page;
                DataTable dt = new DataTable();
                Button x = e.CommandSource as Button;
                TextBox textBox1 = x.FindControl("TextBox1") as TextBox;
                TextBox textBox2 = x.FindControl("TextBox2") as TextBox;
                TextBox textBox3 = x.FindControl("TextBox3") as TextBox;
                Label label = x.FindControl("Label3") as Label;
                sql = "UPDATE AFM_USER SET Name = @Name,MyPhone = @MyPhone,Title = @Title,UpdateDate=@UpdateDate,UpdateUserId=@UpdateUserId WHERE Account=@Account ";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Account", label.Text);
                cmd.Parameters.AddWithValue("@Name", textBox1.Text);
                cmd.Parameters.AddWithValue("@MyPhone", textBox3.Text);
                cmd.Parameters.AddWithValue("@Title", textBox2.Text);
                cmd.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
                cmd.Parameters.AddWithValue("@UpdateUserId", Session["Account"].ToString());
                int a = fun.CmdExec(cmd);
                if (a == 0)
                {
                    fun.message("更新失敗!!!");
                }
                else
                {
                    fun.message("已成功更新了" + a + "筆資料");
                }
                GridView1.EditIndex = -1;
                db_common();
            }
        }
    }

visual studio 2015 找不到參考Microsoft.Office.Interop.Excel



參考 > 加入參考 > 瀏覽 > C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel > 往裡面的資料夾內找到 Microsoft.Office.Interop.Excel.dll
像我的路徑會變成
C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\14.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll



2019年10月20日 星期日

GridView使用自带分页功能时分页方式及样式PagerStyle

https://www.bbsmax.com/A/kPzO8le1Jx/

<%@ Page Title="" Language="C#" MasterPageFile="~/mnd_museum.master" AutoEventWireup="true" CodeFile="op_mgmt.aspx.cs" Inherits="op_mgmt" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>清冊資料管理</title>
    <link rel="stylesheet" href="css/bootstrap3.3.7.css">
    <link rel="stylesheet" href="css/font-awesome.min.css">
    <link rel="stylesheet" href="css/main.css">
    <link rel="icon" type="image/png" href="img/foricon.png">
    <style>
        .mgmttable{
            border:0px;
        }
        .mgmttable tr:first-child td{
            border:0px;
            padding-bottom:10px;

        }
        .bubufxPagerCss table {
            text-align: center;
            margin: auto;
        }

            .bubufxPagerCss table td {
                border: 0px;
                padding: 5px;
            }

        /*.bubufxPagerCss td {
            border-left: #ffffff 3px solid;
            border-right: #ffffff 3px solid;
            border-bottom: #ffffff 3px solid;
        }*/
        
        .bubufxPagerCss a {
            color: #231815;
            text-decoration: none;
            padding: 3px 6px 3px 6px;
            margin: 0 0 0 4px;
            text-align: center;
            border: 1px solid #ac1f24;
        }

        .bubufxPagerCss span {
            color: #fefefe;
            background-color: #ac1f24;
            padding: 3px 6px 3px 6px;
            margin: 0 0 0 4px;
            text-align: center;
            font-weight: bold;
            border: 1px solid #ac1f24;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <div class="right_col">
        <h1 class="pageTitle">清冊資料管理</h1>
        <div class="mgLeft">
            <form id="thisform">
                <table class="leftTable">
                    <asp:Panel ID="Panel1" runat="server" Visible="false">
                        <tr>
                            <th>管理單位:</th>
                            <td>
                                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                            </td>
                            <td></td>
                            <td></td>
                        </tr>
                    </asp:Panel>
                    <tr>
                        <th>典藏號:</th>
                        <td>
                            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                        </td>
                        <th>名稱:</th>
                        <td>
                            <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                </table>
                <div class="btnArea">
                    <asp:Button ID="Button1" runat="server" CssClass="btnBlue btclick" Text="查詢" OnClick="Button1_Click" />

                    <asp:Button ID="Button2" runat="server" CssClass="btnBlue btpick_brown" Text="列印" />

                </div>

            </form>

            <div class="separation"></div>
            <div class="formBox">
                <asp:Panel ID="Panel2" runat="server">
                    <nav aria-label="Page navigation" class="navpagination noPrint">
                       <%-- <ul class="pagination">
                            <li class="page-item">
                                <a class="page-link" href="#" aria-label="Previous">
                                    <span aria-hidden="true">&laquo;</span>
                                    <span class="sr-only">Previous</span>
                                </a>
                            </li>
                            <li class="page-item active"><a class="page-link" href="#">1</a></li>
                            <li class="page-item"><a class="page-link" href="#">2</a></li>
                            <li class="page-item"><a class="page-link" href="#">3</a></li>
                            <li class="page-item">
                                <a class="page-link" href="#" aria-label="Next">
                                    <span aria-hidden="true">&raquo;</span>
                                    <span class="sr-only">Next</span>
                                </a>
                            </li>
                        </ul>--%>
                    </nav>
                    <!-- pagination_top -->

                    <center>
                   <asp:GridView ID="GridView1" class="mgmttable" runat="server" ShowHeader="true"      AutoGenerateColumns="False" HeaderStyle-BackColor="#a93b56" HeaderStyle-ForeColor="White" RowStyle-HorizontalAlign="Center"  Width="100%" OnRowDataBound="GridView1_RowDataBound" OnRowCommand="GridView1_RowCommand" OnDataBound="GridView1_DataBound" OnPreRender="GridView1_PreRender" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" >
                <Columns>
                    <asp:TemplateField SortExpression="ProductID" HeaderText="典藏碼" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("ProductID") %>' ID="TextBox1" ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("ProductID") %>' ID="Label1"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
                    <asp:TemplateField SortExpression="ProductName" HeaderText="名稱(版本)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                              <asp:TextBox runat="server" Text='<%# Bind("ProductName") %>' ID="TextBox2"  ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("ProductName") %>' ID="Label2"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
                    <asp:TemplateField SortExpression="Amount" HeaderText="數量(單位)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                                <asp:TextBox runat="server" Text='<%# Eval("Amount") %>' ID="TextBox3" ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Amount") %>' ID="Label3"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:TemplateField SortExpression="Size" HeaderText="尺寸規格" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                              <asp:TextBox runat="server" Text='<%# Eval("Size") %>' ID="TextBox4" ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Size") %>' ID="Label4"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:TemplateField SortExpression="Kind" HeaderText="性質" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                      <asp:TextBox runat="server" Text='<%# Eval("Kind") %>' ID="TextBox5" ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Kind") %>' ID="Label5"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:TemplateField SortExpression="OriginalHolder" HeaderText="原持有者" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("OriginalHolder") %>' ID="TextBox6"  ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("OriginalHolder") %>' ID="Label6"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:TemplateField SortExpression="Worth" HeaderText="價值(元)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Worth") %>' ID="TextBox7"    ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Worth") %>' ID="Label7"></asp:Label>
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="Material" HeaderText="材質" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Material") %>' ID="TextBox8"    ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Material") %>' ID="Label8"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="ObjectTime" HeaderText="年代" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("ObjectTime") %>' ID="TextBox9"   ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("ObjectTime") %>' ID="Label9"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="Maker" HeaderText="製造者(地)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Maker") %>' ID="TextBox10"   ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Maker") %>' ID="Label10"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="Status" HeaderText="保存狀況" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Status") %>' ID="TextBox11"    ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Status") %>' ID="Label11"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="Place" HeaderText="存儲(陳展地點)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Place") %>' ID="TextBox12"   ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Place") %>' ID="Label12"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="img" HeaderText="影像" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Worth") %>' ID="TextBox13"   ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Worth") %>' ID="Label13"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                                        <asp:TemplateField SortExpression="Remark" HeaderText="備考(解說)" ItemStyle-HorizontalAlign="Center">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" Text='<%# Bind("Remark") %>' ID="TextBox14"    ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("Remark") %>' ID="Label14"></asp:Label>
                        </ItemTemplate>
                                            <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                    <asp:TemplateField SortExpression="" HeaderText="修改" ItemStyle-HorizontalAlign="Center">
                        <ItemTemplate>
                                                 <asp:Button ID="Button3"  runat="server" Text="編輯" CommandArgument='<%# Container.DataItemIndex  %>' ForeColor="#ffffff" BackColor="#555555" BorderStyle="Solid" BorderColor="#555555" BorderWidth="1px" CommandName="edit_admin" Width="90%"  OnClick="Button3_Click" />
                        </ItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>
                </Columns>
                       <HeaderStyle BackColor="#A93B56" ForeColor="White" />
                        <PagerSettings FirstPageText="首頁" LastPageText="末頁" NextPageText="下一頁" PageButtonCount="5"
                            PreviousPageText="上一頁" Mode="NumericFirstLast" Position="Top" />
                        <PagerStyle  Font-Names="宋体" Font-Size="14px" HorizontalAlign="Center"
                            CssClass="bubufxPagerCss" />
                       <RowStyle HorizontalAlign="Center" />
            </asp:GridView>
                         <br />
                        </center>
                </asp:Panel>




            </div>
        </div>
        <div class="mgcenter">

            <asp:Panel ID="Panel3" runat="server">
                <table class="inputTable">
                    <tr>
                        <th>典藏碼:</th>
                        <td>
                            <asp:TextBox ID="TextBox15" runat="server"></asp:TextBox>
                        </td>

                        <th>名稱(版本):</th>
                        <td>
                            <asp:TextBox ID="TextBox16" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>數量(單位):</th>
                        <td>
                            <asp:TextBox ID="TextBox17" runat="server"></asp:TextBox>
                        </td>

                        <th>尺寸規格:</th>
                        <td>
                            <asp:TextBox ID="TextBox18" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>性質:</th>
                        <td>
                            <asp:TextBox ID="TextBox19" runat="server"></asp:TextBox>
                        </td>

                        <th>原持有者:</th>
                        <td>
                            <asp:TextBox ID="TextBox20" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>價值(元):</th>
                        <td>
                            <asp:TextBox ID="TextBox21" runat="server"></asp:TextBox>
                        </td>

                        <th>材質:</th>
                        <td>
                            <asp:TextBox ID="TextBox22" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>年代:</th>
                        <td>
                            <asp:TextBox ID="TextBox23" runat="server"></asp:TextBox>
                        </td>

                        <th>製造者(地):</th>
                        <td>
                            <asp:TextBox ID="TextBox24" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>保存狀況:</th>
                        <td>
                            <asp:TextBox ID="TextBox25" runat="server"></asp:TextBox>
                        </td>

                        <th>存儲(陳展地點):</th>
                        <td>
                            <asp:TextBox ID="TextBox26" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>影像:</th>
                        <td>
                            <asp:Image ID="Image1" runat="server" Width="50px" Height="50px"></asp:Image>
                        </td>

                        <th>備考(解說):</th>
                        <td>
                            <asp:TextBox ID="TextBox28" runat="server" TextMode="MultiLine" Width="100%" Height="100%"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th>註銷:</th>
                        <td>
                            <asp:Button ID="Button4" runat="server" Text="註銷" ForeColor="#ffffff" BackColor="#555555" BorderStyle="Solid" BorderColor="#555555" BorderWidth="1px" Width="50%" OnClick="Button4_Click" />
                        </td>

                        <th>上傳圖檔:</th>
                        <td>
                            <asp:FileUpload ID="FileUpload1" runat="server"></asp:FileUpload>
                        </td>
                    </tr>
                    <tr>
                        <td colspan="4">
                            <asp:Button ID="Button5" CssClass="btpick_brown" runat="server" Text="更新資料" Style="display: block; margin: auto" OnClick="Button5_Click" />
                        </td>
                    </tr>
                </table>
            </asp:Panel>
        </div>
        <!-- mgCenter -->
    </div>
</asp:Content>