显示标签为“asp”的博文。显示所有博文
显示标签为“asp”的博文。显示所有博文

2012年2月16日星期四

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

2012年2月13日星期一

Batch insert

I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
MarkI am confused:confused: What is it that you need?

If you're talking about being able to do BULK INSERT from a stored procedure, you can do that, but you'll have to grant the User ID that will execute it Bulk Insert Administrator server role (bulkadmin.)|||Sorry
I have 2 tables
table 1 - remote, cant use an SP
table 2 - local, can use a SP

I do a select on Table1 and it returns the results,

I want to insert all these results into table 2

whats the best way to do that? (most efficient)

Cheers
Mark|||The problem is that the first table is really slow to access, I have a string of booking Id's i need to pass it and it keeps timing out...its the DB! its on rubbish HW and is badly designed but I cant do anything about that so I have to use the most efficent method of getting the data out, or simply copying the required records between the 2 tables

remember the rubbish DB is not local and I only have read access to it!

whats the most efficent method I should use?

thanks
Mark|||Create a stored procedure on the remote server that retrieves rows via SELECT, and call it instead of doing SELECT from your local server.

2012年2月11日星期六

basics with stored procedures.....please

Hi there, I'm still fairly new to asp.net / c#

I've setup my stored procedure, but now what?
I want to display the information (only one record it will return) on the page / NOT using a datagrid or list, just simple....I guess using an asp:label???
Could someone tell me what I need to do!!!

Also, are there any proper resources out there that just show you right out of the box howto insert / update / delete with sp's?, show / view, just proper simple source code?

THANKS!

my code:
// Creating the database connection
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_conn_main"]);

// Creating the stored procedure 4 the webpage
// Instantiate a Sql Command
SqlCommand webpage_view = new SqlCommand("stpr_webpage_view", objConn);
// Declare that the T-SQL statement that will be executed is a stored procedure
// Use a CommandType.Text to execute a T-SQL Query
webpage_view.CommandType = CommandType.StoredProcedure;
SqlParameter webpage_id_parm = webpage_view.Parameters.Add("@.webpage_id", SqlDbType.Int);
webpage_id_parm.Value = "1";
// Create a SqlDataAdapter to manage the connection and retrieve the data
SqlDataAdapter da_1 = new SqlDataAdapter();
// Instantiate a Dataset that will hold the data retrieved from the database using the select command
DataSet ds_webpage = new DataSet();
da_1.SelectCommand = webpage_view;
// Retrieve the data into the dataset using the SqlDataAdapter.Fill command
da_1.Fill( ds_webpage, "webpage_title" );
DataView dv2 = ds_webpage.Tables[0].DefaultView;
if ( !this.IsPostBack ) {
this.DataBind ( );
}


// C#
// To place a single value from a recordset into a label
// drag an ASP:Label onto the Web form and give it an ID of myLabel
myLabel.Text = "No data";
// Be compulsive and make sure there is a datatable in the dataset and that it has at least one row
if( ds_webpage.Tables.Count > 0 && ds_webpage.Tables[0].Rows.Count > ) ){
myLabel.Text = ds_webpage.Tables[0].Rows[0].ToString();
}

The simplest way to see how stored procedures can be used to select and update data is to let VS.Net create it for you.

1. Go to Design View for a web form.
2. Drag an SqlDataAdapter onto the form. A DataAdapter Configuration Wizard will open
3. Select your database connection and click Next.
4. On the Choose a Query Type screen select the second option "Create a new Stored Procedure". Click Next
5. On the Generate a Stored Procedure screen enter a select statement for the fields you want to use. For example, just something like Select * from SomeTable. Click Next.
6. On the Create the Stored Procedures screen enter appropriate stored procedure names. Be sure to click the "Yes, create them in the database for me" radio button.

Look at the code behind generated for this data adapter to see how it all works. Use it like the one you created above for the select. For the update, after you changed data in the data set just call:
da_1.Update( ds_webpage, "webpage_title" );

Basic, perhaps silly SQLServer 2005 question

I've previously used version 7 and 2000.

I've recently started to learn ASP.net. I've installed
Visual Web Developer 2005 Express and this includes
SQLServer 2005 in the installation. The task manager
and and the list of services definitely show I've got
SQLServer running on my computer.

Here's the problem: how do I actually use it? I can't
find any tools to create and access databases.

I've tried installing the Enterprise Manager from 2000
but this gives me an error message saying I must use the 2005
tools to connect.

So, is there an Enterprise Manager (or similar) for 2005?
Where do I find it?Download Microsoft SQL Server Management Studio Express from
http://www.microsoft.com/downloads/...&DisplayLang=en

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145465194.532705.252780@.u72g2000cwu.googlegr oups.com...
> Download Microsoft SQL Server Management Studio Express from
> http://www.microsoft.com/downloads/...&DisplayLang=en

Thanks, thats great