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.

Try
            sqlConnection.Open()
            sqlManager.EndCurrentEdit()
            Dim sqlUpdate As New SqlCommandBuilder(sqlAdapter)
            sqlAdapter.Update(sqlDataTable)
            sqlConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
        End Try

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 :

select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid

[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...and 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

Create view Employees
as
select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid

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 'employeesStoredProc'

[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

[OuTCasT]

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

i created a trigger for the update of the 2 tables in question

ALTER trigger [dbo].[InsEmployee] on [dbo].[Employee]
after update
as
if not exists(select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid)
begin
raiserror('the employee does not exist',16,1)
rollback transaction
End

[ck9663]

Create a trigger on the view. Not on the table.

Create the trigger on the view. Inside that trigger, that's where you UPDATE all the tables.

-- CK