ASP.NET FAQ - ADO.NET

The following sample is a good example of parameterized queries: How to insert data in database using Textboxes?

Refer When I try to enter null value for DataTime in Database I get error message ‘String was not recognized as a valid DateTime’ or ‘Value of type ’System.DBNull’ cannot be converted to ’String’’

Refer Store Procedure Sample

‘Select distinct <field> from <table>’.This SELECT statement is used to filter out duplicate results from a query’s output. But sometimes the requirement is to use a Stored Procedure which returns the entire data and display the manipulated distinct data in web server control.
To do this use SortedList

For using SortedList import namespace System.Collections

Stored Procedure

Create Procedure GetSuppliers AS

SELECT  * FROM Suppliers
GO

<asp:DataList id='DataList1' runat='server'>
	<ItemTemplate>
		<%#Container.DataItem%>
	</ItemTemplate>
</asp:DataList>

VB.NET

Dim ds As New DataSet
Dim myda As SqlDataAdapter = New SqlDataAdapter('GetSuppliers', 'server=localhost;database=Northwind;uid=sa;pwd=;')
myda.SelectCommand.CommandType = CommandType.StoredProcedure
myda.Fill(ds, 'Table')
Dim slist As SortedList = New SortedList
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
	If Not slist.ContainsValue(dr('Country')) Then
	                slist.Add(dr('Supplierid'), dr('Country'))
	End If
Next
DataList1.DataSource = slist.GetValueList
DataList1.DataBind()
’In case of Dropdownlist
’DropDownList1.DataSource = slist.GetValueList
’DropDownList1.DataBind()

C#

DataSet ds = new  DataSet();
SqlDataAdapter myda    = new SqlDataAdapter('GetSuppliers', 'server=localhost;database=Northwind;uid=sa;pwd=;');
myda.SelectCommand.CommandType = CommandType.StoredProcedure;
myda.Fill(ds, 'Table');
SortedList slist    = new SortedList();
 
foreach( DataRow dr in ds.Tables[0].Rows)
{
	if (! slist.ContainsValue(dr['Country']))  
	{
		slist.Add(dr['Supplierid'].ToString (), dr['Country'].ToString ());
	}
} 
DataList1.DataSource = slist.GetValueList();
DataList1.DataBind();
//In case of DropDownList
//DropDownList1.DataSource = slist.GetValueList
//DropDownList1.DataBind()

<asp:Button id='btnAdd' style='Z-INDEX: 101; LEFT: 104px; POSITION: absolute; TOP: 168px' 
	runat='server' Text='Add Record'></asp:Button>
<asp:TextBox id='txtFirstName' style='Z-INDEX: 102; LEFT: 168px; POSITION: absolute; TOP: 40px'
	runat='server'></asp:TextBox>
<asp:TextBox id='txtLastName' style='Z-INDEX: 103; LEFT: 168px; POSITION: absolute; TOP: 80px'
	runat='server'></asp:TextBox>
<asp:TextBox id='txtDate' style='Z-INDEX: 104; LEFT: 168px; POSITION: absolute; TOP: 120px' runat='server'></asp:TextBox>
<asp:Label id='Label1' style='Z-INDEX: 105; LEFT: 56px; POSITION: absolute; TOP: 240px' runat='server'></asp:Label>

On Page_Load

VB.NET

if not Page.IsPostBack then
’....
end if

C#

if(!Page.IsPostBack )
{
//...
}

Use namespaces System.Data.SqlClient, System.Data.SqlTypes
On Button Click
VB.NET

Dim sqlStmt As String
Dim conString As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
	sqlStmt = 'insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date)  '
	conString = 'server=localhost;database=Northwind;uid=sa;pwd=;'
	cn = New SqlConnection(conString)
	cmd = New SqlCommand(sqlStmt, cn)

	cmd.Parameters.Add(New SqlParameter('@FirstName', SqlDbType.NVarChar, 11))
	cmd.Parameters.Add(New SqlParameter('@LastName', SqlDbType.NVarChar, 40))
	cmd.Parameters.Add(New SqlParameter('@Date', SqlDbType.DateTime))

	sqldatenull = SqlDateTime.Null

	cmd.Parameters('@FirstName').Value = txtFirstName.Text
	cmd.Parameters('@LastName').Value = txtLastName.Text
	If (txtDate.Text = '') Then
		cmd.Parameters('@Date').Value = sqldatenull
		’cmd.Parameters('@Date').Value = DBNull.Value
	Else
		cmd.Parameters('@Date').Value = DateTime.Parse(txtDate.Text)
	End If
	cn.Open()
	cmd.ExecuteNonQuery()
	Label1.Text = 'Record Inserted Succesfully'

Catch ex As Exception
	Label1.Text = ex.Message
Finally
	cn.Close()
End Try

On Button Click
C#

string  sqlStmt ; 
string  conString ; 
SqlConnection cn =null; 
SqlCommand cmd =null; 
SqlDateTime sqldatenull ; 
try
{
	sqlStmt = 'insert into Employees (FirstName,LastName,HireDate) Values (@FirstName,@LastName,@Date)  ';
	conString = 'server=localhost;database=Northwind;uid=sa;pwd=;';
	cn = new SqlConnection(conString);
	cmd = new SqlCommand(sqlStmt, cn);
	cmd.Parameters.Add(new SqlParameter('@FirstName', SqlDbType.NVarChar, 11));
	cmd.Parameters.Add(new SqlParameter('@LastName', SqlDbType.NVarChar, 40));
	cmd.Parameters.Add(new SqlParameter('@Date', SqlDbType.DateTime));
	sqldatenull = SqlDateTime.Null;
	cmd.Parameters['@FirstName'].Value = txtFirstName.Text;
	cmd.Parameters['@LastName'].Value = txtLastName.Text;
	if (txtDate.Text == '') 
	{
		cmd.Parameters ['@Date'].Value =sqldatenull ;
		//cmd.Parameters['@Date'].Value = DBNull.Value;
	}  
	else
	{
		cmd.Parameters['@Date'].Value = DateTime.Parse(txtDate.Text);
	}
	cn.Open();
	cmd.ExecuteNonQuery();
	Label1.Text = 'Record Inserted Succesfully';
}
catch (Exception ex)
{	
	Label1.Text = ex.Message;
}
finally
{
	cn.Close();
}

VB.NET

Protected function TruncateData( Byval strNotes as string)
If strNotes.Length > 20 then
	Return strNotes.Substring(0,20) + '...'
Else
	return strnotes
End function

C#

protected string TruncateData( string strNotes  )
{
if (strNotes.Length > 20)
{
	return strNotes.Substring(0,20) + '...';
}
else
{
	return strNotes;
}
}

VB.NET

’Using DataReader
While dr.Read()
  	 Response.Write((dr('ProductName') + ' '))
	 Response.Write((Convert.ToInt16(dr('discontinued')) + ' 
'))
End While

C#

//Using DataReader
while (dr.Read ())
{
	Response.Write (dr['ProductName'] + ' ');
	Response.Write (Convert.ToInt16 ( dr['discontinued']) + ' 
');
}

The cause of this error may be that you have declared a dataset but have not written the following statement

VB.NET

ds= new DataSet()

C#

ds= new DataSet();

This is caused if you are attempting to use the same DataReader more than once in your code without closing the previous Datareader.
Or you might have a DataReader open on the same connection as the DataAdapter/Command uses. So it is not recommended to share a
connection between a DataReader and a DataAdapter/Command

• Either close the first connection or,
• if you must keep it open as you are executing another reader from within it, use another connection object.

This error is caused if there is no Connection object associated with the Command Object.
To resolve this

VB.NET

Dim cn as new SqlConnection('<your connection string>')
’Dim cn as new OleDbConnection('<your connection string>')
Dim cmd As new SqlCommand('Select * from Products', cn)
’Dim cmd As new OleDbCommand('Select * from Products', cn)

C#

SqlConnection  cn = new SqlConnection ('<your connection string>');
’OleDbConnection cn = new OleDbConnection ('<your connection string>');
SqlCommand cmd = new SqlCommand ('Select * from Products', cn);
’OleDbCommand cmd = new OleDbCommand ('Select * from Products', cn);

If you are using SqlConnection then the connection string should be as follows:

server=localhost;uid=sa;password=;database=northwind
i.e
server=<yourservername>;uid=<youruid>;password=<yourpassword>;database=<yourdbName>'

For SqlConnection we do not provide a Provider . Provider is used in cases where OleDbConnection is used.

Check if you have specified the connection string

VB.NET

dim cn as new SqlConnection('<your connection string>')
’dim cn as new OleDdConnection('<your connection string>')

C#

SqlConnection cn = new SqlConnection('<your connection string>');
OleDbConnection cn = new OleDbConnection('<your connection string>');

Save the data entered in the multiline textbox as

VB.NET

..
cmd.Parameters.Add(new SqlParameter('@Notes' ,SqlDbType.NVarChar ))
cmd.Parameters ('@Notes').Value ='<pre>' + TextBox1.Text   + '</pre>'
....
cn.Open()
cmd.ExecuteNonQuery()

C#

...
cmd.Parameters.Add(new SqlParameter('@Notes' ,SqlDbType.NVarChar ));
cmd.Parameters ['@Notes'].Value ='<pre>' + TextBox1.Text   + '</pre>';
....
cn.Open();
cmd.ExecuteNonQuery();

To retrieve the Data

VB.NET

Response.Write (ds.Tables(0).Rows(0)('Notes').ToString ())

C#

Response.Write (ds.Tables[0].Rows[0]['Notes'].ToString ());

To avoid having to explicitly close the connection associated with the command used to create either a SqlDataReader or and OleDbDataReader, pass the CommandBehavior.CloseConnection argument to the ExecuteReader method of the Connection. i.e VB.NET

dr= cmd.ExecuteReader(CommandBehavior.CloseConnection)

C#

dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);

The associated connection will be closed automatically when the Close method of the Datareader is called. This makes it all the more important to always remember to call Close on your datareaders.

VB.NET

if ds.Tables(0).Rows.Count=  0 then 
	’No record
else
	’Record Found
end if

C#

if (ds.Tables[0].Rows.Count == 0 )
{
	//No record
}
else
{
	//Record Found
}

VB.NET
Sample code 1

C#
Sample Code 2

If you are using the Framework 1.1 , use HasRows

For Framework < 1.1

VB.NET

Dim myconnection As SqlConnection
Dim mycmd As SqlCommand
Dim strSql As String
Dim myReader As SqlDataReader

myconnection = New SqlConnection('Server=localhost;uid=sa;password=;database=northwind;')
strSql = 'Select count(*) from employees;Select * from employees'
mycmd = New SqlCommand(strSql, myconnection)
myconnection.Open()
Dim count As Integer = CInt(mycmd.ExecuteScalar())
myReader = mycmd.ExecuteReader(CommandBehavior.CloseConnection)
If count = 0 Then
	Response.Write('No records found')
Else
           	myReader.NextResult()
            	While myReader.Read()
                	Response.Write(myReader('Employeeid').ToString() + '<BR>')
            	End While
End If

C#

SqlConnection myconnection ;
SqlCommand mycmd ; 
string strSql ;
SqlDataReader myReader ;

myconnection = new SqlConnection('Server=localhost;uid=sa;password=;database=northwind;');
strSql = 'Select count(*) from employees;Select * from employees';
mycmd = new SqlCommand(strSql, myconnection);
myconnection.Open();
int count=(int) mycmd.ExecuteScalar() ;
myReader = mycmd.ExecuteReader(CommandBehavior.CloseConnection);
if (count==0 )
{
	Response.Write('No records found');
}
else
{
	myReader.NextResult ();
	while(myReader.Read ())
	{
		Response.Write(myReader['Employeeid'].ToString () + '<br>');
	}
}

<?xml version='1.0' encoding='utf-8' ?> 
<products> 
	  <product prodId='product1-00' param1='11' /> 
	  <product prodId='product1-00' param1='12' /> 
	  <product prodId='product1-01' param1='13' /> 
	  <product prodId='product1-02' param1='14' /> 
	  <product prodId='product2-00' param1='21' param2='22' /> 
	  <product prodId='product2-00' param1='31' param2='32' /> 
	  <product prodId='product2-01' param1='41' param2='42' /> 
</products>

VB.NET

Dim ds As New DataSet
ds.ReadXml(Server.MapPath('data1.xml'))
Dim dv As New DataView
dv = ds.Tables(0).DefaultView
dv.RowFilter = 'prodId=’product2-00’'
Me.DataGrid1.DataSource = dv
Me.DataBind()

C#

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath('data1.xml'));
DataView dv = new DataView();
dv = ds.Tables[0].DefaultView;
dv.RowFilter = 'prodId=’product2-00’';
this.DataGrid1.DataSource = dv;
this.DataBind();

<asp:label id='Label2' style='Z-INDEX: 106; LEFT: 111px; POSITION: absolute; TOP: 83px' 
	runat='server'>Product ID</asp:label>
<asp:label id='Label1' style='Z-INDEX: 105; LEFT: 110px; POSITION: absolute; TOP: 43px' 
	runat='server'>Product Name</asp:label>
<asp:textbox id='txtProductName' style='Z-INDEX: 104; LEFT: 206px; POSITION: absolute; TOP: 83px'
	runat='server' OnDataBinding='txtDataBind'></asp:textbox>
<asp:textbox id='txtProductid' style='Z-INDEX: 103; LEFT: 204px; POSITION: absolute; TOP: 43px'
	runat='server'>
<asp:button id='btnPrevious' style='Z-INDEX: 102; LEFT: 137px; POSITION: absolute; TOP: 126px'
	runat='server' Text='Previous' OnClick ='PrevBtn'></asp:button>
<asp:button id='btnNext' style='Z-INDEX: 101; LEFT: 243px; POSITION: absolute; TOP: 126px' 
	runat='server' Text='Next' OnClick ='NextBtn'></asp:button>

VB.NET

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
	’Put user code to initialize the page here 
	Try 
		’Fill the DataSet 
		If Not Page.IsPostBack Then 
			ViewState('CurrentPos') = 0 
			Me.DataBind() 
		End If 
	Catch ex As Exception 
		Response.Write(ex.Message & ex.StackTrace) 
	End Try 
End Sub 

protected Sub NextBtn(ByVal sender As System.Object, ByVal e As System.EventArgs)  
Try 
	Dim CurrentPos As Integer = CType(ViewState('CurrentPos'), Integer) 
	CurrentPos += 1 
	If CurrentPos > ds.Tables(0).Rows.Count Then 
		CurrentPos -= 1 
	End If 
	ViewState('CurrentPos') = CurrentPos 
	Me.DataBind() 
Catch ex As Exception 
	Response.Write(ex.Message) 
End Try 
End Sub 

protected Sub PrevBtn(ByVal sender As System.Object, ByVal e As System.EventArgs)  
Try 
	Dim CurrentPos As Integer = CType(ViewState('CurrentPos'), Integer) 
	If CurrentPos > 0 Then 
		CurrentPos -= 1 
	End If 
	ViewState('CurrentPos') = CurrentPos 
	Me.DataBind() 
Catch ex As Exception 
	Response.Write(ex.Message) 
End Try 
End Sub 

protected Sub txtDataBind(ByVal sender As Object, ByVal e As System.EventArgs)  
Try 
	Dim CurrentPos As Integer = CType(ViewState('CurrentPos'), Integer) 
	ViewState('CurrentPos') = (CurrentPos) 
	txtProductid.Text = ds.Tables(0).Rows(CurrentPos).Item('productid') 
	txtProductName.Text = ds.Tables(0).Rows(CurrentPos).Item('productname') 
Catch ex As Exception 
   	Response.Write(ex.Message) 
End Try 
End Sub

C#

DataSet ds;
private void Page_Load(object sender, System.EventArgs e)
{
	// Put user code to initialize the page here
	//Fill the DataSet
	if (!Page.IsPostBack )
	{
		ViewState['CurrentPos'] = 0 ;
		this.DataBind() ;
	}
}
protected void PrevBtn(object sender, System.EventArgs e)
{
	try 
	{
		int CurrentPos    = (int)ViewState['CurrentPos'] ;
		if (CurrentPos > 0 )
		{
			CurrentPos -= 1 ;
		}
		ViewState['CurrentPos'] = CurrentPos ;
		this.DataBind() ;
	}
	catch (Exception ex)
	{
		Response.Write(ex.Message) ;
	}
}

protected void NextBtn(object sender, System.EventArgs e)
{
	try 
	{
		int CurrentPos    = (int)ViewState['CurrentPos'] ;
		CurrentPos += 1 ;
		if( CurrentPos > ds.Tables[0].Rows.Count)   
		{
			CurrentPos -= 1 ;
		} 
		ViewState['CurrentPos'] = CurrentPos ;
		this.DataBind() ;
	}
	catch (Exception ex)
	{
		Response.Write(ex.Message) ;
	}
}

protected void txtDataBind(Object sender ,  System.EventArgs e ) 
{
	try
	{
		int CurrentPos  = (int) ViewState['CurrentPos'];
		ViewState['CurrentPos'] =  CurrentPos ;
		txtProductid.Text = ds.Tables[0].Rows[CurrentPos]['productid'].ToString(); 
		txtProductName.Text = ds.Tables[0].Rows[CurrentPos]['productname'].ToString ();
	}
catch (Exception ex) 
	{
		Response.Write(ex.Message) ;
	}
}

VB.NET

’Fill Dataset
Dim dc As DataColumn
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
	For Each dc In ds.Tables(0).Columns
		Response.Write(dr(dc.ColumnName).ToString())
	Next
Next

C#

//Fill the DataSet
foreach  (DataRow dr in ds.Tables[0].Rows)
{
	foreach( DataColumn dc in ds.Tables[0].Columns)
	{
		Response.Write(dr[dc.ColumnName].ToString());
	}
}

VB.NET

Dim strConn As String = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Server.MapPath('nwind.mdb') & ';'
Dim strsql As String = 'Select * from  Customers'
Dim cn As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand(strsql, cn)
cn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = dr
DataGrid1.DataBind()

C#

string  strConn  = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Server.MapPath('nwind.mdb') + ';';
string strsql  = 'Select * from  Customers';
OleDbConnection cn = new OleDbConnection(strConn);
OleDbCommand  cmd = new OleDbCommand (strsql, cn);
cn.Open ();
OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection );
DataGrid1.DataSource = dr;
DataGrid1.DataBind();

Use namespace System.Data.OleDb

VB.NET

Dim strConn As String = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Server.MapPath('nwind.mdb') & ';'
Dim strsql As String = 'Select * from  Customers'
Dim cn As New OleDbConnection(strConn)
Dim ds As DataSet = New DataSet()
Dim da As New OleDbDataAdapter(strsql, cn)
da.Fill(ds, 'T1')
DataGrid1.DataSource = ds
DataGrid1.DataBind()

C#

string  strConn  = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Server.MapPath('nwind.mdb') + ';';
string strsql  = 'Select * from  Customers';
OleDbConnection cn = new OleDbConnection(strConn);
DataSet ds   = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(strsql, cn);
da.Fill(ds, 'T1');
DataGrid1.DataSource = ds;
DataGrid1.DataBind();

You have to supply the Sql Statement or Stored Procedure for the DataAdapter as

VB.NET

da.SelectCommand.CommandText  = 'Select * from '

or

da = new SqlDataAdapter ('Select * from ', cn)

C#

da.SelectCommand.CommandText  ='Select * from ';

or

da = new SqlDataAdapter ('Select * from ', cn);

Try

VB.NET

dim dt as DateTime = ctype( dr('hiredate').ToString(), DateTime)

C#

DateTime dt= ((DateTime) dr['hiredate']).ToString();

You cannot use the same DataReader to populate 2 Listboxes.But can try out the below workaround

VB.NET

...
cn = New SqlConnection('Server=localhost;uid=sa;database=northwind;pwd=')
cmd = New SqlCommand('select * from products;select * from products', cn)
cn.Open()
dr = cmd.ExecuteReader()
ListBox1.DataSource = dr
ListBox1.DataTextField = 'productname'
ListBox1.DataBind()
dr.NextResult()
ListBox2.DataSource = dr
ListBox2.DataTextField = 'productname'
ListBox2.DataBind()

C#

...
cn = new SqlConnection('Server=localhost;uid=sa;database=northwind;pwd=');
cmd= new SqlCommand ('select * from products;select * from products', cn);
cn.Open();
dr = cmd.ExecuteReader();
ListBox1.DataSource = dr; 
ListBox1.DataTextField = 'productname'; 
ListBox1.DataBind(); 
dr.NextResult();
ListBox2.DataSource = dr; 
ListBox2.DataTextField = 'productname'; 
ListBox2.DataBind();

Try VB.NET

txtCountry.Text = dr('FieldName').ToString()

C#

txtCountry.Text = dr['FieldName'].ToString();

or Try VB.NET

If dbReader('fieldname').ToString= DBnull.Value.ToString()
	’Empty field value
Else
	’Display value
End if

C#

if (dbReader['fieldname').ToString() == DBNull.Value.ToString() )
{
	//Empty field value
}
else
{
	//display Value
}

This error is caused if you have not opened the connection.
Before you read the data using DataReader open the Connection

VB.NET

Dim mycn As New SqlConnection('server=localhost;uid=sa;password=;database=northwind;')
Dim mycmd As New SqlCommand('Select * from Products', mycn)
mycn.Open()
Dim dr As SqlDataReader = mycmd.ExecuteReader
Dim i As Integer
While dr.Read
	i += 1
End While
Response.Write('Count of Records : ' & i)

C#

SqlConnection mycn =new SqlConnection('server=localhost;uid=sa;password=;database=northwind;');
SqlCommand mycmd = new SqlCommand ('Select * from Products', mycn);
mycn.Open();
SqlDataReader dr = mycmd.ExecuteReader();
int i=0;
while(dr.Read())
{
	i+=1;
}
Response.Write('Count of Records : ' + i.ToString());

Use namespace System.Data.OleDb VB.NET

Dim myConnection As New OleDbConnection('Provider=SQLOLEDB.1;Data Source=localhost;database=northwind;User Id=sa;Password=;')
Dim myda As New OleDbDataAdapter('Select * from Orders', myConnection)
Dim ds = New DataSet
myda.Fill(ds, 'table')

Dim dr() As DataRow
Dim id As String = 'ROMEY' ’ 'Field with id ROMEY has more than one records found
Dim sel As String = 'customerid=’' + id + '’'
dr = ds.Tables(0).Select(sel)
Dim i As Integer
For i = 0 To (dr.GetUpperBound(0)) - 1
            Response.Write((dr(i)('Orderid').ToString() + ControlChars.Tab + dr(i)('Customerid').ToString() + ControlChars.Tab + dr(i)('Freight').ToString() + '<br>'))
Next

C#

OleDbConnection myConnection = new OleDbConnection('Provider=SQLOLEDB.1;Data Source=localhost;database=northwind;User Id=sa;Password=;');
OleDbDataAdapter myda= new OleDbDataAdapter ('Select * from Orders', myConnection);
System.Data.DataSet ds= new DataSet ();
myda.Fill (ds,'table');

DataRow[] dr ;
string id ='ROMEY';// 'Field with id ROMEY has more than one records found
string sel ='customerid=’'+ id + '’';
dr = ds.Tables [0].Select (sel);
int i;
for(i=0;i<dr.GetUpperBound(0);i++)
{
    Response.Write (dr[i]['Orderid'].ToString() + '\t' + dr[i]['Customerid'].ToString() + '\t' + dr[i]['Freight'].ToString () + '<br>');
}

Use namespace

System.Data.SqlTypes

VB.NET

Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null

If (txtDate.Text = '') Then
	cmd.Parameters('@Date').Value = sqldatenull
	’cmd.Parameters('@Date').Value = DBNull.Value
Else
	cmd.Parameters('@Date').Value = DateTime.Parse(txtDate.Text)
End If

C#

if (txtDate.Text == '') 
{
	cmd.Parameters ['@Date'].Value =sqldatenull ;
	//cmd.Parameters['@Date'].Value = DBNull.Value;
}  
else
{
	cmd.Parameters['@Date'].Value = DateTime.Parse(txtDate.Text);
}

SELECT * FROM information_schema.tables  where Table_type=’BASE TABLE’

VB.NET

dsData.Tables(0).Rows(0)('FieldName').ToString()

C#

dsData.Tables[0].Rows[0]['FieldName'].ToString()

VB.NET

If dbReader('fieldname').Tostring= DBnull.Value.ToString()
	’Empty field value
Else
	’Display value
End if

C#

if (dbReader['fieldname').ToString() == DBNull.Value.ToString() )
{
	//Empty field value
}
else
{
	//display Value
}

VB.NET

Dim thefilter  as string = 'fieldname=’<fieldvalue>’ '
dbDataView.RowFilter = thefilter			
Repeater1.DataSource = dbDataView
Repeater.DataBind()
</fieldvalue>

C#

string thefilter = 'fieldname=’<fieldvalue>’ ';
dbDataView.RowFilter = thefilter;				
Repeater1.DataSource = dbDataView;
Repeater.DataBind();
</fieldvalue>

VB.NET

ds.Tables('TableName').Rows(0)('ColumnName')

C#

ds.Tables['TableName'].Rows[0]['ColumnName'];

where TableName and ColumnName could be also integer (not in quotes then) to indicate you refer to the table’s or column’s index position.
Rows(0) indicates the first and only row in DataTable’s Rows collection

VB.NET

ds.Tables(0).Rows.Count

C#

ds.Tables[0].Rows.Count ;

Refer Performance Comparison: Data Access Techniques