My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.
Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK
I want to use the returned table name and update the set eh FK's value = null.
Some how it is not working , can some one point me out on correct solution.
I created one stored procedure to update the date difference in the table . in this table i have dt1,dt2,dt3... column and diff1,diff2... I wanted to find the difference between dt2 and dt1, and dt4 and dt3 and put it in separate column.
When I compiled the stored procedure, it did not show any error. But when i execute, it shows the error:
Conversion failed when converting datetime from character string.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER procedure [dbo].[autopost1] as begin declare inner int
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN SET NOCOUNT ON; WITH Darin_Import_With_Key AS ( SELECT [pra_id] ,[pra_ClientPracID]
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
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
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?
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
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 ____________
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???
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
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
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
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
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()
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-
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
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'.
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.
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.
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
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 ...
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.
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.
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
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))
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
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.
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;
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)
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?
