Update A Field With A Stored Procedure
Dec 13, 2004
I need to update a field with the code below, is it possible to do this with a table join? The Query Analyzer is giving me an error at "Join".
An example:
(ManufSerNo is a field in table ASSETS,
Status is a field in table HISTORY)
GO
CREATE PROCEDURE sp_ChngeAssetStatus
@ManufSerNochar(10),
@Statusvarchar(1)
AS
UPDATE HISTORY
SET Status = @Status
JOIN ASSETS
ON HISTORY.AssetID = ASSETS.AssetID
WHERE ManufSerNo = @ManufSerNo
--ELSE
--RAISERROR ('Sorry, but the Asset ID specified does not exist. Record not updated.', 10 , 1)
GO
View 2 Replies
ADVERTISEMENT
Dec 20, 2006
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
View 1 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 30, 2007
Hello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
View 6 Replies
View Related
Mar 22, 2008
Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate]
(
@ID int,
@Field1 nvarchar(50),
@Field2 nvarchar(50)=null,
@Field3 nvarchar(50)=null,
@Field4 nvarchar(50)
)
AS
UPDATE MyTable
SET Field1 = @Field1,
if (@Field2<>null) Field2 = @Field2,
if (@Field2<>null) Field3 = @Field3,
Field4 = @Field4
WHERE ID = @ID
I saw example before but i can't remember where.
Thank you in advance
View 4 Replies
View Related
Jul 20, 2005
Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
View 2 Replies
View Related
Oct 27, 2006
Hello Everyone,
I have the following stored procedure:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_MLSReport_LRR]
@UserId int,
@ReportGroupId int = null,
@BranchTranType varchar(50) = null,
@BranchDivision varchar(50) = null,
@BranchRegion varchar(50) = null,
@BranchNbr int = null,
@BranchSatId varchar(1) = null,
@BeginDate datetime,
@EndDate datetime
AS
-- DECLARE @UserId int,
-- @ReportGroupId int,
-- @BranchTranType varchar(50),
-- @BranchDivision varchar(50),
-- @BranchRegion varchar(50),
-- @BranchNbr int,
-- @BranchSatId varchar(1),
-- @BeginDate datetime,
-- @EndDate datetime
--
-- SET @UserId = 4602
-- --SET @ReportGroupId = 46
-- SET @BranchDivision = 'DENVER DIRECT'
-- SET @BeginDate = '4/27/05'
-- SET @EndDate = '9/30/05'
SET NOCOUNT ON
DECLARE @UseCTD int
SET @UseCTD = CASE WHEN @BranchTranType IS NULL
AND @BranchDivision IS NULL
AND @BranchRegion IS NULL
AND @BranchNbr IS NULL
AND @BranchSatId IS NULL THEN 1 ELSE 0 END
SELECT DISTINCT
L.ProviderId,
L.OriginalProviderId,
S.SourceCode,
SG.SourceGroupCode,
SGDescription=SG.Description,
RG.ReportGroupId ,
ReportGroupDesc=RG.Description ,
L.VendorId,
VEN.VendorName,
B2.BranchTranType,
B2.BranchDivision,
B2.BranchRegion,
B2.BranchName,
L.BranchNbr,
L.BranchSatId ,
Branch=CONVERT(varchar, L.BranchNbr) + L.BranchSatId,
LoNbr = ISNULL(L.LONbr, 9999),
LoName = ISNULL(LO.LOName, ' - NOT ASSIGNED -') ,
LoEmploymentStatus = LO.EmploymentStatus,
LeadCount = 1,
Completed = CASE WHEN L.RecCompleted = 'C' THEN 1 ELSE 0 END,
InCompleted = CASE WHEN L.RecCompleted = 'I' THEN 1 ELSE 0 END ,
NoContact = CASE WHEN LD.Disposition = 16 THEN 1 ELSE 0 END,
Contacted = CASE WHEN LD.Disposition <> 16 THEN 1 ELSE 0 END,
Pending = CASE WHEN LD.Disposition IS NULL THEN 1 ELSE 0 END,
NoSale = CASE WHEN LD.Disposition <> 1 THEN 1 ELSE 0 END,
Apps1003 = CASE WHEN LD.Disposition = 1 THEN 1 ELSE 0 END ,
AppraisalsOrdered = CASE WHEN ISNULL(T3._@8034, 0) = 0 THEN 0 ELSE 1 END,
ClosedApps= CASE WHEN (@UseCTD = 1 AND SMT2._H0770 IS NOT NULL) OR SMT._H0770 IS NOT NULL THEN 1 ELSE 0 END,
LoanVolume = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(T1._@2026, 0) ELSE ISNULL(T1._@2026, 0) END,
OrigLoanBalance = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2._H0360, 0) ELSE ISNULL(SMT._H0360, 0) END,
Revenue = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2.calc_TotalIncomeDollars, 0) ELSE ISNULL(SMT.calc_TotalIncomeDollars, 0) END,
MarketingCost = CASE WHEN L.VendorId IN (59, 60) THEN 0 ELSE ISNULL(S.Cost, 0) END,
CAM.LeadTypeId,
SMT.calc_TotalIncomeDollars,
SP.SubjectAddrState,
SP.SubjectSCF,
S.LeadFormLabel
FROM MLS..tbl_MLS_Leads L (NOLOCK)
INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId)
INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId)
INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId)
INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr)
INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId)
LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr)
LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN)
LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr)
LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr)
LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM)
LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM)
LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId)
LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId)
LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID)
LEFT JOIN (
SELECT
LONbr,
LOName = FullName,
EmploymentStatus,
ReHireDate,
HireDate,
TermDate
FROM MLS..vw_MLS_UsersWithLONbrs (NOLOCK)
) LO ON (LO.LONbr = L.LONbr)
LEFT JOIN MLS..tbl_MLS_Campaigns CAM (NOLOCK) ON (CAM.CampaignId = SG.CampaignId)
LEFT JOIN MLS..tbl_MLS_Vendors VEN (NOLOCK) ON (VEN.VendorId = L.VendorId)
WHERE L.DateReceived BETWEEN @BeginDate AND @EndDate + ' 23:59:59'
AND (CASE WHEN @ReportGroupId IS NULL THEN 1 WHEN @ReportGroupId = RG.ReportGroupId THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchTranType IS NULL THEN 1 WHEN @BranchTranType = B2.BranchTranType THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchDivision IS NULL THEN 1 WHEN @BranchDivision = B2.BranchDivision THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchRegion IS NULL THEN 1 WHEN @BranchRegion = B2.BranchRegion THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchNbr IS NULL THEN 1 WHEN @BranchNbr = L.BranchNbr THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchSatId IS NULL THEN 1 WHEN @BranchSatId = L.BranchSatId THEN 1 ELSE 0 END) = 1
AND L.ProviderId = L.OriginalProviderId
AND L.VendorId NOT IN (59, 60, 131) -- Exclude Turndown or Ghost leads
AND L.Deleted = 0
I need to add the field DateReceived from the following view:
dbo.vw_MLS_Leads
How should I code this?
As always thanks for the great answers and suggestions in advance.
Have a great day!
Kurt
View 8 Replies
View Related
Apr 15, 2008
Hello,I have a stored procedure: -- Get an individual league match by IDALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]( @LeagueMatchID int)ASSET NOCOUNT ONSELECT * FROM mb_LeagueMatch WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchIDThe mb_LeagueMatch table has a column named IsActive that is a bit datatype.The value for all rows is set to true (in database explorer in visual studio 2005).When I execute the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.Has anyone seen this before?Thanks,Howard
View 4 Replies
View Related
Apr 19, 2005
How can i create a stored procedure that count or sum value of field
e.g.
f1 f2
f3 f4 f5
record 1
1 2
3 1
and get answer like this 1=4 - 2=1 - 3=1
View 1 Replies
View Related
Apr 16, 2004
I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.
Thanks,
Larry
View 5 Replies
View Related
Dec 28, 2007
I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:
CREATE PROCEDURE sp_UpdateProductsTable
@prodName varchar(50),
@prodManufacturer varchar(50),
@prodShortDescrip text,
@prodLongDescrip text,
@prodCatId int,
@prodPrice varchar(6),
@prodSortOrder int,
@prodActive int,
@prodId int
AS
if( @prodId <> 0 )
begin
update Products set
Name = @prodName,
Manufacturer = @prodManufacturer,
ShortDescription = @prodShortDescrip,
LongDescription = @prodLongDescrip,
CategoryID = @prodCatId,
Price = @prodPrice,
SortOrder = @prodSortOrder,
Active = @prodActive
where ID = @prodId;
select ID as prodId from Products where ID = @prodId
end
if( @prodId = 0 )
begin
insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID, SortOrder, Active )
values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip, @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
SELECT SCOPE_IDENTITY() AS prodId
end
GO
I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.
Thanks in advance for your help.
View 8 Replies
View Related
Jul 23, 2005
I have this stored procedure that loops through a table and updates acouple of fields. For some reason one of the fields is not beingupdated. If I run the same code from query analyzer, it works fine.Let me know if anyone can figure out why @lastscandate would ever beNULL. If it is null it should be equal to @maildate. The senerio thatseems to fail is when no records are returned from the select statementto fill in @lastscandate. This should then active the next ifstatement and set the @lastscandate equal to the @maildate. MailDateis always filled in in the database and LastScanDate will be NULL.Thanks for your help.DECLARE c1 CURSOR LOCAL FORSELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid= 0) AND (m.maildate ='' OR m.maildate IS NULL)AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycleAND p.status NOT IN ('BM', 'PM'))OPEN c1FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycleWHILE @@fetch_status = 0BEGIN--Get MailDate from Manifest - if NULL then use GetDateset @maildate = NULLSELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycleif @maildate is NULLset @maildate = getdate()--Get Last Scan Date from Transactions - if NULL then use MailDateset @lastscandate = NULLselect @lastscandate=max(actiondate) from transactions whereacctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303if @lastscandate is NULLset @lastscandate = @maildateBEGIN TRANSACTIONUPDATE master SET printstatus = 'ML', maildate = @maildate,lastscandate=@lastscandateWHERE id = @midINSERT INTO transactions (initials, actionid, machinelogin, acctno,ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,@cycle, 'Update Mail Dates')COMMIT TRANSACTIONFETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycleENDCLOSE c1
View 1 Replies
View Related
Dec 14, 2006
How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies
View Related
Apr 24, 2007
Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
View 2 Replies
View Related
Jan 7, 2004
Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.
Thank you,
View 5 Replies
View Related
May 4, 2003
Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
View 3 Replies
View Related
Mar 19, 2001
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
Thanks!
View 1 Replies
View Related
Oct 27, 1998
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
View 2 Replies
View Related
Oct 24, 2007
Hello everybody,
i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how
to get a pointer to the data and how to read from it.
But i want to copy the whole data.
Does anybody know how to do that?
Thanks in advance
A.Gempp
-------------------------------
I'm using SQL Server 2000
View 4 Replies
View Related
May 15, 2008
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
My stored procedure is the following:
CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
View 2 Replies
View Related
Sep 25, 2005
i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
View 1 Replies
View Related
Nov 29, 2011
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code:
SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate
FROM dbo.Applicants
WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr)
ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
View 3 Replies
View Related
Jul 20, 2005
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies
View Related
Jul 20, 2005
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
View 1 Replies
View Related
Jun 16, 2006
Can someone walk me through the code for my update_command event?Every article I read and every tutorial I walk through has a slightly different way of doing this task.It's confusing trying to understand which code-behind variables I need in my update_command event and how to pass them to a stored procedure.
Please help me connect the dots.
I have a SQL server table that looks like this (Both data types are char)
Status_Id Status_DescriptionA ActiveP Planned
I have a SQL stored procedure that looks like this…
create procedure dbo.usp_Update_Status_Master(@status_id char(1),@status_description char(30))asupdate status_masterset status_description = @status_descriptionwhere status_id = @status_idGO
Here is my code behind…
Imports SystemImports System.DataImports System.Data.SqlClientImports System.ConfigurationImports System.Data.OdbcPublic Class WebForm1 Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then Call LoadStatusMasterGrid() End If End Sub Public Sub LoadStatusMasterGrid() Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("sqlConn")) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("usp_Select_Status_Master", connection) Command.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(Command) Dim table As DataTable = New DataTable adapter.Fill(table) dgStatusMaster.DataSource = table dgStatusMaster.DataKeyField = "status_id" dgStatusMaster.DataBind() Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub Private Sub dgStatusMaster_EditCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.EditCommand dgStatusMaster.EditItemIndex = e.Item.ItemIndex dgStatusMaster.DataBind() Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_CancelCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.CancelCommand dgStatusMaster.EditItemIndex = -1 Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_UpdateCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.UpdateCommand ‘ How do I code this part? End SubEnd Class
Thanks in advance for taking the time.Tim
View 1 Replies
View Related
Feb 18, 2008
hi
i have 2 columns linked ( 1 to a textbox and the other to drop down list)
when i try to update i get 1 of the to the update SP but not the other and get this error
Procedure or Function 'Update_ActiveCity' expects parameter '@Cities', which was not supplied.
_________________ this is the code of the aspx ____________
<asp:GridView ID="grdD" runat="server" AutoGenerateColumns="False" DataKeyNames="CountryCode" DataSourceID="dsGrdD" OnRowDataBound="grdD_RowDataBound"><Columns><asp:TemplateField><ItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Bind("Country") %>' />
<asp:DropDownList ID="ddlCities" runat="server" />
</ItemTemplate></asp:TemplateField></Columns></asp:GridView>
<asp:SqlDataSource ID="dsGrdD" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
SelectCommand="Select_Cities" SelectCommandType="StoredProcedure" UpdateCommand="'Update_ActiveCity' " UpdateCommandType="StoredProcedure" CacheExpirationPolicy="Sliding">
<SelectParameters> <asp:SessionParameter Name="ListCode" SessionField="ListCode" Type="String" /> </SelectParameters>
</asp:SqlDataSource>
_______________________ this is the code behind ____________________protected void grdD_RowDataBound(object sender, GridViewRowEventArgs e)
{DropDownList ddl = e.Row.FindControl("ddlCities") as DropDownList;if (ddl != null)
{
string s = DataBinder.Eval(((GridViewRow)e.Row).DataItem, "Cities").ToString();ddl.DataSource = s.Split(',');
ddl.DataBind();
}
}
_______________________________________________________________________________-
View 9 Replies
View Related
May 3, 2008
here is the procedureALTER PROCEDURE dbo.UpdateContact
(@ContactId bigint,@FirstName nvarchar(50),
@LastName nvarchar(50),@Telephone nvarchar(50),
@Addressline nvarchar(150),@State nvarchar(100),
@City nvarchar(100),@PostalCode varchar(50),
@Email nvarchar(50),
@MobilePhone varchar(50))
AS
SET NOCOUNT ON
UPDATE ContactSET FirstName = @FirstName,
LastName = @LastName,
Telephone = @Telephone,
MobilePhone = @MobilePhone,
Email = @Email,
Addressline = @Addressline,
City = @City,
State = @State,
PostalCode = @PostalCodeWHERE ContactId = @ContactId
RETURN
what is the problem if i execute the storedprocedure separately it is working but when i call the storedprocedure in the code it fails.
It gives an error as "syntax error near Updatecontact"
Any ideas???
View 1 Replies
View Related
May 27, 2004
I'm sorry for asking a lot of questions, but it is driving me crazy that I can't figure out how to do this update..
Let say I got two tables, both with an "ID".
TBL 1 TBL 2
ID <--> ID
STATUS
If they match, I want to update the status on tbl1 as "matched"
How would I preform this with a stored procedure??
I am currently using a view and then updating the view (I KNOW ITS BAD!)
View 4 Replies
View Related
Apr 22, 2005
I have an Update stored procedure that I am trying to update in the query analyzer to make sure it works, because it is not working from .NET.
Here is the stored procedure:
CREATE PROCEDURE Update_Homeowner (@TransactionID int, @DealerID varchar (50), @FirstName varchar(50), @LastName varchar(50), @Add1 varchar(50), @Add2 varchar(50), @City varchar(50), @State varchar(50), @Zip varchar(50))
AS UPDATE Homeowner
SET @DealerID=DealerID, @FirstName=FirstName,@LastName=LastName,@Add1=Add1,@Add2=Add2,@City=City,@State=State,@Zip=Zip
WHERE TransactionID = @TransactionID
GO
Here is how I am calling it in the Query Analyzer:
Update_Homeowner 47,'VT125313','test','tests','barb','','test','mo','23423'
It will not update, but I get the message (1 row(s) affected).
Any ideas???Thanks,Barb Cox
View 4 Replies
View Related
Aug 4, 2005
What am I doing wrong in this code:<CODE>Select Results.custIDFrom Results If (Results.custID = DRCMGO.custID)Begin Update Results SET Results.DRCMGO = 'Y'ENDELSEBegin Update Results SET Results.DRCMGO = 'N'END<CODE>I'm trying to do an IF / ELSE statement:-- if the custIDs in my Results table and my DRCMGO table match then I want to set DRCMGO to Y-- if they don't match I want to set it to NWhat is wrong with this syntax. If someone could let me know i would greatly appriciate it (I'm doing it as SQL Books Online is telling me to) Thanks in advance everyone. RB
View 1 Replies
View Related
Nov 30, 2005
Trying to Get this to work correctly...I Only want the latest(meaning most recent) entry of the Name(Column) Database = ProductsTest2To be also entered into Name(Column) Database = LocationOutsideUSABut When I run the code below it updates all fields that are contained in the entire [Name(Column)] of Database = LocationOutsideUSA with the same data entered.Thanks Inadvance...____________________________________________________________________________________UPDATE LocationOutsideUSASET Name = ProductsTest2.NameFROM
ProductsTest2SELECT MAX(Name) AS MaxName FROM ProductsTest2WHERE ProductsTest2.UID = ProductsTest2.UID
View 3 Replies
View Related
May 16, 2002
Is it possible to update a temporary file inside a stored procedure from calling another procedure. I am trying to leverage a sp that does a custom pricing routine and want to call it from another sp like so, the second procedure returns a set of records, very simular to a select.
UPDATE #tb_items
SET price = T1.sellprice, freight = T1.freight
FROM (usp_pricecalculator '700', '', '', '', '("B354-20")' ,'1') T1
WHERE #tb_items.itnbr = T1.itnbr
I have also looked into calling this sp into a cursor and updating in a loop on the cursor and had no luck
please help, much appreciation
JIM
View 1 Replies
View Related
Jan 7, 2005
I'm updating a record using the following SP:
CREATE PROCEDURE dbo.Sp_Del_Req_Record
(
@abrID int,
@logl_del_dt datetime,
@phys_del_dt datetime
)
AS
UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_LOGL_DEL_DT = @logl_del_dt,
ABR_DETLS_PHYS_DEL_DT = @phys_del_dt
WHERE ABR_DETLS_ID = @abrID
GO
I have the following command code:
Dim Sp_Del_Req_Record__abrID
Sp_Del_Req_Record__abrID = ""
if(Request("AlloFundID") <> "") then Sp_Del_Req_Record__abrID = Request("AlloFundID")
Dim Sp_Del_Req_Record__logl_del_dt
Sp_Del_Req_Record__logl_del_dt = ""
if(Now() <> "") then Sp_Del_Req_Record__logl_del_dt = Now()
Dim Sp_Del_Req_Record__phys_del_dt
Sp_Del_Req_Record__phys_del_dt = ""
if(Now() <> "") then Sp_Del_Req_Record__phys_del_dt = Now()
%>
<%
set Sp_Del_Req_Record = Server.CreateObject("ADODB.Command")
Sp_Del_Req_Record.ActiveConnection = MM_DBConn_STRING
Sp_Del_Req_Record.CommandText = "dbo.Sp_Del_Req_Record"
Sp_Del_Req_Record.CommandType = 4
Sp_Del_Req_Record.CommandTimeout = 0
Sp_Del_Req_Record.Prepared = true
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@RETURN_VALUE", 3, 4)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@abrID", 3, 1,4,Sp_Del_Req_Record__abrID)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@logl_del_dt", 135, 1,8,Sp_Del_Req_Record__logl_del_dt)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@phys_del_dt", 135, 1,8,Sp_Del_Req_Record__phys_del_dt)
Sp_Del_Req_Record.Execute()
%>
I get a wrong data type error thrown at the following line:
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@abrID", 3, 1,4,Sp_Del_Req_Record__abrID)
I'm not sure where I am going wrong.
Any help is appreciated. Thanks.
-D-
View 3 Replies
View Related
