Dynamic Database Owner

I've got a GridView on my webform bound to a SQLDataSource (called sqlZKEWILL). Works fine as long as I hard-code the "dbo" database owner in the Select statement, as in:

select EXIDV, Carton_Count, Carton_Total, dbo.ZINSPECTION where EXIDV = @.CartonID

The problem is that I need the database owner name to be dynamic. This will be a parameter that I read from another SQL table. For example, I'm doing this:

Select USERID, DatabaseOwner from tblUsers where UserID = 'Kimm'

I need to take the DatabaseOwner from this second Select statement, and use it as part of that first select statement.

On the CodeBehind page, I tried replacing "dbo" with the Database Owner field (which I saved in a session variable) with the following code:

PrivateSub gvLineItems_DataBinding(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles gvLineItems.DataBinding

sqlZKEWILL.SelectCommand = sqlZKEWILL.SelectCommand.Replace("dbo", Session("DatabaseOwner").ToString.Trim)

EndSub

But I am still getting the error message: "Invalid object name: dbo.ZINSPECTION". It doesn't seem to recognize that I've replaced "dbo" with a different value.

Any thoughts on getting this to work would be appreciated.

Hi,

From your description, it seems that you want to change the select command in the DataBinding event of your SqlDataSource, right?

Based on my knowledge, when the DataBinding event has fired, the select command has been in "ready for execute" status, so when you modify the select command in that event, it won't affect this time's execution. I suggest that you should modify your select command in some events of data bound control. Such as databinding event of GridView control.

Besides, since you are changing the database owner, please make sure that if the database owner matches the data tables, otherwise, you can't access the those data objects.

Thanks.

|||

Thank you for the advice. The solution I ended up with is similar to your suggestion. I ended up re-binding the GridView every time the user enters values in a text box, to allow me to dynamically change both the database owner and the where claus as needed. I appreciate your taking the time to help.