Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,

I have a question regarding a dataset.

How do I return only one record from a dataset that has mulitple records.

If anyone can help me, that would be great! Here is my code in advance. I am currently using VS 2010 if that helps.

Thank you

VB
Public Function ReadPolicyStep1(ByVal custnum As String, ByVal symb0l As String, ByVal policynum As String) As DataSet 

  Dim con As OdbcConnection = New OdbcConnection(_connString)
  Dim cmd As OdbcCommand = New OdbcCommand("SELECT CUST0NO, SYMBOL, POLICY0NUM, MODULE, TYPE0ACT FROM CCC00200 WHERE CUST0NO = ? AND SYMBOL = ? AND POLICY0NUM = ? ORDER BY MODULE DESC", con)

            cmd.Parameters.AddWithValue("@custnum", custnum)
            cmd.Parameters.AddWithValue("@symb0l", symb0l)
            cmd.Parameters.AddWithValue("@policynum", policynum)

            cmd.CommandType = CommandType.Text 

            Dim da As New OdbcDataAdapter(cmd)
            Dim ds As New DataSet() 

            Using da
               da.Fill(ds)
            End Using 

            Return ds 

        End Function


[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 21-Jun-12 8:01am
v2
Comments
Prosan 22-Jun-12 0:45am    
which database u are using

Try:
SQL
SELECT TOP 1 CUST0NO, SYMBOL, POLICY0NUM, MODULE, TYPE0ACT FROM CCC00200 WHERE CUST0NO = ? AND SYMBOL = ? AND POLICY0NUM = ? ORDER BY MODULE DESC
 
Share this answer
 
Comments
Maciej Los 21-Jun-12 14:18pm    
Good answer, my 5!
Member 3491662 21-Jun-12 14:37pm    
Thank you for the fast response. I really appreciate it.

When I changed the select statement it returns an error message.

ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token 1 was not valid. Valid tokens: + - AS <identifier>.

I think the reason is because the syntax is different when using a DB2 database, but I am not 100% sure.

Do you know of any other solution. I will try to check and see what is the syntax to pull only one record in a DB2 database, "if there is a solution".

Thank you
HI...

Try this code...


Public Function ReadPolicyStep1(ByVal custnum As String, ByVal symb0l As String, ByVal policynum As String) As DataRow

    Dim con As OdbcConnection = New OdbcConnection(_connString)
    Dim cmd As OdbcCommand = New OdbcCommand("SELECT CUST0NO, SYMBOL, POLICY0NUM, MODULE, TYPE0ACT FROM CCC00200 WHERE CUST0NO = ? AND SYMBOL = ? AND POLICY0NUM = ? ORDER BY MODULE DESC", con)

    cmd.Parameters.AddWithValue("@custnum", custnum)
    cmd.Parameters.AddWithValue("@symb0l", symb0l)
    cmd.Parameters.AddWithValue("@policynum", policynum)

    cmd.CommandType = CommandType.Text

    Dim da As New OdbcDataAdapter(cmd)
    Dim ds As New DataSet()

    Using da
        da.Fill(ds)
    End Using

   If ds.Tables(0).Rows.Count > 0 Then
        Return Nothing
    Else
        Return ds.Tables(0).Rows(0)
    End If

End Function
 
Share this answer
 
v2
SQL
Dim cmd As OdbcCommand = New OdbcCommand("SELECT top 1 CUST0NO, SYMBOL, POLICY0NUM, MODULE, TYPE0ACT FROM CCC00200 WHERE CUST0NO = ? AND SYMBOL = ? AND POLICY0NUM = ? ORDER BY MODULE DESC", con)
 
Share this answer
 
Comments
Member 3491662 22-Jun-12 9:06am    
Hey Prosan! The database I am using is DB2 which is why "SELECT TOP 1" did not work, however I did find something that should work.

Thank you for your help. I really appreciate it.
Thanks everyone for the help. I found a solution that I beleive might work.

Due to the fact I am working with a DB2 database and NOT microsoft sql or t-sql the syntax is different when returning only one record.

Instead of saying "SELECT TOP 1 bla, bla, bla, FROM TABLE" I needed to code "SELECT bla, bla, bla FROM TABLE FETCH FIRST ROW ONLY".

So far that appeared to work just fine for what I am trying to accomplish.

Thank you again for all your help.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900