DataSet populated by multiple DataSources at once

Hi all,
I have seen this question asked many times, but never an answer posted.
Here is the situation: I need to connect dynamically to an unknown
number of databases at runtime, which the user will be no part of (any
parameters involving the Servers or DBs will be internal/hidden). The
data in each of these databases will then be consolidated and
represented in a single dataset. We are using SQL Server 2005.
Is there a way to use multiple datasources (i.e. get multiple tables
from separate databases/servers) and use them all in the same dataset
without using stored procedures? I know this can be done in Crystal,
but as yet there is no documentation on how to do this with SQL
Reporting Services. Are there any articles or examples out there of
this that do not involve consolidating the data into another database
first or using stored procedures?
Thanks!
Lance MOkay, through some "suggestiongs" this is what I have found out. There
are about 3 ways of dynamically creating this dataset (there are more
but these are the easiest and most useful ways), each of them trickier
than the last.
1)The first way is to use the SQL Server Linked Server feature. I was
told to research other options XD.
2)Use embedded code to go in and access the databases and return a Data
Reader or something. This is okay, but a pain to have to add to
multiple reports.
3)and the winner is... custom Data Processing Extentions(DPEs). DPEs
are what you use to make connection strings in the first place, and
there are ways to make your own that can take variable paramaters and
do all sorts of nifty things for you. This way is very difficult (the
first time or if you have forgotten how) because you have to make the
server trust it, and from what I have heard MSDN documentation on this
subject is incorrect. The actual coding itself shouldn't be a problem
for a moderate developer.
Option 3 is what I am investigating, I will post an update as to what
my final solution is.
Lance M|||I have a need for the same functionality. Have you had any success with
option 3?|||Okay, I have finished testing (for the most part). Provided that I can
install the DPE everything should work perfectly (knock on synthetic
wood). I created the 6 or so class files required for a DPE, but most
importantly I managed to connect dynamically to the databases and
return a table made of the elements of data queried from the DBs. This
table will then be passed to the ExecuteReader method of one of those
classes, and if I followed any samples (that had poor/no documentation)
right, then I should be able to summon those fields within any report
that uses a dataset based on that DPE. I also have a grand hope that I
will be able to somehow execute stored procedures with my DPE as well,
thus making it more robust and user friendly (haha).
One issue that I would like to note is that if the table you are
building happens to be gargantuan, the report could be incredibly slow
or just refuse to show up even fashionably late. In these cases, you
may want to have the report run overnight, and use a snapshot that is
somewhat up to date for report viewers. If you absolutely must have
these types of reports running any time a user selects them, I suggest
you look for a new career.
Good Luck!
Lance M

databse restore

Hello,
I have situation where I will need to restore from.
Monday -- Full backup + Transaction Log ever hour
Tuesday -- Deferential Backup +Transaction Log ever hour
Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
suspect and I will need to restore from my backup
Do I go and restore Full backup + deferential backup of Tuesday +
deferential Backup of Wednesday + Transaction log that was taken after that
deferential backup on Wednesday ?
Thanks,Hello,
I would recommend to find out first why database became suspect and fix that
problem. To restore use Monday full backup, the last differential backup
(Wednesday) and all the transaction log backups since the last differential
backup.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ron A" wrote:
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
> suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after that
> deferential backup on Wednesday ?
>
> Thanks,
>
>|||Ron
How about restoring FULL backup + LOG backup at 7PM ( I assume you took
backup log file just after a database became corrupted)
"Ron A" <omranu@.Gmail.com> wrote in message
news:er$LhgoHIHA.2100@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM
> database suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after
> that deferential backup on Wednesday ?
>
> Thanks,
>
>

Database update and insert problem

Hi,

I have 3 short(ish) questions, If someone could help I'd be very grateful..

This is the situation - I have a formview which contains a button, which on clicking should insert a row into one table, and update a column value of another. I will provide the code at the end.

1. On clicking, I get the error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.

it doesnt say where, but the stack trace is:

[SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1089
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +643
 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
 System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1151
 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +429
 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +88
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +86
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886

2. When updating, at the moment it will update with a new quantity provided in the text box, but id like the quantity in the textbox subtracted from the current column value - how would this be done?

3. For inserting, i was wondering if i need a command in the button_click to activate the insert command, similar to the one i have one for the update. I have tried but i cant seem to get the syntax right...

Code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = @.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@.User)"+
"INSERT INTO Transactions (Itemid)VALUES (@.productID)"+
"INSERT INTO Transactions (itemname)VALUES (@.Itemsname)"+
"INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+
"INSERT INTO Transactions (Qty)VALUES (@.qty)"+
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", TextBox1.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );

}

Huge thanks for anyone who can help shed some light!!

Thanks again,

Jon

1. Debug your application and see the query which is being executed by the SqlDataSource's update event. There might be a space missing in the query. The error is self explanatory - there is syntax error. Try to run the query in the query analyzer.

2.

' After successful updationdim qty asInteger =Integer(QuantityTB.TextBox)dim available asInteger =Integer(AvailableTB.TextBox)' Update the available text boxAvailableTB.TextBox = available - qty

3.

if e.CommandName ="insert" then SqlDataSource1.Insert()end if

|||

Hi, thanks for your response!

1. I had tried debugging but it didn't detect any errors or warnings.. is this what you mean or am i missing the point?

2. I meant to update the column in the database table - it currently updates with just the number that is written in the textbox, but id like it to take away the number written in the textbox from the number that is in their already - do you mean use this code to create a textbox, then update the table with this new value?

3. Is that VB? Do you have it in C# if it is? Or if it isn't, where shall I put it? it doesn't like the syntax in button2_click...

Thanks alot for your help!!!

Cheers,

Jon

|||

1. When I meant debug, put a breakpoint after the query has been formed and now check the value of the query. Copy this to the query analyzer and see if it runs fine. If it does then your query is fine, problem lies somewhere else. If it does not, watch out the error thrown by query analyzer. In your original post, you had written that you were getting error message. Did that error disappear?

2. When you insert the ordered quantity, I thought you would deduct the quantity from the stock. I would suggest, whenever you want to insert a ordered product, then it should be like one transaction.

a. Check if the ordered quantity is not bigger than the available stock.

b. Only add the record, if the above condition is true. If the transaction went through OK, rebind the grid, so that it will show new values.

c. Rebinding of the gridview is good practice, because there can be more than a single user attempting to fill out the order.

No, you do not need to create a new textbox. I was just showing how to update the UI.

3. It is in VB, but simple to convert to C#. It ideally would be in the command event handler of the Insert button.

void InsertCommandBtn_Command(Object sender, CommandEventArgs e) {if (e.CommandName =="insert") { SqlDataSource1.Insert(); }}

|||

Hi cheers for help.

Your code seems to work fine. Im getting a different error message now, and need help updating the table - Ill put it to the forum.

Thanks again!

Jon