Binding 2 tables

[norminator]

Having a small problem; have any of you guys ever binded 2 tables to a form? Meaning I am using an sql that joins 2 tables. Thus I need to bind the results to a form obviously using the same dataset or whatever i am clueless first time attempting something like this? How is this done

[psybuck2002us]

U can use 2 approach. You can write a stored procedure in the database and then call it from VB or you can include the SQL command in the VB code and query the database. I dont recommend the latter since it is slower and harder to maintain. So you can:

Call a Stored Procedure

dim cmd as new SQLCommand()

with cmd
    .commandtype = CommandType.StoredProcedure
    .commandtext = "yourstoredproceduresname"
    .CommandTimeout = 0 
    .Connection.ConnectionString = "The connection string for the database here"
end with

dim da as new sqldatadapter(cmd)
dim ds as new dataset

da.Fill(ds)

''' Bind Column1 from table to a drop down list

ddl.datasource = ds.Tables(0)
ddl.datatextfield = "Column1"
ddl.databind()

[norminator]

umm well how do you go ahead and bind it to a textbox for one and how do you use binding manager base with it?
eg.

me.txtFirstName.Databindings.add("Text", ds, "tblMembers.FirstName")
bmb = Me.bindingcontext(ds, "tblMembers")

above is the data binding done with one table; but with 2 tables what will u do?

ok figured out the how to bind to a text box part:

me.txtFirstName.Databindings.add("Text", ds.Tables(0), "Column1")

still trying to figure out the binding manager base section.

[norminator]

umm well how do you go ahead and bind it to a textbox for one and how do you use binding manager base with it?
eg.

me.txtFirstName.Databindings.add("Text", ds, "tblMembers.FirstName")
bmb = Me.bindingcontext(ds, "tblMembers")

above is the data binding done with one table; but with 2 tables what will u do?

ok figured out the how to bind to a text box part:

me.txtFirstName.Databindings.add("Text", ds.Tables(0), "Column1")

still trying to figure out the binding manager base section.

well i am getting an error when the form loads "Cannot bind to property or column Column1 on Datasource. Parameter name: dataMember

[psybuck2002us]

I used "Column1" to refer to the name of the first column in your table. It may not be Column1 for you. Also, If you are coding in asp.net, I would suggest you not use the bining manager base.

[norminator]

I used "Column1" to refer to the name of the first column in your table. It may not be Column1 for you. Also, If you are coding in asp.net, I would suggest you not use the bining manager base.

well a better thing to do than to use Column1 (which i know represents the first column) is to use in the sql statement or stored procedure is to use the AS keyword eg select firstname as fname ..... so instead of using Column1 you use fname. However everything is binded properly but here is the next problem as i stated earlier i am using binding manager base (vb.net) so therefore I am trying to scroll through the records . It aint working as the line

bmb = Bindingcontect(ds, ds.tables(0).toString)

not working

Usually where u place ds.tables(0).tostring you will put the table name in quotes

[norminator]

Now here is the status I am now using a dataview to scroll thorugh the records

bmb = me.bindingcontext(dvDataview)

and everything is running fine

But how do u go ahead from here to delete and save to this dataset that has multiple tables. As if I do it the normal way I immediately receive an error that states: Dynamic SQL generation is not supported against multiple base tables

[psybuck2002us]

Now here is the status I am now using a dataview to scroll thorugh the records

bmb = me.bindingcontext(dvDataview)

and everything is running fine

But how do u go ahead from here to delete and save to this dataset that has multiple tables. As if I do it the normal way I immediately receive an error that states: Dynamic SQL generation is not supported against multiple base tables

DataVIEW, keyword VIEW. You cant update the database using the binding manager base and a dataview. You need to sort it out with the dataset. As for the method of deleting with the binding manager base, i would have to go home and check some old code i have, cuz i dont rememeber how to us it.

[norminator]

DataVIEW, keyword VIEW. You cant update the database using the binding manager base and a dataview. You need to sort it out with the dataset. As for the method of deleting with the binding manager base, i would have to go home and check some old code i have, cuz i dont rememeber how to us it.

Yute I never said update using them lol I use them to help me navigate through the database for example if my sql statement was Select * from Members i will use binding manager base to navigate through it. The reason why I use dataview as it helps me with the join sql. The question is now how do you update this dataset then therefore updating the database with the dataadapter:

The normal way is jus to call the update command of the dataadapter but that giving me an error as i stated before.


da.update(ds, ds.tables(0).tablename)

that is giving me the error which i stated above.

I did some research and found out that you have to go ahead and change the update statement in the dataadapter:

using
da.updatecommand.commandtext = "update statement"

what will that update statement look like or is their an easier way of doing it

[psybuck2002us]

Yute I never said update using them lol I use them to help me navigate through the database for example if my sql statement was Select * from Members i will use binding manager base to navigate through it. The reason why I use dataview as it helps me with the join sql. The question is now how do you update this dataset then therefore updating the database with the dataadapter:

The normal way is jus to call the update command of the dataadapter but that giving me an error as i stated before.


da.update(ds, ds.tables(0).tablename)

that is giving me the error which i stated above.

I did some research and found out that you have to go ahead and change the update statement in the dataadapter:

using
da.updatecommand.commandtext = "update statement"

what will that update statement look like or is their an easier way of doing it

Scrap the dataview. It will not work for you. Write the join sql in a stored procedure and then call it with a sqlcommand obect and a sqldataadapter. Use the data adapter to populate the dataset. Create the bmb using the dataset. That will work. BTW, when u use the fill methot of the data adapter, ensure to put a name for the table as the second argument, so when u create the bmb, you can provide that same name as the table name.

As for updating, the da has properties where you define the sql to update, delete, etc. If you dragged the data adapter to the form, then this would have been assigned automatically, if you declared it via the code, then you have to assign them manually. You would have to assign them before you used the data adapter as an argument for declaring the bmb.

If you have done all that, then I believe the bmb has it's own delet and update functions.

Use Google dude