Multiple Datasets into DataGrid

[Meleagant]

I am trying to populate an infragistics web data grid with 3 or 4 datasets. Specifically, creating a SQL String and populating a dataset, then merging that dataset to a master dataset. When I am done I will bind the web grid to the master dataset. Here's what I mean:

        Dim oOracleConn As OracleConnection = New OracleConnection()
        oOracleConn.ConnectionString = ConnStr
        oOracleConn.Open()

        Dim tmpdt As New DataTable
        Dim dtMaster As New DataTable

        SqlStr = "Select PtName as Data, PtType as Type Where PtNo = “ & ptno
        Dim oCmd As New OracleCommand(SqlStr, oOracleConn)
        Dim oClinicalReader As OracleDataReader = oCmd.ExecuteReader

        tmpdt.Load(oClinicalReader)
        dtMaster.Merge(tmpdt)

        tmpdt.Dispose()
        tmpdt = New DataTable

        SqlStr = "Select ProcNo as Data, ProcType as Type Where PrNo = “ & ptno
        oCmd.CommandText = SqlStr
        oClinicalReader = oCmd.ExecuteReader
        
        tmpdt.Load(oClinicalReader)
        dtMaster.Merge(tmpdt)
        
        tmpdt.Dispose()
	‘*** 3 – 4 More Sql Statements and Merges

        dtClinical.DataSource = dtMaster
        dtClinical.DataBind()

On the second merge I keep getting the error “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”. So I threw the .merge into a Try block and wrote out “dtmaster.Constraints.Count” and I got 0. Am I actually violating the constraints on the tables in the database? I noticed there was a .Remove method, but I don’t want to change the db at all. Will calling the Remove method actually delete the constraint from the db? If anyone can help me fill this danged grid with info please let me know.

Thanks,
-- Joe --

 

[jbenson001]

I would create a stored procedure, and have it return just one dataset with all the info you need.

 

[Meleagant]

Good plan, but I'm ashamed to say that I have no idea how to do that Oracle. I've always worked for MS Sql shops and yes I would have put it in a proc w/ a bunch of unions. I've been learning the nuances of oracle so there is one more item to put on the list. In the interim I've found that if I do this:

       dsMaster.EnforceConstraints = False
       ds.EnforceConstraints = False
       
       oCmd.CommandText = SqlStr
       oAdapter.SelectCommand = oCmd
       ds.Clear()
       oAdapter.Fill(ds)

       dsMaster.Merge(ds)

I don't get the constraint error. So problem temporarily solved, but definitely worth revisiting.

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[jbenson001]

Glad you found a work around.. but might be a good idea to learn more Oracle stuff if you can

Jim

 

[IT4EVR]

The only way to return data from a procedure in Oracle is with a Ref Cursor. You can either pass it back to the client and fill a dataset or datareader.

I suggest you read the link below.

http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_refcursors.html

 

[IT4EVR]

You can also pass back multiple cursors simultaneously.

Here is another link I found helpful...

http://msdn2.microsoft.com/en-us/library/4s2zbbsz(VS.80).aspx

I strongly suggest you go to the Oracle.com web site as well. They have a developer's site for .NET/Oracle.

http://www.oracle.com/technology/tech/dotnet/index.html

 

[Meleagant]

Thanks a lot for the links, I am going to check them out now. I've been working with MS Sql for 9 years now and I can do almost anything. Picking up Oracle has been like knowing how to speak Italian but trying to speak Spanish. I needed to use the DatePart function just now only to find it doesn't exist. There has to be something but what it is I don't know. Well off to the Oracle forums!

Once again thank you all!!!
-- Joe --

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[ca8msm]

Whilst not really on topic, check out

http://www.sql-ref.com/

if you are changing to Oracle (or vice-versa) for some handy tips on functions.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.