By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Dynamic SQL generation

OuTCasT
100+
P: 374
Anyone know how to get passed this prblem

Dynamic SQL generation is not supported against multiple base tables
Apr 2 '08 #1
Share this Question
Share on Google+
16 Replies


100+
P: 152
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
Apr 2 '08 #2

debasisdas
Expert 5K+
P: 8,127
Waht exactly you are trying to do ?
Apr 2 '08 #3

OuTCasT
100+
P: 374
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.

Expand|Select|Wrap|Line Numbers
  1. Try
  2.             sqlConnection.Open()
  3.             sqlManager.EndCurrentEdit()
  4.             Dim sqlUpdate As New SqlCommandBuilder(sqlAdapter)
  5.             sqlAdapter.Update(sqlDataTable)
  6.             sqlConnection.Close()
  7.         Catch ex As Exception
  8.             MsgBox(ex.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
  9.         End Try
that gives me the error
would it work if I wrote an update command for each table rather :??
Apr 2 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Where's the query?

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

-- CK
Apr 2 '08 #5

OuTCasT
100+
P: 374
Where's the query?

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

-- CK
i was testing to get the information :

Expand|Select|Wrap|Line Numbers
  1. select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid
Apr 3 '08 #6

OuTCasT
100+
P: 374
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 ?
Apr 3 '08 #7

ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '08 #8

OuTCasT
100+
P: 374
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

Expand|Select|Wrap|Line Numbers
  1. Create view Employees
  2. as
  3. 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 ?
Apr 3 '08 #9

ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '08 #10

OuTCasT
100+
P: 374
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'
Apr 3 '08 #11

ck9663
Expert 2.5K+
P: 2,878
I thought you said "
By default, a view based on one or many tables is updateable" ?

-- CK
Apr 3 '08 #12

OuTCasT
100+
P: 374
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.
Apr 3 '08 #13

OuTCasT
100+
P: 374
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....
Apr 3 '08 #14

ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '08 #15

OuTCasT
100+
P: 374
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

Expand|Select|Wrap|Line Numbers
  1. ALTER trigger [dbo].[InsEmployee] on [dbo].[Employee]
  2. after update
  3. as
  4. if not exists(select s.employeeID , e.firstname , e.Lastname , s.wages from employee e, salary s where e.employeeid = s.employeeid)
  5. begin
  6. raiserror('the employee does not exist',16,1)
  7. rollback transaction
  8. End
Apr 3 '08 #16

ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.