孔雀魚基因和介紹
--
這裡面資料不錯喔
邏輯等於0與1和ON與OFF,是程式創作的思維. 如何利用創作來支援生活面呢? 唯有.. 一直努力在程式領域上突破及更新自我;更是要有失敗的心情準備!! 聯絡方式 LINE ID : wushig (池龍工作室) 0931507058 巫先生
2013年6月28日 星期五
2013年6月27日 星期四
如何接收 Store Procedure 的傳回值
參考引用:如何接收 Store Procedure 的傳回值
MSDN:Using a Stored Procedure with Output Parameters
--
CREATE PROCEDURE mysp_QueryData (
@id int,
@LastName Varchar(30) output
)
AS
BEGIN
SELECT @LastName = LastName
FROM Customer
WHERE CustomerID = @id;
END
--
取值:
DECLARE @lname varchar(30);
EXEC mysp_QueryData '1',@lname output;
PRINT @lname;
MSDN:Using a Stored Procedure with Output Parameters
--
CREATE PROCEDURE mysp_QueryData (
@id int,
@LastName Varchar(30) output
)
AS
BEGIN
SELECT @LastName = LastName
FROM Customer
WHERE CustomerID = @id;
END
--
取值:
DECLARE @lname varchar(30);
EXEC mysp_QueryData '1',@lname output;
PRINT @lname;
2013年6月26日 星期三
2013年6月24日 星期一
2013年6月18日 星期二
How to add a 'Boolean' column to ms-access via SQL in vb.net
參考引用:How to add a 'Boolean' column to ms-access via SQL in vb.net
--
ALTER TABLE tabDatafiveMinutely
ADD COLUMN CON0001 BIT DEFAULT 0 NOT NULL
--
ALTER TABLE tabDatafiveMinutely
ADD COLUMN CON0001 BIT DEFAULT 0 NOT NULL
Access and SQL Server Views with Boolean Values; when is True -1 or 1
參考引用:Access and SQL Server Views with Boolean Values; when is True -1 or 1 (10th February 2010)
--
CREATE TABLE BooleanTest(
AnId INT IDENTITY(1,1) PRIMARY KEY,
ABoolean BIT,
ADescription VARCHAR(50)
)
GO
INSERT INTO BooleanTest(ABoolean,ADescription)
VALUES (1,'This is true')
GO
INSERT INTO BooleanTest(ABoolean,ADescription)
VALUES (0,'This is false')
GO
INSERT INTO BooleanTest(ADescription)
VALUES ('This is null')
GO
-- False Test
SELECT * FROM BooleanTest
WHERE ABoolean = 0
GO
-- True Test
SELECT * FROM BooleanTest
WHERE ABoolean = 1
GO
-- NULL Test
SELECT * FROM BooleanTest
WHERE ABoolean IS NULL
GO
--
CREATE TABLE BooleanTest(
AnId INT IDENTITY(1,1) PRIMARY KEY,
ABoolean BIT,
ADescription VARCHAR(50)
)
GO
INSERT INTO BooleanTest(ABoolean,ADescription)
VALUES (1,'This is true')
GO
INSERT INTO BooleanTest(ABoolean,ADescription)
VALUES (0,'This is false')
GO
INSERT INTO BooleanTest(ADescription)
VALUES ('This is null')
GO
-- False Test
SELECT * FROM BooleanTest
WHERE ABoolean = 0
GO
-- True Test
SELECT * FROM BooleanTest
WHERE ABoolean = 1
GO
-- NULL Test
SELECT * FROM BooleanTest
WHERE ABoolean IS NULL
GO
2013年6月15日 星期六
how to create a ms access db and table
參考1:how to create a ms access db and table using adodb in vb.net
參考2:Create a table in MS Access Database using SQL via VB.NET
參考3:Create Database Table with VB.NET
參考4:How to create Access table with VB.NET
參考5:Set Primary Key In Access Database
參考6:Table Management for Microsoft Access Example
--
建立資料表+PK:
CREATE TABLE Employees (First_Name TEXT(20), Last_Name TEXT(25), dob DATETIME, Constraint Employees_PK Primary Key (First_Name, Last_Name, dob));
--
建立索引:
CREATE UNIQUE INDEX Emp_Phone_IDX ON Employees (emp_phone);
--
增加欄位:
ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);
--
變更欄位屬性或長度:
ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);
--
移除欄位:
ALTER TABLE Employees DROP COLUMN Emp_Email;
參考2:Create a table in MS Access Database using SQL via VB.NET
參考3:Create Database Table with VB.NET
參考4:How to create Access table with VB.NET
參考5:Set Primary Key In Access Database
參考6:Table Management for Microsoft Access Example
--
建立資料表+PK:
CREATE TABLE Employees (First_Name TEXT(20), Last_Name TEXT(25), dob DATETIME, Constraint Employees_PK Primary Key (First_Name, Last_Name, dob));
--
建立索引:
CREATE UNIQUE INDEX Emp_Phone_IDX ON Employees (emp_phone);
--
增加欄位:
ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);
--
變更欄位屬性或長度:
ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);
--
移除欄位:
ALTER TABLE Employees DROP COLUMN Emp_Email;
2013年6月14日 星期五
VBNET Parameter Queries in MS Access
參考引用:Parameter Queries in ASP.NET with MS Access
參考:ASP.NET下OleDbCommand使用參數連接SQL Server
參考:OleDbCommand.Parameters 屬性
---
A selection of code samples for executing queries against MS Access using parameters.
Making use of the ASP.NET 2.0 datasource controls is fine, but it is important to understand how to manually create data access code. Best practice dictates that, at the very least, parameters are used to represent values that are passed into the SQL to be executed, rather than un-sanitised values straight from the user. The main reason for this cannot be over-emphasised in terms of its importance - it protects the application against SQL Injection attacks. In addition, parameters do not require delimiters. Therefore there is no need to worry about octothorpes (#) or apostrophes for dates, or doubling single quotes in strings.
These samples all assume that the values being passed into the parameters have been properly validated for datatype, existence, range etc, according to the business rules for the application. The serverside validation code is not included, as it will differ from app to app, and is not the focus of these samples anyway. However, it is important to stress that all user input must be validated server-side before being included in a SQL statement. Better to reject it outright, rather than have to unpick rubbish that pollutes the database...
The required components are an OleDbConnection object, a ConnectionString property, an OleDbCommand object and an OleDbParameterCollection. These all reside in the System.Data.OleDb namespace, which needs to be referenced. Also, the connection string is held in the Web.Config, and a static method GetConnString() has been created in a class called Utils (also static) to retrieve it:
[C#]
public static string GetConnString()
{
return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
}
[VB]
Public Shared Function GetConnString() As String
Return WebConfigurationManager.ConnectionStrings("myConnStr").ConnectionString
End Function
For simplicity, you can replace Utils.GetConnString with a valid Access connection string such as:
"Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb"
To make use of |DataDirectory| make sure that your database file is in the App_Data folder of your web site.
OleDb Parameters are recognised by their position, not by their name. Consequently, it is vital to ensure that parameters are added to the collection in the order they appear in the SQL, otherwise a "Too few parameters..." exception could occur. At the very least, your values will get inserted into the wrong fields, or nothing happens at all. For the sake of code readability, AddWithValues(string, object) can take a non-empty string giving a name to the parameter, although an empty string ("") will do.
One final note about parameter markers: in the samples below, the markers are represented by question marks ( ? ). Access (or the Jet provider) is also happy to work with SQL Server-style parameter markers that are prefixed with @, so the first example CommandText can be replaced with:
"Insert Into Contacts (FirstName, LastName) Values (@FirstName, @LastName)"
INSERT
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Insert Into Contacts (FirstName, LastName) Values (?,?)"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
UPDATE
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
DELETE
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Delete * From Contacts Where FirstName = ? And LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Delete * From Contacts Where FirstName = ? And LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
SELECT
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Select * From Contacts Where FirstName = ? And LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Response.Write(reader["FirstName"].ToString() + " " + reader["LastName"].ToString());
}
}
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Select * From Contacts Where FirstName = ? And LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
Using reader As OleDbDataReader = cmd.ExecuteReader()
While reader.Read()
Response.Write(reader("FirstName").ToString() + " " + reader("LastName").ToString())
End While
End Using
End Using
End Using
Saved Queries
The code samples above will work equally well with minimal changes for Saved Queries in MS Access. The CommandType will need to be changed to "StoredProcedure", and the name of the query needs to be passed as a string in place of the SQL statement. As an example, if a Saved Query was created in Access called AddContact, this is how the INSERT example would alter:
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "AddContact";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "AddContact"
Using Conn As New OleDbConnection(ConnString)
Using Cmd As New OleDbCommand(SqlString, Conn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
Cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
Conn.Open()
Cmd.ExecuteNonQuery()
End Using
End Using
You may end up using a legacy Access database, which has embedded spaces in the names of the queries. I know - only an idiot does this sort of thing. Well, the download version of Northwind.mdb (from Microsoft) has embedded spaces in object names... Anyway, the way to get round this is to surround the query name with [ ] brackets:
string query = "[Current Product List]";
參考:ASP.NET下OleDbCommand使用參數連接SQL Server
參考:OleDbCommand.Parameters 屬性
---
A selection of code samples for executing queries against MS Access using parameters.
Making use of the ASP.NET 2.0 datasource controls is fine, but it is important to understand how to manually create data access code. Best practice dictates that, at the very least, parameters are used to represent values that are passed into the SQL to be executed, rather than un-sanitised values straight from the user. The main reason for this cannot be over-emphasised in terms of its importance - it protects the application against SQL Injection attacks. In addition, parameters do not require delimiters. Therefore there is no need to worry about octothorpes (#) or apostrophes for dates, or doubling single quotes in strings.
These samples all assume that the values being passed into the parameters have been properly validated for datatype, existence, range etc, according to the business rules for the application. The serverside validation code is not included, as it will differ from app to app, and is not the focus of these samples anyway. However, it is important to stress that all user input must be validated server-side before being included in a SQL statement. Better to reject it outright, rather than have to unpick rubbish that pollutes the database...
The required components are an OleDbConnection object, a ConnectionString property, an OleDbCommand object and an OleDbParameterCollection. These all reside in the System.Data.OleDb namespace, which needs to be referenced. Also, the connection string is held in the Web.Config, and a static method GetConnString() has been created in a class called Utils (also static) to retrieve it:
[C#]
public static string GetConnString()
{
return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
}
[VB]
Public Shared Function GetConnString() As String
Return WebConfigurationManager.ConnectionStrings("myConnStr").ConnectionString
End Function
For simplicity, you can replace Utils.GetConnString with a valid Access connection string such as:
"Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb"
To make use of |DataDirectory| make sure that your database file is in the App_Data folder of your web site.
OleDb Parameters are recognised by their position, not by their name. Consequently, it is vital to ensure that parameters are added to the collection in the order they appear in the SQL, otherwise a "Too few parameters..." exception could occur. At the very least, your values will get inserted into the wrong fields, or nothing happens at all. For the sake of code readability, AddWithValues(string, object) can take a non-empty string giving a name to the parameter, although an empty string ("") will do.
One final note about parameter markers: in the samples below, the markers are represented by question marks ( ? ). Access (or the Jet provider) is also happy to work with SQL Server-style parameter markers that are prefixed with @, so the first example CommandText can be replaced with:
"Insert Into Contacts (FirstName, LastName) Values (@FirstName, @LastName)"
INSERT
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Insert Into Contacts (FirstName, LastName) Values (?,?)"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
UPDATE
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
DELETE
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Delete * From Contacts Where FirstName = ? And LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Delete * From Contacts Where FirstName = ? And LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
SELECT
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "Select * From Contacts Where FirstName = ? And LastName = ?";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Response.Write(reader["FirstName"].ToString() + " " + reader["LastName"].ToString());
}
}
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Select * From Contacts Where FirstName = ? And LastName = ?"
Using conn As New OleDbConnection(ConnString)
Using cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
conn.Open()
Using reader As OleDbDataReader = cmd.ExecuteReader()
While reader.Read()
Response.Write(reader("FirstName").ToString() + " " + reader("LastName").ToString())
End While
End Using
End Using
End Using
Saved Queries
The code samples above will work equally well with minimal changes for Saved Queries in MS Access. The CommandType will need to be changed to "StoredProcedure", and the name of the query needs to be passed as a string in place of the SQL statement. As an example, if a Saved Query was created in Access called AddContact, this is how the INSERT example would alter:
[C#]
string ConnString = Utils.GetConnString();
string SqlString = "AddContact";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "AddContact"
Using Conn As New OleDbConnection(ConnString)
Using Cmd As New OleDbCommand(SqlString, Conn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
Cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
Conn.Open()
Cmd.ExecuteNonQuery()
End Using
End Using
You may end up using a legacy Access database, which has embedded spaces in the names of the queries. I know - only an idiot does this sort of thing. Well, the download version of Northwind.mdb (from Microsoft) has embedded spaces in object names... Anyway, the way to get round this is to surround the query name with [ ] brackets:
string query = "[Current Product List]";
Dynamic rdlc report
參考引用:Dynamic rdlc report
--
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
If Me.TextBox1.Text = "" Or IsNumeric(Me.TextBox1.Text) = False Then
Return
End If
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim DelSql As String
Dim tmpSQL As String
MyConn = New ADODB.Connection
MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\boxus\Geral\Dados\UTLT.accdb;Persist Security Info = False"
MyConn.Open()
Try
DelSql = "DELETE FROM UNICRE_REPORT_COMM"
MyConn.Execute(DelSql)
tmpSQL = "INSERT INTO UNICRE_REPORT_COMM (C_NAME, TRX_AMNT, ACT, BUY_L_NAME, BUY_F_NAME, BUYER_MAIL, USER_LOG, SH_MESSAGE, TRX_DATE, AUT_NUMBER, DT_FECHO, TRXS_TOTAL, COMM, UTIL, CC_NOME, CC_LOCAL, CC_NOME_LOGIN, CC_OFFICE, CC_DEP) SELECT A.CORPORATE_NAME, A.TRANSACTION_AMOUNT, A.ACTION, A.BUYER_LAST_NAME, A.BUYER_FIRST_NAME, A.BUYER_EMAIL, A.USER_LOGIN, A.SHORT_MESSAGE, A.TRANSACTION_DATE, A.AUTORIZATION_NUMBER, B.DATA_FECHO, B.TRX_TOTAL, B.COMISSAO, B.UTILIZADOR, C.NOME, C.LOCALIZACAO, C.NOME_LOGIN, C.OFFICE, C.DEP FROM FECHO_UNICRE A, FECHO_UNICRE_REP B, UNICRE_CC C WHERE (B.DATA_FECHO = A.TRANSACTION_DATE AND C.NOME_LOGIN = A.USER_LOGIN)"
MyConn.Execute(tmpSQL)
Dim rs1 As ADODB.Recordset
Dim SomaSql As String
SomaSql = "SELECT SUM(TRX_AMNT) FROM UNICRE_REPORT_COMM"
rs1 = New ADODB.Recordset
Call rs1.Open(SomaSql, MyConn)
Me.TextBox2.Text = rs1.Fields(0).Value
MyConn.Close()
Dim p1 As New ReportParameter("teste", Val(Me.TextBox1.Text))
Dim p2 As New ReportParameter("soma", Val(Me.TextBox2.Text))
Unicre_Report_Comm.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p1, p2})
Unicre_Report_Comm_SR.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p1, p2})
Me.Close()
Catch ex As Exception
MsgBox(ex.Message).ToString()
End Try
Unicre_Report_Comm.Show()
Unicre_Report_Comm_SR.Show()
End Sub
--
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
If Me.TextBox1.Text = "" Or IsNumeric(Me.TextBox1.Text) = False Then
Return
End If
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim DelSql As String
Dim tmpSQL As String
MyConn = New ADODB.Connection
MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\boxus\Geral\Dados\UTLT.accdb;Persist Security Info = False"
MyConn.Open()
Try
DelSql = "DELETE FROM UNICRE_REPORT_COMM"
MyConn.Execute(DelSql)
tmpSQL = "INSERT INTO UNICRE_REPORT_COMM (C_NAME, TRX_AMNT, ACT, BUY_L_NAME, BUY_F_NAME, BUYER_MAIL, USER_LOG, SH_MESSAGE, TRX_DATE, AUT_NUMBER, DT_FECHO, TRXS_TOTAL, COMM, UTIL, CC_NOME, CC_LOCAL, CC_NOME_LOGIN, CC_OFFICE, CC_DEP) SELECT A.CORPORATE_NAME, A.TRANSACTION_AMOUNT, A.ACTION, A.BUYER_LAST_NAME, A.BUYER_FIRST_NAME, A.BUYER_EMAIL, A.USER_LOGIN, A.SHORT_MESSAGE, A.TRANSACTION_DATE, A.AUTORIZATION_NUMBER, B.DATA_FECHO, B.TRX_TOTAL, B.COMISSAO, B.UTILIZADOR, C.NOME, C.LOCALIZACAO, C.NOME_LOGIN, C.OFFICE, C.DEP FROM FECHO_UNICRE A, FECHO_UNICRE_REP B, UNICRE_CC C WHERE (B.DATA_FECHO = A.TRANSACTION_DATE AND C.NOME_LOGIN = A.USER_LOGIN)"
MyConn.Execute(tmpSQL)
Dim rs1 As ADODB.Recordset
Dim SomaSql As String
SomaSql = "SELECT SUM(TRX_AMNT) FROM UNICRE_REPORT_COMM"
rs1 = New ADODB.Recordset
Call rs1.Open(SomaSql, MyConn)
Me.TextBox2.Text = rs1.Fields(0).Value
MyConn.Close()
Dim p1 As New ReportParameter("teste", Val(Me.TextBox1.Text))
Dim p2 As New ReportParameter("soma", Val(Me.TextBox2.Text))
Unicre_Report_Comm.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p1, p2})
Unicre_Report_Comm_SR.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p1, p2})
Me.Close()
Catch ex As Exception
MsgBox(ex.Message).ToString()
End Try
Unicre_Report_Comm.Show()
Unicre_Report_Comm_SR.Show()
End Sub
2013年6月13日 星期四
VB6 RDS
參考引用:如何： 使用 RDS 從在 Visual Basic 程式中
參考:RDS Code Examples in Visual Basic
--
Dim rs As Object 'Recordset
Dim ds As Object 'RDS.DataSpace
Dim df As Object 'RDSServer.DataFactory
Private Sub Form_Load()
Set ds = CreateObject("RDS.DataSpace")
Set df = ds.CreateObject("RDSServer.DataFactory", _
"http://myserver")
End Sub
Private Sub Command1_Click()
'This query returns a recordset over HTTP.
Dim strCn As Variant, strSQL As Variant
strCn = "dsn=pubs;Username=;PWD="
strSQL = "select * from authors"
Set rs = df.Query(strCn, strSQL)
Debug.Print rs(0) 'Print Row 1, Col 1 to Debug window
End Sub
Private Sub Command2_Click()
'This example executes an action query but does not return
'a recordset.
Dim strCn As Variant, strSQL As Variant
strCn = "dsn=pubs;Username=;PWD="
strSQL = "Update authors Set au_fname = 'Jon' Where au_lname" _
& " Like 's%'"
df.Query strCn, strSQL
End Sub
參考:RDS Code Examples in Visual Basic
--
Dim rs As Object 'Recordset
Dim ds As Object 'RDS.DataSpace
Dim df As Object 'RDSServer.DataFactory
Private Sub Form_Load()
Set ds = CreateObject("RDS.DataSpace")
Set df = ds.CreateObject("RDSServer.DataFactory", _
"http://myserver")
End Sub
Private Sub Command1_Click()
'This query returns a recordset over HTTP.
Dim strCn As Variant, strSQL As Variant
strCn = "dsn=pubs;Username=
strSQL = "select * from authors"
Set rs = df.Query(strCn, strSQL)
Debug.Print rs(0) 'Print Row 1, Col 1 to Debug window
End Sub
Private Sub Command2_Click()
'This example executes an action query but does not return
'a recordset.
Dim strCn As Variant, strSQL As Variant
strCn = "dsn=pubs;Username=
strSQL = "Update authors Set au_fname = 'Jon' Where au_lname" _
& " Like 's%'"
df.Query strCn, strSQL
End Sub
2013年6月12日 星期三
Get Column Name and Data Types of Access Tables
vb6:Field type reference - names and values for DDL, DAO, and ADOX
參考引用:Get Column Name and Data Types of Access Tables
--
Private Sub Demo(ByVal ConnectionString As String)
Using cn As New OleDbConnection(ConnectionString)
Dim Result = SchemaInfo(cn.ConnectionString, "Table1")
For Each row As DataRow In Result.Rows
Console.WriteLine("Name={0} Type={1}", row("ColumnName"), row("DataType"))
Next
End Using
End Sub
Public Function SchemaInfo(ByVal ConnectionString As String, ByVal TableName As String) As DataTable
Dim dt As New DataTable With {.TableName = "Schema"}
dt.Columns.AddRange( _
New DataColumn() _
{ _
New DataColumn("ColumnName", GetType(System.String)), _
New DataColumn("DataType", GetType(System.String)) _
} _
)
Using cn As New OleDbConnection(ConnectionString)
Using cmd As New OleDbCommand("SELECT * FROM " & TableName, cn)
cn.Open()
Dim Reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim schemaTable = Reader.GetSchemaTable()
schemaTable.TableName = "TableSchema"
Dim sw As New IO.StringWriter
schemaTable.WriteXml(sw)
Dim Doc = New XDocument
Doc = XDocument.Parse(sw.ToString)
Dim query = _
( _
From T In Doc... _
Select _
Name = T..Value, _
DataType = T..Value.Split(","c)(0).Replace("System.", "") _
).ToList
For Each item In query
Dim Row As DataRow
Row = dt.NewRow
Row("ColumnName") = item.Name
Row("DataType") = item.DataType
dt.Rows.Add(Row)
Next
End Using
End Using
Return dt
End Function
----
上面寫這麼多,套用取 data type
OleDbType myDT = (OleDbType)row["DATA_TYPE"];
VBNET:
Dim myDT As OleDbType = CType(row("DATA_TYPE"), OleDbType)
參考引用:Get Column Name and Data Types of Access Tables
--
Private Sub Demo(ByVal ConnectionString As String)
Using cn As New OleDbConnection(ConnectionString)
Dim Result = SchemaInfo(cn.ConnectionString, "Table1")
For Each row As DataRow In Result.Rows
Console.WriteLine("Name={0} Type={1}", row("ColumnName"), row("DataType"))
Next
End Using
End Sub
Public Function SchemaInfo(ByVal ConnectionString As String, ByVal TableName As String) As DataTable
Dim dt As New DataTable With {.TableName = "Schema"}
dt.Columns.AddRange( _
New DataColumn() _
{ _
New DataColumn("ColumnName", GetType(System.String)), _
New DataColumn("DataType", GetType(System.String)) _
} _
)
Using cn As New OleDbConnection(ConnectionString)
Using cmd As New OleDbCommand("SELECT * FROM " & TableName, cn)
cn.Open()
Dim Reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim schemaTable = Reader.GetSchemaTable()
schemaTable.TableName = "TableSchema"
Dim sw As New IO.StringWriter
schemaTable.WriteXml(sw)
Dim Doc = New XDocument
Doc = XDocument.Parse(sw.ToString)
Dim query = _
( _
From T In Doc...
Select _
Name = T.
DataType = T.
).ToList
For Each item In query
Dim Row As DataRow
Row = dt.NewRow
Row("ColumnName") = item.Name
Row("DataType") = item.DataType
dt.Rows.Add(Row)
Next
End Using
End Using
Return dt
End Function
----
上面寫這麼多,套用取 data type
OleDbType myDT = (OleDbType)row["DATA_TYPE"];
VBNET:
Dim myDT As OleDbType = CType(row("DATA_TYPE"), OleDbType)
VB.NET Fields in an Access database table
參考引用:Fields in an Access database table
如何使用 GetOleDbSchemaTable 和 Visual Basic.NET 中擷取結構描述資訊
--
Module Module1
'How to get a list of columns from an access database given a table name
Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\users.mdb")
Dim restrictions() As String = {"", "", "userinfo", ""}
Dim SchemaTable As DataTable
Public Sub connect()
oleConn.Open()
SchemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, Nothing, Nothing})
Dim int As Integer
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE"
'EXCEPTION : Table is not part of table column
Then
'Add items to list box
Form1.comboselecttable.Items.Add(SchemaTable.Rows(int)!COLUMN_NAME.ToString())
End If
'// Get list of tables columns
Next
'Form1.comboselecttable.Items.Add(dbRet.Rows(0)!TABLE_NAME.ToString())
'// dbRet will have a column name called "COLUMN_NAME" which will contain all the columns for the table
End Sub
Public Sub endconn()
oleConn.Close()
End Sub
End Module
如何使用 GetOleDbSchemaTable 和 Visual Basic.NET 中擷取結構描述資訊
--
Module Module1
'How to get a list of columns from an access database given a table name
Dim oleConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\users.mdb")
Dim restrictions() As String = {"", "", "userinfo", ""}
Dim SchemaTable As DataTable
Public Sub connect()
oleConn.Open()
SchemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, Nothing, Nothing})
Dim int As Integer
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE"
'EXCEPTION : Table is not part of table column
Then
'Add items to list box
Form1.comboselecttable.Items.Add(SchemaTable.Rows(int)!COLUMN_NAME.ToString())
End If
'// Get list of tables columns
Next
'Form1.comboselecttable.Items.Add(dbRet.Rows(0)!TABLE_NAME.ToString())
'// dbRet will have a column name called "COLUMN_NAME" which will contain all the columns for the table
End Sub
Public Sub endconn()
oleConn.Close()
End Sub
End Module
VB.Net - How can I check if a primary key exists in an Access DB
參考引用:VB.Net - How can I check if a primary key exists in an Access DB
--
Public Shared Function getKeyNames(tableName As [String], conn As DbConnection) As List(Of String)
Dim returnList = New List(Of String)()
Dim mySchema As DataTable = TryCast(conn, OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New [Object]() {Nothing, Nothing, tableName})
' following is a lengthy form of the number '3' :-)
Dim columnOrdinalForName As Integer = mySchema.Columns("COLUMN_NAME").Ordinal
For Each r As DataRow In mySchema.Rows
returnList.Add(r.ItemArray(columnOrdinalForName).ToString())
Next
Return returnList
End Function
--
Public Shared Function getKeyNames(tableName As [String], conn As DbConnection) As List(Of String)
Dim returnList = New List(Of String)()
Dim mySchema As DataTable = TryCast(conn, OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New [Object]() {Nothing, Nothing, tableName})
' following is a lengthy form of the number '3' :-)
Dim columnOrdinalForName As Integer = mySchema.Columns("COLUMN_NAME").Ordinal
For Each r As DataRow In mySchema.Rows
returnList.Add(r.ItemArray(columnOrdinalForName).ToString())
Next
Return returnList
End Function
OleDb Adapter
參考引用:OleDb Adapter
--
不錯的寫法,收藏
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.Common
Imports System.Data
Public Class MainClass
Shared Sub Main()
Dim dsUsers As New DataSet("Users")
Try
' Define a connection object
Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
' Create a data adapter to retrieve records from db
Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
' Define each column to map
Dim dcmUserID As New DataColumnMapping("ID", "EmployeeID")
Dim dcmFirstName As New DataColumnMapping("FirstName", "FirstName")
Dim dcmLastName As New DataColumnMapping("LastName", "LastName")
' Define the table containing the mapped columns
Dim dtmUsers As New DataTableMapping("Table", "Employee")
dtmUsers.ColumnMappings.Add(dcmUserID)
dtmUsers.ColumnMappings.Add(dcmFirstName)
dtmUsers.ColumnMappings.Add(dcmLastName)
' Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers)
' Fill the dataset
daUsers.Fill(dsUsers)
' Declare a command builder to create SQL instructions
' to create and update records.
Dim cb As New OleDbCommandBuilder(daUsers)
' Insert a new record in the DataSet
Dim r As DataRow = dsUsers.Tables(0).NewRow()
r("FirstName") = "Eddie"
r("LastName") = "Robinson"
dsUsers.Tables(0).Rows.Add(r)
' Insert the record even in the database
daUsers.Update(dsUsers.GetChanges())
' Align in-memory data with the data source ones
dsUsers.AcceptChanges()
' Print successfully message
Console.WriteLine("A new record has been" & _
" added to the database.")
Catch ex As Exception
' Reject DataSet changes
dsUsers.RejectChanges()
' An error occurred. Show the error message
Console.WriteLine(ex.Message)
End Try
End Sub
End Class
--
不錯的寫法,收藏
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.Common
Imports System.Data
Public Class MainClass
Shared Sub Main()
Dim dsUsers As New DataSet("Users")
Try
' Define a connection object
Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
' Create a data adapter to retrieve records from db
Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
' Define each column to map
Dim dcmUserID As New DataColumnMapping("ID", "EmployeeID")
Dim dcmFirstName As New DataColumnMapping("FirstName", "FirstName")
Dim dcmLastName As New DataColumnMapping("LastName", "LastName")
' Define the table containing the mapped columns
Dim dtmUsers As New DataTableMapping("Table", "Employee")
dtmUsers.ColumnMappings.Add(dcmUserID)
dtmUsers.ColumnMappings.Add(dcmFirstName)
dtmUsers.ColumnMappings.Add(dcmLastName)
' Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers)
' Fill the dataset
daUsers.Fill(dsUsers)
' Declare a command builder to create SQL instructions
' to create and update records.
Dim cb As New OleDbCommandBuilder(daUsers)
' Insert a new record in the DataSet
Dim r As DataRow = dsUsers.Tables(0).NewRow()
r("FirstName") = "Eddie"
r("LastName") = "Robinson"
dsUsers.Tables(0).Rows.Add(r)
' Insert the record even in the database
daUsers.Update(dsUsers.GetChanges())
' Align in-memory data with the data source ones
dsUsers.AcceptChanges()
' Print successfully message
Console.WriteLine("A new record has been" & _
" added to the database.")
Catch ex As Exception
' Reject DataSet changes
dsUsers.RejectChanges()
' An error occurred. Show the error message
Console.WriteLine(ex.Message)
End Try
End Sub
End Class
2013年6月10日 星期一
MSSQL bcp 運用
參考引用
--
--建立XML格式檔
bcp DataBase.dbo.Table format nul -T -c -x -f filename.xml
--匯出資料
bcp DataBase.dbo.Table out filename.txt -c -T
--匯入資料，資料表必須是存在的實體資料表。
bcp DataBase.dbo.Table in filename.txt -T -t -f filename.xml
匯出查詢結果
bcp "SELECT * FROM database.dbo.table Where a>1" queryout filename.txt -c -T
將遠端資料匯出至本機D槽
bcp Table out d:\file.txt -k -n -c -S 192.168.0.100 -U xxx-P xxx
將遠端資料匯出至網路分享空間
bcp Table out \\192.168.0.101\tmp\file.txt -k -n -c -S 192.168.0.100 -U xxx-P x
--
--建立XML格式檔
bcp DataBase.dbo.Table format nul -T -c -x -f filename.xml
--匯出資料
bcp DataBase.dbo.Table out filename.txt -c -T
--匯入資料，資料表必須是存在的實體資料表。
bcp DataBase.dbo.Table in filename.txt -T -t -f filename.xml
匯出查詢結果
bcp "SELECT * FROM database.dbo.table Where a>1" queryout filename.txt -c -T
將遠端資料匯出至本機D槽
bcp Table out d:\file.txt -k -n -c -S 192.168.0.100 -U xxx-P xxx
將遠端資料匯出至網路分享空間
bcp Table out \\192.168.0.101\tmp\file.txt -k -n -c -S 192.168.0.100 -U xxx-P x
MSSQL FOR XML 運用
參考1:Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax
參考2:大量匯入與匯出 XML 文件的範例 (SQL Server)
參考3:將 Table 資料匯出成 XML
參考4:[T-SQL] 匯入Xml IN DB 並轉換為 Table
--
簡單的是:
select * from books for xml auto
參考2:大量匯入與匯出 XML 文件的範例 (SQL Server)
參考3:將 Table 資料匯出成 XML
參考4:[T-SQL] 匯入Xml IN DB 並轉換為 Table
--
簡單的是:
select * from books for xml auto
2013年6月9日 星期日
豐年蟲孵化方法
參考引用:豐年蟲孵化方法
--
豐年蝦（Artemia）又稱 鹹水豐年蝦（Brine Shrimp）而其卵（豐年蝦卵、Artemia Cysts or Brine Shrimp Eggs）
豐年蝦分布甚廣，除兩極地區外，在世界上許多地區的含有碳酸鹽、硫酸鹽和氯化鈉的湖泊中，以及沿海鹽田等高鹽水域都有不同程度的分布。全世界已報導的豐年蝦產地有500多處。目前豐年蝦卵產品主要來自中國、美國、伊朗和俄羅斯等20多個國家的不同地理品系，其中以美國加裡佛尼亞的舊金山灣、大鹽湖以及中國東部沿海鹽田、青海、西藏和新疆南部鹽湖出產的豐年蝦卵品質最優。可見，中國已經是世界上生產豐年蝦卵的大國之一。
自1775年以來，人們就開始了對豐年蝦的研究，迄今已有 200餘年的歷史。但用於水產餌料，僅始於上世紀30年代。在1933年美國的Seale 把豐年蝦初孵的無節幼體作為疑?仔魚的活餌料，隨後挪威的 Rollefson等也用豐年蝦無節幼體作為活餌料培養魚苗，均獲成功，從而證實了豐年蝦的無節幼體是仔、幼魚的優質餌料。在上世紀50年代後期，中科院海洋所張孝威教授首次發現了我國的豐年蝦卵，經10餘種海產魚苗培育實驗，也證明了豐年蝦的無節幼體是仔、幼魚的良好的餌料。同時開展了豐年蝦卵的調查、採集、孵化和培養的研究，積累了許多寶貴的資料。由於豐年蝦無節幼體是魚、蝦和蟹的優質活餌料，因此受到全球水產養殖工作者的高度重視。在比利時肯特大學成立了世界豐年蝦參考中心，並先後在美國得克薩斯州（1979年）和比利時安特沃普城（1985年）舉行了第一、第二屆國際豐年蝦學術會議。
豐年蝦,它是一種廣泛分布於全球且耐高鹽的小型甲殼類動物。豐年蝦卵是由豐年蝦所產生的休眠卵，目前世界上已經記載的豐年蝦卵有100多個品系。豐年蝦無節幼體具有大量的卵?，並含有豐富的蛋白質和脂肪（註：其中蛋白質約含60%、脂肪約含20%），它的成體所含營養成份也很高。因此，豐年蝦作為一種重要的生物餌料,一直受到人們的廣泛重視。當前世界上85%以上的水產養殖動物的育苗均以豐年蝦作為餌料來源。
豐年蝦卵的採收季節一般是在秋天，但春天也有少量豐年蝦卵可供採收。除了美國加州莫諾豐年蝦產沉性休眠外，其他地區多數豐年蝦產生出的休眠卵均為浮性卵。卵常被風浪沖擊到岸邊堆積在 一起，呈淺紅褐色。可直接從岸邊刮取，或用特製的小抄網，在下風處撈取飄浮於水面或懸浮水中的豐年蝦卵，一般常用方形小抄網（網口40目、 網袋60目），如果用於收集水體中的鹵蟲豐年蝦卵，常用圓形的小抄網（上面40目、下面90目）。另外，也可在池邊挖坑或構築浮柵， 使卵集中在局部水體中，以便採收。
【製作過程】
剛採集的卵切忌堆積,一般要經過如下加工步驟﹕
1.清洗:清洗一般用錐形桶,先用淡水沖洗,再用海水沖洗.沖洗的主要目 的是除去雜物、
卵殼及表面上的污染。
2.脫水:濕卵脫水的方法一般是先離心,後陰乾；而乾卵，則應該先低溫後真空乾燥。
3.晾乾:在晾乾過程中,要防止溫度過高(不超過40度)和在陽光下曝曬。
4.篩選:用不同的孔徑的篩網分層過篩。
5.包裝:真空或沖氮密封包裝。
6.儲藏:豐年蝦卵要先在-18度下放一周冷凍存存,再在常溫下放一周， 對於乾卵一般
低溫儲 藏即可。
經過以上步驟處理的豐年蝦卵，儲藏數年，卵化率仍可保持70%左右。
【豐年蟲孵化方法】
孵化準備：
1.使用乾淨的容器(大小視個人需求量而定)
2.在容器的上部設置光源(建議使用，增加孵化率)
3.在容器的底部安裝氣管，在缸的底部打氣
4.加入乾淨清水及的鹽，比例約1公升水/45公克鹽，精鹽或粗鹽皆可
5.最後加入豐年蝦卵比例約2.0公克卵/一公升水
孵化條件：
1.溫度﹕28-30℃
2.ＰＨ﹕8.0-9.0
3.打氣﹕足夠的氣泡使豐年蝦卵保持懸浮在鹽水中
4.光照﹕鹽水表面為2000lux
5.密度﹕2.0克卵╱升水。
孵化收集：
1.在理想的孵化條件下，孵化24小時
2.停止打氣
3.覆蓋孵化缸的表面（遮光）
4.靜置10分鐘後將沉在底部的豐年蝦排出或虹吸出來，可再靜置10分鐘，收集缸中剩餘
的豐年蝦。
保存方法 ：
1.降低液面（約1~2公分），增加空氣接觸面積（使溶氧量增加）
2.保存在溫度0℃-5℃的乾燥避光的地方（如冰箱冷藏庫）
豐年蝦卵的營養成分分析﹕豐年蝦卵含有豐富的蛋白質,氨基酸組成齊全,粗脂肪含量比較高,其中不飽和脂肪酸高於飽和脂肪酸,帶殼卵的不飽和脂肪酸為48.15%,脫殼卵的為54.82%。帶殼卵的飽和脂肪酸是脫殼卵的1.5倍。豐年蝦卵的殼是一種含鐵脂蛋白,魚和蝦的幼體不能消化它。但動物實驗證明小鼠可以消化鹵蟲卵殼,所以用豐年蝦卵加工食品不必考慮殼的問題,而且有的不飽和脂肪酸、無機元素在殼中含量比較高,如DHA（二十二碳六烯酸）,Se、Zn、Fe等;食用粉碎、脫殼的豐年蝦卵能提高動物肝中的γ-亞麻酸（必需脂肪酸）,而DHA和EPA都明顯高於對照組。另一個特徵是可明顯提高肝中Fe2+的含量,Fe2+是血紅素的主要成分,因此豐年蝦卵可作為一種補血劑,而且食鹵蟲豐年蝦卵可提高腦蛋白含量。 從營養分析上可以確定豐年蝦卵含有豐富的蛋白質、氨基酸、不飽和脂肪酸和無機元素。動物實驗證明,食用豐年蝦卵對血脂沒有影響,而且能提高肝中的DHA、EPA及血紅素和腦蛋白的含量。
去殼豐年蝦卵的應用﹕P.Sorgelocs等人（1977）用次氯酸鈉溶液處理休眠卵可除去卵外殼,剩下一層透明的內膜（即孵化膜）,處理後的卵大小為200∼300μm,富含卵黃,可直接投餵養殖對象,也可孵化成無節幼體。去殼卵與不去殼的卵相比,可以得到較高的孵化率。去殼卵用飽和鹽水（NaCl）或飽和MgCl2溶液脫水後可冰凍保存數日,孵化率並不降低;也可利用液氮冷凍保存去殼卵。使用去殼卵的優點是:
（1）可以直接投喂,不需再經過孵化管理操作
（2）去殼豐年蝦卵在加工的過程中殺死了卵殼上的細菌、真菌,特別是聚縮蟲,
杜絕了因豐年蝦卵消毒不嚴格而導致的疾病傳染
（3）省去了無節幼體出膜時所需消耗的能量,故其營養優於自行孵出的無節幼體
（4）去殼卵售價便宜,比進口豐年蝦卵的價格低50%以上。
其不足之處在於:
（1）去殼卵缺乏懸浮性,投喂時潑撒是否均勻將直接影響餌料效果,切忌一次投喂過多,
卵 沉入池底不僅造成浪費,而且還會污染水質;以小型家用水泵的噴水嘴作為投餌器,
可取得良好效果。
（2）用次氯酸鹽作為去殼藥物,鹵蟲卵去殼脫氯後雖用大量海水沖洗仍有異味,
影響使用效果。
用魚蝦引誘劑烏魚漿或貽貝漿處理,可消除異味。去殼鹵蟲卵作為魚蝦幼體的餌料,具有廣闊的開發前景。
豐年蝦卵脫殼﹕豐年蟲卵200克浸入三升水中，充氧2小時，再用150－200微米的篩絹過濾、清洗。然後再加1.4L水，25克CaO和55工業漂白粉，同時使溫度保持在20oC，然後再加25克CaO和55工業漂白粉。當卵懸浮後呈桔黃色時，用150－200um網目的篩絹過濾，然後用水沖洗直到無氯氣為止，再將卵浸泡再含1克硫代硫酸鈉中，充分攪拌，使脫膜的卵下沉。得到脫膜的卵洗淨。
豐年蟲卵消毒﹕4kg的卵放在0.05%的硫代硫酸鈉中浸泡數分鐘，排干水，加0.5kg的工業漂白粉攪拌，加冰，保持在40oC下放置10分鐘，用浮游植物網過濾得到卵清洗，再用0.05%的硫代硫酸鈉浸泡2－5min，洗淨，可以卵化。
孵化方法﹕
（1） 豐年蝦卵在干燥條件下可以較長期保存,當放入鹽度為20 ∼30 ,溫度25∼27℃的水
中,並滿足孵化所需的溶氧量,經24∼48小時後就孵出幼體。
（2） 孵化豐年蝦卵時,可用甘油鹽水洗去沾在卵上的雜質和泥沙,取得上浮的潔淨卵子;也可
將卵子先行速凍,從-3℃漸至-15℃,冷藏1∼3個月後再行孵化,可得到60%∼90%的
孵化率。
（3） 直接將豐年蝦冬卵（或者經過消毒、去殼後）放入海水中，水溫保持在28－30oC，
經過24－30小時的直接卵化就可發育成無節幼體。改進方法﹕豐年蝦卵在消毒、充分
浸泡後裝入篩絹製成的袋中，放入25oC的卵化室，避免強光照射，保證供氧充足，8
－10小時後變成燈籠幼體（卵與幼體沒有分開，呈燈籠狀），再放入28－30oC的海水 中。變成無節幼體。
------------豐年蝦幼體的加工與應用﹕初孵幼體呈桔黃色,體內含豐富卵黃,是魚蝦類育苗時期非常合適的活餌料。無節幼體孵化後除直接應用外,還可用低溫進行冷藏。即將剛孵化出來的無節幼體保存在5∼10℃條件下（應充氣以免窒息）,冷藏24小時甚至更長時間。在豐年蝦應用中尚存在著一些問題,如無節幼體的日常分離、撈取和保存需要大量勞力;對於初期仔魚或蝦蟹類的?敿D狀幼體,剛孵出的無節幼體（大小約為400∼500μm）,仍嫌過大;無節幼體與卵殼不易分離得很好。卵殼被魚蝦吞食後會引起腸梗塞,造成大量死亡,還會污染水質。
豐年蝦成體的應用豐年蝦成體有相當高的營養價值,含蛋白質60%,灰分10%,除各種必需氨基酸和多聚不飽和脂肪酸外,還含有較多的維生素。可直接投喂,用於多種水產動物的幼體或成體（觀賞魚）。豐年蝦含有較多的脫皮激素,可促進親蝦性成熟。從1980年起,我國對蝦養殖業的發展使豐年蝦的用量空前大增。巴西和泰國分別用鮮活豐年蝦投餵對蝦的後期幼體和遮目魚的稚魚,均得到良好的飼養效果。巴西的鹽田和美國加州有大量的鮮活豐年蝦出售,泰國、菲律賓及澳大利亞則有人工養殖的豐年蝦出售。世界上約有20個企業專門生產豐年蝦成體冷凍品及罐頭。以每噸水放15g優質豐年蝦卵的密度,一周內可生產出20kg成蟲（濕重）或2kg豐年蝦乾品。
其它應用﹕作為水產苗種內服藥的活體膠囊魚蝦幼體因微生物傳染而引起死亡對生產威脅很大。利用豐年蝦作為活載體,以強化技術加入藥物或疫苗,使其填載到豐年蝦腸道內,魚蝦幼體喜食豐年蝦,藥物被直接帶進魚蝦體內,起到了防病治病的效果。
存在問題及應用前景目前在應用中存在的主要問題﹕
根據養殖對象使用不同卵徑的蝦卵可節省餌料,從而提高育苗的質量和降低成本。
營養強化目前市場上流通的商品豐年蝦卵以美國產的為主,但其ω3系列高度不飽和脂肪酸明顯不足,其初孵豐年蝦幼體用於對蝦育苗還可以,如果用來喂養海產仔魚則會引起營養缺乏癥。為提高豐年蝦的營養水準,通常采取濃縮MC（擬微囊小球藻）投喂及魚油製品。然而由於豐年蝦的腸道難於消化細胞壁堅硬的MC和面包酵母;又因多數魚油製品會附著在豐年蝦體表,導致豐年蝦活力下降。故優良營養強化劑的開發有待今後進一步研究。對豐年蝦的營養強化應注意其合理性,若用於仔魚的活餌料,較多地采用DHA、磷脂和抗壞血酸（Vc）來強化,一般連續2天投餵經DHA強化的輪蟲就足以滿足仔魚的生理需求。若仔魚在攝食輪蟲階段就已獲得足夠的ω3HUFA,那麼豐年蝦的營養強化不必過分強調,因為營養過多也不利於個體發育。
品種改良近年來,世界上許多水產養殖專家為改善豐年蝦的品質開展了添加營養劑等方面的大量研究,這些研究均未涉及豐年蝦在遺傳上的品質改良。若能通過雜交,提高遺傳變異水平,再借助於誘變處理和海洋生物工程技術,培育出抗逆性強、營養價值高的豐年蝦新品系,就將為魚類和甲殼類養殖業的發展奠定十分關鍵性的餌料基礎。
豐年蝦去殼卵的應用採用豐年蝦去殼卵不僅可以得到較高的孵化率,且是魚、蝦、蟹早期幼體極好的適口餌料。當前,積極開展豐年蝦去殼卵的應用研究,可以對大量孵化率低的豐年蝦卵資源（估計有幾萬噸）充分利用。我國的水產養殖業要從傳統的以鯉科魚類為主體的格局,轉向以高值海淡水魚蝦為主角的局面, 豐年蝦作為幼體的初期餌料,其作用和地位不容忽視。因此,有必要對我國的豐年蝦資源進行系統調查,對不同地區、不同品系的豐年蝦進行生物學及生理、生化等方面的基礎研究,為豐年蝦增養殖與接種提供技術依據。另一方面,應大力發展豐年蝦養殖業,在實施我國西部大開發的戰略中,把高原鹽湖豐年蝦資源的利用列入規劃,實現東西部漁業開發的聯動;我國南方也可以象東南亞國家那樣利用旱季養殖豐年蝦,以充分利用鹽田。
--
豐年蝦（Artemia）又稱 鹹水豐年蝦（Brine Shrimp）而其卵（豐年蝦卵、Artemia Cysts or Brine Shrimp Eggs）
豐年蝦分布甚廣，除兩極地區外，在世界上許多地區的含有碳酸鹽、硫酸鹽和氯化鈉的湖泊中，以及沿海鹽田等高鹽水域都有不同程度的分布。全世界已報導的豐年蝦產地有500多處。目前豐年蝦卵產品主要來自中國、美國、伊朗和俄羅斯等20多個國家的不同地理品系，其中以美國加裡佛尼亞的舊金山灣、大鹽湖以及中國東部沿海鹽田、青海、西藏和新疆南部鹽湖出產的豐年蝦卵品質最優。可見，中國已經是世界上生產豐年蝦卵的大國之一。
自1775年以來，人們就開始了對豐年蝦的研究，迄今已有 200餘年的歷史。但用於水產餌料，僅始於上世紀30年代。在1933年美國的Seale 把豐年蝦初孵的無節幼體作為疑?仔魚的活餌料，隨後挪威的 Rollefson等也用豐年蝦無節幼體作為活餌料培養魚苗，均獲成功，從而證實了豐年蝦的無節幼體是仔、幼魚的優質餌料。在上世紀50年代後期，中科院海洋所張孝威教授首次發現了我國的豐年蝦卵，經10餘種海產魚苗培育實驗，也證明了豐年蝦的無節幼體是仔、幼魚的良好的餌料。同時開展了豐年蝦卵的調查、採集、孵化和培養的研究，積累了許多寶貴的資料。由於豐年蝦無節幼體是魚、蝦和蟹的優質活餌料，因此受到全球水產養殖工作者的高度重視。在比利時肯特大學成立了世界豐年蝦參考中心，並先後在美國得克薩斯州（1979年）和比利時安特沃普城（1985年）舉行了第一、第二屆國際豐年蝦學術會議。
豐年蝦,它是一種廣泛分布於全球且耐高鹽的小型甲殼類動物。豐年蝦卵是由豐年蝦所產生的休眠卵，目前世界上已經記載的豐年蝦卵有100多個品系。豐年蝦無節幼體具有大量的卵?，並含有豐富的蛋白質和脂肪（註：其中蛋白質約含60%、脂肪約含20%），它的成體所含營養成份也很高。因此，豐年蝦作為一種重要的生物餌料,一直受到人們的廣泛重視。當前世界上85%以上的水產養殖動物的育苗均以豐年蝦作為餌料來源。
豐年蝦卵的採收季節一般是在秋天，但春天也有少量豐年蝦卵可供採收。除了美國加州莫諾豐年蝦產沉性休眠外，其他地區多數豐年蝦產生出的休眠卵均為浮性卵。卵常被風浪沖擊到岸邊堆積在 一起，呈淺紅褐色。可直接從岸邊刮取，或用特製的小抄網，在下風處撈取飄浮於水面或懸浮水中的豐年蝦卵，一般常用方形小抄網（網口40目、 網袋60目），如果用於收集水體中的鹵蟲豐年蝦卵，常用圓形的小抄網（上面40目、下面90目）。另外，也可在池邊挖坑或構築浮柵， 使卵集中在局部水體中，以便採收。
【製作過程】
剛採集的卵切忌堆積,一般要經過如下加工步驟﹕
1.清洗:清洗一般用錐形桶,先用淡水沖洗,再用海水沖洗.沖洗的主要目 的是除去雜物、
卵殼及表面上的污染。
2.脫水:濕卵脫水的方法一般是先離心,後陰乾；而乾卵，則應該先低溫後真空乾燥。
3.晾乾:在晾乾過程中,要防止溫度過高(不超過40度)和在陽光下曝曬。
4.篩選:用不同的孔徑的篩網分層過篩。
5.包裝:真空或沖氮密封包裝。
6.儲藏:豐年蝦卵要先在-18度下放一周冷凍存存,再在常溫下放一周， 對於乾卵一般
低溫儲 藏即可。
經過以上步驟處理的豐年蝦卵，儲藏數年，卵化率仍可保持70%左右。
【豐年蟲孵化方法】
孵化準備：
1.使用乾淨的容器(大小視個人需求量而定)
2.在容器的上部設置光源(建議使用，增加孵化率)
3.在容器的底部安裝氣管，在缸的底部打氣
4.加入乾淨清水及的鹽，比例約1公升水/45公克鹽，精鹽或粗鹽皆可
5.最後加入豐年蝦卵比例約2.0公克卵/一公升水
孵化條件：
1.溫度﹕28-30℃
2.ＰＨ﹕8.0-9.0
3.打氣﹕足夠的氣泡使豐年蝦卵保持懸浮在鹽水中
4.光照﹕鹽水表面為2000lux
5.密度﹕2.0克卵╱升水。
孵化收集：
1.在理想的孵化條件下，孵化24小時
2.停止打氣
3.覆蓋孵化缸的表面（遮光）
4.靜置10分鐘後將沉在底部的豐年蝦排出或虹吸出來，可再靜置10分鐘，收集缸中剩餘
的豐年蝦。
保存方法 ：
1.降低液面（約1~2公分），增加空氣接觸面積（使溶氧量增加）
2.保存在溫度0℃-5℃的乾燥避光的地方（如冰箱冷藏庫）
豐年蝦卵的營養成分分析﹕豐年蝦卵含有豐富的蛋白質,氨基酸組成齊全,粗脂肪含量比較高,其中不飽和脂肪酸高於飽和脂肪酸,帶殼卵的不飽和脂肪酸為48.15%,脫殼卵的為54.82%。帶殼卵的飽和脂肪酸是脫殼卵的1.5倍。豐年蝦卵的殼是一種含鐵脂蛋白,魚和蝦的幼體不能消化它。但動物實驗證明小鼠可以消化鹵蟲卵殼,所以用豐年蝦卵加工食品不必考慮殼的問題,而且有的不飽和脂肪酸、無機元素在殼中含量比較高,如DHA（二十二碳六烯酸）,Se、Zn、Fe等;食用粉碎、脫殼的豐年蝦卵能提高動物肝中的γ-亞麻酸（必需脂肪酸）,而DHA和EPA都明顯高於對照組。另一個特徵是可明顯提高肝中Fe2+的含量,Fe2+是血紅素的主要成分,因此豐年蝦卵可作為一種補血劑,而且食鹵蟲豐年蝦卵可提高腦蛋白含量。 從營養分析上可以確定豐年蝦卵含有豐富的蛋白質、氨基酸、不飽和脂肪酸和無機元素。動物實驗證明,食用豐年蝦卵對血脂沒有影響,而且能提高肝中的DHA、EPA及血紅素和腦蛋白的含量。
去殼豐年蝦卵的應用﹕P.Sorgelocs等人（1977）用次氯酸鈉溶液處理休眠卵可除去卵外殼,剩下一層透明的內膜（即孵化膜）,處理後的卵大小為200∼300μm,富含卵黃,可直接投餵養殖對象,也可孵化成無節幼體。去殼卵與不去殼的卵相比,可以得到較高的孵化率。去殼卵用飽和鹽水（NaCl）或飽和MgCl2溶液脫水後可冰凍保存數日,孵化率並不降低;也可利用液氮冷凍保存去殼卵。使用去殼卵的優點是:
（1）可以直接投喂,不需再經過孵化管理操作
（2）去殼豐年蝦卵在加工的過程中殺死了卵殼上的細菌、真菌,特別是聚縮蟲,
杜絕了因豐年蝦卵消毒不嚴格而導致的疾病傳染
（3）省去了無節幼體出膜時所需消耗的能量,故其營養優於自行孵出的無節幼體
（4）去殼卵售價便宜,比進口豐年蝦卵的價格低50%以上。
其不足之處在於:
（1）去殼卵缺乏懸浮性,投喂時潑撒是否均勻將直接影響餌料效果,切忌一次投喂過多,
卵 沉入池底不僅造成浪費,而且還會污染水質;以小型家用水泵的噴水嘴作為投餌器,
可取得良好效果。
（2）用次氯酸鹽作為去殼藥物,鹵蟲卵去殼脫氯後雖用大量海水沖洗仍有異味,
影響使用效果。
用魚蝦引誘劑烏魚漿或貽貝漿處理,可消除異味。去殼鹵蟲卵作為魚蝦幼體的餌料,具有廣闊的開發前景。
豐年蝦卵脫殼﹕豐年蟲卵200克浸入三升水中，充氧2小時，再用150－200微米的篩絹過濾、清洗。然後再加1.4L水，25克CaO和55工業漂白粉，同時使溫度保持在20oC，然後再加25克CaO和55工業漂白粉。當卵懸浮後呈桔黃色時，用150－200um網目的篩絹過濾，然後用水沖洗直到無氯氣為止，再將卵浸泡再含1克硫代硫酸鈉中，充分攪拌，使脫膜的卵下沉。得到脫膜的卵洗淨。
豐年蟲卵消毒﹕4kg的卵放在0.05%的硫代硫酸鈉中浸泡數分鐘，排干水，加0.5kg的工業漂白粉攪拌，加冰，保持在40oC下放置10分鐘，用浮游植物網過濾得到卵清洗，再用0.05%的硫代硫酸鈉浸泡2－5min，洗淨，可以卵化。
孵化方法﹕
（1） 豐年蝦卵在干燥條件下可以較長期保存,當放入鹽度為20 ∼30 ,溫度25∼27℃的水
中,並滿足孵化所需的溶氧量,經24∼48小時後就孵出幼體。
（2） 孵化豐年蝦卵時,可用甘油鹽水洗去沾在卵上的雜質和泥沙,取得上浮的潔淨卵子;也可
將卵子先行速凍,從-3℃漸至-15℃,冷藏1∼3個月後再行孵化,可得到60%∼90%的
孵化率。
（3） 直接將豐年蝦冬卵（或者經過消毒、去殼後）放入海水中，水溫保持在28－30oC，
經過24－30小時的直接卵化就可發育成無節幼體。改進方法﹕豐年蝦卵在消毒、充分
浸泡後裝入篩絹製成的袋中，放入25oC的卵化室，避免強光照射，保證供氧充足，8
－10小時後變成燈籠幼體（卵與幼體沒有分開，呈燈籠狀），再放入28－30oC的海水 中。變成無節幼體。
------------豐年蝦幼體的加工與應用﹕初孵幼體呈桔黃色,體內含豐富卵黃,是魚蝦類育苗時期非常合適的活餌料。無節幼體孵化後除直接應用外,還可用低溫進行冷藏。即將剛孵化出來的無節幼體保存在5∼10℃條件下（應充氣以免窒息）,冷藏24小時甚至更長時間。在豐年蝦應用中尚存在著一些問題,如無節幼體的日常分離、撈取和保存需要大量勞力;對於初期仔魚或蝦蟹類的?敿D狀幼體,剛孵出的無節幼體（大小約為400∼500μm）,仍嫌過大;無節幼體與卵殼不易分離得很好。卵殼被魚蝦吞食後會引起腸梗塞,造成大量死亡,還會污染水質。
豐年蝦成體的應用豐年蝦成體有相當高的營養價值,含蛋白質60%,灰分10%,除各種必需氨基酸和多聚不飽和脂肪酸外,還含有較多的維生素。可直接投喂,用於多種水產動物的幼體或成體（觀賞魚）。豐年蝦含有較多的脫皮激素,可促進親蝦性成熟。從1980年起,我國對蝦養殖業的發展使豐年蝦的用量空前大增。巴西和泰國分別用鮮活豐年蝦投餵對蝦的後期幼體和遮目魚的稚魚,均得到良好的飼養效果。巴西的鹽田和美國加州有大量的鮮活豐年蝦出售,泰國、菲律賓及澳大利亞則有人工養殖的豐年蝦出售。世界上約有20個企業專門生產豐年蝦成體冷凍品及罐頭。以每噸水放15g優質豐年蝦卵的密度,一周內可生產出20kg成蟲（濕重）或2kg豐年蝦乾品。
其它應用﹕作為水產苗種內服藥的活體膠囊魚蝦幼體因微生物傳染而引起死亡對生產威脅很大。利用豐年蝦作為活載體,以強化技術加入藥物或疫苗,使其填載到豐年蝦腸道內,魚蝦幼體喜食豐年蝦,藥物被直接帶進魚蝦體內,起到了防病治病的效果。
存在問題及應用前景目前在應用中存在的主要問題﹕
根據養殖對象使用不同卵徑的蝦卵可節省餌料,從而提高育苗的質量和降低成本。
營養強化目前市場上流通的商品豐年蝦卵以美國產的為主,但其ω3系列高度不飽和脂肪酸明顯不足,其初孵豐年蝦幼體用於對蝦育苗還可以,如果用來喂養海產仔魚則會引起營養缺乏癥。為提高豐年蝦的營養水準,通常采取濃縮MC（擬微囊小球藻）投喂及魚油製品。然而由於豐年蝦的腸道難於消化細胞壁堅硬的MC和面包酵母;又因多數魚油製品會附著在豐年蝦體表,導致豐年蝦活力下降。故優良營養強化劑的開發有待今後進一步研究。對豐年蝦的營養強化應注意其合理性,若用於仔魚的活餌料,較多地采用DHA、磷脂和抗壞血酸（Vc）來強化,一般連續2天投餵經DHA強化的輪蟲就足以滿足仔魚的生理需求。若仔魚在攝食輪蟲階段就已獲得足夠的ω3HUFA,那麼豐年蝦的營養強化不必過分強調,因為營養過多也不利於個體發育。
品種改良近年來,世界上許多水產養殖專家為改善豐年蝦的品質開展了添加營養劑等方面的大量研究,這些研究均未涉及豐年蝦在遺傳上的品質改良。若能通過雜交,提高遺傳變異水平,再借助於誘變處理和海洋生物工程技術,培育出抗逆性強、營養價值高的豐年蝦新品系,就將為魚類和甲殼類養殖業的發展奠定十分關鍵性的餌料基礎。
豐年蝦去殼卵的應用採用豐年蝦去殼卵不僅可以得到較高的孵化率,且是魚、蝦、蟹早期幼體極好的適口餌料。當前,積極開展豐年蝦去殼卵的應用研究,可以對大量孵化率低的豐年蝦卵資源（估計有幾萬噸）充分利用。我國的水產養殖業要從傳統的以鯉科魚類為主體的格局,轉向以高值海淡水魚蝦為主角的局面, 豐年蝦作為幼體的初期餌料,其作用和地位不容忽視。因此,有必要對我國的豐年蝦資源進行系統調查,對不同地區、不同品系的豐年蝦進行生物學及生理、生化等方面的基礎研究,為豐年蝦增養殖與接種提供技術依據。另一方面,應大力發展豐年蝦養殖業,在實施我國西部大開發的戰略中,把高原鹽湖豐年蝦資源的利用列入規劃,實現東西部漁業開發的聯動;我國南方也可以象東南亞國家那樣利用旱季養殖豐年蝦,以充分利用鹽田。
Get Column Name and Data Types of Access Tables
參考引用
--
Private Sub Demo(ByVal ConnectionString As String)
Using cn As New OleDbConnection(ConnectionString)
Dim Result = SchemaInfo(cn.ConnectionString, "Table1")
For Each row As DataRow In Result.Rows
Console.WriteLine("Name={0} Type={1}", row("ColumnName"), row("DataType"))
Next
End Using
End Sub
Public Function SchemaInfo(ByVal ConnectionString As String, ByVal TableName As String) As DataTable
Dim dt As New DataTable With {.TableName = "Schema"}
dt.Columns.AddRange( _
New DataColumn() _
{ _
New DataColumn("ColumnName", GetType(System.String)), _
New DataColumn("DataType", GetType(System.String)) _
} _
)
Using cn As New OleDbConnection(ConnectionString)
Using cmd As New OleDbCommand("SELECT * FROM " & TableName, cn)
cn.Open()
Dim Reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim schemaTable = Reader.GetSchemaTable()
schemaTable.TableName = "TableSchema"
Dim sw As New IO.StringWriter
schemaTable.WriteXml(sw)
Dim Doc = New XDocument
Doc = XDocument.Parse(sw.ToString)
Dim query = _
( _
From T In Doc... _
Select _
Name = T..Value, _
DataType = T..Value.Split(","c)(0).Replace("System.", "") _
).ToList
For Each item In query
Dim Row As DataRow
Row = dt.NewRow
Row("ColumnName") = item.Name
Row("DataType") = item.DataType
dt.Rows.Add(Row)
Next
End Using
End Using
Return dt
End Function
--
Private Sub Demo(ByVal ConnectionString As String)
Using cn As New OleDbConnection(ConnectionString)
Dim Result = SchemaInfo(cn.ConnectionString, "Table1")
For Each row As DataRow In Result.Rows
Console.WriteLine("Name={0} Type={1}", row("ColumnName"), row("DataType"))
Next
End Using
End Sub
Public Function SchemaInfo(ByVal ConnectionString As String, ByVal TableName As String) As DataTable
Dim dt As New DataTable With {.TableName = "Schema"}
dt.Columns.AddRange( _
New DataColumn() _
{ _
New DataColumn("ColumnName", GetType(System.String)), _
New DataColumn("DataType", GetType(System.String)) _
} _
)
Using cn As New OleDbConnection(ConnectionString)
Using cmd As New OleDbCommand("SELECT * FROM " & TableName, cn)
cn.Open()
Dim Reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim schemaTable = Reader.GetSchemaTable()
schemaTable.TableName = "TableSchema"
Dim sw As New IO.StringWriter
schemaTable.WriteXml(sw)
Dim Doc = New XDocument
Doc = XDocument.Parse(sw.ToString)
Dim query = _
( _
From T In Doc...
Select _
Name = T.
DataType = T.
).ToList
For Each item In query
Dim Row As DataRow
Row = dt.NewRow
Row("ColumnName") = item.Name
Row("DataType") = item.DataType
dt.Rows.Add(Row)
Next
End Using
End Using
Return dt
End Function
Get Colunm Data type
參考引用:Get Colunm Data type
--
Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
'Sql Query 1
Dim sql As String ="SELECT ID, FirstName, LastName FROM Employee; "
'Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
' Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
' Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
' Get column data types
Console.WriteLine("Data types: {0} | {1}", _
thisReader.GetDataTypeName(0).PadLeft(13), _
thisReader.GetDataTypeName(1))
'Close DataReader
thisReader.Close()
Catch ex As SqlException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class
--
Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
'Sql Query 1
Dim sql As String ="SELECT ID, FirstName, LastName FROM Employee; "
'Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
' Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
' Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
' Get column data types
Console.WriteLine("Data types: {0} | {1}", _
thisReader.GetDataTypeName(0).PadLeft(13), _
thisReader.GetDataTypeName(1))
'Close DataReader
thisReader.Close()
Catch ex As SqlException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class
2013年6月8日 星期六
VB.Net 透過JRO 修復Access MDB
參考引用來源:VB.Net 透過 JRO 修復 Access MDB
--
VB.Net 透過 JRO Compact / Repair Access MDB
Imports System
Imports System.IO
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' 將C:\db1.mdb 做Compact / Repair
If Compact_MDB("C:\db1.mdb") Then MessageBox.Show("OK !")
End Sub
Private Function Compact_MDB(ByVal strFile As String) As Boolean
' Jet Access (MDB) 連線字串; Jet ( Joint Engine Technology )
Dim strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}"
' 或"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5"
' Path.GetTempFileName 方法: 在磁碟上建立具命之零位元組的唯一暫存檔案，
' 然後傳回該檔案的完整路徑。
Dim strTmpFile As String = Path.GetTempFileName.Replace(".tmp", ".mdb") ' 把tmp 副檔名改成mdb
' 建立物件陣列存放引數(參數) , 來源, 目的
Dim objPara As Object() = New Object() {String.Format(strCn, strFile), String.Format(strCn, strTmpFile)}
' Activator 成員: 包含本機或遠端建立物件型別的方法，或者取得對現有遠端物件的參考。
' Activator.CreateInstance 方法(Type) : 使用最符合指定參數的建構函式，建立指定型別的執行個體。
Dim objJRO As Object = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"))
' Type.GetTypeFromProgID 方法: 取得與指定的程式識別項(ProgID) 關聯的型別；
' 如果在載入Type 時發生錯誤，則傳回null。
' JRO.JetEngine 為Microsoft Jet and Replication Objects X.X library
' Type.InvokeMember 方法
' Type.InvokeMember (String, BindingFlags, Binder, Object, Object[])
objJRO.GetType.InvokeMember("CompactDatabase", Reflection.BindingFlags.InvokeMethod, _
Nothing, objJRO, objPara)
' 使用指定的繫結條件約束並符合指定的引數清單，來叫用指定的成員。
' BindingFlags 列舉型別,InvokeMethod 指定要叫用方法。
File.Delete(strFile) ' File.Delete 方法: 刪除Compact 前之mdb 檔
File.Move(strTmpFile, strFile) ' File.Move 方法: 將Compact 過的mdb 檔改成(回)正確檔名
' Marshal.ReleaseComObject 方法釋放JRO COM 物件
Runtime.InteropServices.Marshal.ReleaseComObject(objJRO)
objJRO = Nothing
Return True
End Function
End Class
---
奇怪,看他這篇是2008 的;現採用2012卻發生錯誤...百思不解
找到微軟官網 如何使用 Visual Basic.NET compact Microsoft Access 資料庫中
::在 [加入參考] 對話方塊中，按一下 [ COM ] 索引標籤，然後選取Microsoft Jet 和複寫物件 2。x庫。按一下 [選取以將其新增到選取的元件。按一下 [確定]
所以還是要靠com 來運作,可能 bjJRO.GetType.InvokeMember 這方法在 vs2012 已不適用
引用com 在使用上就簡單了!
Dim jro As JRO.JetEngine
jro = New JRO.JetEngine()
jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=5")
--
VB.Net 透過 JRO Compact / Repair Access MDB
Imports System
Imports System.IO
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' 將C:\db1.mdb 做Compact / Repair
If Compact_MDB("C:\db1.mdb") Then MessageBox.Show("OK !")
End Sub
Private Function Compact_MDB(ByVal strFile As String) As Boolean
' Jet Access (MDB) 連線字串; Jet ( Joint Engine Technology )
Dim strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}"
' 或"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5"
' Path.GetTempFileName 方法: 在磁碟上建立具命之零位元組的唯一暫存檔案，
' 然後傳回該檔案的完整路徑。
Dim strTmpFile As String = Path.GetTempFileName.Replace(".tmp", ".mdb") ' 把tmp 副檔名改成mdb
' 建立物件陣列存放引數(參數) , 來源, 目的
Dim objPara As Object() = New Object() {String.Format(strCn, strFile), String.Format(strCn, strTmpFile)}
' Activator 成員: 包含本機或遠端建立物件型別的方法，或者取得對現有遠端物件的參考。
' Activator.CreateInstance 方法(Type) : 使用最符合指定參數的建構函式，建立指定型別的執行個體。
Dim objJRO As Object = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"))
' Type.GetTypeFromProgID 方法: 取得與指定的程式識別項(ProgID) 關聯的型別；
' 如果在載入Type 時發生錯誤，則傳回null。
' JRO.JetEngine 為Microsoft Jet and Replication Objects X.X library
' Type.InvokeMember 方法
' Type.InvokeMember (String, BindingFlags, Binder, Object, Object[])
objJRO.GetType.InvokeMember("CompactDatabase", Reflection.BindingFlags.InvokeMethod, _
Nothing, objJRO, objPara)
' 使用指定的繫結條件約束並符合指定的引數清單，來叫用指定的成員。
' BindingFlags 列舉型別,InvokeMethod 指定要叫用方法。
File.Delete(strFile) ' File.Delete 方法: 刪除Compact 前之mdb 檔
File.Move(strTmpFile, strFile) ' File.Move 方法: 將Compact 過的mdb 檔改成(回)正確檔名
' Marshal.ReleaseComObject 方法釋放JRO COM 物件
Runtime.InteropServices.Marshal.ReleaseComObject(objJRO)
objJRO = Nothing
Return True
End Function
End Class
---
奇怪,看他這篇是2008 的;現採用2012卻發生錯誤...百思不解
找到微軟官網 如何使用 Visual Basic.NET compact Microsoft Access 資料庫中
::在 [加入參考] 對話方塊中，按一下 [ COM ] 索引標籤，然後選取Microsoft Jet 和複寫物件 2。x庫。按一下 [選取以將其新增到選取的元件。按一下 [確定]
所以還是要靠com 來運作,可能 bjJRO.GetType.InvokeMember 這方法在 vs2012 已不適用
引用com 在使用上就簡單了!
Dim jro As JRO.JetEngine
jro = New JRO.JetEngine()
jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=5")
2013年6月6日 星期四
自訂函數裏的select table名稱可否為變數?
參考引用:自訂函數裏的select table名稱可否為變數?
--
=====================================
ALTER FUNCTION [dbo].[V_GettableXXX]
(
@T_tablename varchar(15) ,
@T_year varchar(4)
)
RETURNS table
AS
BEGIN
DECLARE @tableXXX varchar(25)
set @tableXXX=@T_tablename+@T_year
EXEC ('select * from ' + @tableXXX)
END
底下提供我測試的寫法。回傳值的部份我並未處理與測試，請各位自行嚐試囉!!
DECLARE @tablename AS NVARCHAR(50)
SET @tablename = 'aEnrichRPTDB.dbo.dimDate'
EXEC ('SELECT * FROM ' + @tablename)
--
=====================================
ALTER FUNCTION [dbo].[V_GettableXXX]
(
@T_tablename varchar(15) ,
@T_year varchar(4)
)
RETURNS table
AS
BEGIN
DECLARE @tableXXX varchar(25)
set @tableXXX=@T_tablename+@T_year
EXEC ('select * from ' + @tableXXX)
END
底下提供我測試的寫法。回傳值的部份我並未處理與測試，請各位自行嚐試囉!!
DECLARE @tablename AS NVARCHAR(50)
SET @tablename = 'aEnrichRPTDB.dbo.dimDate'
EXEC ('SELECT * FROM ' + @tablename)
動態將多筆資料的特定欄位依分隔符號組成字串
請參考來源
--
可運用 FOR XML PATH('') 來處理
SELECT T1.id, ( STUFF( (
SELECT ',' + [name]
FROM t
WHERE T.id = T1.id
FOR XML PATH('')
), 1, 1, ''
)
) AS [name]
FROM T1
GROUP BY id
--
可運用 FOR XML PATH('') 來處理
SELECT T1.id, ( STUFF( (
SELECT ',' + [name]
FROM t
WHERE T.id = T1.id
FOR XML PATH('')
), 1, 1, ''
)
) AS [name]
FROM T1
GROUP BY id
多筆記錄合併成一筆
參考引用
--
***這個方法是採用迴圈方式去處理
假設TableA為 tableA(food,price)，裡面有資料如下：
food | price
西瓜 20
蘋果 50
香蕉 20
鳳梨 30
接下來想把相同金額的水果進行分類，想要的格式如下：
(用逗號分隔水果的話，輸出成csv格式就會自動再分格成不同欄位唷！)
#tempA
price | food
20 西瓜,香蕉
30 鳳梨
50 蘋果
指令：
--先將
select distinct price , cast(' ' as varchar(max)) into #tempA from tableA
--宣告游標
DECLARE food_cursor CURSOR for select * from #tempA
OPEN food_cursor
--宣告變數
DECLARE @result varchar(200) --進行字串串接使用
DECLARE @food varchar(50)
DECLARE @price varchar(50)
DECLARE @cmd varchar(200) --debug時，輸出select指令
--將游標指向下一筆紀錄，擷取值設進@food以及@price
FETCH NEXT FROM tables_cursor INTO @food,@price
--開始跑迴圈
WHILE (@@FETCH_STATUS <> -1)
BEGIN
--set @cmd = 'select food,@result='+@result+ ',price from (select food from #tempA where price=' + @price+')Duration'
set @result=''
select @result=@result+','+food from (select food from #tempA where price=@price)Duration
--PRINT 'Contact Name after: ' + @food + '_' + @price + '_' + @result+'---'+@cmd
update #tempA set result=rtrim(@result) where price=@price
FETCH NEXT FROM food_cursor INTO @food,@price
end
CLOSE food_cursor
DEALLOCATE food_cursor
--
***這個方法是採用迴圈方式去處理
假設TableA為 tableA(food,price)，裡面有資料如下：
food | price
西瓜 20
蘋果 50
香蕉 20
鳳梨 30
接下來想把相同金額的水果進行分類，想要的格式如下：
(用逗號分隔水果的話，輸出成csv格式就會自動再分格成不同欄位唷！)
#tempA
price | food
20 西瓜,香蕉
30 鳳梨
50 蘋果
指令：
--先將
select distinct price , cast(' ' as varchar(max)) into #tempA from tableA
--宣告游標
DECLARE food_cursor CURSOR for select * from #tempA
OPEN food_cursor
--宣告變數
DECLARE @result varchar(200) --進行字串串接使用
DECLARE @food varchar(50)
DECLARE @price varchar(50)
DECLARE @cmd varchar(200) --debug時，輸出select指令
--將游標指向下一筆紀錄，擷取值設進@food以及@price
FETCH NEXT FROM tables_cursor INTO @food,@price
--開始跑迴圈
WHILE (@@FETCH_STATUS <> -1)
BEGIN
--set @cmd = 'select food,@result='+@result+ ',price from (select food from #tempA where price=' + @price+')Duration'
set @result=''
select @result=@result+','+food from (select food from #tempA where price=@price)Duration
--PRINT 'Contact Name after: ' + @food + '_' + @price + '_' + @result+'---'+@cmd
update #tempA set result=rtrim(@result) where price=@price
FETCH NEXT FROM food_cursor INTO @food,@price
end
CLOSE food_cursor
DEALLOCATE food_cursor
T-SQL 使用純量變數 求當月有幾天
CREATE FUNCTION dbo.fn_dayofmonth (@dt datetime)
RETURNS int AS
BEGIN
declare @nextmonth datetime
set @nextmonth=dateadd(m,1,@dt)
return(day(dateadd(day,-day(@nextmonth),@nextmonth)))
END
取得資料庫中所有的資料表名稱 + 取得某一資料表的所有欄位名稱
引用來源
--
最近在寫程式時有用到，主要是因為對方的資料庫不給還原、附加，只他X的給你個SQL 語法介面，要麻你就執行SQL插入，要麻你就等對方幫你匯入，
不過對方說要排隊，所以乾脆自己些產生資料表資料插入語法，引此找了一些特殊的SQL語法：
1.取得資料庫表單數量
select count(*) as totaltablenumber from sysobjects where xtype = 'U';
2.取得資料表名稱(tablename)及欄位數量(columnnumber)
select name as tablename, info as columnnumber from sysobjects where xtype = 'U';
3.取得某一資料表內所有欄位名稱
select b.name from sysobjects as a, syscolumns as b where a.xtype = 'U' and a.id = b.id and a.name='資料表單名稱';
3.1 取得某一資料表內所有欄位名稱
EXEC sp_columns 表單名稱
4.另外3的話不會照欄位順序排，請加入： order by b.colorder
5.取得資料庫所有資料表名稱
select * from sysobjects where xtype = 'U'
以上為SQL 2000 OK 2005請使用下列語法
6.在SQL SERVER 2005抓取資料庫的SQL語法
select name from master.dbo.sysdatabases
7.抓取資料表的SQL語法
select Table_name from INFORMATION_SCHEMA.TABLES order by Table_name
8.抓取欄位的SQL語法
select column_name from INFORMATION_SCHEMA.COLUMNS where table_name='TableName'
--
最近在寫程式時有用到，主要是因為對方的資料庫不給還原、附加，只他X的給你個SQL 語法介面，要麻你就執行SQL插入，要麻你就等對方幫你匯入，
不過對方說要排隊，所以乾脆自己些產生資料表資料插入語法，引此找了一些特殊的SQL語法：
1.取得資料庫表單數量
select count(*) as totaltablenumber from sysobjects where xtype = 'U';
2.取得資料表名稱(tablename)及欄位數量(columnnumber)
select name as tablename, info as columnnumber from sysobjects where xtype = 'U';
3.取得某一資料表內所有欄位名稱
select b.name from sysobjects as a, syscolumns as b where a.xtype = 'U' and a.id = b.id and a.name='資料表單名稱';
3.1 取得某一資料表內所有欄位名稱
EXEC sp_columns 表單名稱
4.另外3的話不會照欄位順序排，請加入： order by b.colorder
5.取得資料庫所有資料表名稱
select * from sysobjects where xtype = 'U'
以上為SQL 2000 OK 2005請使用下列語法
6.在SQL SERVER 2005抓取資料庫的SQL語法
select name from master.dbo.sysdatabases
7.抓取資料表的SQL語法
select Table_name from INFORMATION_SCHEMA.TABLES order by Table_name
8.抓取欄位的SQL語法
select column_name from INFORMATION_SCHEMA.COLUMNS where table_name='TableName'
2013年6月4日 星期二
2013年6月3日 星期一
ToolStripDropDownItem 用法
MSDN:ToolStripDropDownItem.DropDownItems Property
參考引用:Creating a Event handle for a DropDownItem in a MenuStrip
--
Private Sub managerToolBar()
Dim food As ToolStripMenuItem = New ToolStripMenuItem
Dim addToolStripItem As ToolStripMenuItem = New ToolStripMenuItem("&add")
AddHandler addToolStripItem.Click, AddressOf Me.addToolStripItem_Click
food.DropDownItems.Add(addToolStripItem)
Me.menuStrip1.Items.Add(food)
End Sub
Private Sub addToolStripItem_Click(ByVal sender As Object, ByVal e As EventArgs)
MessageBox.Show("add")
End Sub
參考引用:Creating a Event handle for a DropDownItem in a MenuStrip
--
Private Sub managerToolBar()
Dim food As ToolStripMenuItem = New ToolStripMenuItem
Dim addToolStripItem As ToolStripMenuItem = New ToolStripMenuItem("&add")
AddHandler addToolStripItem.Click, AddressOf Me.addToolStripItem_Click
food.DropDownItems.Add(addToolStripItem)
Me.menuStrip1.Items.Add(food)
End Sub
Private Sub addToolStripItem_Click(ByVal sender As Object, ByVal e As EventArgs)
MessageBox.Show("add")
End Sub
訂閱：
文章 (Atom)