私人公開筆記本

2019年3月13日星期三

Delphi 備忘錄

Chr(13) 代表換行；有時候在程式碼中，會有些字串希望可以換行，就可以使用Chr(13)；也可以在程式碼中直接加上#13#10可以換行，例如：'ABCD'+#13#10+'EFGH'。
Chr(9) 代表Tab鍵，也可以輸入#9。

如果想在一個字串中加入單引號，必須打上兩個單引號，例如：'Hello~ 我是''王小明'''。

內建函數
1. IntToStr 數字轉字串；範例：IntToStr('2019') + 'Hello~'。
2. quotedstr 在字串前後加上單引號；範例：quotedstr('Hello~')。
3. Format 字串格式化；範例：Format('Hello 我是%s，我的專長是%s',['王小明','程式設計'])，輸出的結果：Hello 我是王小明，我的專長是程式設計。
4. Exit 中斷執行。

快捷鍵
Delphi 7開發環境
Shift + Ctrl + U或 I 選取程式碼在按下快捷鍵，可增減縮排。

2019/10/02
InputQuery函數的提示文字有缺字
參考資料：inputbox的提示字字數會少SHOW出來
在程式碼中使用InputQuery函數，只要提示的字數到一定的長度，中文字就會有缺字的情況發生，要解決這個問題只要在FormCreate事件中設定DefFontData.Name為新細明體即可，程式碼如下。
procedure TfrmTEST1.FormCreate(Sender: TObject);
begin
....
DefFontData.Name := '新細明體';
....
end;

2019年1月12日星期六

Windows Server 2008 R2 之 RPC 伺服器無法使用

　　公司有支小程式是使用C#寫的，專門處理AD(Active Directory)，但最近因為Server的網路流量異常，公司同仁就把一些區域連線的設定拿掉，當我再次使用小程式要去修改AD時，就造成沒辦法順利新增或修改，直到我使用逐步執行找原因時，發現程式都會停在設定AD密碼這行程式碼，我也開始在網路上找資料都無法解決，直到我想起公司同仁有去設定區域連線的設定時，我將其中一項設定打勾，再次去執行程式就可以順利執行了，以下是我C#遇到的錯誤訊息。

C#錯誤訊息
System.Reflection.TargetInvocationException: 引動過程的目標傳回例外狀況
RPC 伺服器無法使用。 (發生例外狀況於 HRESULT: 0x800706BA)

解決方法
開啟網路和共用中心→區域連線→內容→勾選「File and Printer Sharing for Microsoft Networks」

以上是我遇到的問題，不見得各位也和我一樣遇到同樣的問題，但我還是在此特別紀錄一下。

2018年12月4日星期二

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

2018年9月19日星期三

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)

2018年8月31日星期五

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

執行過程，如下：

2018年3月2日星期五

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>


</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) & "><</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) & ">«</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) & ">></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) & ">»</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

2018年2月15日星期四

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

訂閱：文章 (Atom)

2019年3月13日 星期三

2019年1月12日 星期六

2018年12月4日 星期二

2018年9月19日 星期三

2018年8月31日 星期五

2018年3月2日 星期五

2018年2月15日 星期四