Select a random records SQL SERVER, Oracle, MySql

Database > Random Records

--Select random rows with Microsoft SQL Server
--NEWID Creates a unique value of type uniqueidentifier.

SELECT * FROM table ORDER BY NEWID()

--Select random rows with MySQL
SELECT * FROM table ORDER BY RAND()

--Select random records with Oracle:
SELECT * FROM table ORDER BY dbms_random.value

How to extract words out from a string c#

C# > String > Spilt

 
Split returns a string array that contains the substrings in this instance that are delimited by elements of a specified Unicode character array. 

Example
 

string _strInput = "aaa bbb ccc ddd";

string[] words = _strInput.Split ( new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries );

foreach(string s in words)

{

  // value of s: aaa, bbb, ccc, ddd

}

Null data in Oracle using the ORDER BY clause

From:
Control null data in Oracle using the ORDER BY clause
Date: June 19th, 2007
Author: Bob Watkins

NULL data, which is sometimes called “absent” data, can be difficult to work with in relational databases. When a query contains the ORDER BY clause to sort the output, NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC). In effect, NULL is treated as a very large value by Oracle.

This can create reports that are difficult to read. Consider a simple query in Oracle’s HR sample schema. Let’s say you want to list the names and commission percents of all employees in descending order, including those who get no commission (commission_pct is NULL). The following simple query does this:

SELECT employee_id, last_name, first_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

The problem is that all employees with no commission come out on top. You have to read through them all to find those who actually have a commission.

Starting with Oracle 8i, there is a little known syntax in the ORDER BY clause that fixes this. All you have to do is change the last line above to the following:

ORDER BY commission_pct DESC NULLS LAST;

The null rows will sort to the bottom after all the rows that contain commission data. You can also use NULLS FIRST when you’re sorting in ascending order, and you want the NULL rows to appear at the top of the report.

If you’re still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL). Use something like the following in your ORDER BY:

ORDER BY NVL(commission_pct, -1);

This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output. You won’t see the (-1) values because the query only sorts by the NVL function — it doesn’t display it in the SELECT lis

Oracle/PLSQL: Instr Function

Oracle/PLSQL > Functions > Instr

Instr function returns the location of a substring in a string.

The syntax for the instr Oracle function is:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

Example

select INSTR('test', 'e') from dual

result:
2

Oracle/PLSQL: Oracle System Tables

Oracle/PLSQL > Oracle System Tables

--------------------------------------------------------------------------------

Below is an alphabetical listing of the Oracle system tables that are commonly used.

System Table Description

ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

Oracle FOR LOOP

The syntax for the FOR Loop in Oracle is:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;

Get current day , month, year Oracle

Oracle > Date Time > Get current day , month, year Oracle

select to_number(to_char(sysdate,'dd')) day,to_number(to_char(sysdate,'mm')) month,to_number(to_char(sysdate,'yyyy')) year from dual;

SQL COUNT

SQL Server > Built-in Functions > COUNT

COUNT returns the number of rows that matches a specified criteria.
The NULL values will not be count.

Example

SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) FROM table_name
SELECT COUNT(DISTINCT column_name) FROM table_name

How to pass Database logon info to a Crystal Report at runtime in C#

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

private CrystalReport1 crReportDocument = new
CrystalReport1 ();
private Database crDatabase;
private Tables crTables;
private Table crTable;
private TableLogOnInfo crTableLogOnInfo;
private ConnectionInfo crConnectionInfo = new
ConnectionInfo ();

//Setup the connection information structure
//to log on to the data source for the report.
// If using ODBC, this should be the DSN. If using
// OLEDB, etc, this should be the physical server name


crConnectionInfo.ServerName = "DSN or
Server Name";

// If you are connecting to Oracle there is no
// DatabaseName. Use an empty
// string i.e. crConnectionInfo.DatabaseName = "";

crConnectionInfo.DatabaseName = "DatabaseName";
crConnectionInfo.UserID = "Your UserID";
crConnectionInfo.Password = "Your Password";

// This code works for both user tables and stored
procedures

//Get the table information from the report
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;

//Loop through all tables in the report and apply the
//connection information for each table.
for (int i = 0; i < crTables.Count; i++)
{
crTable = crTables ;
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo =
crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);

//If your DatabaseName is changing at runtime, specify
//the table location. For example, when you are
reporting
//off of a Northwind database on SQL server
//you should have the following line of code:

crTable.Location = crConnectionInfo.DatabaseName + ".dbo." +
crTable.Location.Substring(crTable.Location.LastIndexOf
(".") + 1)
}

//Set the viewer to the report object to
//be previewed.

crystalReportViewer1.ReportSource =
crReportDocument;

Default web browser Internet Explorer

Tools -> Internet Options ->Programs


Tools -> Internet

Added Microsoft Access as linked server SQL Server

SQL Server > Linked Server > Add

sp_addlinkedserver

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources

sp_addlinkedsrvlogin

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

Example: Add Microsoft Access as linked server SQL Server
 

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'ACCES',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\data\DB1.mdb'
GO

-- set password

EXEC sp_addlinkedsrvlogin 'ACCES', false, 'sa', 'Admin', NULL

First, last day of the month Oracle

Oracle > Date & Time > First day and last day of the month Oracle

SELECT (trunc(sysdate, 'mm')) first_day FROM dual;

SELECT last_day(trunc(sysdate)) last_date FROM dual;

--result

FIRST_DAY
-----------
12/1/2010

LAST_DATE
-----------
12/31/2010

Duplicate rows SQL Server

SQL Server > Scripts > Duplicate rows SQL Server

Here is a script to find number of duplicate rows in SQL Server

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL

DROP TABLE tbl1;
GO
 

CREATE TABLE tbl1

(
       id     int ,
       name   varchar(50)
);
GO
 

INSERT INTO tbl1 values  (1,'john'), (2,'dan') , (1,'john')

GO
 

SELECT

  id,
  count(name) 'Number of duplicate rows'
FROM
  tbl1
GROUP BY
  id
HAVING
  count(name) > 1
GO

Result:
id Number of duplicate rows
1 2

 

send fax C#

Send fax C# using using FAXCOMLib;

public void SendFax(string DocumentName, string FileName, string RecipientName, string FaxNumber)
 {
         FAXCOMLib.FaxServer faxServer = new FAXCOMLib.FaxServerClass();
         faxServer.Connect(Environment.MachineName);
         FAXCOMLib.FaxDoc faxDoc = (FAXCOMLib.FaxDoc)faxServer.CreateDocument(FileName);
         faxDoc.RecipientName = RecipientName;
         faxDoc.FaxNumber = FaxNumber;
         faxDoc.Tsid = "Fax";
         faxDoc.DisplayName = DocumentName;
         int Response = faxDoc.Send();
          faxServer.Disconnect();
}

 

 

Get windows temporary path c#

C# > Files > GetTempPath

GetTempPath retrieves the path of the directory designated for temporary files.

Order to check for the existence of environment variables:

1. TMP environment variable.
2. TEMP environment variable.
3. USERPROFILE environment variable.
4. Windows directory.

Example:

String tempPath = System.IO.Path.GetTempPath();

FRM-40654: Record has been updated by another user

Oracle > dml_data_target_name

FRM-40654: Record has been updated by another user

The case that I had was i changed the query of 1 database block dynamically use set_block_property(’BLOCK1′,Query_Data_Source_Name,’TABLE_X’). This BLOCK1 previously assign to TABLE_A. TABLE_X and TABLE_A has same fields structure.


FRM-40654

As summary, my form blocks condition were like this :
BLOCK1 = TABLE_A

Solution:

use set_block_property on pre_query trigger to change the query_data_source_name to TABLE_X.

Everything looks fine when i load the form, but when i tried to change the field value, it gave me this form error FRM-40654. I search around in Google and only can found several peoples face the same frm-40654 but in different situation.


set_block_property(’BLOCK1′,dml_data_target_name,’TABLE_X’)

Rename Microsoft Access Table

VB 6 > DAO > Rename table

(Used DAO 3.6 as reference.)

Public Sub rentbl()
  Dim dbSS As Database
  Dim strDbName As String
  strDbName = "c:\temp\test.mdb"
  Set dbSS = OpenDatabase(strDbName)
  dbSS.CreateTableDef
  dbSS.TableDefs("R2").Name = "Org_R2"
  dbSS.Close
  Set dbSS = Nothing
End Sub

Introduction to the C#

Home page
“Introduction to C# Programming Language”,
created by Rich Tebb from Content Master Ltd Company and available at the MSDN web site (Microsoft Developer Network), is an easy and fun way to get started with C# language. Basically, this introductory lesson is a combination of written text and video, and covers the initial concepts about developing applications on C#. The approach of the lesson is to present computer programming as a fun activity, in which you can find satisfaction.
You can either take this lesson on-line from the MSDN web page, or you can download it to your computer, so you don’t need to be connected during your study time.
When you download and install this lesson, one new folder and one new subfolder will be created. The main folder, named “Introduction to CSharp Programming Language” will be created with the following three files:
1. “Introduction to CSharp.wmv”, a 23 minute Windows Media Video file containing the lesson in video format;
2. “Introduction to CSharp.doc”, a 26-page Microsoft Word document with the written lesson;
3. “EULA.ftr”, a Rich Text Format document containing license information;
The subfolder named “My First Application CSharp”, will contain some auxiliary files needed to execute the practice proposed by the lesson.
You will find four lessons within the document:
1. Lesson 1: Your First ‘Hello World’ Program: initiation program to write a message on the screen.
2. Lesson 2: Using Methods and Variables.
3. Lesson 3: Controlling Program Flow: to learn how to control the sequence of tasks performed by the program.
4. Lesson 4: Creating Your Own Classes: to learn how to work with this type of code containers.
The text is accompanied by images showing the programming process and written code. This lesson is the best way to begin studying C# language, and also the less time consuming one. After taking this lesson, you can go on and take the “Absolute Beginner’s Series C# Series” course, a collection of fifteen lessons available also at MSDN. The course provides a deeper introduction to C# language, and is oriented to people interested in designing and building Windows based applications. If you’re reading this, it’s pretty sure that you’re one of those people.
Back to the lesson description, we should mention that it is available for free for anyone who’s eager to learn but there is one prerequisite: Visual C# 2005 Express Edition, another lesson available at MSDN web page. Besides, this lesson assumes that you have basic knowledge about using a computer, such as starting a program and browse your computer with Windows Explorer.

Remove list items Oracle Forms

Oracle > Forms > List > Remove

How to remove items from  Oracle Forms list?

"Ctrl + Shift + >" - add list element.
"Ctrl + Shift + <" - remove list element.

Connection Strings

Database > Connection Strings


.NET Framework Data Provider for SQL Server
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Microsoft Jet OLE DB 4.0
Microsoft Access
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;Jet OLEDB:Database Password=password;

Microsoft Excel
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Text File
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";


MySQL Connector/Net
Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;