Data Grids with Multiple Tables

[eileen]

i have created a datagrid which link with 2 different tables.

Eg Tables A & Tables B
Using a Combo Box select of Order No, both table A & Tables B information will display in one data grid

Order No -> (Order No) Tables A (Part Code) -> Tables B (Part Code) -> display information

Problem :-
I have already link the data adapter & dataset. anyway, when i execute this program, it display error as below.

Script :-
OleDbDataAdapter2.SelectCommand.CommandText = "select tbl_excel.excel_ord_no, tbl_excel.excel_ord_item, tbl_lead.lead_part," & _
"tbl_excel.excel_part_code, tbl_lead.lead_dc, tbl_excel.excel_qty," & _
"tbl_lead.lead_vendor, tbl_excel.excel_del_status, tbl_lead.lead_time," & _
"tbl_lead.lead_delete FROM tbl_excel LEFT OUTER JOIN " & _
"tbl_lead ON tbl_excel.excel_part_code = tbl_lead.lead_part" & _
"WHERE (tbl_excel.excel_ord_no = '" & cb_ord.Text & "')" & _
"ORDER BY tbl_excel.excel_ord_no, tbl_excel.excel_ord_item, tbl_excel.excel_part_code "

Src_excel_lead1.Clear()
OleDbDataAdapter2.Fill(Src_excel_lead1)

Errors:-
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


Can anyone please help me on this problem.

Thank you.

Regards,
Eileen

[jhammer]

First of all, there might be a problem in your SQL statement.
Second there this may be a compatability issue with the dataset schema and the schema of your query.

One last suggestion:
Using JoinView to Show Queries From Multiple Tables (http://j1hammer.blogspot.com/2006/01/using-joinview-to-show-queries-from.html)
This way you don't need to call the database again to get the query, but use the already existing schema.

[edwinMuthu]

Hi, U R Concepts is right

check u r Query there's u r Problem

[eileen]

Dear EdwinMuthu & jhammer,

Thanks for your kind reply.

My SQL statement is correct cause when i execute it into SQL Enterprise Manager there are an output display the correct data.

I wonder why, when i debug my system, the error stop at below line:-

OleDbDataAdapter2.Fill(Src_excel_lead1)

I do not know what is the reason, can anyone help me ?

Thanks.

[aniskhan]

try oledbcommand object
conn.Open()

sSQL = "your Query"

cmd = New OleDb.OleDbCommand(sSQL, conn)

adapter.SelectCommand = cmd

adapter.Fill(ds)

conn.Close()

[eileen]

Dear Aniskan,

i have changed the script according to your advice.


OleDbConnection2.Open()

sqlselect = "select tbl_excel.excel_ord_no, tbl_excel.excel_ord_item, tbl_lead.lead_part," & _
"tbl_excel.excel_part_code, tbl_lead.lead_dc, tbl_excel.excel_qty," & _
"tbl_lead.lead_vendor, tbl_excel.excel_del_status, tbl_lead.lead_time," & _
"tbl_lead.lead_delete FROM tbl_excel LEFT OUTER JOIN " & _
"tbl_lead ON tbl_excel.excel_part_code = tbl_lead.lead_part " & _
"WHERE (tbl_excel.excel_ord_no = '" & cb_ord.Text & "')" & _
"ORDER BY tbl_excel.excel_ord_no, tbl_excel.excel_ord_item, tbl_excel.excel_part_code "


cmd = New OleDb.OleDbCommand(sqlselect, OleDbConnection2)
OleDbDataAdapter2.SelectCommand = cmd
OleDbDataAdapter2.Fill(Src_excel_lead1)
OleDbConnection2.Close()


=> There is a runtime error show
An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll

Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

[eileen]

Dear all,

Finally i have successfull resolve this problem. Thank you very much for your kind support.

Instead of setting not null to the Part Code in table 'tbl_excel', i set it to null(can be null value). Therefore it allow me to execute and shown the output that i want.

Regards,
Eileen