Dynamic SQL generation

[OuTCasT]

Anyone know how to get passed this prblem

Dynamic SQL generation is not supported against multiple base tables

[gpl]

Im not entirely sure what you mean - you can write dynamic sql to run against multiple tables.

Are you saying that you have a tool that generates dynamic sql for you and it wont work against multiple tables ?

Graham

[debasisdas]

Waht exactly you are trying to do ?

[OuTCasT]

Waht exactly you are trying to do ?

i have a win form
and 2 tables
need to get info from both tables onto the form on page load
so i used a join

created sqlDataAdapter and filled the datatable
now i want to save the data.

[CODE=vb]Try
sqlConnection.O pen()
sqlManager.EndC urrentEdit()
Dim sqlUpdate As New SqlCommandBuild er(sqlAdapter)
sqlAdapter.Upda te(sqlDataTable )
sqlConnection.C lose()
Catch ex As Exception
MsgBox(ex.Messa ge, MsgBoxStyle.OkO nly, "SQL Exception Error")
End Try[/CODE]

that gives me the error
would it work if I wrote an update command for each table rather :??

[ck9663]

Where's the query?

This could be more of a VB question than a SQL question.

-- CK

[OuTCasT]

Where's the query?

This could be more of a VB question than a SQL question.

-- CK

i was testing to get the information :

[CODE=sql]select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid[/CODE]

[OuTCasT]

Where's the query?

This could be more of a VB question than a SQL question.

-- CK

i filled the dataTable with the sqlAdapter...an d created a currency manager to navigate through the records.

Should i create an update command for each table ?

[ck9663]

If you bind that query in a some sort of adapter or data object, the query is valid but it will be treated as a view. By default, you can not update a view.

-- CK

[OuTCasT]

If you bind that query in a some sort of adapter or data object, the query is valid but it will be treated as a view. By default, you can not update a view.

-- CK

By default, a view based on one or many tables is updateable.

ive created a view now

[CODE=sql]Create view Employees
as
select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid[/CODE]

pretty simple now how would i use it in my code ?

[ck9663]

Well now that you have a view. Try updating it first in query analyzer. Once you did it, try to implement the same technique in your apps.

-- CK

[OuTCasT]

If you bind that query in a some sort of adapter or data object, the query is valid but it will be treated as a view. By default, you can not update a view.

-- CK

No that wont work.
Gives me an error saying cannot use it because it is a view and not a stored proc.
now ive created a sproc and the information is displayed but when i try add another record and update the datatable it gives me and error saying incorrect syntax near 'employeesStore dProc'

[ck9663]

I thought you said "
By default, a view based on one or many tables is updateable" ?

-- CK

[OuTCasT]

I thought you said "
By default, a view based on one or many tables is updateable" ?

-- CK

Thats what is says in my SQL book.
Just went to look how to insert,update.

[OuTCasT]

I thought you said "
By default, a view based on one or many tables is updateable" ?

-- CK

what i can do is, create 2 select statements that can be update by themselves, but at the end of the day there is going to be like 6 tables with loads of information that has to be update etc....

[ck9663]

Create a view with all the columns you need. Create a trigger for that view to handle the insert to all tables. Use Transaction processing so that you can rollback if one of the insert fails.

-- CK