Stored Procedure Update Problem

Dec 3, 2003

I have a webpage (language is c#) that is being used to add, update and delete information stored in a database. The database uses a date field and a location field as the primary key value so that only one entry can be made for each day for any particular location. The problem I am having is that when the user clicks submit the information should either be updated or added for the particular day and location. It works fine when I am adding a new entry but if an entry already exists it will not update the information. The delete button also works properly. I am passing parameter values from asp:textboxes to a stored procedure when the user clicks on the submit button. In the procedure I am determining whether or not there is already information for that location and date, if there is the information should be updated, if not the information should be inserted. Here is the sample code that I am using right now, if anyone could please explain why the insert statement works fine but the update won't I would greatly appreciate it.





Stored Procedure





CREATE PROCEDURE uSP_AddSRT





@SRTdate DateTime = null,


@Location VarChar(30) =null,


@TruckNonWarWorked Float = null,


@TruckNonWarBilled Float = null,


@TruckWarWorked Float = null,


@TruckWarBilled Float = null,


@ServiceNonWarWorked Float = null,


@ServiceNonWarBilled Float = null,


@ServiceWarWorked Float = null,


@ServiceWarBilled Float = null,


@FtlWorked Float = null,


@FtlBilled Float = null,


@DetroitWorked Float = null,


@DetroitBilled Float = null,


@CatWorked Float = null,


@CatBilled Float = null,


@CumminsWorked Float = null,


@CumminsBilled Float = null,


@EatonWorked Float = null,


@EatonBilled Float = null,


@MeritorWorked Float = null,


@MeritorBilled Float = null,


@MercWorked Float = null,


@MercBilled Float = null,


@FccWorked Float = null,


@FccBilled Float = null,


@DriveLineWorked Float = null,


@DriveLineBilled Float = null,


@Comments Text = null


AS


If EXISTS(SELECT * FROM dbo.tblSRT WHERE dbo.tblSRT.Location = @Location AND dbo.tblSRT.SRTdate = @SRTdate)





begin


UPDATE dbo.tblSRT


SET


TruckNonWarWorked = @TruckNonWarWorked,


TruckNonWarBilled = @TruckNonWarBilled,


TruckWarWorked = @TruckWarWorked,


TruckWarBilled = @TruckWarBilled,


ServiceNonWarWorked = ServiceNonWarWorked,


ServiceNonWarBilled = @ServiceNonWarBilled,


ServiceWarWorked = @ServiceWarWorked,


ServiceWarBilled = @ServiceWarBilled,


FtlWorked = @FtlWorked,


FtlBilled = @FtlBilled,


DetroitWorked = @DetroitWorked,


DetroitBilled = @DetroitBilled,


CatWorked = @CatWorked,


CatBilled = @CatBilled,


CumminsWorked =@CumminsWorked,


CumminsBilled = @CumminsBilled,


EatonWorked = @EatonWorked,


EatonBilled = @EatonBilled,


MeritorWorked = @MeritorWorked,


MeritorBilled = @MeritorBilled,


MercWorked = @MercWorked,


MercBilled = @MercBilled,


FccWorked = @FccWorked,


FccBilled = @FccBilled,


DriveLineWorked = @DriveLineWorked,


DriveLineBilled = @DriveLineBilled,


Comments = @Comments





WHERE Location = @Location AND SRTdate = @SRTdate





End





Else





begin





INSERT INTO dbo.tblSRT(SRTdate,Location,TruckNonWarWorked,TruckNonWarBilled,TruckWarWorked,TruckWarBilled,ServiceNonWarWorked,ServiceNonWarBilled,


ServiceWarWorked,ServiceWarBilled,FtlWorked,FtlBilled,DetroitWorked,DetroitBilled,CatWorked,CatBilled,CumminsWorked,


CumminsBilled,EatonWorked,EatonBilled,MeritorWorked,MeritorBilled,MercWorked,MercBilled,FccWorked,FccBilled,DriveLineWorked,DriveLineBilled,Comments)


VALUES(@SRTdate,@Location,@TruckNonWarWorked,@TruckNonWarBilled,@TruckWarWorked,


@TruckWarBilled,@ServiceNonWarWorked,@ServiceNonWarBilled,@ServiceWarWorked,@ServiceWarBilled,@FtlWorked,@FtlBilled,@DetroitWorked,


@DetroitBilled,@CatWorked,@CatBilled,@CumminsWorked,@CumminsBilled,@EatonWorked,@EatonBilled,@MeritorWorked,@MeritorBilled,@MercWorked,@MercBilled,@FccWorked,@FccBilled,@DriveLineWorked,@DriveLineBilled,@Comments)





End


GO











AddSRT.aspx





//now create the connection, the sql statement, and the sql parameters


SqlConnection connection = new SqlConnection("server=localhost; uid=sa; pwd=; Database=SRTworkTime");


SqlCommand command = new SqlCommand ("uSP_AddSRT", connection);


command.CommandType = CommandType.StoredProcedure;





command.Parameters.Add("@SRTdate",SqlDbType.DateTime);


command.Parameters[0].Value = Date.Value;





command.Parameters.Add("@Location",SqlDbType.VarChar,30);


command.Parameters[1].Value = Location.Value;





command.Parameters.Add("@TruckNonWarWorked",SqlDbType.Float);


if(txtTruckNonWarWorked.Text != "" && txtTruckNonWarWorked.Text != null){


command.Parameters[2].Value = txtTruckNonWarWorked.Text;


}


else{


command.Parameters[2].Value = null;


}





command.Parameters.Add("@TruckNonWarBilled",SqlDbType.Float);


if(txtTruckNonWarBilled.Text != "" && txtTruckNonWarBilled.Text != null){


command.Parameters[3].Value = txtTruckNonWarBilled.Text;


}


else{


command.Parameters[3].Value = null;


}





command.Parameters.Add("@TruckWarWorked",SqlDbType.Float);


if(txtTruckWarWorked.Text != "" && txtTruckWarWorked.Text != null){


command.Parameters[4].Value = txtTruckWarWorked.Text;


}


else{


command.Parameters[4].Value = null;


}





command.Parameters.Add("@TruckWarBilled",SqlDbType.Float);


if(txtTruckWarBilled.Text != "" && txtTruckWarBilled.Text != null){


command.Parameters[5].Value = txtTruckWarBilled.Text;


}


else{


command.Parameters[5].Value = null;


}





command.Parameters.Add("@ServiceNonWarWorked",SqlDbType.Float);


if(txtServiceNonWarWorked.Text != "" && txtServiceNonWarWorked.Text != null){


command.Parameters[6].Value = txtServiceNonWarWorked.Text;


}


else{


command.Parameters[6].Value = null;


}





command.Parameters.Add("@ServiceNonWarBilled",SqlDbType.Float);


if(txtServiceNonWarBilled.Text != "" && txtServiceNonWarBilled.Text != null){


command.Parameters[7].Value = txtServiceNonWarBilled.Text;


}


else{


command.Parameters[7].Value = null;


}





command.Parameters.Add("@ServiceWarWorked",SqlDbType.Float);


if(txtServiceWarWorked.Text != "" && txtServiceWarWorked.Text != null){


command.Parameters[8].Value = txtServiceWarWorked.Text;


}


else{


command.Parameters[8].Value = null;


}





command.Parameters.Add("@ServiceWarBilled",SqlDbType.Float);


if(txtServiceWarBilled.Text != "" && txtServiceWarBilled.Text != null){


command.Parameters[9].Value = txtServiceWarBilled.Text;


}


else{


command.Parameters[9].Value = null;


}





command.Parameters.Add("@FtlWorked",SqlDbType.Float);


if(txtFtlWorked.Text != "" && txtFtlWorked.Text != null){


command.Parameters[10].Value = txtFtlWorked.Text;


}


else{


command.Parameters[10].Value = null;


}





command.Parameters.Add("@FtlBilled",SqlDbType.Float);


if(txtFtlBilled.Text != "" && txtFtlBilled.Text != null){


command.Parameters[11].Value = txtFtlBilled.Text;


}


else{


command.Parameters[11].Value = null;


}





command.Parameters.Add("@DetroitWorked",SqlDbType.Float);


if(txtDetroitWorked.Text != "" && txtDetroitWorked.Text != null){


command.Parameters[12].Value = txtDetroitWorked.Text;


}


else{


command.Parameters[12].Value = null;


}


command.Parameters.Add("@DetroitBilled",SqlDbType.Float);


if(txtDetroitBilled.Text != "" && txtDetroitBilled.Text != null){


command.Parameters[13].Value = txtDetroitBilled.Text;


}


else{


command.Parameters[13].Value = null;


}





command.Parameters.Add("@CatWorked",SqlDbType.Float);


if(txtCatWorked.Text != "" && txtCatWorked.Text != null){


command.Parameters[14].Value = txtCatWorked.Text;


}


else{


command.Parameters[14].Value = null;


}





command.Parameters.Add("@CatBilled",SqlDbType.Float);


if(txtCatBilled.Text != "" && txtCatBilled.Text != null){


command.Parameters[15].Value = txtCatBilled.Text;


}


else{


command.Parameters[15].Value = null;


}





command.Parameters.Add("@CumminsWorked",SqlDbType.Float);


if(txtCumminsWorked.Text != "" && txtCumminsWorked.Text != null){


command.Parameters[16].Value = txtCumminsWorked.Text;


}


else{


command.Parameters[16].Value = null;


}





command.Parameters.Add("@CumminsBilled",SqlDbType.Float);


if(txtCumminsBilled.Text != "" && txtCumminsBilled.Text != null){


command.Parameters[17].Value = txtCumminsBilled.Text;


}


else{


command.Parameters[17].Value = null;


}





command.Parameters.Add("@EatonWorked",SqlDbType.Float);


if(txtEatonWorked.Text != "" && txtEatonWorked.Text != null){


command.Parameters[18].Value = txtEatonWorked.Text;


}


else{


command.Parameters[18].Value = null;


}





command.Parameters.Add("@EatonBilled",SqlDbType.Float);


if(txtEatonBilled.Text != "" && txtEatonBilled.Text != null){


command.Parameters[19].Value = txtEatonBilled.Text;


}


else{


command.Parameters[19].Value = null;


}





command.Parameters.Add("@MeritorWorked",SqlDbType.Float);


if(txtMeritorWorked.Text != "" && txtMeritorWorked.Text != null){


command.Parameters[20].Value = txtMeritorWorked.Text;


}


else{


command.Parameters[20].Value = null;


}





command.Parameters.Add("@MeritorBilled",SqlDbType.Float);


if(txtMeritorBilled.Text != "" && txtMeritorBilled.Text != null){


command.Parameters[21].Value = txtMeritorBilled.Text;


}


else{


command.Parameters[21].Value = null;


}





command.Parameters.Add("@MercWorked",SqlDbType.Float);


if(txtMercWorked.Text != "" && txtMercWorked.Text != null){


command.Parameters[22].Value = txtMercWorked.Text;


}


else{


command.Parameters[22].Value = null;


}





command.Parameters.Add("@MercBilled",SqlDbType.Float);


if(txtMercBilled.Text != "" && txtMercBilled.Text != null){


command.Parameters[23].Value = txtMercBilled.Text;


}


else{


command.Parameters[23].Value = null;


}





command.Parameters.Add("@FccWorked",SqlDbType.Float);


if(txtFccWorked.Text != "" && txtFccWorked.Text != null){


command.Parameters[24].Value = txtFccWorked.Text;


}


else{


command.Parameters[24].Value = null;


}





command.Parameters.Add("@FccBilled",SqlDbType.Float);


if(txtFccBilled.Text != "" && txtFccBilled.Text != null){


command.Parameters[25].Value = txtFccBilled.Text;


}


else{


command.Parameters[25].Value = null;


}





command.Parameters.Add("@DriveLineWorked",SqlDbType.Float);


if(txtDriveLineWorked.Text != "" && txtDriveLineWorked.Text != null){


command.Parameters[26].Value = txtDriveLineWorked.Text;


}


else{


command.Parameters[26].Value = null;


}





command.Parameters.Add("@DriveLineBilled",SqlDbType.Float);


if(txtDriveLineBilled.Text != "" && txtDriveLineBilled.Text != null){


command.Parameters[27].Value = txtDriveLineBilled.Text;


}


else{


command.Parameters[27].Value = null;


}





command.Parameters.Add("@Comments",SqlDbType.Text);


if(txtComments.Text != "" && txtComments.Text != null){


command.Parameters[28].Value = txtComments.Text;


}


else{


command.Parameters[28].Value = null;


}





//open the connection and execute the query


connection.Open();


command.ExecuteNonQuery();


connection.Close();





Response.Redirect("AddSRTConfirm.aspx");


Thanks,


Brian Nicoloff

View 3 Replies


ADVERTISEMENT

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

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

Stored Procedure - Update Statement Does Not Seem To Update Straight Away

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

Need Help With A SQL Update Stored Procedure

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

Update To Stored Procedure

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

Update Stored Procedure

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

Stored Procedure Update..

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

Update Stored Procedure HELP

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

IF / ELSE -- Update Stored Procedure

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

Update Stored Procedure Help!!!

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

Update From Stored Procedure

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

Update Stored Procedure

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

Update Stored Procedure

Jan 22, 2006

I need to create a stored procedure that will update, an insert will try to insert an entire row and I am only trying to update old data with new data. For instance if I move all the 99 terms from the active table to the term table and lets say for example their [hiredate], [ID], [firstname], but after the update is done I realize I forgot to include the [lastname] field, see what I mean??? Or I just wanted to UPDATE old data with new data?? Would this stored procedure work


CREATE PROCEDURE [InsertTerms]
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION]
(ReasonTerminated)

SELECT a.DESCRIPTION
FROM DesireeTerm3
WHERE TERIMINATION.TM#= DesireeTerm3.Employee
RETURN
GO

View 14 Replies View Related

UPDATE Stored Procedure

May 9, 2007

In this stored procedure I attempt to update the AGE and SERV_AGE fields with the days difference between a date parameter and a List Date and Service Date in any of a number of tables we have that contain these two fields. I attempt to pass in the date parameter and the table name to use.
=====================================
CREATE PROCEDURE up_UpdateAcctAge
@strTableName nvarchar(50),
@dteWeekDate datetime

AS
UPDATE @strTableName
SET AGE = DATEDIFF(D, ASSIGN_DT, CONVERT(DATETIME, @dteWeekDate, 102)),
SERV_AGE = DATEDIFF(D, SERV_DT, CONVERT(DATETIME,@dteWeekDate, 102))
GO
=========================================

Any idea why I am getting the following message when I check syntax?

Server: Msg 137, Level 15, State 2, Procedure up_UpdateAcctAge, Line 7
Must declare the variable '@strTableName'.

Any help you can give this novice is appreciated.

View 4 Replies View Related

Update Stored Procedure Help

Jan 2, 2008

I have been given the task to create a stored procedure that will update employee's leave balances. My part of the task was just to create the stored procedure within my sql server 2005 database that the programmer can use each time leave balances change in the current leave system. There are 4 types of leave balances: Annual Leave, Sick Leave, Family Leave, and Other Leave. The problem I am having is that I was told that all 4 values will not be passed each time, so my stored procedure needs to allow empty/Null values to be entered for some fields without overwriting the existing data. Also any errors should not cause issues with the current leave system, I am guessing if an error occurs then I need an email sent to me stating the issue and if successful then no message.

I have written this stored procedure:


CREATE PROCEDURE [dbo].[usp_updateuser]

@Emp_SSN int,

@Annual_Forward decimal(10,2),

@Sick_Forward decimal(10,2),

@Family_Forward decimal(10,2),

@Other_Forward decimal(10,2)

AS

UPDATE OT_MAIN

SET

EmpAnnual_Forward = @Annual_Forward,

EmpSick_Forward = @Sick_Forward,

EmpFamily_Forward = @Family_Forward,

EmpOther_Forward = @Other_Forward

WHERE

Emp_SSN=@Emp_SSN


I can execute the procedure using exec and then passing the 4 variables, but I don't know how to do the errors and messages and the allow null values without over writing.

Any help would be greatly appreciated. I am really new to sql server, and expecially new to stored procedures.

Thank you.

View 3 Replies View Related

Stored Procedure Update Problem

Dec 7, 2006

HI
 
I WRITTEN STORED PROCEDUE UPDATE.
isuppose in my table there are 2 rows belongs to same tr id.
trid     city
1     aaaa
1    bbb
 
 
i want result like
 
trid    city
1      ttttt
1    jjjjj
 
but i m getting result like
 
 
trid  city
1   jjjj
1  jjjj
i wnat to update city individually.
like i will change 1 st row city as 'ttttt'
and 2 nd row city as 'jjjjjj'
for paticular tr id.
 
i ma getting problem like its updating city for  2 rows for that particular tr id .
i wnat to update city individually.
please help me.
 
thanks
swapprose.
 

View 1 Replies View Related

Insert Or Update With Stored Procedure

Dec 27, 2006

I'm doing this more as a learning exercise than anything else.  I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key.  If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr

View 5 Replies View Related

Stored Procedure To Update Ntext Value In SQL

Jul 4, 2007

Hi
My SP to update a value type ntext don't work,  ALTER PROCEDURE UpdateMultiContentFullDescriptionByID
(@ContentID int,
@FullDescription ntext)
AS
UPDATE MultifunctionalContent
SET FullDescription = @FullDescription
WHERE ContentID = @ContentID
RETURNPublic Sub UpdateMultiContentFullDescriptionByID(ByVal ContentID As String, ByVal FullDescription As String)
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(Const.ConnectionString)
Dim myCommand As SqlCommand = New SqlCommand("UpdateMultiContentFullDescriptionByID", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add an input parameter and supply a value for it
myCommand.Parameters.Add("@ContentID", SqlDbType.Int, 4)
myCommand.Parameters("@ContentID").Value = ContentID

' Add an input parameter and supply a value for it
myCommand.Parameters.Add("@FullDescription", SqlDbType.NText, 8000)
myCommand.Parameters("@FullDescription").Value = FullDescription

' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub Then how to ...

View 2 Replies View Related

Stored Procedure - Update (Succesful?)

Dec 11, 2007

I wrote a stored procedure to update a record.  It works just fine.But in my VB.Code how do I prompt myself if the Update was unsuccessful?i.e. In my SQL statement of my Store Procedure, if I pass an Invalid parameter PassOCN, I want my user to know the update was not successful.  The parameter string does not exist in my table to update. VB code: Protected Sub Button13_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Update OCN designation in tblPortCarriersList ' Define database connection
Dim connect As String = "Server=localhostSqlExpress;" & _ "Database=lnp;Integrated Security=True" Using conn As New SqlConnection(connect) 'Open the connection
conn.Open()

' Using Store Procedure
Dim passOCN As String Dim updateOCN As String
passOCN = "1236"
updateOCN = "2287"

Dim Update As New SqlCommand() Update.Connection = conn Update.CommandType = Data.CommandType.StoredProcedure Update.CommandText = "sp_UpdateOcnTblPortCarriers"
Update.Parameters.Add("@UpdateOCN", System.Data.SqlDbType.NVarChar, 4) Update.Parameters.Add("@PassOCN", System.Data.SqlDbType.NVarChar, 4) Update.Parameters("@UpdateOCN").Value = updateOCN Update.Parameters("@PassOCN").Value = passOCN Update.ExecuteNonQuery() End Using usersLabel.Text = "Finished Updating OCN tblPortCarrier"

End Sub Stored Procedure: USE [lnp]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_UpdateOcnTblPortCarriers] -- Add the parameters for the stored procedure here@UpdateOCN NVarChar(4),@PassOCN NVarChar(4)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Update statements for procedure hereUPDATE [dbo].tblPortCarriers SET [CarrierPortOCN] = @UpdateOCN Where [CarrierPortOCN] = @PassOCNENDThanks in advance for your assistance. 

View 8 Replies View Related

Update Stored Procedure Not Updating

Apr 12, 2008

Hello,I'm working on a grant management database for a project in my databases class and I'm having some issues updating grants into the database.Here is my situation:How this page works is, it gets a query string from a search_grant.aspx page.  In this query string, it gets the grant ID of the grant the user wants to edit.  If the grant id is a valid grant, it then, upon page_load in C#:1.) Creates an Sql connection set to a viewGrant stored procedure,2.) Adds in all the necessary parameters as output variables3.) Sets private members declared inside of the partial class to those values it gets from the stored procedure4.) Sets textbox controls on the page to those values5.) Displays the page with all the populated data from the stored procedureThat part works fine.  I was having an issue where clicking the update button would not grab the new values that the user input into the textboxes.  I later realized that the Page_Load code was being re-executed BEFORE the button was being clicked (kind of dumb but...whatever).  To fix it, I placed all of the code to do the above statements inside of a: if (!Page.IsPostBack){ // Do code here}That works fine.  The problem, however, is that it's STILL not updating.  The stored procedure works just fine inside of the management studio, but not in the ASP Page.  The code is similar to that of my new_grant.aspx page, which creates a grant into the database.  Why that works and this doesn't, I don't know.  Even when I hard code the values into the parameters in C#, it's not updating the data!  There are no errors that are being returned, so this has really boggled my mind.Any help is greatly appreciated! Here is some sample code of what I'm doing:protected void Update_button_Click(object sender, EventArgs e){ // Create SQL connection to update Grant string ConnectionString = "connection string which works fine"; SqlConnection sqlConnection2 = new SqlConnection(); try { sqlConnection2.ConnectionString = ConnectionString; sqlConnection2.Open(); } catch (Exception Ex) { if (sqlConnection2 != null) { sqlConnection2.Dispose(); } SQLErrorLabel.Text = Ex.Message; SQLErrorLabel.Visible = true; return; } // ------------------ Update values into database ------------------- // Create the statement to use on the database SqlCommand editGrant = new SqlCommand("editGrant", sqlConnection2); editGrant.CommandType = CommandType.StoredProcedure; editGrant.Connection = sqlConnection2; // Set our values for each variable GrantName = GrantName_input.Text; ProjectDescription = ProjDesBox.Text; ReportingYear = Int32.Parse(ReportYearBox.SelectedItem.ToString()); ActivityStarted = Activity_Date.Text; DateSubmitted = Date_Submitted.Text; Audit = chkAudit.Checked; TypeID = Type_ID_input.SelectedValue; FunderID = Funder_List.SelectedValue; StatusID = Status_ID_input.SelectedValue; AcademicDepartmentID = AcademicID_List.SelectedValue; PIID = PI_List.SelectedValue; ContractNumber = txtContractNum.Text; ESUAccountNumber = txtESUAccountNum.Text; AmountAwarded = txtAmount.Text; AwardDate = Award_Date.Text; DateContractSigned = txtDateSigned.Text; ReportingNotes = ReportingNotesbox.Text; NotesNotes = GrantNotesbox.Text; string ReportingTimestamp = DateTime.Now.ToString(); string ReportingWho = Membership.GetUser().ToString(); string NotesTimestamp = DateTime.Now.ToString(); string NotesWho = Membership.GetUser().ToString(); #region insertParams // Add our parameters that SQL will be using editGrant.Parameters.AddWithValue("@GrantID", GID); editGrant.Parameters["@GrantID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@GrantName", GrantName); editGrant.Parameters["@GrantName"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ProjectDescription", ProjectDescription); editGrant.Parameters["@ProjectDescription"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingYear", ReportingYear); editGrant.Parameters["@ReportingYear"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ActivityStarted", ActivityStarted); editGrant.Parameters["@ActivityStarted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateSubmitted", DateSubmitted); editGrant.Parameters["@DateSubmitted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@Audit", Audit); editGrant.Parameters["@Audit"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@TypeID", TypeID); editGrant.Parameters["@TypeID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@StatusID", StatusID); editGrant.Parameters["@StatusID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@FunderID", FunderID); editGrant.Parameters["@FunderID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AcademicDepartmentID", AcademicDepartmentID); editGrant.Parameters["@AcademicDepartmentID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@PIID", PIID); editGrant.Parameters["@PIID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ContractNumber", ContractNumber); editGrant.Parameters["@ContractNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ESUAccountNumber", ESUAccountNumber); editGrant.Parameters["@ESUAccountNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AmountAwarded", AmountAwarded); editGrant.Parameters["@AmountAwarded"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AwardDate", AwardDate); editGrant.Parameters["@AwardDate"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateContractSigned", DateContractSigned); editGrant.Parameters["@DateContractSigned"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingNotes", ReportingNotes); editGrant.Parameters["@ReportingNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingTimestamp", ReportingTimestamp); editGrant.Parameters["@ReportingTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingWho", ReportingWho); editGrant.Parameters["@ReportingWho"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesNotes", NotesNotes); editGrant.Parameters["@NotesNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesTimestamp", NotesTimestamp); editGrant.Parameters["@NotesTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesWho", NotesWho); editGrant.Parameters["@NotesWho"].Direction = ParameterDirection.Input; #endregion // Execute the UPDATE statement to Grant editGrant.ExecuteNonQuery(); sqlConnection2.Close(); sqlConnection2.Dispose(); Response.Redirect("editedGrant.aspx?id=" + GrantIDBox.Text);}EDIT: Problem Solved! Problem was the that the GrantID wasn't being properly set.

View 6 Replies View Related

Stored Procedure Update Problem

Apr 30, 2008

Hey guys.I have a problem with the an update stored procedure.It seems the code is executing and there are no errors, but there record in the database that should be updating isn't having any information changed. My vb.net code is thus:  'Create a New Connection for the Stored Procedure
Dim myConnection As New Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
myConnection.Open()
Dim myCommand As New Data.SqlClient.SqlCommand("UPDATE_FORM", myConnection)
myCommand.CommandType = Data.CommandType.StoredProcedure
Dim case_link_id = CType(Me.FormView1.FindControl("label1"), Label).Text
Dim case_link_id_number = CInt(case_link_id)
'Add the rest of the insert parameters

myCommand.Parameters.AddWithValue("Form_Case_Link_ID", case_link_id_number)

If ((CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text <> "")) Then
myCommand.Parameters.AddWithValue("Returned_Date", Convert.ToDateTime(CType(Me.FormView1.FindControl("Returned_DateTextBox"), TextBox).Text))

Else : myCommand.Parameters.AddWithValue("Returned_Date", DBNull.Value)

End If

myCommand.Parameters.AddWithValue("Na", CType(Me.FormView1.FindControl("NA"), CheckBox).Checked)

myCommand.Parameters.AddWithValue("Notes", CType(Me.FormView1.FindControl("NotesTextBox"), TextBox).Text)

'Execute the command
myCommand.ExecuteNonQuery()

myConnection.Close()

Response.Redirect("~/success.aspx")   and my stored procedure looks like this:  ALTER PROCEDURE [dbo].[UPDATE_FORM]
@form_case_link_id as int,
@Returned_Date as datetime,
@NA as bit,
@Notes as varchar(500)

as UPDATE tbl_Forms_Case_Link

SET

returned_date = @returned_date,
na = @na,
notes = @notes

WHERE form_case_link_id = @form_case_link_id  Can anyone help me with this? Many thanks.DS

View 4 Replies View Related

Stored Procedure Update Table

Jan 20, 2004

Hi,

I've got a stored procedure which should update a table (add new customer record)
When I run it locally everythings fine,

Since uploading it all to the web it no longer seems to add a new record,
I've debugged it and it seems that the output parameters is set to nothing.

I believe it's a permissions issue but the user i'm using has full access to both the table
and permission to execute the stored procedure is there any error handling I can
do to capture the exact error? the code I use to execute the sProc is below

thanks for any help

Dave



Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

' Calculate the New CustNo using Output Param from SPROC
Dim custNo As Integer = CInt(parameterCustNo.Value)

Return custNo.ToString()
Catch
<---This is where it's dropping in can I put any
Error handling in to show me the error?
Return String.Empty
End Try

View 3 Replies View Related

Update/Insert Stored Procedure

Nov 16, 2004

I am trying to take some SQL queries written by Visual Studio, one insert and one update and combine them into a single stored procedure. The insert procedure should be included in the update procedure and a check should be done for an existing record based upon the primary key. If it exist, an update command should be performed, else an insert. I also need to wrap the procedure in a transaction and rollback if any errors have occurred, else commit the transaction. If I have the following Insert and Update statements, can anyone help me write the stored procedure I need? Again, the current statements were automatically created and could be modified as needed.

INSERT INTO tblClub(ClubPKID, ClubName) VALUES (@ClubPKID, @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @@IDENTITY)

UPDATE tblClub SET ClubPKID = @ClubPKID, ClubName = @ClubName WHERE (ClubPKID = @Original_ClubPKID) AND (ClubName = @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @ClubPKID)

Thanks!

View 2 Replies View Related

Update Row Using Stored Procedure With Timestamp

Oct 10, 2005

Hi,I'm attempting to update one row using strored procedure which check timestamp field.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'tbCharakterystyki_Update')BEGINDROP Procedure tbCharakterystyki_UpdateENDGO
CREATE Procedure dbo.tbCharakterystyki_Update@CH_ID int ,@CH_CHARAKTERYSTYKA VARCHAR(30),@CH_OPIS VARCHAR(300),@CH_TIMESTAMP timestampAS
UPDATE tbCharakterystyki SET ch_charakterystyka = @CH_CHARAKTERYSTYKA,
ch_opis = @CH_OPIS
WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP
GO
Function which call stored procedure:public int CallSP(DataRow dr)    {      try       {             SqlConnection conn = new SqlConnection(this.strConnection);        SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;         cmd.CommandType = CommandType.StoredProcedure;         cmd.CommandText = "dbo.tbCharakterystyki_Update";
        SqlParameter p1 = new SqlParameter();        p1.ParameterName = "@CH_ID";        p1.SqlDbType = SqlDbType.Int;        p1.Value = dr[0];
        SqlParameter p2 = new SqlParameter();        p2.ParameterName = "@CH_CHARAKTERYSTYKA";        p2.SqlDbType = SqlDbType.VarChar;        p2.Value = dr[1];
        SqlParameter p3 = new SqlParameter();        p3.ParameterName = "@CH_OPIS";        p3.SqlDbType = SqlDbType.VarChar;        p3.Value = dr[2];
        SqlParameter p4 = new SqlParameter();        p4.ParameterName = "@CH_TIMESTAMP";        p4.SqlDbType = SqlDbType.VarChar;        p4.Value = dr[3];
        cmd.Parameters.Add(p1);        cmd.Parameters.Add(p2);        cmd.Parameters.Add(p3);        cmd.Parameters.Add(p4);                cmd.Connection.Open();        cmd.ExecuteNonQuery();        cmd.Connection.Close();        return 0;          }      catch ( Exception err )       {         ShowError(err.Message)                return null;      }     }
And function which call "CallSP()":private void Update(){DataSet ds = helper.GiveDataSetCharakterystyki()DataRow dr = ds.Tables[0].Select("ch_id=51")[0];
dr[1] = "new value";CallSP(dr);}When I fire Update(), stored procedure (dbo.tbCharakterystyki_Update) is not started, Sql Profiler doesn't show it.Whilst when I modify stored procedure, removing WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP and put only WHERE ch_id = @CH_ID , it works perfectly.What I did wrong?
 

View 1 Replies View Related

UPDATE Within An IF Statement Of A Stored Procedure

Feb 10, 2006

Hi

I want to run a stored procedure which updates a password.  I
have the a table 'users' which has columns 'name' (as primary key) and
'pwd', which hold the username and password respectively.  The
stored procedure accepts @username (the username), @pwdOld and @pwdNew
(the old and new passwords).

The following procedure returns 1 if the user inputs the correct old password.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword1 " & _
                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _
                  ") " & _
                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _
                  "SELECT " & _
                  "@name = name, " & _
                 
"@actualPassword = pwd " & _
                  "FROM users " & _
                 
"WHERE name = @username " & _
                 
"If @name is not null " & _
                 
"  If @pwdOld = @actualPassword " & _
                 
"    Return 1 " & _
                  "  Else " & _
                 
"  Return -1 " & _
                  "Else " & _
                  "Return -1"


The following procedure updates the old password to the new password.  So job done.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword2 " & _
                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _
                  ") " & _
                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _
                 
"    UPDATE users " & _
                 
"    SET pwd = @pwdNew " & _
                 
"    WHERE name = @userName "

But using two procedures must be inefficient and slow.  However, I
have not been able to combine the two.  I would have thought I
should be able to replace "return 1" in the first procedure with the
UPDATE statement in the second procedure but I cannot save this
procedure.
i.e.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword1 " & _

                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _

                  ") " & _

                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _

                  "SELECT " & _

                  "@name = name, " & _
                 
"@actualPassword = pwd " & _

                  "FROM users " & _
                 
"WHERE name = @username " & _
                 
"If @name is not null " & _
                 
"  If @pwdOld = @actualPassword " & _
                 
"    UPDATE users " & _
                 
"    SET pwd = @pwdNew " & _
                 
"    WHERE name = @userName "
                 
"    Return 1 " & _

                  "  Else " & _
                 
"  Return -1 " & _

                  "Else " & _

                  "Return -1"

Any ideas please?
Thanks in advance

Mike

View 4 Replies View Related

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 View Related

Batch Update In Stored Procedure

Feb 26, 2004

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?


CREATE PROCEDURE dbo.MySP
@userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@userIDs)

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF

View 8 Replies View Related

Stored Procedure For Update Statement

Sep 24, 2013

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

Test CodeTest Name Normal Value
6 Blood Urea 20 - 45
12 HBA1C Glycoslated Haemoglobin4.0 - 6.0
86 Serum Creatinine 0.7 - 1.2
147 Fasting Blood Sugar 60 - 100  
292 POST PRANDIAL BLOOD SUGAR 5 - 150

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Patient_Id, Test_ID,Test_Result,and Test_Status Columns.

Patient IdTest CodeResultStatus
27924 6 51NULL
27924 12 5.5NULL
27924 86 0.9NULL
27924 147 55NULL
27924 292 59NULL

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column for multiple rows in Transaction Table.

If my Test_Result value is less than Low Value of Normal_Values then i need to Update Test_Status as L and
if Test_Result value is greater than High Value of Normal_Values then i need to Update Test_Status as H and
if the Test_Result Value is in between Low and High Value of Normal_Values then i need to update a blank space

How to do this in sql server?

View 4 Replies View Related

Update YtdExpenses With A Stored Procedure

Oct 16, 2006

I'm trying to update the ytdExpenses from the monthly Expenses. I'm submitting to the SQL database a currency amount everymonth and need for the ytdExpenses to be calculated from the sum of the monthly expenses so that I can display it on a form. I've tried everything and still can't figure this out. Will I need a stored procedure or a trigger? I want to place the update ytdExpense total at the top of my database so when I open a new for the form picks up the top most value and displays it in the form. Also, as soon as someone submit to the database the ytdExpense should calculate and show up on the form right then. My database looks like this

ReportID   ReportDate   monthlyExpense   ytdExpenses

4 Â Â  Â Â Â Â Â Â Â Â Â 10/12/06 Â Â Â Â Â Â Â Â Â Â Â $1000 Â Â Â Â Â Â Â Â Â Â Â Â $10,000
3              11/12/06             $2000             $9,000             
2              12/12/06             $3000              $7,000
1              1/12/06              $4000              $4,000





I just want to add the monthly expenses up and display them in the ytdExpenses Column at the top everytime someone submit tho the database. That way the form picks up the ytdExpenses at the top of the database everytime. Here is what I got so far...Thanks ahead of time....

create trigger tu_trigger_name on yourtable for update
as
begin
update t
set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk
end

View 20 Replies View Related

Stored Procedure To Update A Table(s)

Jun 20, 2007

Hi,

Does anyone know if it's possible to pass the table name to a stored procedure so that it can update a value in the table named.

I need to have one stored procedure which will update data in any table.

If I have stored procedure called UpdateTable, which takes 5 parameters @TableName, @PrimaryKeyName, @PrimaryKeyValue, @ColumnName, @ColumnValue

Then I would have something like

UPDATE @TableName
Set @ColumnName = @ColumnValue
Where @PrimaryKeyName = @PrimaryKeyValue

Cheers

Rohan

View 3 Replies View Related

Insert/update In One Stored Procedure

Jul 30, 2007

Hi:

I am trying to write a stored procedure that when passed a name, value it will

1. update the record if the name exists
2. create a new record if it doesnt

how would i do this?

View 5 Replies View Related

Stored Procedure To Update Columns

Sep 2, 2006

Hello,I've written an insert trigger to fill in data on 5 columns based on the keyfield column after a record is added to a table. The trigger works fine.But what I also want to do is to write a stored procedure that will updatethe 5 columns for the entire table based on the table key field column. I'mnew to both triggers and stored procedures and I can't figure out how tomake a stored procedure do what I want.Can someone help me to get me started? Here is the trigger code that I amtrying to get to work as a stored procedure. All I'm trying to do is tobreak up an entry that contains dashes into separate fields that aredelimited by the dashes. As an example if field CABLENO is equal toI-IJB-200-45, then I want to break that up into 4 separate fields containingI, IJB, 200, 45 respectively.Thanks,Al Willis--------------------------------------------------------------------------------------------------------IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CABLE_SEGMENTS' AND TYPE='TR')DROP TRIGGER IM.CABLE_SEGMENTSGOCREATE TRIGGER CABLE_SEGMENTSON IM.CAB_MFOR INSERTASDECLARE @CABLENO_REMAIN VARCHAR(40),@DASH_POS SMALLINT,@SEG1 VARCHAR(40),@SEG2 VARCHAR(40),@SEG3 VARCHAR(40),@SEG4 VARCHAR(40),@SEG5 VARCHAR(40)SELECT @CABLENO_REMAIN = CABLENO FROM INSERTEDSELECT @SEG1 = NULLSELECT @SEG2 = NULLSELECT @SEG3 = NULLSELECT @SEG4 = NULLSELECT @SEG5 = NULLSELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG1 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG2 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG3 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG4 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @SEG5 = @CABLENO_REMAINENDELSESELECT @SEG4 = @CABLENO_REMAINENDELSESELECT @SEG3 = @CABLENO_REMAINENDELSESELECT @SEG2 = @CABLENO_REMAINENDELSESELECT @SEG1 = @CABLENO_REMAINUPDATE IM.CAB_M SET CABLESEG1 = @SEG1,CABLESEG2 = @SEG2,CABLESEG3 =@SEG3,CABLESEG4 = @SEG4,CABLESEG5 = @SEG5FROM INSERTED INSWHERE IM.CAB_M.CABLENO = INS.CABLENOGO--------------------------------------------------------------------------------------------------------

View 2 Replies View Related

Stored Procedure - INSERT/UPDATE

Aug 27, 2007

HI,

I€™m trying to do a Stored Procedure (SP) that stores data from a form in ASP.NET.
If the record does not exist, it will create a new record first.

This is my first SP, so if best practise is done another way, I would like to hear about it.

Code:




Code Snippet
CREATE PROCEDURE dbo.StorUserRecord

/* some of these are for future use */
@UpdateMetode Int,
@UserIndex Int,
@UserFirstName NVarChar,
@UserLastName NVarChar,
@UserPassword NVarChar,
@UserPrivateMail NVarChar,
@UserCompanyMail NVarChar,
@UserTitle Int,
@UserSecLevel NVarChar,
@UserAddressLine1 NVarChar,
@UserAddressLine2 NVarChar,
@UserZipCode Int,
@UserPrivateMobilNumber NVarChar,
@UserCompanyMobilNumber NVarChar,
@UserHomeNumber NVarChar,
@UserCompanyDirectNumber NVarChar,
@UserCPR NVarChar,
@UserWorkerID NVarChar,
@UserDepartment Int,
@UserActive Bit,
@UserAccess Bit,
@UserDeleted Bit
AS

BEGIN


DECLARE @UseThisUserIndex Int;
IF NOT @UserIndex > 0

/* Create new record and return the "auto id" from UserIndex */
BEGIN

INSERT INTO [User] (UserFirstName) VALUES (@UserFirstName)

OUTPUT INSERTED.UserIndex

INTO @UseThisUserIndex
GO
END
ELSE

/* Use the "auto id" from the @UserIndex */
BEGIN

@UseThisUserIndex = @UserIndex
END

END
RETURN





I'm getting this error:


Msg 102, Level 15, State 1, Procedure StorUserRecord, Line 33

Incorrect syntax near 'OUTPUT'.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'ELSE'.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved