Handle Database Null Values in SQL Server Select Query

Database Null Values in SQL Server Select Query


Syntex :
isnull( dbcolumnName , '' )


Query :
Select isnull( Countryid , '' ) From tluCountry

زندگی کو ضروریات

اپنی زندگی کو ضروریات تک محدود رکھو، خواہشات سے پرہیز کرو کیونکہ ضروریات فقیر کی بھی پوری ہوتی ہیں اور خواہشات بادشاہ کی بھی ادھوری رہ جاتی ہیں

Convert int to string in a sql Query

Make your columns' data type match to use UNION (ALL).


CAST(ItemId as VARCHAR(50)) as col1
Or
CONVERT(VARCHAR(50),ItemId) as colI1

Fill Dataset with StoredProcedure ASP.NET C#

GridView DataBind with Dataset

GridView Fill Dataset with StoredProcedure ASP.NET C#

===========================================================

DataSet MyQueryMethod(string sqlQueryString)

{

DataSet ds = new DataSet();

System.Data.SqlClient.

SqlConnection sqlConnection = new System.Data.SqlClient. SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString" ].ConnectionString);

System.Data.SqlClient.

SqlDataAdapter dataAdapter = new System.Data.SqlClient. SqlDataAdapter();

System.Data.SqlClient.

SqlCommand select = new System.Data.SqlClient. SqlCommand(sqlQueryString);

select.CommandType =

CommandType.StoredProcedure;

select.Connection = sqlConnection;

select.Parameters.Add(

"@startdate", SqlDbType.DateTime).Value = TextBox1.Text;

select.Parameters.AddWithValue(

"@enddate", SqlDbType.DateTime).Value = TextBox2.Text ;

dataAdapter.SelectCommand = select;

dataAdapter.Fill(ds);

return ds;

}

===========================================

on Load event for call the function

GridView1.DataSource = MyQueryMethod("BrowseUserCharge");

GridView1.DataBind();

Handle Database Null Values in VB.NET/ASP.NET

Handle Database Null Values in VB.NET/ASP.NET


Public Function IsDBNull(ByVal dbvalue) As Boolean
Return dbvalue Is DBNull.Value
End Function

Public Function FixNull(ByVal dbvalue) As String
If dbvalue Is DBNull.Value Then
Return ""
Else
'NOTE: This will cast value to string if
'it isn't a string.

Return dbvalue.ToString
End If
End Function




happy programming
Asma Qureshi

ADO.NET Architecture

ADO.NET is a data access technology from Microsoft .Net Framework , which provides communication between relational and non-relational systems through a common set of components . ADO.NET was built for a disconnected architecture , so it enables truly disconnected data access and data manipulation through its Dataset Object, which is completely independent from the Data Source.
ado.net-architecture.JPG

The two key components of ADO.NET are Data Providers and DataSet . The .Net Framework includes mainly three Data Providers for ADO.NET. The Microsoft SQL Server , OLEDB and ODBC are the main Data Providers in the .Net Framework. In the following pages you can see each component of ADO.NET in details with source code.




The following diagrams show the basic ADO.NET component architecture. The diagram on the left shows the Data Provider classes:

Command, Connection, DataAdapter, and DataReader. These classes provide bridges between .NET applications and data sources. The diagram on the right shows the DataSet class hierarchy. These classes create the .NET application's representation of a set of data.

"Unable to start debugging on the web server. You do not have permission to debug the application. The URL for this project is in the Internet zone."

Unable to start debugging on the web server VS.NET 2003


You may receive the error "Error while trying to run project: Unable to start debugging on the web server.
You do not have permission to debug the application. The URL for this project is in the Internet zone.
Click Help for more information."

This is one of the various errors that appear when you are debugging and may haunt you until you
do the following fix:-

1. In the Internet Explorer, "Tools" Menu, select "Internet Options".

2. Switch to "Security" Tab.

3. Click on "Internet" (The Globe Icon. Its actually the default selected).

4. Click on "Custom Level" in the bottom.

5. Scroll down to find the "User Authentication" section.

6. Select "Automatic logon with current username and password".

7. Click "Ok" twice to exit.

This should solve the issue.

Please note that this resolution is specific only if you get the exact error message as
mentioned in the beginning of this article.
There are various reasons you get the "Unable to start debugging on the webserver".
For a complete list of solutions, please check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vsdebug.asp


Cheers and Happy Programming !!!

How to merge tables in a Dataset - Sql Server

DataSet ....

The DataSet contains copy of the data we requested through the SQL statement. The SqlDataAdapter object allows us to populate DataTable in a DataSet. We can use Fill method in the SqlDataAdapter for populating data in a Dataset. We can populate Dataset with more than one table at a time using SqlDataAdapter Object. The DataTableCollection contains zero or more DataTable objects.

In some situation we want to combine the result of multiple SQL query as a single result set. In that case we can use the Dataset's Merge method for doing this. The tables involved in the merge should be identical, that is the columns are similar data types .


Private Sub GetUsersList()

Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim firstSql As String
Dim secondSql As String
Dim i As Integer
connetionString = "Data Source=.;Initial Catalog=Nafex;User ID=sa;Password=111"
firstSql = "Select * from tblUser"
secondSql = "Select * from tluVrType"
connection = New SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(firstSql, connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Table(0)")
adapter.SelectCommand.CommandText = secondSql
adapter.Fill(ds, "Table(1)")
adapter.Dispose()
command.Dispose()
connection.Close()

ds.Tables(0).Merge(ds.Tables(1))
dt = ds.Tables(0)

For i = 0 To dt.Rows.Count - 1
MsgBox(dt.Rows(i).Item(0) & " -- " & dt.Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try

End Sub

String Format for Double ( 0.00)

String Format for Double [C#]

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.

Digits after decimal point

This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

[C#]

// just two decimal places
String.Format("{0:0.00}", 123.4567);      // "123.46"
String.Format("{0:0.00}", 123.4);         // "123.40"
String.Format("{0:0.00}", 123.0);         // "123.00"

Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.

[C#]

// max. two decimal places
String.Format("{0:0.##}", 123.4567);      // "123.46"
String.Format("{0:0.##}", 123.4);         // "123.4"
String.Format("{0:0.##}", 123.0);         // "123"

Digits before decimal point

If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.

[C#]

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567);      // "123.5"
String.Format("{0:00.0}", 23.4567);       // "23.5"
String.Format("{0:00.0}", 3.4567);        // "03.5"
String.Format("{0:00.0}", -3.4567);       // "-03.5"

Thousands separator

To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.

[C#]

String.Format("{0:0,0.0}", 12345.67);     // "12,345.7"
String.Format("{0:0,0}", 12345.67);       // "12,346"

Zero

Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).

Following code shows how can be formatted a zero (of double type).

[C#]

String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0);            // "0"
String.Format("{0:#.0}", 0.0);            // ".0"
String.Format("{0:#.#}", 0.0);            // ""

Align numbers with spaces

To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.

[C#]

String.Format("{0,10:0.0}", 123.4567);    // "     123.5"
String.Format("{0,-10:0.0}", 123.4567);   // "123.5     "
String.Format("{0,10:0.0}", -123.4567);   // "    -123.5"
String.Format("{0,-10:0.0}", -123.4567);  // "-123.5    "

Custom formatting for negative numbers and zero

If you need to use custom format for negative float numbers or zero, use semicolon separator „;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.

[C#]

String.Format("{0:0.00;minus 0.00;zero}", 123.4567);   // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567);  // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0);        // "zero"

Some funny examples

As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.

[C#]

String.Format("{0:my number is 0.0}", 12.3);   // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3);          // "12aaa.bbb3"

OFAC Block Person List

Office of Foreign Assets Control ("OFAC")

The Office of Foreign Assets Control ("OFAC") of the US Department of the Treasury administers and enforces economic and trade sanctions based on US foreign policy and national security goals against targeted foreign countries and regimes, terrorists, international narcotics traffickers, those engaged in activities related to the proliferation of weapons of mass destruction, and other threats to the national security, foreign policy or economy of the United States. OFAC acts under Presidential national emergency powers, as well as authority granted by specific legislation, to impose controls on transactions and freeze assets under US jurisdiction. Many of the sanctions are based on United Nations and other international mandates, are multilateral in scope, and involve close cooperation with allied governments.


http://www.treas.gov/offices/enforcement/ofac/sdn/index.shtml


OFAC web services into our project (OFAC with .NET VB/V#)

There are many web services online live sites who provided the OFAC web services.
But this OFAC web services is not free only for Free try Testing after that Buy the OFAC web services and implement into project.

I Implement one of the OFAC web services here are the Steps for Implementation OFAC.
this is one of the best site for OFAC web services i m useing this .






https://www.xignite.com/Login.aspx?ReturnUrl=/MyAccount/MyAccountHome.aspx&mesg=yes


Step 1:
Create Registration

Step 1:
Login into your Account.

Step 3:
Create VB.NET Project (Web Project)

Step 4:
Header Code for OFAC Web services Code into your Project.

Dim objSearchResults As New com.xignite.www.SearchResults

'RemoteQuotes is the web reference to the XigniteQuotes service
'objRemoteQuote is an instance of the service proxy
Dim objRemoteQuote As New com.xignite.www.Header
'assign your username / password , same as you Registration on xignite.com

objRemoteQuote.Password = "*******
objRemoteQuote.Username = "*****@****.com"

'assign the header to the service proxy
objOFACService.HeaderValue = objRemoteQuote
'----------------------------------------Searching OFAC CODE

objOFACService.SearchByAddressAsync(com.xignite.www.OFACTypes.All , "Person Name","Address" , "City" ,"Country" , com.xignite.www.SearchTypes.Contains )
Dim ct, adct As Integer
For ct = 0 To result.Count
address = result.Matches(ct).Addresses.ToString()
If result.Matches(ct).Addresses.Length > 0 Then
For adct = 0 To result.Matches(ct).Addresses.Length
txtaddress.Text = result.Matches(ct).Addresses(adct).Country.ToString()
OfacChk = True
OfacResponsIDnumb = result.Matches(ct).Id
Next

End If

Next



========================================
OFAC Person match in Live OFAC List.
by Asma Qureshi
Project manager
Dubai UAE