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 ListItem
For Each s In cblStep1.Items
If s.Selected Then
strStep1 += s.Text + ", "
End If
Next

Dim strStep2 As String = ""
Dim s2 As ListItem
For Each s2 In cblStep2.Items
If s2.Selected Then
strStep2 += s2.Text + ", "
End If
Next

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


ADVERTISEMENT

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

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

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

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

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

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

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

T-SQL (SS2K8) :: Updating Statement Depending On 2 Fields

Sep 30, 2015

I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.

For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R

But my problem are That i can have Many combination on refkey, for example:

B,BC,BCR,C,CB,CBR,R...and so on.

Also i need to separate the letters with comma, like:

B
B,C
B,C,R
.....

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

~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

SQL DTS Query Using WHILE Statement Successfully Executes (only Updating 200 Of 1494 Records)

Jul 23, 2005

I am running a DTS Package.I have a temp table with 1494 records. I am inserting a 'Y' or'N'into a temp table #HasClaims.The TempTable name with the Provider Id's(PRPR_ID) is#TempFACETSNODupesThe @identityID is an identity field counting back from 1494 to 1I count back from the Max value of the identityid (1494) in the Whileloop until I get through all the records. The idea is to check for theexistance of a claims and authorization record and put a 'Y' or 'N'record in the temptable #HasClaims.This is running in the Execute SQL Task object of the DTS Package.The Package runs successfully but only inserts 200 rows into the newtemp table. There should be a row for each provider. Each time itruns, the number of rows it returns is different. Sometimes it is 205,then 185, then 210, before it completes the DTS package.Has anyone run into While looping problems within an Execute SQL taskin a DTS package(SQL 2000)like this--------------------------------------------------------------SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupesWhile @identityID >= 1BEGIN@PRPRID is the placeholder for the PRPR_ID (Provider)SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =@identityIDIF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERECLCL.PRPR_ID = @PRPRID)BEGININSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'Y', @identityID)ENDELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'N', @identityID)------------------------------------------------------------SELECT @identityID = @identityID - 1END

View 7 Replies View Related

Updating Trigger Problem : Trying To Replace The Values From The Upadate Statement

Apr 25, 2007

I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.



Here's my situation



tst_update

ID int

Col1 varchar(10



I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.



Can someone help.



Thanks,

Ashish

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

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

Update Statement

Aug 22, 2007

Dim lblock As Boolean           chkChecked = lblock        strSQL = "UPDATE CLIENTS SET "            If blnCompleted = True Then            strSQL = strSQL & "COMPLETED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', "            Else            strSQL = strSQL & "LAST_SAVED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', "            End If            strSQL = strSQL & "COMMENTS = '" & FixString(txtcomments.Text) & "' " _            & "WHERE client_ID = " & iclientID & ""I want to put my booleen value lblock to sql too, I probably need value of it, It is checkbox, called  chkblock, . how would I include this to update statement  database field for that  BLOCK =

View 1 Replies View Related

Help On Update Statement

Sep 7, 2007

Hi, i nid help on update statement. I using 03 and a microsoft sql server 2000 database.
I use a more simple example of my error. A Northwind Database is use to update the Region table(RegionDescription)
User will 1st go in WebForm2.aspx and enter a id, if found will retrieve the data to WebForm1.aspx. User type "1" and retrieve Eastern to  TextBox1.
User can choose to update the table by typing in a diff word into TextBox1. But when i type any word(e.g East) the page is refresh back to Webform1.aspx with the not updated data and the database is also not updated. Any idea? 
 
WebForm2.aspx.vb Imports System.Data.SqlClient Public Class WebForm2
Inherits System.Web.UI.PageWeb Form Designer Generated Code Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page hereEnd SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Session("id") = TextBox1.Text
Response.Redirect("WebForm1.aspx")End Sub
End Class
 
WebForm1.aspx.vb Imports System.Data.SqlClient Public Class WebForm1
Inherits System.Web.UI.Page
Web Form Designer Generated CodeDim cnn As New SqlConnection("Data Source=(local); Initial Catalog=Northwind;User ID=******; Password=******") Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Label1.Text = Session("id")
retrieveTitle()End SubSub retrieveTitle()
cnn.Open()Dim cmd As New SqlCommand
cmd.CommandText = "SELECT * FROM Region WHERE RegionID = '" + Session("id") + "'"
cmd.Connection = cnnDim dr As SqlDataReader
dr = cmd.ExecuteReader()
If dr.Read() Then
TextBox1.Text = dr("RegionDescription").ToString
End If
cnn.Close()End SubSub UpdateTitle(ByVal title As String)
cnn.Open()Dim sqlstr As String = "UPDATE Region SET RegionDescription = '" + title + "' WHERE RegionID = '" + Session("id") + "'"
Trace.Write(sqlstr)Dim cmd As New SqlCommand(sqlstr, cnn)
cmd.ExecuteNonQuery()
cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
UpdateTitle(TextBox1.Text)End Sub
End Class

View 4 Replies View Related

Update SQL Statement

Mar 13, 2008

I have a SQL Table with the following columns
ID, Date, Meeting, Venue, Notes
What Update statement do i need to update a simple grid view in Visual Studio?I have been experementing but when i click update it updates the whole column insted of the one i was trying to update.
Please can you help? Thanks

View 1 Replies View Related

Sql Update Statement

Jun 4, 2008

I need some help. please.  Here is what I got.  From the webpage I can pull the following data to update a table. update Vehicle set Name='TestUnitName' ,Make='TestMake', Model='TestModel', SoftwareVersion='TestVersion', DynamicChange='1', ProviderID='1', Description='TestDescription', VIN='TestVIN', IMEI='TestSIM', EngineTypeId=' ', Phone=' ', MobilePhoneProviderID='' where VehicleID=64 But I also get the error: "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Vehicle_EngineTypes". The conflict occurred in database "Telemetry", table "dbo.EngineTypes", column 'EngineTypeId'.The statement has been terminated." How do I solve this issue? Actually what I am trying to do is, when EngineTypeId=' ' , I want to set is to NULL and same for the MobilePhoneProviderID.  Any help would be appreciated. Thanks in advance.

View 14 Replies View Related

UPDATE Statement

May 17, 2004

Hey all,

I need to set a column value where the id is within a string. However, I need to set the column to a default value if the id is not within the string. Hope that was easy to understand!

ie:

This currently works...

SET @strSQL = 'UPDATE tblTest SET Archive = 1 WHERE RecID IN (' + @IDList + ')

If the ID is not in the list then I want that column set to 0. How can I do this?

Thanks in advance,

Pete

View 5 Replies View Related

UPDATE Statement

Sep 2, 2004

Hi
I use a update sub, the problem is that i got an error, the error is:
Syntax error in UPDATE statement.
I guess the UPDATE statement is:
strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"

Remark:I use Acceess DataBase, exactly the same code works fine in SQL, i just changed the DataBase(From Access to SQL).

Is the problem can be in other place?
Thank you very much for your assistance.

View 3 Replies View Related

Update Statement ?

Oct 14, 2004

Hi, I'm having trouble writing this update statement and was wondering if anyone could help me out :

My database is sort of set up lilke this:
There are 3 tables: Orders, OrderDetails and Inventory

Orders has a pk called OrderID.
OD has several ProductIDs listed for that OrderID
Inventory has 2 fields, the pk InventoryID(which is the same as
ProductID) and QOH

So OD kinda looks like this:
OrderID ProductID Quantity Cost
192 12 2 $10
192 3 1 $12
192 14 2 $50
193 12 1 $11
.... .
...
...

so what i want to do is take each productID for a specific orderid
and decrement the inventory for it by OD.quantity


This is what I was trying

UPDATE Inventory
Set QOH = QOH - @qty
WHERE Inventory.InventoryID IN (
SELECT ProductID
FROM OrderDetails
WHERE OrderDetails.OrderID = @OrderID
and @qty = OrderDetails.Quantity
)


but, i'm having no luck...... any suggestions ?

View 2 Replies View Related







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