‘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
CreateProcedure GetSuppliers ASSELECT * FROM Suppliers
GO
Dim ds AsNew 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()
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 AsException
Label1.Text = ex.Message
Finally
cn.Close()
End Try
Protected functionTruncateData( Byval strNotes as string)
If strNotes.Length > 20then
Return strNotes.Substring(0,20) + '...'
Else
return strnotes
End function
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.
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
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.
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>');
}
}
Dim ds AsNew DataSet
ds.ReadXml(Server.MapPath('data1.xml'))
Dim dv AsNew 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 = newDataView();
dv = ds.Tables[0].DefaultView;
dv.RowFilter = 'prodId=’product2-00’';
this.DataGrid1.DataSource = dv;
this.DataBind();
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
IfNot Page.IsPostBack Then
ViewState('CurrentPos') = 0
Me.DataBind()
EndIf
Catch ex AsException
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 AsInteger = CType(ViewState('CurrentPos'), Integer)
CurrentPos += 1If CurrentPos > ds.Tables(0).Rows.Count Then
CurrentPos -= 1EndIf
ViewState('CurrentPos') = CurrentPos
Me.DataBind()
Catch ex AsException
Response.Write(ex.Message)
End Try
End Sub
protected Sub PrevBtn(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
Dim CurrentPos AsInteger = CType(ViewState('CurrentPos'), Integer)
If CurrentPos > 0Then
CurrentPos -= 1EndIf
ViewState('CurrentPos') = CurrentPos
Me.DataBind()
Catch ex AsException
Response.Write(ex.Message)
End Try
End Sub
protected Sub txtDataBind(ByVal sender AsObject, ByVal e As System.EventArgs)
Try
Dim CurrentPos AsInteger = 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 AsException
Response.Write(ex.Message)
End Try
End Sub
//Fill the DataSetforeach (DataRow dr in ds.Tables[0].Rows)
{
foreach( DataColumn dc in ds.Tables[0].Columns)
{
Response.Write(dr[dc.ColumnName].ToString());
}
}
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 AsNew OleDbConnection(strConn)
Dim cmd AsNew 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();
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 AsNew OleDbConnection(strConn)
Dim ds As DataSet = New DataSet()
Dim da AsNew 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 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();
Dim mycn AsNew SqlConnection('server=localhost;uid=sa;password=;database=northwind;')
Dim mycmd AsNew 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());
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' ’ 'Fieldwithid ROMEY has more than one recordsfound
Dim sel AsString = 'customerid=’' + id + '’'
dr = ds.Tables(0).Select(sel)
Dim i AsIntegerFor i = 0To (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 foundstring 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++)>
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
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
