Live Chat Icon For mobile
Live Chat Icon

ASP.NET FAQ

Find answers for the most frequently asked questions
Expand All Collapse All

‘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()
Permalink

<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();
}

Permalink

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;
}
}
Permalink

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.
Permalink

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);
Permalink

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 ());
Permalink

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.

Permalink

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>');
	}
}


Permalink

<?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();
Permalink

<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) ;
	}
} 
Permalink

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());
	}
}
Permalink

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();
Permalink

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();

Permalink

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(); 
Permalink

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());

Permalink

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() + '
'))
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()="" dr[i]['freight'].tostring="" ()="" '<br="">');
}
</dr.getupperbound(0);i++)>
Permalink

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);
}
Permalink

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
}
Permalink

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

Permalink

Share with

Share on twitter
Share on facebook
Share on linkedin

Couldn't find the FAQs you're looking for?

Please submit your question and answer.