VB.NET Crystal Reports Load Dynamically for mssql login

參考引用來源:VB.NET Crystal Reports Load Dynamically
---
使用 crystal report 設計階段階沒問題! 但電腦關機後,再次使用時;卻出現須要登入的問題
底下的範例即可解決!

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        Dim cryRpt As New ReportDocument
        Dim crtableLogoninfos As New TableLogOnInfos
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crConnectionInfo As New ConnectionInfo
        Dim CrTables As Tables
        Dim CrTable As Table

        cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

        With crConnectionInfo
            .ServerName = "YOUR SERVER NAME"
            .DatabaseName = "YOUR DATABASE NAME"
            .UserID = "YOUR DATABASE USERNAME"
            .Password = "YOUR DATABASE PASSWORD"
        End With

        CrTables = cryRpt.Database.Tables
        For Each CrTable In CrTables
            crtableLogoninfo = CrTable.LogOnInfo
            crtableLogoninfo.ConnectionInfo = crConnectionInfo
            CrTable.ApplyLogOnInfo(crtableLogoninfo)
        Next

        CrystalReportViewer1.ReportSource = cryRpt
        CrystalReportViewer1.Refresh()
    End Sub
End Class

VB.NET Crystal Reports for Beginners (mssql )

請參考來源:VB.NET Crystal Reports for Beginners
--

Crystal Reports Without Database

請參考來源:Crystal Reports Without Database
--
基本的 datatable 用法

Crystal Reports - 設計 Master / Detail 報表

請參考來源:Crystal Reports - 設計 Master / Detail 報表
--

VB.Net crystal report connection string

參考引用來源:VB.Net crystal report connection string
--
 Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load
        SetReportDb(My.Settings.colorlabConnectionString, CrystalReportViewer1, rpt_inwardreport1)
    End Sub



Public Sub SetReportDb(ByVal ConnectionString As String, ByRef CrystalReportViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer, ByRef reportDocument As ReportClass)
        'Get SQL Server Details
        Dim builder As New System.Data.Common.DbConnectionStringBuilder()

        builder.ConnectionString = ConnectionString


        Dim zServer As String = TryCast(builder("Data Source"), String)
        Dim zDatabase As String = TryCast(builder("Initial Catalog"), String)
        Dim zUsername As String = TryCast(builder("User ID"), String)
        Dim zPassword As String = TryCast(builder("Password"), String)

        Dim ciReportConnection As New ConnectionInfo

        ciReportConnection.ServerName = zServer
        ciReportConnection.DatabaseName = zDatabase
        ciReportConnection.UserID = zUsername
        ciReportConnection.Password = zPassword

        'Assign data source details to tables

        For Each table As Table In reportDocument.Database.Tables
            table.LogOnInfo.ConnectionInfo = ciReportConnection
            table.ApplyLogOnInfo(table.LogOnInfo)
        Next

        For Each subrep As ReportDocument In reportDocument.Subreports
            For Each table As Table In subrep.Database.Tables
                table.LogOnInfo.ConnectionInfo = ciReportConnection
                table.ApplyLogOnInfo(table.LogOnInfo)
            Next
        Next

        'Assign data source details to the report viewer
        If CrystalReportViewer.LogOnInfo IsNot Nothing Then
            Dim tlInfo As TableLogOnInfos = CrystalReportViewer.LogOnInfo
            For Each tbloginfo As TableLogOnInfo In tlInfo
                tbloginfo.ConnectionInfo = ciReportConnection
            Next
        End If
        reportDocument.VerifyDatabase()
        reportDocument.Refresh()
        CrystalReportViewer.ReportSource = reportDocument
        CrystalReportViewer.Refresh()
    End Sub

Creating a Report Connected to a Secure SQL Server Database

請參考來源:MSDN Creating a Report Connected to a Secure SQL Server Database
--
1.In Solution Explorer, right-click the project name that is in bold type, point to Add, and then click Add New Item.
2.In the Add New Item dialog box, in the Templates view, select the Crystal Report template.
3.In the Name field, enter the name "NorthwindCustomers.rpt", and then click Open.
Note   If you have not registered before, you may be asked to register. To find out how to register, see Crystal Reports Registration and Keycode.
4.In the Create New Crystal Report Document panel of the Crystal Reports Gallery dialog box, select Using a Report Wizard.
5.In the Choose an Expert panel, select Standard. Click OK.
The Standard Report Creation Wizard window appears.
6.In the Available Data Sources panel, expand the Create New Connection folder.
7.From the subfolder that opens, expand the OLE DB (ADO) folder.
The OLE DB (ADO) window appears.
8.Select Microsoft OLE DB Provider for SQL Server, and then click Next.
9.Enter the values for your database server, user id and password into the Server, User ID and Password fields.
10.From the Database drop down list, select "Northwind."
11.Leave the Integrated Security checkbox unchecked because you are using SQL Server authentication instead of NT authentication.
Click Finish.
The OLE DB folder is now expanded, showing your database server, and within it, the Northwind database.
12.Expand the nodes Northwind, dbo, and Tables, and then select the Customers table.
13.Click the > symbol to move the table into the Selected Tables panel, and then click Next.
14.Expand the Customers table, and then hold down the Ctrl key while you click CompanyName, ContactName and City.
15.Click the > symbol to move these fields into the Fields to Display panel, then click Next.
16.In the Available Fields panel, under Report Fields, select Customer.City, then click the > symbol to move the field into the Group By panel, and then click Finish.
The NorthwindCustomers report is created and loaded into the main window of Visual Studio.

MSSQL 重新編號 (renumber)的值來更新資料表

參考來源:利用SQL指令以重新編號(renumber)的值來更新資料表
--

--將G1群組的user重新編號
declare @num INT
select @num = 0
UPDATE account
SET @num = @num + 1,
     SEQ = @num
WHERE GroupName='G1'

孔雀魚~養殖相關資料

孔雀魚~養殖相關資料
--
超多的唷,可以慢慢看

冷凍魚飼料

沒空孵蝦的,可以找工廠買冷凍魚飼料
價格好像有在漲 =.=
冷凍魚飼料工廠
---
我沒有在賣 =.= 單純收藏而已!

mssql varchar(max) 長度不足解決方法

一般 varchar(max) 會用到不足,都是跑複雜度高的統計數據
那該如何解決這問題呢?
目前使用的方法,採用2個變數來處理 !
例:
declare @a  varchar(max)
declare @b  varchar(max) 

exec (@a+@b)

mssql count 運用

select (select COUNT(*) from book) as 總本數,(select COUNT(*) from book where vipon<>'') as 會員數,
((select COUNT(*) from book) -(select COUNT(*) from book where vipon<>'')) as 非會員數

mssql count union all

參考引用來源:SQL: Combine Select count(*) from multiple tables
--

SELECT SUM(A)
FROM
(
    SELECT 1 AS A
    UNION ALL
    SELECT 1 AS A
    UNION ALL
    SELECT 1 AS A
    UNION ALL
    SELECT 1 AS A
) AS B




select sum(counts) from (
select count(1) as counts from aa
union all
select count(1) as counts from bb
) as cc

mssql count sum

select name, COUNT(name) as count from Table
group by name
    Union all
select 'SUM' name, COUNT(name)
from Table

坪數 如何計算?

坪數 如何計算?


房子要裝潢、施工 會需要丈量, 然後就會出現很多的單位, 在此列出一些常見的.


1坪 如何計算?

長(公尺) x 寬(公尺) x 0.3025 = 坪數
1坪的大小想像: 1個雙人床約為1坪 (180cm * 180cm)
36才 = 1坪
以台尺計算方式

6台尺 × 6台尺 = 1坪
長(台尺) × 寬(台尺) ÷ 36 = 地坪數
以公分計算方式

180 cm * 180 cm = 32400 平方公分 (1坪)
長(公尺) x 寬(公尺) ÷ 32400 = 地坪數
冷氣 與 房間坪數計算方式

下述參考此篇內容: 冷氣 vs. 房間坪數計算方式

先計算室內空間的坪數
冷房噸數計算: 坪數 x 每坪估算之標準 = 總須求熱量
計算單位：12000(BTU/H)＝3000(Kcal/h)＝ 1冷凍噸 （以每小時為單位）
如何將 冷氣單位 kcal 換算 成冷氣的噸數?

kcal x 4 ÷ 8000 = 噸
8000BTU 為 1噸
所需要的噸數計算方式則為:

普通狀況 1坪 = 400kcal/h
有西曬 1坪 = 500kcal/h
西曬+頂樓 1坪 = 650kcal/h
冷氣視房間坪數為準, 180cm * 180cm = 1坪, 通常會介紹大約一個雙人床為一坪, 以此類推，能放入兩張雙人床則為兩坪大小.

1.0噸-適用 4~5坪
1.3噸-適用 5~7坪
1.5噸-適用 6~9坪
1.7噸-適用 7~10坪
2.5噸-適用 11~14坪
3.o噸-適用 14~17坪
3.5噸-適用 15~18坪
在自行考慮加上 西曬、頂樓 等狀況.

10進制轉(2進制、8進制、16進制)

System.Convert.ToString(10, 2); //十進位轉換二進位
System.Convert.ToString(10, 8); //十進位轉換八進位
System.Convert.ToString(10, 16); //十進位轉換十六進位

Graphics.DrawString 置中

參考引用來源:MSDN如何：對齊繪製的文字
--

Dim text1 As String = "Use StringFormat and Rectangle objects to" & _
    " center text in a rectangle."
Dim font1 As New Font("Arial", 12, FontStyle.Bold, GraphicsUnit.Point)
Try
    Dim rect1 As New Rectangle(10, 10, 130, 140)

    ' Create a StringFormat object with the each line of text, and the block
    ' of text centered on the page.
    Dim stringFormat As New StringFormat()
    stringFormat.Alignment = StringAlignment.Center
    stringFormat.LineAlignment = StringAlignment.Center

    ' Draw the text and the surrounding rectangle.
    e.Graphics.DrawString(text1, font1, Brushes.Blue, rect1, stringFormat)
    e.Graphics.DrawRectangle(Pens.Black, rect1)
Finally
    font1.Dispose()
End Try

PrintPreviewDialog zoom

Code Snippet
int PreferredZoomValue = 75;
PrintPreviewDialog dgl = new PrintPreviewDialog();
dgl.PrintPreviewControl.Zoom = PreferredZoomValue / 100f;