Showing posts with label asp. Show all posts
Showing posts with label asp. Show all posts

Friday, March 30, 2012

HELP!!! Creating Nested Data in Repeated Region Problem

Hi All, Would appreciate some assistance with the attached, I'm using some fairly complicated recordsets (ASP VBScript) for a property search website, I have a page where a user selects search by County this in turn takes them to a page which lists all the towns in the county and number of live properties in each, I'm using 4 recordsets for this - 3 of which list the towns (townsA2I, townsJ2R & townsS2Z) the 4th is a Property Count, examples of the recordsets are below (using townsAtoI and LiveProperties -

TownsA2I

<%
Dim TownA2I
Dim TownA2I_numRows

Set TownA2I = Server.CreateObject("ADODB.Recordset")
TownA2I.ActiveConnection = MM_recruta2_STRING
TownA2I.Source = "SELECT towncountyID, Town, County FROM dbo.easytolettowncounty WHERE Town LIKE 'A%' AND County = '" + Replace(TownA2I__MMColParam, "'", "''") + "' OR Town LIKE 'B%' AND County = '" + Replace(TownA2I__MMColParam1, "'", "''") + "' OR Town LIKE 'C%' AND County = '" + Replace(TownA2I__MMColParam2, "'", "''") + "' OR Town LIKE 'D%' AND County = '" + Replace(TownA2I__MMColParam3, "'", "''") + "' OR Town LIKE 'E%' AND County = '" + Replace(TownA2I__MMColParam4, "'", "''") + "' OR Town LIKE 'F%' AND County = '" + Replace(TownA2I__MMColParam5, "'", "''") + "' OR Town LIKE 'G%' AND County = '" + Replace(TownA2I__MMColParam6, "'", "''") + "' OR Town LIKE 'H%' AND County = '" + Replace(TownA2I__MMColParam7, "'", "''") + "' OR Town LIKE 'I%' AND County = '" + Replace(TownA2I__MMColParam8, "'", "''") + "' ORDER BY Town ASC"
TownA2I.CursorType = 0
TownA2I.CursorLocation = 2
TownA2I.LockType = 1
TownA2I.Open()

TownA2I_numRows = 0
%>

LiveProperties

<%
Dim LiveProperties
Dim LiveProperties_numRows

Sub sLivePropertyCount(vLocation)
vCount=0

Set LiveProperties = Server.CreateObject("ADODB.Recordset")
LiveProperties.ActiveConnection = MM_recruta2_STRING
LiveProperties.Source = "SELECT COUNT(PropertyID) As NumberofProperties, propertylive, propertylocation FROM dbo.easytoletproperty WHERE propertylive = 'y' AND propertylocation = " & vLocation & " GROUP BY propertylocation, propertylive"
LiveProperties.CursorType = 0
LiveProperties.CursorLocation = 2
LiveProperties.LockType = 1
LiveProperties.Open()

vCount=(LiveProperties("NumberofProperties"))
Response.write(vCount)

LiveProperties.Close()
Set LiveProperties = Nothing
end sub

LiveProperties_numRows = 0
%>

I've tried following the tutorial here http://www.webthang.co.uk/tuts/tuts_dmx/rob9/rob9.asp but when i review the page i get the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'Barnet'.

/PropertiesbyTown1.asp, line 338 "

Where line 338"

Where line 338 is the "LiveProperties.Open()" of the above recordset, when i try to view bindings on this recordset Dreamweaver gives me the following errors -

"Column 'dbo.easytoletproperty.propertylive' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'dbo.easytoletproperty.propertylocation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

I would appreciate any assistance -

Try adding propertylocation to the GROUP BY clause as indicated by the error message.

Monday, March 19, 2012

HELP! Incomplete data in SQL Server table.

Hi SQL Server Experts,

I really need some help ASAP.I thank you in advance.

We have an ASP/MS SQL Server Shopping cart application,developed by an
independent developer.We really have a problem where the Order_details
are not getting stored completely.

The concerned Tables are:

Table1 Order_Info
Stores Order_ID,customer data,Total_Amount,Shipping details etc.
(One Record per Order.)

Table2 Order_details
Stores Order_ID,Product_Id,Price and Quantity.
(Multiple Records per Order.Basically stores the shopping cart items,
one by one.)

Table3 Payment_Info
Stores Order_ID and Transaction Details
(One Record per Order.)

Now after a customer checks out and the payment transaction has been
approved the order details are stored this way.

Step 1: First a new record is added to Table1 (Order_Info).

Step 2: A 'For' loop adds new records - one for each item in the cart-
to Table2(Order_details).

Step 3: A new record is added to the Table3 (Payment_Info).

Recently, we have been having problems with Step 2, where only a
partial list of the item details are stored in the Database and Step 3
is not executed at all.

And we don't have the problem all the time.

What could be the problem with Step 2 where a series of record are
added to a table continuosly? does this make SQL Server "too busy" so
that subsequent Add operations are not done?
Can someone see an apparent problem?

Any solution is greatly appeciated.

Please HELP ME!

Thanks.

SriramHi Sriram

If the Step 2 and 3 are really executed but no records are stored in the
database
there should be some error codes/messages returned by sql server.
You have to modify your application to log those errors so that you can
check them.

If you can't modify your application, you can use the SQL Profiler to make a
trace so
that you can try to insert the records manually with the SQL Analyzer (cut
and paste sql text).
By doing that you may reproduce the error and see the messages displayed by
sql.

Possible errors are:
database is not auto expand
log is full
duplicate keys (application bug)
etc...

By the way, you can also have a look at the sql server log with
Enterprise Manager.

Regards

Alain

"Sriram" <sriram.santhanam@.gmail.com> a crit dans le message de
news:67039024.0409071329.427c2f23@.posting.google.c om...
> Hi SQL Server Experts,
> I really need some help ASAP.I thank you in advance.
> We have an ASP/MS SQL Server Shopping cart application,developed by an
> independent developer.We really have a problem where the Order_details
> are not getting stored completely.
> The concerned Tables are:
> Table1 Order_Info
> Stores Order_ID,customer data,Total_Amount,Shipping details etc.
> (One Record per Order.)
> Table2 Order_details
> Stores Order_ID,Product_Id,Price and Quantity.
> (Multiple Records per Order.Basically stores the shopping cart items,
> one by one.)
> Table3 Payment_Info
> Stores Order_ID and Transaction Details
> (One Record per Order.)
> Now after a customer checks out and the payment transaction has been
> approved the order details are stored this way.
> Step 1: First a new record is added to Table1 (Order_Info).
> Step 2: A 'For' loop adds new records - one for each item in the cart-
> to Table2(Order_details).
> Step 3: A new record is added to the Table3 (Payment_Info).
> Recently, we have been having problems with Step 2, where only a
> partial list of the item details are stored in the Database and Step 3
> is not executed at all.
> And we don't have the problem all the time.
> What could be the problem with Step 2 where a series of record are
> added to a table continuosly? does this make SQL Server "too busy" so
> that subsequent Add operations are not done?
> Can someone see an apparent problem?
> Any solution is greatly appeciated.
> Please HELP ME!
> Thanks.
> Sriram|||Sriram (sriram.santhanam@.gmail.com) writes:
> Recently, we have been having problems with Step 2, where only a
> partial list of the item details are stored in the Database and Step 3
> is not executed at all.
> And we don't have the problem all the time.
> What could be the problem with Step 2 where a series of record are
> added to a table continuosly? does this make SQL Server "too busy" so
> that subsequent Add operations are not done?
> Can someone see an apparent problem?

Forget about SQL Server being "too busy". This is an application error,
that I can tell from this little information. What the error is, I can
of course not tell. But likely reasons:

1) The loop is faulty and simply fails to include some rows.

2) Some of the order_details rows fails to save because of constraint
violations, and the application does not have proper error handling
to detect the error and rollback the transaction. (Or it does not
even start a transaction.)

As Alain suggested, use the Profiler to see what is being sent to SQL
Server. You can also trace for errors. Note that if you trace for both
StmtStarting and StmtCompleted and don't see Completed for a statement,
it may be because it failed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks alain and Erland for replying.

The only problem is,

The application works everytime I test it.Only when someone is placing
an order from other geographical areas problem occurs.Even, when I place
the exact same order again, it goes through without any problem. All
values are stored correctly.

I cannot find out why or when this problem occurs.That is why I am
wondering if it is something else other than an application error.

Further, duplicate keys are allowed for this Table.

I'm currently rewriting the application.Any suggestion is greatly
appreciated.

Thanks.

Sriram

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Sriram Santhanam (sriram.santhanam@.gmail.com) writes:
> The only problem is,
> The application works everytime I test it.Only when someone is placing
> an order from other geographical areas problem occurs.Even, when I place
> the exact same order again, it goes through without any problem. All
> values are stored correctly.
> I cannot find out why or when this problem occurs.That is why I am
> wondering if it is something else other than an application error.

It could be deadlocks that are not correctly handled. Do you have
deadlock tracing enabled? If not, use Enterprise Manager to add
-T1204 and -T3605 to the startup parameters, and restart SQL Server
(assuming that you can afford a restart). If you can correlated the
deadlock with the incomplete orders, you have a lead.)

Of course, it could be a problem that is related to the web server only,
in which case I have no clues at all.

> Further, duplicate keys are allowed for this Table.

Eh? That does not sound good.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks Erland,

I would enable Deadlock Tracing and check it out.

As you had mentioned that duplicate keys are bad(I think so too but
someone had done the DB design long time back)
is there a better way to store the item details in the order
(Product_Id,Price,Qty at the minimum) than the current scenario I had
described above - Table2 where each record consists of
Order_Id,Product_Id,Price,Qty and whenever Item details are required a
query of all records by the Order_id is done.

Thanks.
Sriram

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Sriram Santhanam (sriram.santhanam@.gmail.com) writes:
> As you had mentioned that duplicate keys are bad(I think so too but
> someone had done the DB design long time back)
> is there a better way to store the item details in the order
> (Product_Id,Price,Qty at the minimum) than the current scenario I had
> described above - Table2 where each record consists of
> Order_Id,Product_Id,Price,Qty and whenever Item details are required a
> query of all records by the Order_id is done.

The obvious natural key is (Order_id, Product_id), but that presumes that
a product cannot be ordered at two prices. For instance, because there is
a limited offer, and the customer wants more. This situation could be
handled with promotion codes.

A more common way is probably to add a rowno within the Order. If this
rowno is computed in the client, there is a protection of entering the
same row twice.

In general it is difficult to be precise with detailed knowledge of the
business rules.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 9, 2012

Help! Can't pass names with apostrophe from ASP to SQL7 proc

I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.
When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.
I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?
BTW, here's an example of the proc.
========================================
=============
CREATE proc getName
@.CUSTNAME varchar(15)
as
declare @.SQL varchar(4000)
set @.SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @.CUSTNAME + '%'''
exec (@.SQL)
========================================
==============
I've passed the name as below and still get errors.
getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double
singles in the name chr(39)
Nothing works due to the syntex. What can I do, please HELP!!
Thank you,
DaeI don't understand why you are using dynamic sql for this..
I guess you simplified the actual query for the post :)
But anyways.. try this.
getName 'O''''Neil' -- 4 single quotes|||this would work
getname 'o''''niel'
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> I don't understand why you are using dynamic sql for this..
> I guess you simplified the actual query for the post :)
> But anyways.. try this.
> getName 'O''''Neil' -- 4 single quotes|||Or this should work:
SET QUOTED_IDENTIFIER OFF
EXEC getName "O'Anders"
"Vadivel" wrote:
> this would work
> getname 'o''''niel'
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "Omnibuzz" wrote:
>

Wednesday, March 7, 2012

HELP! -> Stored procedure and ASP.NET

I've created a SP in MS SQL Server 2005 Express. In ASP.NET I want to use this SP, but somehow my data is not added...what am I doing wrong?
Also: how can I efficiently debug a SP?

Here's my SP:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[sp_InsertNewUser]

-- Add the parameters for the stored procedure here

(@.UserNamenchar(20),

@.pwdnchar(15),

@.Emailnchar(35),

@.IsMalebit,

@.BirthDatedatetime,

@.Countrysmallint,

@.PerfectSexnchar(1),

@.HasHTMLbit,

@.HasOffersbit,

@.HasNewsLetterbit

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

INSERTINTO dbo.tblMember(UserName,pwd,Email,IsMale,BirthDate,Country,PerfectSex,HasHTML,HasOffers,HasNewsLetter)

VALUES(@.UserName,@.pwd,@.Email,@.IsMale,@.BirthDate,@.Country,@.PerfectSex,@.HasHTML,@.HasOffers,@.HasNewsLetter)

END

Here's my code:
Dim BirthDate As DateTime = CDate(ddlMonth.SelectedValue + "-" & ddlDay.SelectedValue & "-" & ddlYear.SelectedValue)
Dim ActivationCode As String = Session.SessionID
Randomize()
Dim sPwd As String = CStr(Int((1000000 * Rnd()) + 100000))
Dim MyCommand As New SqlCommand("sp_InsertNewUser", DBConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@.UserName", tbUserName.Text))
MyCommand.Parameters.Add(New SqlParameter("@.pwd", sPwd))
MyCommand.Parameters.Add(New SqlParameter("@.Email", tbEmail.Text))
MyCommand.Parameters.Add(New SqlParameter("@.IsMale", ddlSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.BirthDate", BirthDate))
MyCommand.Parameters.Add(New SqlParameter("@.Country", ddlCountry.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.PerfectSex", ddlPerfectSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasHTML", ddlEmailSupport.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasOffers", cbProdServices.Checked))
MyCommand.Parameters.Add(New SqlParameter("@.HasNewsLetter", True))

You've missed the pertinent part of your code. After you add the parameters, are you actually executing your MyCommand? And, where is this code running and are you sure its running?|||

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.
I've put everything in a try catch statement but no errors occur! I've checked the field definitions and they are good as well...

but I take it you dont see any syntax errors?
And do you know how I can debug a SP?

Thanks!

|||

Peter Smith wrote:


Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.


I guess we'll have to trust you on this one.

Peter Smith wrote:


but I take it you dont see any syntax errors?


I don't see any obvious syntax errors. Have you tried running your stored procedure from Query Analyzer?

Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text

Peter Smith wrote:


And do you know how I can debug a SP?


Which IDE are you using? Visual Studio 2003? I know this includes a stored procedure debugging capability from within Server Explorer but I have not really used it myself (seeHOW TO: Debug Stored Procedures in Visual Studio .NET). I know there is a Transact-SQL debugger available from within Query Analyzer (seeStarting the Debugger in BOL) and I've only played around with that a bit. Unfortunately I debug stored procedures using old-fashioned manual methods: running the stored procedure in Query Analyzer, examining the results, tweaking the code if there are errors/unexpected results. I don't use a formal debugging tool.|||

I dont know how to use that quote thing ;)

"Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text"


actually I use ASP.NET 2.0, I just didnt know I could also do it otherwise.
I will now use that simple constructor you provided, although I dont really see the purpose of specifying the datatype...is that for better debugging, or faster code or...I dont know...

I use Visual Web Developer 2005 Express and SQL Server 2005 Express...can I then still use the tools (query analyzer and server explorer) you mentioned?

Thanks!

HELP! ODBC becoming unusable

Here goes: We have situation at a customer site where users are suddenly unable to connect to SQL 2K via ASP using ODBC via DSN. While the customer is in this state, our Windows clients, which connect using OLE DB (ADO) to the same DB as the ASP, seem to be connecting without problems. Therefore, the problem seems to be with ODBC.

Additional details about the problem site:
SQL ODBC 3.50
Win2K Server SP 4.
SQL2k SP2.
MDAC 2.62
<100 users total
Moderate usage of our ASP interface

Note: SQL2k and IIS are on the same LAN-side server.

FYI: Another part of the problem is that I'm a developer and am not that familiar with the tools available to help me troubleshoot this kind of problem.

We've tried using Perfmon to monitor connection pooling, but nothing stood-out.

We've tried using netstat (with various switches), but nothing stood-out.

Does anybody have any advice?At least apply SP3 to your SQL.|||http://www.winnetmag.com/Windows/Article/ArticleID/3507/3507.html and take help of ODBC trace.
Check this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;268591 and corresponding referenced KBAs to take help of ODBC tracing to troubleshoot the problem.

Ensure both client and server are enabled with similar protocol, service packs as referred.|||Originally posted by rdjabarov
At least apply SP3 to your SQL.

Thanks. We will be advising them to update SQL to SP3, but it's not very comforting to us to give what they'll perceive as a solution, when we heavn't even identified the problem.|||While many of us here know client connectivity, it's still a SQL Server forum, not SQL Client ;)

I'd follow Satya's suggestions, at least you can see for yourself if you're even getting to the server.|||Originally posted by Satya
http://www.winnetmag.com/Windows/Article/ArticleID/3507/3507.html and take help of ODBC trace.
Check this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;268591 and corresponding referenced KBAs to take help of ODBC tracing to troubleshoot the problem.

Ensure both client and server are enabled with similar protocol, service packs as referred.
That's some great information! But won't this only trace activity across an open connection (implicit or explicit)? If so, I'm not sure it'll help because the problems is that at some point, the ASP are simply unable to connect via DSN using ODBC.

Given that it's as though SQL is simply inaccessable via the DSN, even when the ADO OLE DB client app is connecting fine, we've been speculating (with help from Microsoft) that the problem is with ODBC's connection pooling. We've tried to set-up performance logging of connection pooling in-house in an effort to determine it's potential usefulness to our scenario in production, but our in-house graphs show no activity...probably due to the relatively low activity in our development environment. We walked the customer through setting it up on their server anyhow.

Just recently, the problem occurred again on the customer's server. Before they could reboot the server, we took the opportunity to run NETSTAT to view the sockets for clues, but there were less than 20 results. Possibly because they have SQL and IIS on the same server?

This sucks because all we want to do is get an idea of how to duplicate the problem in a controlled environment so we can either a) adjust our ASP accordingly or b) give them a solution that we know addresses the problem.

Thoughts, comments, advice?|||Originally posted by rdjabarov
While many of us here know client connectivity, it's still a SQL Server forum, not SQL Client ;)

I'd follow Satya's suggestions, at least you can see for yourself if you're even getting to the server.
Was the frist comment a suggestion that my question (i.e. how to troubleshoot SQL connectivity issues) is outside of the scope of this forum? If so, please advise a more approriate forum. Thanks.

HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison

HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison

HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status
,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I c
an
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of th
e
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison

HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison

HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of the
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too! :)
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too! :)
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Helps if I add the tables for you too! :)
>> look at msdb..sysjobhistory and msdb..sysjobs
>> Immy
>> "segis bata" <segisbata@.hotmail.com> wrote in message
>> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>>
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison

Friday, February 24, 2012

Help with writing a Stored Procedure

Hi there,
I would be really grateful with some assistance with this, i am 100% new to
SQL & Stored Procedures. I'm building an ASP web application, i need to be
able to update two fields in two related tables & could really do with some
assistance. The tables are tbCompany and tbemployee. tbemployee is related t
o
tbCompany through a field in tbemployee named CompanyId (int). The fields
that i need to update are CompanySuspendedAccount in tbCompany
EmpCompAccoutSuspended in tbemployee. Needless to say there could be multipl
e
records in tbemployee that are related to the one record in tbCompany.
Ideally i will need to write two stored procedures, the first that updates
all records to yes and the second to no.
I would be really grateful for some assitance with this - Many thanks
GarethAssuming some thing that you didn=B4t posted a SP could bve something
like this for you:
CREATE PROCEDURE UPDSuspendCode
(
@.CompanyId INT,
@.Suspend BIT
)
AS
BEGIN
DECLARE @.Error INT
BEGIN TRANSACTION
UPDATE tbCompany
SET CompanySuspendedAccount =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.@.Error
UPDATE tbemployee
SET EmpCompAccoutSuspended =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.Error + @.@.Error
IF @.Error > 0
BEGIN
RAISERROR('And Error has occured during updating the suspend
status',16,1)
ROLLBACK
END
ELSE
COMMIT
END
HTH, Jens Suessmeyer.|||Thanks Jene, it looks good to me, presumably here i am setting the value to
Suspend? So i could use @.yes BIT for the suspending of the accounts and @.no
BIT to unsuspend. Both the fields CompanySuspendedAccount &
EmpCompAccoutSuspended are nvarchar so presumably it would need to be as
above unless i change the database? Thanks for your help
Gareth
"Jens" wrote:

> Assuming some thing that you didn′t posted a SP could bve something
> like this for you:
> CREATE PROCEDURE UPDSuspendCode
> (
> @.CompanyId INT,
> @.Suspend BIT
> )
> AS
> BEGIN
> DECLARE @.Error INT
> BEGIN TRANSACTION
>
> UPDATE tbCompany
> SET CompanySuspendedAccount = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.@.Error
>
> UPDATE tbemployee
> SET EmpCompAccoutSuspended = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.Error + @.@.Error
> IF @.Error > 0
> BEGIN
> RAISERROR('And Error has occured during updating the suspend
> status',16,1)
> ROLLBACK
> END
> ELSE
> COMMIT
> END
>
> HTH, Jens Suessmeyer.
>|||"@.yes BIT " Thats what my @.Suspend is for, you don=B4t need a @.Suspende
AND @.Unsuspense because the twi BITs can never evaluate to true (can be
either switch on AND off, right ).
You don=B4t have to change the database because BIT a Integer data type
with the constraint of being 1, 0, or NULL, so implicit conversion will
be used.
DECLARE @.Suspense BIT
DECLARE @.SomeColumn NVARCHAR(200)
SET @.Suspense =3D 1
SET @.SomeColumn =3D @.Suspense
Print @.SomeColumn
HTH, jens Suessmeyer.

Sunday, February 19, 2012

Help with update forms

Hey Guys
Still very new to asp/sql but trying to learn stuff as I go along.
I'm trying to create an update form where it pulls in an existing record, I
can update it, then submit the changes.
The problem i'm having is when i click to update the record I get this error
message
Cannot update identity column 'id'
In the update form, the ID column is hidden, so i'm not trying to change
that.
How do I get around it?
Thanks for your help.
RichHow are you performing the update? Can you post the actual SQL Statement?
You can't update an IDENTITY column.
David Portas
SQL Server MVP
--|||Hi David
Here's the statement - it was done through dreamweaver, so the code probably
isn't as neat as it should be.
' *** Update Record: set variables
If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <>
"") Then
MM_editConnection = MM_musicone_STRING
MM_editTable = "dbo.m1_web_content"
MM_editColumn = "id"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "articleupdated.asp"
MM_fieldsStr =
" id|value|Publish_Date|value|Section|valu
e|Section_Type|value|Title|value|In
tro|value|Full_Text|value|LiveDate|value
|HyperLink|value|ImageSrc|value|Live
|value"
MM_columnsStr =
" id|none,none,NULL|Publish_Date|',none,NU
LL|Section|',none,''|Section_Type|'
,none,''|Title|',none,''|Intro|',none,''
|Full_Text|',none,''|LiveDate|',none
,NULL|HyperLink|',none,''|ImageSrc|',non
e,''|Live|none,1,0"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "")
Then
' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " &
MM_recordId
If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
Any advice would be appreciated!
Thanks
Rich
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zOKdnaCOHNU1EQ7fRVn-gA@.giganews.com...
> How are you performing the update? Can you post the actual SQL Statement?
> You can't update an IDENTITY column.
> --
> David Portas
> SQL Server MVP
> --
>|||It looks like your UPDATE statement is attempting to update every column (in
the FOR loop). Aside from the specific problem you are having this seems
like a very error-prone and inefficient implementation. In general, avoid
generating SQL dynamically in code. Put your data access code in stored
procs and pass parameters from ASP to the proc. I'm not an ASP expert but
here's someone who is and has some tips and examples of good practices in
ASP:
http://www.aspfaq.com/show.asp?id=2201
http://www.aspfaq.com/show.asp?id=2424
David Portas
SQL Server MVP
--|||Thanks David, some really useful info that - much appreciated.
Richard
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:vaqdnR2tCdwjAg7fRVn-gA@.giganews.com...
> It looks like your UPDATE statement is attempting to update every column
> (in the FOR loop). Aside from the specific problem you are having this
> seems like a very error-prone and inefficient implementation. In general,
> avoid generating SQL dynamically in code. Put your data access code in
> stored procs and pass parameters from ASP to the proc. I'm not an ASP
> expert but here's someone who is and has some tips and examples of good
> practices in ASP:
> http://www.aspfaq.com/show.asp?id=2201
> http://www.aspfaq.com/show.asp?id=2424
> --
> David Portas
> SQL Server MVP
> --
>