Windows CMD指令

2023/04/06

1. 切換目錄

例如：cd C:\Temp

2.  搜尋檔案

例如：dir /s C:\Temp\202303*.zip

3. 複製檔案

COPY C:\ABC\Hello.zip C:\temp
將C:\ABC\Hello.zip檔案複製到C:\temp

2018/12/05
1. 利用nslookup指令，用網址查詢IP
範例：nslookup 網址
例如：nslookup www.abc.com.tw

ASP.NET 綜合筆記

2019/08/26
不限數目的參數陣列ParamArray
參考資料：https://docs.microsoft.com/zh-tw/dotnet/visual-basic/programming-guide/language-features/procedures/parameter-arrays


2018/09/20
'網頁檔案名稱
System.IO.Path.GetFileName(Request.PhysicalPath)

VB.NET 使用 DES / AES 字串加密

參考資料：[C#.NET] 字串及檔案 利用 DES / AES 演算法加解密

此篇參考上方的參考資料，並翻成VB.NET。

Imports Microsoft.VisualBasic
Imports System.Security.Cryptography '加密
Imports System.Text
Imports System.IO
Public Class EnDe
'字串長度必須為8
    Dim DesKey As String = "abc!@#df", DesIv As String = "+_)xyz*&"

    Public Sub New()
    End Sub

    '參考資料https://dotblogs.com.tw/yc421206/archive/2012/04/18/71609.aspx
'source:要加密的字串
    Function DesEncrypt(ByVal source As String) As String
        Dim des As DESCryptoServiceProvider = New DESCryptoServiceProvider()
        Dim key() As Byte = Nothing
        Dim iv() As Byte = Nothing
        Dim dataByteArray() As Byte = Nothing

        key = Encoding.ASCII.GetBytes(DesKey)
        iv = Encoding.ASCII.GetBytes(DesIv)
        dataByteArray = Encoding.UTF8.GetBytes(source)

        des.Key = key
        des.IV = iv

        Dim encrypt As String = String.Empty
        Using ms As MemoryStream = New MemoryStream()
            Try
                Using cs As CryptoStream = New CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write)
                    cs.Write(dataByteArray, 0, dataByteArray.Length)
                    cs.FlushFinalBlock()

                    Dim sb As StringBuilder = New StringBuilder()
                    For Each b As Byte In ms.ToArray()
                        sb.AppendFormat("{0:X2}", b)
                    Next
                    encrypt = sb.ToString()
                End Using
            Catch ex As Exception
                encrypt = "error"
            End Try
        End Using

        Return encrypt
    End Function

    '解密 encrypt:要解密的字串
    Function DesDecrypt(ByVal encrypt As String) As String

        Dim a1 As Integer = ((encrypt.Length) / 2)
        a1 = a1 - 1
        Dim dataByteArray(a1) As Byte

        For x1 As Integer = 0 To a1
            Dim i As Integer = Convert.ToInt32(encrypt.Substring(x1 * 2, 2), 16)
            dataByteArray(x1) = CByte(i)
        Next

        Dim des As DESCryptoServiceProvider = New DESCryptoServiceProvider()
        Dim key() As Byte = Nothing
        Dim iv() As Byte = Nothing

        key = Encoding.ASCII.GetBytes(DesKey)
        iv = Encoding.ASCII.GetBytes(DesIv)

        des.Key = key
        des.IV = iv

        Dim DesString As String = String.Empty
        Using ms As MemoryStream = New MemoryStream()
            Try
                Using cs As CryptoStream = New CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write)
                    cs.Write(dataByteArray, 0, dataByteArray.Length)
                    cs.FlushFinalBlock()
                    DesString = Encoding.UTF8.GetString(ms.ToArray())
                End Using
            Catch ex As Exception
                DesString = "error"
            End Try
        End Using
        Return DesString
    End Function

End Class

用ASP.NET WebForm來測試，畫面很簡單，兩個TextBox、兩個Button(分別是加密、解密)，程式碼如下。

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim str_en As String = String.Empty
        Dim ed As EnDe
        ed = New EnDe()
        str_en = ed.DesEncrypt(TextBox1.Text)
        Response.Write("[" & str_en & "]")

    End Sub
    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim str_de As String = String.Empty
        Dim ed As EnDe
        ed = New EnDe()
        str_de = ed.DesDecrypt(TextBox2.Text)
        Response.Write("[" & str_de & "]")
    End Sub
End Class

執行過程，如下：

ASP.NET 使用DataTable實作分頁

參考資料：How To Copy DataRows Between DataTables by Using Visual Basic .NET

　　這個作法是將資料庫的資料先存到DataTable，然後程式碼中會設定每一頁顯示幾筆資料，並算出目前從第幾筆資料到第幾筆資料，換頁之後在計算出，下一頁該從第幾筆到第幾筆資料，只是這個作法在每一次換頁的時候，都會重新和資料庫拿資料。

前端程式碼
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="test_pagination.aspx.vb" Inherits="test_test_pagination" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <link rel="stylesheet" href="../css/bootstrap.css" />
    <script src="../js/jquery-1.11.3.js"></script>
    <script src="../js/bootstrap.js"></script>
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <nav id="nav_pagehead" runat="server">
            <ul id="paging_head" runat="server" class="pagination">
            </ul>
        </nav>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <nav>
            <ul id="paging_foot" runat="server" class="pagination">
            </ul>
        </nav>
    </div>
    </form>
</body>
</html>

後端程式碼
Imports System.Data
Imports System.Data.SqlClient

Partial Class test_test_pagination
    Inherits System.Web.UI.Page
    Dim str_sql As String = String.Empty
    Dim def_dt As DataTable = Nothing

    '=============================
    Dim prows As Integer = 20 '每一頁多少筆數(此處100筆分成一頁)
    Dim this_page As Integer = 1 '目前在第幾頁
    Dim maxpage As Integer = 10 '最多顯示頁數(不變動)
    Dim showpage As Integer = 10 '計算目前顯示頁數

    Sub Call_get_paging()
        '取得目前分頁頁數
        If (Not Request("p") Is Nothing) Then
            If Regex.IsMatch(Request("p").ToString, "^\d+$") = False Then '數字會回傳true
                '不是數字,設定回1
                this_page = 1
            Else
                this_page = CInt(Request("p").ToString)
            End If
        Else
            this_page = 1
        End If
    End Sub

    Private Sub form1_Load(sender As Object, e As EventArgs) Handles form1.Load
        If Not Page.IsPostBack Then


            def_dt = Nothing
            str_sql = "select TOP 5000 * from Student"
　'假設def_dt已經有資料了，請自行換成自己的DataTable
            def_dt = queryDataTable2(str_sql)

            Call_get_paging() '取得目前的頁數，傳到this_page變數

            Dim rows_count As Integer = 0 '資料的總筆數
            rows_count = def_dt.Rows.Count

            Dim dt1 As DataTable = CopyData_NewDataTable(def_dt, this_page, prows)
            If Not dt1 Is Nothing Then
                GridView1.DataSource = dt1
                GridView1.DataBind()
                GridView1.CssClass = "table table-bordered"
            End If
            def_dt.Clear()
            def_dt.Dispose()
            def_dt = Nothing
            'Response.Write("total_rows=" & total_rows & "<br/>")

            If rows_count > 0 Then
                's1開始頁數;tot_x1 全部的頁數
                Dim s1 As Integer = 1, tot_x1 As Integer = 0
                tot_x1 = Fix(rows_count / prows) '總筆數/每一頁多少筆數
                '總筆數 求餘數,餘數>0就加1頁
                If (CInt(rows_count) Mod prows) > 0 Then
                    tot_x1 = tot_x1 + 1
                End If
                '==========================
                '頁數變動
                If this_page >= 7 Then
                    If ((tot_x1 + 1) - ((this_page - 6) + 1)) >= maxpage Then
                        s1 = (this_page - 6) + 1
                        showpage = maxpage + (this_page - 6)
                    Else
                        s1 = ((this_page - 6) + 1) + (((tot_x1 + 1) - ((this_page - 6) + 1)) - maxpage)
                        showpage = tot_x1
                    End If
                Else
                    '預設
                    s1 = 1 '第一頁開始
                    showpage = maxpage '顯示的頁數
                End If
                '==========================
                If tot_x1 > 1 Then
                    Dim str_ago As String = String.Empty, str_back As String = String.Empty, str_num As String = String.Empty
                    Dim str_navpag As String = String.Empty, str_navpag_laquo As String = String.Empty, str_navpag_ago As String = String.Empty, str_navpag_back As String = String.Empty, str_navpag_raquo As String = String.Empty
                    For i As Integer = s1 To tot_x1
                        str_num = CStr(i)
                        If str_num.Length = 1 Then
                            str_num = "0" & str_num
                        End If
                        If i = this_page Then
                            str_navpag = str_navpag & "<li class=" & Chr(34) & "active" & Chr(34) & "><a>" & str_num & "<span class=" & Chr(34) & "sr-only" & Chr(34) & ">(current)</span></a></li>"
                            If i > 1 Then
                                str_navpag_ago = "<li><a href=" & Chr(34) & "test_pagination.aspx?p=" & (i - 1) & Chr(34) & " aria-label=" & Chr(34) & "Previous" & Chr(34) & "><span aria-hidden=" & Chr(34) & "true" & Chr(34) & ">&lt;</span></li>"
                                If i > 2 Then
                                    str_navpag_laquo = "<li><a href=" & Chr(34) & "test_pagination.aspx?p=1" & Chr(34) & " aria-label=" & Chr(34) & "Previous" & Chr(34) & "><span aria-hidden=" & Chr(34) & "true" & Chr(34) & ">&laquo;</span></li>"
                                End If
                            End If
                            If i < tot_x1 Then
                                str_navpag_back = "<li><a href=" & Chr(34) & "test_pagination.aspx?p=" & (i + 1) & Chr(34) & " aria-label=" & Chr(34) & "Next" & Chr(34) & "><span aria-hidden=" & Chr(34) & "true" & Chr(34) & ">&gt;</span></li>"
                                If (tot_x1 - i) > 2 Then
                                    str_navpag_raquo = "<li><a href=" & Chr(34) & "test_pagination.aspx?p=" & tot_x1 & Chr(34) & " aria-label=" & Chr(34) & "Next" & Chr(34) & "><span aria-hidden=" & Chr(34) & "true" & Chr(34) & ">&raquo;</span></li>"
                                End If
                            End If
                        Else
                            str_navpag = str_navpag & "<li><a href=" & Chr(34) & "test_pagination.aspx?p=" & i & Chr(34) & ">" & str_num & "</a></li>"
                        End If
                        If i = showpage Then
                            Exit For
                        End If
                    Next

                    Me.paging_head.InnerHtml = str_navpag_laquo & str_navpag_ago & str_navpag & str_navpag_back & str_navpag_raquo
                    Me.paging_foot.InnerHtml = str_navpag_laquo & str_navpag_ago & str_navpag & str_navpag_back & str_navpag_raquo
                Else
                    Me.nav_pagehead.Attributes.Add("Style", "display:none;")
                    Me.paging_head.InnerHtml = ""
                    Me.paging_foot.InnerHtml = ""
                End If
            Else
                Me.nav_pagehead.Attributes.Add("Style", "display:none;")
                Me.paging_head.InnerHtml = ""
                Me.paging_foot.InnerHtml = ""
            End If
        End If


    End Sub

    Function CopyData_NewDataTable(ByVal def_dt1 As DataTable, ByVal this_page As Integer, ByVal get_count As Integer) As DataTable
        Dim New_dt As DataTable = Nothing
        If Not def_dt1 Is Nothing Then
            '從第幾筆開始，第幾筆結束
            Dim data_Start_count As Integer = 0, data_End_count As Integer = 0
            data_Start_count = (this_page * get_count) - get_count + 1
            data_End_count = this_page * get_count

            New_dt = def_dt1.Clone() '將table的結構複製到新的DataTable
            For y1 As Integer = data_Start_count To data_End_count
                If (y1 - 1) > (def_dt1.Rows.Count - 1) Then
                    Exit For
                Else
                    New_dt.ImportRow(def_dt1.Rows(y1 - 1))
                End If
            Next
            Return New_dt
        Else
            Return Nothing
        End If

    End Function

End Class

VB.NET 連結Access下達Insert指令並取回自動編號Id

參考資料：Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT.


此篇單存只是做個紀錄，目前測試是有效的，可以在下達Insert指令後，透過SELECT @@IDENTITY去取得Access資料庫中的自動編號。

    '|DataDirectory|意思是您的Access資料庫必須將它放在專案中的App_Data資料夾底下
    Public strsqlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|" & WebConfigurationManager.AppSettings("acc_dbname").ToString & ";Jet OLEDB:DataBase Password=" & WebConfigurationManager.AppSettings("acc_dbpwd").ToString & ";Persist Security Info=False"
    Public sqlCon As OleDbConnection 'Connection物件建立ASP.NET程式與資料庫間的連結
    Public E_SQL As Boolean 'SQL指令執行成功或失敗
    Public sql_enq As String = Nothing
    Public Sub ODB_Open()
        '假如資料庫目前以關閉,就開啟資料庫連結
        sqlCon = New OleDbConnection(strsqlCon)
        sqlCon.Open() '開啟資料庫連結
    End Sub

    Public Function ODB_E_insert_getID1(ByVal sqlstr As String, ByVal pv As String, ByVal sqlvalue_p() As String) As String
        Dim insert_cmd As OleDbCommand = Nothing, insert_return As String = "NULL"

        Dim sql_p() As String = Nothing
        sql_p = pv.Split(",") 'sql字串中的參數
        sql_enq = Nothing

        If (sql_p.Length - 1) <> (sqlvalue_p.Length - 1) Then
            E_SQL = False
            insert_return = "err_Lv1"
        Else
            Try
                insert_cmd = sqlCon.CreateCommand()
                Dim x_end As Integer = sql_p.Length - 1
                For x As Integer = 0 To x_end
                    If insert_cmd.Parameters.Contains(sql_p(x)) Then
                        If sqlvalue_p(x) = String.Empty Then
                            insert_cmd.Parameters(sql_p(x)).Value = DBNull.Value
                        Else
                            insert_cmd.Parameters(sql_p(x)).Value = sqlvalue_p(x)
                        End If

                    Else
                        If sqlvalue_p(x) = String.Empty Then
                            insert_cmd.Parameters.AddWithValue(sql_p(x), DBNull.Value) '讓ADO.NET自行判斷型別轉換
                        Else
                            insert_cmd.Parameters.AddWithValue(sql_p(x), sqlvalue_p(x)) '讓ADO.NET自行判斷型別轉換
                        End If
                    End If
                Next

                insert_cmd.CommandText = sqlstr

                Dim insert_enq As Integer = 0
                insert_enq = insert_cmd.ExecuteNonQuery()

                If insert_enq = 1 Then
                    insert_cmd.CommandText = "SELECT @@IDENTITY"
                    insert_return = CStr(insert_cmd.ExecuteScalar())
                    E_SQL = True
                Else
                    insert_return = "err_Lv3" '新增失敗
                End If
            Catch ex As Exception
                insert_return = "err_Lv2"
            End Try
        End If

        If Not insert_cmd Is Nothing Then
            insert_cmd.Dispose()
        End If

        Return insert_return
    End Function

    Public Sub ODB_Close()

        If sqlCon.State = ConnectionState.Open Then
            sqlCon.Close() '關閉資料庫連結
            sqlCon.Dispose()
        End If
    End Sub

Access SQL

2018/03/11
Access使用Like，Access中的*在SQL Server中代表%，但是在ASP.NET中使用System.Data.OleDb還是使用%

2018/02/16
1.使用 StrComp 函數，區分大小寫；此函數如果回傳0代表等於。
SELECT * From TestUser Where StrComp(帳號欄位,'使用者輸入帳號',0)=0 and StrComp(密碼欄位,'使用者輸入密碼',0)=0

2018/02/10
1.日期時間
SELECT  Format(Now(),'yyyy/mm/dd hh:nn:ss');

VB.NET 利用登錄檔，紀錄程式使用次數

參考資料1：[VB6][VBA][C#][JAVA] 製作簡易 時間/次數 測試版 試用版
參考資料2：讀取和寫入登錄 (Visual Basic)
或者搜尋Computer.Registry

Step 1. 畫面上建立三個Button，分別是Button1、Button2、Button3，Button1屬性Name為「點我 增加使用次數」、Button2屬性Name為「刪除使用次數」、Button3屬性Name為「完整刪除」，建議先看一下參考資料2。

Code 如下

Public Class Form1

    '點我 增加使用次數
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        '開啟TEST_Company1\TestAPP01
        Dim regVersion = My.Computer.Registry.CurrentUser.OpenSubKey("TEST_Company1\TestAPP01", True)

        If regVersion Is Nothing Then
            '假如不存在，就在HKEY_CURRENT_USER底下，建立TEST_Company1\TestAPP01
            '這裡TEST_Company1\TestAPP01，有點像是電腦中的資料夾路徑的概念，程式會幫我們儲存在登錄檔的HKEY_CURRENT_USER底下，並建立TEST_Company1\TestAPP01
            regVersion = My.Computer.Registry.CurrentUser.CreateSubKey(
                         "TEST_Company1\TestAPP01") ' Key doesn't exist; create it.
        End If

        Dim int_usage_count As Integer = 0
        If regVersion IsNot Nothing Then
            int_usage_count = regVersion.GetValue("usage_count", 0)
            int_usage_count = int_usage_count + 1
            regVersion.SetValue("usage_count", int_usage_count)
            regVersion.Close()

            MsgBox("使用次數" & int_usage_count)
        End If

    End Sub

    '刪除使用次數
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        '開啟TEST_Company1\TestAPP01
        Dim regVersion = My.Computer.Registry.CurrentUser.OpenSubKey("TEST_Company1\TestAPP01", True)

        If Not regVersion Is Nothing Then
            '假如存在
            If regVersion IsNot Nothing Then
                regVersion.DeleteValue("usage_count")
                regVersion.Close()
                MsgBox("刪除完成")
            End If
        End If

    End Sub

    '完整刪除
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        '開啟TEST_Company1
        Dim regVersion = My.Computer.Registry.CurrentUser.OpenSubKey("TEST_Company1", True)

        If Not regVersion Is Nothing Then
            '假如存在，就整個刪除
            My.Computer.Registry.CurrentUser.DeleteSubKeyTree("TEST_Company1")

            '假如只要刪除TestAPP01底下的資料，可以使用以下程式碼刪除
            'My.Computer.Registry.CurrentUser.DeleteSubKey("TEST_Company1\TestAPP01")

            regVersion.Close()
            MsgBox("全部刪除完成")
        End If
    End Sub
End Class

Step 2.執行程式完後，可以開啟「登入編輯程式」(開始>搜尋程式及檔案>輸入regedit)