[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

SelectCommand On CodeBind Page Not Working

I have a SelectCommand with Distinct in it but it continues to pull all records within a record set but I only want the first record of the record set displayed on the screen.  Each record set can have up to 50 records with a unique id, but the same sub number (aaid) in the record set.  The sub number relates to a header table.

Example (ID = Unique, AAID = SubNumber)
ID AAID
1  10
2  10
3  10
4  10
5  11
6  11
7  11
8  12
9  12
and so.

The issue is it still brings back all records within the record set.

SqlDataSource1.SelectCommand = "select distinct ID, AAID, Requestor_LName, Email_Address_of_Submitter, Field_Approver_Name, RRUU, Docload_Doc_No, Line_Amount, FFIS_Status, BV_DATE, ASC_DATE_PROCESSED, Line_No from AA_MANIFEST INNER JOIN (Select Min(ID) minid From [AA_MANIFEST] Group By AAID)A On minid = id where REQUESTOR_LNAME like '%" & TextBox1.Text & "%' or Email_Address_of_Submitter like '%" & TextBox1.Text & "%' or Field_Approver_Name like '%" & TextBox1.Text & "%' or RRUU like'%" & TextBox1.Text & "%' or AAID like '%" & TextBox1.Text & "%' or Docload_Doc_No like '%" & TextBox1.Text & "%'"

Open in new window

0
gagnonmv
Asked:
gagnonmv
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you show the current sample resultset
0
 
gagnonmvAuthor Commented:
Its just like the one above.
0
 
Anthony PerkinsCommented:
This is a duplicate question and you already got the answer here:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24367407.html
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
M3mph15Commented:
Hi,

if you just want the first record then just use a DataReader object and grab the first value.

-M3mph15
0
 
gagnonmvAuthor Commented:
This appears to be a duplicate question, the problems are similar but the functions are different. This one is embedded in a code behind page and always for a text box to insert the varible.  SO if this is confusing>
0
 
M3mph15Commented:
So gagnonmv is this problem solved for you or not??
0
 
gagnonmvAuthor Commented:
No it has not been solved and I have never setup a data reader. Can you provide an example? Thanks
0
 
M3mph15Commented:
Hi,

The below example is written for an Oracle database but can be easily modified for SQL server database (e.g. OracleDataReader would become OleDbDataReader i think)

Dim sql as String = "select distinct ID, AAID, Requestor_LName, Email_Address_of_Submitter, Field_Approver_Name, RRUU, Docload_Doc_No, Line_Amount, FFIS_Status, BV_DATE, ASC_DATE_PROCESSED, Line_No from AA_MANIFEST INNER JOIN (Select Min(ID) minid From [AA_MANIFEST] Group By AAID)A On minid = id where REQUESTOR_LNAME like '%" & TextBox1.Text & "%' or Email_Address_of_Submitter like '%" & TextBox1.Text & "%' or Field_Approver_Name like '%" & TextBox1.Text & "%' or RRUU like'%" & TextBox1.Text & "%' or AAID like '%" & TextBox1.Text & "%' or Docload_Doc_No like '%" & TextBox1.Text & "%'"
 
Dim _source As String = "Data Source=[name of database];Persist Security Info=True;Password=[password];User ID=[username]" 'Replace [*] with relevant data
 
Dim _conn as new OracleConnection(_source)
Dim command As OracleCommand = _conn.CreateCommand()
command.CommandText = sql
 
Dim reader As OracleDataReader
 'So at this point you have set up the connection properties for the database.
Try
                _conn.Open() 'Opens connection to the database
                reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
              _conn.Close()
End Try
 
If reader IsNot Nothing Then
                If reader.HasRows() Then
                         String val = ""
reader.Read()
                         val = reader.getValue([index]) 'index starts at 0 for the first record returned.
reader.close() 'Make sure to close your reader which in turn will close the connection.
                Else
                         'Code for record doesn't exist Insert/Update
                End If
                reader.Close()
Else
  'Error Handling for failure to create reader
End If

So by retrieving the first record returned using the Read() method hopefully that solves your problem.

HTH
-M3mph15
0
 
gagnonmvAuthor Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now