UPDATE Not Updating

May 23, 2005

Hello all,

I've been working on a stored procedure that updates values I have
stored in a table on a SQL Server 2000 db and I'm not sure what kind of
problem I'm having.  I pass the stored procedure the required
parameters, run it, and everything goes fine until I check the db and
no updates have been done.  I've gone back and checked the return
value of command.ExecuteNonQuery and it's '0' so it's not indicating
that anything went wrong.  I've tried updating values that are
already present values that were previously 'Null' and I get the same
result everytime, no update.  Any suggestions?  Below is my
stored procedure


SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO


ALTER        PROCEDURE dbo.UpdateApplication


    (

@1 varchar(9),

@2 varchar(50),

@2_DBA varchar(50),

@3 varchar(25),

@3_other varchar(150),

@4_street1 varchar(100),

@4_state varchar(2),

@4_street2 varchar(100),

@4_zip varchar(10),

@4_city varchar(50),

@5_street1 varchar(100),

@5_state varchar(2),

@5_street2 varchar(100),

@5_zip varchar(10),

@5_city varchar(50),

@6 bit,

@6_web varchar(50),

@7 varchar(15),

@7_percent smallint,

@8 varchar(30),

@8_other varchar(100),

@9 varchar(50),

@9_street1 varchar(100),

@9_state varchar(2),

@9_street2 varchar(100),

@9_zip varchar(10),

@9_city varchar(50),

@10 varchar(50),

@10_interest varchar(50),

@10_street1 varchar(100),

@10_state varchar(2),

@10_street2 varchar(100),

@10_zip varchar(10),

@10_city varchar(50),

@11 varchar(50),

@11_interest varchar(50),

@11_street1 varchar(100),

@11_state varchar(2),

@11_street2 varchar(100),

@11_zip varchar(10),

@11_city varchar(50),

@12_Name varchar(50),

@12_Number varchar(15),

@13_Name varchar(50),

@13_Number varchar(15),

@14_years smallint,

@14_months smallint,

@14_man_years smallint,

@14_man_months smallint,

@15_years smallint,

@15_months smallint,

@15_stories smallint,

@16 bit,

@17 bit,

@18 bit,

@19_company varchar(50),

@19_premium money,

@20 bit,

@20_explain text,

@21_sqft int,

@21_apl_sqft int,

@21_apt_sqft int,

@21_units int,

@21_lro_sqft int,

@22_mon_thurs_o varchar(7),

@22_mon_thurs_c varchar(7),

@22_fri_o varchar(7),

@22_fri_c varchar(7),

@22_sat_o varchar(7),

@22_sat_c varchar(7),

@22_sun_o varchar(7),

@22_sun_c varchar(7),

@23 bit,

@23a_date varchar(10),

@23a_type varchar(50),

@23a_paid money,

@23a_reserved money,

@23a_open bit,

@23b_date varchar(10),

@23b_type varchar(50),

@23b_paid money,

@23b_reserved money,

@23b_open bit,

@23c_date varchar(10),

@23c_type varchar(50),

@23c_paid money,

@23c_reserved money,

@23c_open bit,

@23d_date varchar(10),

@23d_type varchar(50),

@23d_paid money,

@23d_reserved money,

@23d_open bit,

@24 bit,

@25 bit,

@26 bit,

@27 bit,

@28 bit,

@29 bit,

@30 bit,

@31 bit,

@32 bit,

@33 bit,

@34 bit,

@35 bit,

@36 bit,

@37 bit,

@38 bit,

@39 bit,

@39_explain text,

@40 bit,

@40_num smallint,

@40_types varchar(50),

@41 varchar(100),

@42a bit,

@42a_num smallint,

@42a_types varchar(100),

@42a_members smallint,

@42a_others varchar(50),

@42b bit,

@42c_sqft smallint,

@42c_week smallint,

@42d bit,

@42e bit,

@42f bit,

@42f_explain varchar(50),

@42g bit,

@42g_explain varchar(50),

@43a bit,

@43b bit,

@43c bit,

@43d bit,

@43e bit,

@43f bit,

@44 bit,

@44a bit,

@44a_states varchar(150),

@44b smallint,

@44c smallint,

@44d varchar(25),

@44d_explain varchar(50),

@45_food_p money,

@45_food_n money,

@45_alch_p money,

@45_alch_n money,

@45_alch_tr_p money,

@45_alch_tr_n money,

@45_cc_p money,

@45_cc_n money,

@45_opc_p money,

@45_opc_n money,

@45_other varchar(50),

@45_other_p money,

@45_other_n money,

@45_total_p money,

@45_total_n money,

@46a_am smallint,

@46a_am_three smallint,

@46b_ff money,

@46b_mf money,

@46c varchar(50),

@47a bit,

@47b bit,

@47c bit,

@47d smallint,

@48_gen money,

@48_fire money,

@48_prod money,

@48_med money,

@48_eac money,

@48_pers money,

@49 bit,

@49_months varchar(100),

@50 bit,

@50_when varchar(50),

@51 bit,

@51_when varchar(50),

@52 bit,

@53 bit,

@54 bit,

@55 varchar(10),

@56_roof smallint,

@56_elec smallint,

@56_plum smallint,

@56_heat smallint,

@57 bit,

@58 bit,

@58_percent smallint,

@59 varchar(20),

@60 varchar(45),

@61 bit,

@61a varchar(100),

@61a_other varchar(50),

@61a_veg bit,

@61b bit,

@61c bit,

@61c_clean varchar(50),

@61c_date varchar(10),

@61d varchar(3),

@62a varchar(6),

@62b varchar(4),

@62c_value varchar(20),

@62c money,

@62d_build money,

@62d_coins smallint,

@62e_con money,

@62e_coins smallint,

@62f_bus money,

@62f_coins smallint,

@62f varchar(3),

@62g varchar(50),

@63a_ins money,

@63a_out money,

@63b_ins money,

@63b_out money,

@63c money,

@63d smallint,

@63e bit,

@Record int

)


AS

   

UPDATE Application


SET                 

[1]=@1 ,

[2]=@2 ,

[2_DBA]=@2_DBA ,

[3]=@3 ,

[3_other]=@3_other ,

[4_street1]=@4_street1 ,

[4_street2]=@4_street2 ,

[4_city]=@4_city ,

[4_state]=@4_state ,

[4_zip]=@4_zip ,

[5_street1]=@5_street1 ,

[5_street2]=@5_street2 ,

[5_city]=@5_city ,

[5_state]=@5_state ,

[5_zip]=@5_zip ,

=@6 ,

[6_web]=@6_web ,

[7]=@7 ,

[7_percent]=@7_percent ,

=@8,

[8_other]=@8_other ,

[9]=@9 ,

[9_street1]=@9_street1 ,

[9_street2]=@9_street2 ,

[9_city]=@9_city ,

[9_state]=@9_state ,

[9_zip]=@9_zip ,

[10]=@10 ,

[10_interest]=@10_interest ,

[10_street1]=@10_street1 ,

[10_street2]=@10_street2 ,

[10_city]=@10_city ,

[10_state]=@10_state ,

[10_zip]=@10_zip ,

[11]=@11 ,

[11_interest]=@11_interest ,

[11_street1]=@11_street1 ,

[11_street2]=@11_street2 ,

[11_city]=@11_city , [11_state]=@11_state , [11_zip]=@11_zip , [12_Name]=@12_Name , [12_Number]=@12_Number ,

[13_Name]=@13_Name , [13_Number]=@13_Number , [14_years]=@14_years , [14_months]=@14_months , [14_man_years]=@14_man_years ,

[14_man_months]=@14_man_months , [15_years]=@15_years , [15_months]=@15_months , [15_stories]=@15_stories ,

[16]=@16 , [17]=@17 , [18]=@18 , [19_company]=@19_company , [19_premium]=@19_premium , [20]=@20 , [21_sqft]=@21_sqft ,

[21_apl_sqft]=@21_apl_sqft , [21_apt_sqft]=@21_apt_sqft , [21_units]=@21_units , [21_lro_sqft]=@21_lro_sqft ,

[22_mon_thurs_o]=@22_mon_thurs_o , [22_fri_o]=@22_fri_o , [22_sat_o]=@22_sat_o , [22_sun_o]=@22_sun_o ,

[22_mon_thurs_c]=@22_mon_thurs_c , [22_fri_c]=@22_fri_c , [22_sat_c]=@22_sat_c , [22_sun_c]=@22_sun_c ,

[23]=@23 , [23a_date]=@23a_date , [23a_type]=@23a_type , [23a_paid]=@23a_paid , [23a_reserved]=@23a_reserved ,

[23a_open]=@23a_open , [23b_date]=@23b_date , [23b_type]=@23b_type , [23b_paid]=@23b_paid , [23b_reserved]=@23b_reserved ,

[23b_open]=@23b_open , [23c_date]=@23c_date , [23c_type]=@23c_type , [23c_paid]=@23c_paid , [23c_reserved]=@23c_reserved ,

[23c_open]=@23c_open , [23d_date]=@23d_date , [23d_type]=@23d_type , [23d_paid]=@23d_paid , [23d_reserved]=@23d_reserved ,

[23d_open]=@23d_open , [24]=@24 , [25]=@25 , [26]=@26 , [27]=@27 , [28]=@28 , [29]=@29 , [30]=@30 , [31]=@31 ,

[32]=@32 , [33]=@33 , [34]=@34 , [35]=@35 , [36]=@36 , [37]=@37 , [38]=@38 , [39]=@39 , [39_explain]=@39_explain ,

[40]=@40 , [40_num]=@40_num , [40_types]=@40_types , [41]=@41 , [42a]=@42a , [42a_num]=@42a_num , [42a_types]=@42a_types ,

[42a_others]=@42a_others , [42a_members]=@42a_members , [42b]=@42b , [42c_sqft]=@42c_sqft , [42c_week]=@42c_week ,

[42d]=@42d , [42e]=@42e , [42f]=@42f , [42f_explain]=@42f_explain , [42g]=@42g , [42g_explain]=@42g_explain ,

[43a]=@43a , [43b]=@43b , [43c]=@43c , [43d]=@43d , [43e]=@43e , [43f]=@43f , [44]=@44 , [44a]=@44a ,

[44a_states]=@44a_states , [44b]=@44b , [44c]=@44c , [44d]=@44d , [44d_explain]=@44d_explain , [45_food_p]=@45_food_p ,

[45_food_n]=@45_food_n , [45_alch_p]=@45_alch_p , [45_alch_n]=@45_alch_n , [45_alch_tr_p]=@45_alch_tr_p ,

[45_alch_tr_n]=@45_alch_tr_n , [45_cc_p]=@45_cc_p , [45_cc_n]=@45_cc_n ,[45_opc_p]=@45_opc_p ,

[45_opc_n]=@45_opc_n , [45_other]=@45_other , [45_other_p]=@45_other_p , [45_other_n]=@45_other_n ,

[45_total_p]=@45_total_p , [45_total_n]=@45_total_n , [46a_am]=@46a_am , [46a_am_three]=@46a_am_three ,

[46b_ff]=@46b_ff , [46b_mf]=@46b_mf , [46c]=@46c , [47a]=@47a , [47b]=@47b , [47c]=@47c , [47d]=@47d , [48_gen]=@48_gen ,

[48_fire]=@48_fire , [48_prod]=@48_prod , [48_med]=@48_med , [48_eac]=@48_eac , [48_pers]=@48_pers , [49]=@49 ,

[49_months]=@49_months , [50]=@50 , [50_when]=@50_when , [51]=@51 , [51_when]=@51_when , [52]=@52 , [53]=@53 ,

[54]=@54 , [55]=@55 , [56_roof]=@56_roof , [56_elec]=@56_elec , [56_plum]=@56_plum , [56_heat]=@56_heat ,

[57]=@57 , [58]=@58 , [58_percent]=@58_percent , [59]=@59 , [60]=@60 , [61]=@61 , [61a]=@61a , [61a_other]=@61a_other ,

[61a_veg]=@61a_veg , [61b]=@61b , [61c]=@61c , [61c_clean]=@61c_clean , [61c_date]=@61c_date ,

[61d]=@61d , [62a]=@62a , [62b]=@62b , [62c]=@62c , [62c_value]=@62c_value , [62d_build]=@62d_build ,

[62d_coins]=@62d_coins , [62e_con]=@62e_con , [62e_coins]=@62e_coins , [62f]=@62f , [62f_bus]=@62f_bus ,

[62f_coins]=@62f_coins , [62g]=@62g , [63a_ins]=@63a_ins , [63a_out]=@63a_out , [63b_ins]=@63b_ins ,

[63b_out]=@63b_out , [63c]=@63c , [63d]=@63d , [63e]=@63e , [20_explain]=@20_explain


WHERE [Record] = @Record


GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

View 11 Replies


ADVERTISEMENT

Update Not Updating

Dec 27, 2005

Basically I'm trying to use these new  2.0 features but not having success although the thing doesn't break. 
The command .Update() appears to work but nothing is changed.
The code elements are below:
<code>
<!-- ************************************************************************ -->
Sub Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs)Try      SpecificJOB.Update()Catch except As Exception      Label1.Text = except.ToStringEnd Try
      Label1.Text = "The record was updated successfully!"End Sub
<!-- ************************************************************************ -->
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"  DataSourceID="SpecificJOB"OnItemUpdating="Update" >
<Fields>
<asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
<asp:TemplateField  HeaderText="Job Details">

<EditItemTemplate>   JobID:<asp:Label ID="lbJobID" runat="server" Text='<%# Eval("JobID") %>'></asp:Label>
   Title:<asp:TextBox ID="txtTitle" runat="server" Text='<%# Bind("Title") %>' ></asp:TextBox><br />
   Type:<asp:DropDownList ID="ddTYPE" runat="server"                DataSourceID="JobTYPE" DataTextField="description"               DataValueField="jobTypeID" SelectedValue='<%# Bind("jobTypeID") %>'> </asp:DropDownList></EditItemTemplate>

</asp:TemplateField>
</Fields>
</asp:DetailsView>

<!-- ************************************************************************ -->

<asp:SqlDataSource ID="SpecificJOB" runat="server" ConnectionString="<%$ ConnectionStrings:DBConn %>" ProviderName="<%$ ConnectionStrings:DBConn.ProviderName %>"
SelectCommand="SELECT JobID, jobTypeID, .................WHERE (JobID = ?)"UpdateCommand="UPDATE [Jobs] SET [jobTypeID] = ?, [Title] = ? WHERE ([JobID] = ?)">

<SelectParameters><asp:ControlParameter ControlID="GridView1" DefaultValue="0" Name="?" PropertyName="SelectedValue" /></SelectParameters>



<UpdateParameters><asp:ControlParameter Name="ddTYPE" ControlId="DetailsView1" PropertyName="SelectedValue"/><asp:ControlParameter Name="txtTitle" ControlId="DetailsView1"/><asp:ControlParameter Name="lbJobID" ControlId="DetailsView1" /></UpdateParameters>

</asp:SqlDataSource>
</code>
I think a couple of days is easily long enough to try to figure out this problem.  Would love some assistance if it is obvious.  Thanks!
 
Geoff

View 1 Replies View Related

SQL Update Not Updating

Mar 21, 2008

Hi, I have a problem, it is that when I try to update a record in my SQL server database, it is not updated and I recieve no error messages. This is the code behind the update button. The stored procedure is "sqlupdate".





Code Snippet

Dim ListingID As String = Request.QueryString("id").ToString
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand("sqlupdate", con)
cmd.CommandType = CommandType.StoredProcedure
Dim id As SqlParameter = cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier)
id.Direction = ParameterDirection.Input
id.Value = ListingID

Dim PlaceName As SqlParameter = cmd.Parameters.Add("@PlaceName", SqlDbType.VarChar)
PlaceName.Direction = ParameterDirection.Input
PlaceName.Value = PlaceNameTB.Text

Dim Location As SqlParameter = cmd.Parameters.Add("@Location", SqlDbType.VarChar)
Location.Direction = ParameterDirection.Input
Location.Value = LocationTB.Text

Dim PropertyType As SqlParameter = cmd.Parameters.Add("@PropertyType", SqlDbType.VarChar)
PropertyType.Direction = ParameterDirection.Input
PropertyType.Value = PropertyTypeTB.Text

Dim Description As SqlParameter = cmd.Parameters.Add("@Description", SqlDbType.VarChar)
Description.Direction = ParameterDirection.Input
Description.Value = DescriptionTB.Text

Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception

End Try

View 5 Replies View Related

Update Statement Not Updating

Jun 26, 2006

I can't get my form to update my db.  Is there something wrong with the update statement?  I'm not getting any errors either.  I have two check Box Lists:
Dim strStep1 As String = ""Dim s As ListItemFor Each s In cblStep1.ItemsIf s.Selected ThenstrStep1 += s.Text + ", "End IfNext
Dim strStep2 As String = ""Dim s2 As ListItemFor Each s2 In cblStep2.ItemsIf s2.Selected ThenstrStep2 += s2.Text + ", "End IfNext
Dim ConnString As String = ConfigurationManager.AppSettings("ConnectionString")Dim Conn As New SqlConnection(ConnString)Dim CommandString As String = "UPDATE [tblAccess] SET [complete1] = ?, [complete2] = ?" & _"WHERE empName = @empName"
Dim Command As New SqlCommand(ConnString, Conn)Command.Parameters.AddWithValue("complete1", strStep1)Command.Parameters.AddWithValue("complete2", strStep2)
Any help is appreciated!

View 9 Replies View Related

Update Command Is Updating All My Records!

Feb 16, 2007

I wrote a sproc which does four things:
1.  It looks at an option master to see if the record exists before inserting a new one
2.  If the record is not there it inserts the optino record
3.  Once the record is inserted I have to run a CASE statement on the record to determine its level
4.  Once the level is determined, the record needs to be updated with the correct level.
1-3 work fine (when run without the update command).
However, even though I set a criteria, UPDATE still updates and not the one records.
Any idea why? set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]


@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit

AS

--check to see if the option record exists

IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)

BEGIN
SELECT ' This option already exists in your Option Master'
END
ELSE BEGIN

--if the option record option does not exist, insert it

INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )

SELECT ' Added to Option Master Successfully'
END

--once the option record is inserted, case it to find the its level (1-9)
--update the record with the approciate level.

UPDATE Optionmaster
SET optionlevel (

SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND
PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND
PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND
PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND
ElevationID > '0' THEN '1'
END AS OptionLevel --provides the option level required to update the record
FROM optionmaster
WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID))
--even through I specify the above criteria, it upates all records. 
 
 

View 9 Replies View Related

Coalesce Is Not Working And Update Is No Updating

Nov 21, 2007

My code worked a few weeks ago and has since stop working, reasons are totally not clear to me as to what happended.
However, I need to get this thing up and running.  It will not longer Coalesce data entry. Iran the debugger and the correct values are in the specified objects as if it is the first time I run the page for a person it will input data, but not of subsequent data entry attempts.
My code: ( I trully appreciate your help)
Ayo
'Using "With/End With" pass content to columns from text objects and datatime variables (see above)With cmdCommentUpdate
.Parameters.Add(New SqlClient.SqlParameter("@UserID", ddlEmployeeSuperCmt.SelectedValue)).Parameters.Add(New SqlClient.SqlParameter("@Today", bDate))
.Parameters.Add(New SqlClient.SqlParameter("@Comments", dtToday & " " & UCase(userNamedbInsert) & " " & txtComment.Text & " " & vbCrLf)).Parameters.Add(New SqlClient.SqlParameter("@CommenterLogon", UCase(userNamedbInsert)))
.Parameters.Add(New SqlClient.SqlParameter("@CommentDate", dtNow))
'Establish the type of commandy object
.CommandType = CommandType.Text
'Pass the Update nonquery statement to the commandText object previously instantiated.CommandText = "UPDATE ATTTble" & _
" SET Comments = COALESCE(Comments, '') + @Comments, CommenterLogon = @CommenterLogon, CommentDate = @CommentDate" & _
" WHERE (UserID = @UserID) AND (Today = '" & lblDate.Text & "') "
End With

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

Update Statement Not Updating All Records

Jul 27, 2012

I have the following code:

TRUNCATE TABLE [Temp_Export];
INSERT INTO [Temp_Export]
(

[Code]....

The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?

View 6 Replies View Related

~TableAdapter.Update Not Updating The Database

Apr 28, 2006

I am designing my first database (Visual Basic & SQL Server Express 2005) and it appears as if the database isn't updating. I am running it by hitting F5, closing by hitting the "X" and then hitting F5 again to check if the changes have stuck. They don't. These are the instructions given in the tutorial.

Any ideas? Thanks.

In particular I have tried two step-by-step tutorials distributed by Microsoft: 1. Absolute Beginner's Video Series, Lesson09; and 2. the Help tutorial: Managing Your Records (ms-help://MS.VSExpressCC.v80/MS.NETFramework.v20.en/dv_vbcnexpress/html/1ccdb8f1-4162-4a54-af17-231007eb529b.htm)

The code for the form is:

Public Class Form1


Private Sub AddressesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddressesBindingNavigatorSaveItem.Click


Me.Validate()

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing


Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


'TODO: This line of code loads data into the 'FirstDatabaseDataSet.Addresses' table. You can move, or remove it, as needed.

Me.AddressesTableAdapter.Fill(Me.FirstDatabaseDataSet.Addresses)

End Sub
End Class

View 3 Replies View Related

Updating Sql DB. My Code Compiles, But Doesn't Update.

Sep 15, 2006

C#, Webforms, VS 2005, SQL Hi all, quick hit question.  I'm trying to update a table with an employee name and hire date.  Session variable of empID, passed from a previous page (successfully) determines which row to plop the update into. It's not working even though i compiles and makes it all the way through the code to the txtReturned.Text = "I made it" debug line...Any thoughts?    1 string szInsSql;
2
3 string sConnectionString = "Data Source=dfssql;Database=MyDB;uid=myID;pwd=myPWD";
4 SqlConnection objConn = new SqlConnection(sConnectionString);
5
6 objConn.Open();
7
8 szInsSql = "UPDATE empEmployee SET " +
9 "Name = '" + this.txtName.Text + "', " +
10 "HireDate = '" + this.txtHireDate.Text + "', " +
11 "WHERE empID = '" + Session[empID] + "'";
12
13 SqlCommand objCmd1 = new SqlCommand(szInsSql, objConn);
14 objCmd1.ExecuteNonQuery();
15
16 txtReturned.Text = "I made it";
 It's got to be a ' or a , out of place but I've looked at this code for a half hour straight, trying a variety of changes...and it still doesn't update the DB...Any help would be great.  Thank you! -Corby- 

View 3 Replies View Related

Updating The Same Column Multiple Times In One Update Statement

Jul 23, 2005

I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0

View 8 Replies View Related

Why Is This SQL UPDATE Query Not Updating When This Code Is Used Under Button.click.

Mar 20, 2008

Why is this SQL UPDATE query not updating when this code is used under button.click.

Dim ListingID As String = Request.QueryString("id").ToString
Dim sqlupdate As String = "UPDATE Listings SET PlaceName = '" & PlaceName.Text & "', Location = '" & Location.SelectedValue & "', PropertyType = '" & PropertyType.SelectedValue & "', Description = '" & Description.Text & "', Price = '" & Price.Text & "' WHERE ListingID ='" & ListingID & "'"
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand(sqlupdate, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()

View 12 Replies View Related

SQL Parameter Update Not Updating Changed Null/blank Values

Oct 24, 2006

I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.

SqlConnection1.Open()

strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "

cmdCategoriesUpdate.CommandText = strSQLu

With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()

View 3 Replies View Related

Problem With Update When Updating All Rows Of A Table Through Dataset And Saving Back To Database

Feb 24, 2006

Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify  a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType = CommandType.Text;
snUpdate.CommandText = "Update TestTable set shipdate = @shipdate";
snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds, "Table1");
conn.Close();
 
-Thanks

View 4 Replies View Related

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.

Any suggestions?

View 7 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

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

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View 1 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Stored Proc Won't Update From C# .NET Code, But Will Update When Testing On Its Own.

Jul 23, 2006

I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
   SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
 The Stored Procedure:
  ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)

/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId

IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId

IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN 

View 2 Replies View Related

Update Function: Why SQL Server Update An Empty String With 0?

May 13, 2008

I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.

View 23 Replies View Related

Plz Help...update Value In Multiple Db Table Using Single 'update Command'

Mar 18, 2005

hi,friends

we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------

i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.

i want to update value in multiple database table(more than one) using single 'update command'

i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.

it's urgent.

thanks in advance.

View 2 Replies View Related

Seperate Date And Time Merge; Done At SQL Update Or C# .NET Then Update

Feb 18, 2006

I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar

View 1 Replies View Related

How To Create A Update Button To Update Two SqlDataSource Controls?

May 10, 2006

I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
   sqlDataSource1.Update();
   sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
 
Zhang

View 2 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

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

Update Never Finishes. Update Utilizes Indexes And Usually Takes 2 Min To Run.

Feb 8, 2008



Hello

I have interesting situation with one of my update statement.
Update takes 2 min to run and usually updates 20000 rows.

However lately update executes for longest time - 10 hours.
After I reindex table the updates run fine again

Please advice what can cause this
I do not think reindexing table before every update is good idea

We are running SQL 2000 SP4, Windows 2003 Server

Thank you in advance for all your help
Armine

View 17 Replies View Related

Update Query To Update Separate Chars

Mar 26, 2007

Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it.  1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';  

View 2 Replies View Related







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