error select statement with multiple table in pgsqldatatable

[shilpa24]

hi,
i am using pgsqldatatable class to insert record
but i m facing a wired problem if i use following simple query

dim myDataTable as PgSqlDataTable= New PgSqlDataTable(New PgSqlCommand("select * from table1", Database.Connection))

everything works fine my record get inserted
but if i use query to fetch record from multiple table like this

dim myDataTable as PgSqlDataTable= New PgSqlDataTable(New PgSqlCommand("select * from table1,table2 where table1.field1=table2.field1", Database.Connection))

then records not insert no error appears
but no record inserted in database

i m using devart version 5.50.214.0
please help its very urgent

[shilpa24]

hi,
Anybody have any idea, how can i fixed this issue, please help i need urgent solution

[Pinturiccio]

The PgSqlDataTable class extends the Devart.Common.DbDataTable class to provide a single object that can be used to access and update data from a data source. When you use multiple table select command, you will receive the exception, that insert command cannot be generated for multiple tables. In this case you should generate insert command by yourself. You have to assign two update commands to your myDataTable.InsertCommand and add 'unprepared execute=True' to your ConnectionString.
We will consider that both table1 and table2 have two fields: field1 and field2 are the fields of table1, and field3 and field1 are the fields of table2.
Creating InsertCommand:

Code: Select all

myDataTable.InsertCommand = New PgSqlCommand("INSERT INTO table1 (field1, field2) VALUES (:field1, field2); INSERT INTO table2 (field3, field1) VALUES (:field3, :field1,)",Database.Connection)
myDataTable.InsertCommand.Parameters.Add(New PgSqlParameter("field1", PgSqlType.Int, 0, "field1"))
myDataTable.InsertCommand.Parameters.Add(New PgSqlParameter("field2", PgSqlType.Int, 0, "field2"))
myDataTable.InsertCommand.Parameters.Add(New PgSqlParameter("field3", PgSqlType.Int, 0, "field3"))

Then add a new row and update the datatable:

Code: Select all

myDataTable.Rows.Add(New Object() {10, 11, 12})
myDataTable.Update()

New rows will be inserted to table1 and table2.

You can create this command in design time. For this you should perform the following:
1. Drag PgSqlDataTable on your form.
2. Double click on it.
3. Specify the select command for the first table.
4. Open the third tab "Command Generator" and generate the insert command.
5. Copy it to the Clipboard.
6. Perform the 1-4 steps for the second table.
7. Paste the first command after the second one. Separate the commands with the semicolon ';' character.
8. Click the Edit button.
9. Switch to the Parameters tab.
10. Specify the source column if it is not specified automatically.

[shilpa24]

thanx Pinturiccio for your reply ,

but i dont want to insert record into two table ,i want to fetch record from two tables but insert record in only one table

i have made some modification in my code now record get inserted but binding context is not working

here is my code

myDataTable = New PgSqlDataTable(New PgSqlCommand("select * from table1,departments where table1.departmentid=departments.departmentid", Database.Connection))

With myDataTable
.Fill()
.InsertCommand = New PgSqlCommand("insert into table1(costcodeid,costcode) Values(DEFAULT,:costcode) returning costcodeid", Database.Connection)
.InsertCommand.Parameters.Add(":costcode", PgSqlType.Int, 0, "costcode")
End With
myDataTable.Columns("costcodeid").DefaultValue = 0
Call txtCostcode.DataBindings.Add("Text", myDataTable, "costcode")
Me.BindingContext(myDataTable).CancelCurrentEdit()
Me.BindingContext(myDataTable).AddNew()

and on save button
Me.BindingContext(myDataTable).EndCurrentEdit()
myDataTable.Update()
myDataTable.AcceptChanges()

with this code record get inserted but textbox value not get save

if i write simple single table query in select statement then everything works fine but i want to fetch record from multiple table is that some thing i m missing in above code

[Pinturiccio]

Saying that the TextBox value is not saved, did you mean that TextBox is empty after the update? If yes, the reason for this is that

Code: Select all

Me.BindingContext(myDataTable).AddNew()

will create a new row with (costcodeid,costcode) with values (0, Null). The second item of this row (i.e., null) will be bound to textBox, thus erasing the previous value.

Could you please specify the expected behaviour of this code:

Code: Select all

Me.BindingContext(myDataTable).CancelCurrentEdit()
Me.BindingContext(myDataTable).AddNew()

Me.BindingContext(myDataTable).EndCurrentEdit()

Have you tried inserting a new row with 'costcode' equal to the value in TextBox, or, e.g., updating the existing row?

[shilpa24]

Saying that the TextBox value is not saved, did not mean that TextBox is empty after the update
with this i mean that textbox(i.e txtcostcode) value which i entered, is not get saved in database
only autoincremented field get saved and all the fields which i entered through binding context remain blank in database

i use the below code
Me.BindingContext(myDataTable).CancelCurrentEdit()
Me.BindingContext(myDataTable).AddNew()

because when my form load with this code one new row get created in datatable, as you say with values
(0, Null) but when i click on save button
with the below code
Me.BindingContext(myDataTable).EndCurrentEdit()
all the value which i enter in form get inserted in datatable

and with mydatatable.update() method all that values which are in datatable get inserted in database

i think you didnt get my question,

see i want to fetch the record from two tables (table1 and departments) ,but insert record in only one table i.e table1

two tables field are like this
table1(costcodeid,costcode,departmentid)
departments(departmentid,departmentname)

from my form i m inserting costcode and departmentid in table1, in the same form i have one grid which shows costcode and departmentname,
just to show the departmentname in grid, thats y i want to fetch record from two table departments and table1

but when i write this query

myDataTable = New PgSqlDataTable(New PgSqlCommand("select table1.*,departments.departmentname from table1,departments where
table1.departmentid=departments.departmentid", Database.Connection))

no values from textbox get saved in database

[Pinturiccio]

Could you please explain the logic of your update?
When you click on button "Save", what value are inserted in database and what value do you want to insert? Where is this value from (from gridview or textbox or both)? I understand that you want to insert data only for table1 (costcode, departmentid). But how do you use the TextBox.Text value in the insert statement?

A small test project will be appreciated.

[Pinturiccio]

Thank you for the test project. We have reproduced the issue. We will investigate it and notify you as soon as possible.

[Pinturiccio]

When you fetch data from more than one table, all columns became read-only. Therefore, when you bind data from some column to a textbox, the value from the textbox can't be saved to the read-only column.
If you want your code to work correctly, you should add only one row to your code, after filling the DataTable and before DataBindings the data.

Code: Select all

myDataTable.Columns("cost").ReadOnly = False

[shilpa24]

Hello Pinturiccio

Thanks for the solution , now everything works fine