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]";
 

亞魚野紅+亞魚全紅(20130609)

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

aspfree

aspfree
---
有 asp / asp.net / vbnet / .net 等程式開發的文章

檔案.印表機分享 port

共分: tcp 139,445 , udp 137,138
若不知道,可以查本身WIN OS 的記錄 PORT

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

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)

Data Types And Access Specifiers In Visual Basic .NET

參考引用:Data Types And Access Specifiers In Visual Basic .NET