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

Update Statement With COALESCE

Sep 23, 2007

Hi All,
It seems to me I am missing some thing while using update with COALESCE...Please help me...

I have two tables called @table1 and @table2

@table1
------------------------
ID | filelis
---------------------
1 |

2 |
3 |
------------------------

@table2
------------------------
ID | file
---------------------
1 | a.txt

1 | b.txt
1 | c.txt
2 | a.exe

2 | b.exe
2 | c.exe
3 | a.alto

3 | b.alto
3 | c.alto
------------------------

I need to get file name from @table2 and update in @table1 with coma separated for each ID

the updated @table1 should be below
------------------------
ID | filelis
---------------------
1 | a.txt, b.txt, c.txt

2 | a.exe, b.exe, c.exe
3 | a.alto, b.alto, c.alto
------------------------

HERE IS THE SCRIPT for above two tables
==================================

declare @table1 table

(

ID INT,

files varchar(MAX)

)

INSERT INTO @table1 (ID,files) values(1,'')

INSERT INTO @table1 (ID,files) values(2,'')

INSERT INTO @table1 (ID,files) values(3,'')

declare @table2 table

(

ID INT,

[file] varchar(255)

)

INSERT INTO @table2 (ID,[file]) values(1,'a.txt')

INSERT INTO @table2 (ID,[file]) values(1,'b.txt')

INSERT INTO @table2 (ID,[file]) values(1,'c.txt')

INSERT INTO @table2 (ID,[file]) values(2,'a.exe')

INSERT INTO @table2 (ID,[file]) values(2,'b.exe')

INSERT INTO @table2 (ID,[file]) values(2,'c.exe')

INSERT INTO @table2 (ID,[file]) values(3,'a.alto')

INSERT INTO @table2 (ID,[file]) values(3,'b.alto')

INSERT INTO @table2 (ID,[file]) values(3,'c.alto')

select * from @table1

select * from @table2
=================================================

I have tried like this but no luck..Thanks in Advance.

update @table1

set files = COALESCE(t2.[file] + ',','') + t2.[file]

from @table1 t1,@table2 t2 where t1.ID = t2.ID

View 5 Replies View Related

Updating Column In A Dataset Not Working

Aug 8, 2006

Hi all,
I'm trying to update various rows in my DB with a new value once an action occurs. I am using the following code which does not throw any exceptions, but also does not change the values. Can someone steer me in the right direction?
public static void ChangeRefCode()
{
SqlConnection dbConn = new SqlConnection(ConnectDB.getConnectString());
SqlDataAdapter dataAdapt = new SqlDataAdapter("Select * from Posting",dbConn);

DataSet ds = new DataSet();

SqlCommandBuilder sqBuilder = new SqlCommandBuilder(dataAdapt);
dataAdapt.Fill(ds, "Posting");
foreach (DataRow dr in ds.Tables["Posting"].Rows)
{
dr["ref_code"] = DateTime.Today.Ticks + "-" + dr["poster"].ToString();
}
try
{
dataAdapt.Update(ds, "Posting");
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
dbConn.Close();
}
}

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

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:

Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try

When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

Updating To SQL Server Management Studio Developer Edition Is Not Working

Jul 13, 2007

I got VS Pro which came with SQL Server Management Studio Developer Edition. Very exciting! However, when I go to update from SQL Server Management Studio Express to Dev Edition it says that there are tools / components already installed. I completely uninstalled everything having to do with SQL Server from my machine and tried again. It still says that the tools / components are installed. I see that there are still several instances of SQL Server still on my machine. So I tried reinstalling Express again. However, now the Configuration Manager has a snap-in that won't start. Any help would be much appreciated.
Thanks

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

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

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

Update Not Working Using VB

Jan 27, 2008

Hello Just having an issue with my code not updating my tables. Fairly new to asp so its frustrating me that its not working Here is my code 1 Protected Sub btnAddBusDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddBusDetails.Click
2 Dim datasource As New SqlDataSource()
3 datasource.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString()
4
5 datasource.UpdateCommand = "Update PrincipalContact set [princName] = @Name, [princPosition] = 'The man', [princContactNumber] = 'At home' Where ([username] = @userName);"
6 datasource.UpdateCommandType
7 datasource.UpdateParameters.Add("userName", My.User.Name)
8 datasource.UpdateParameters.Add("Name", Principal_Name.Text)
9 datasource.UpdateParameters.Add("Position", Principal_Position.Text)
10 datasource.UpdateParameters.Add("Contact", Principal_Contact.Text)
11 datasource.Update()
12
13
14 Dim datasource2 As New SqlDataSource()
15 datasource2.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString()
16
17 datasource2.UpdateCommand = "Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName, [tradingAs] = @tradingAs, [businessDescription] = @businessDescription, [principalAddress] = @principalAddress, [tradingStatus] = @tradingStatus, [numberOfEmployees] = @numberOfEmployees, [locationsByState] = @locationsByState Where ([userName] = @userName);"
18 datasource2.UpdateParameters.Add("userName", My.User.Name)
19 datasource2.UpdateParameters.Add("insolvencyPractitioner", Insolvency_Practitioner.Text)
20 datasource2.UpdateParameters.Add("companyName", Company_Name.Text)
21 datasource2.UpdateParameters.Add("tradingAs", Trading_As.Text)
22 datasource2.UpdateParameters.Add("businessDescription", Description_of_Business.Text)
23 datasource2.UpdateParameters.Add("principalAddress", Principal_Address.Text)
24 datasource2.UpdateParameters.Add("tradingStatus", Trading_status.Text)
25 datasource2.UpdateParameters.Add("numberOfEmployees", Number_of_Employees.Text)
26 datasource2.UpdateParameters.Add("locationsByState", Location_by_state.Text)
27
28 datasource2.Update()
29
30 Response.Redirect("~/Default.aspx")
31
32
33
34 End Sub No errors are occurring and when I replace the parameters with actual text it works.eg. Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName .... to Update Firm set [insolvencyPractitioner] = 'Luke', [companyName] = 'A Company' ..... Not sure why this is happening. Can someone please give me a hand. Thanks

View 5 Replies View Related

UPDATE Not Working

Mar 19, 2000

I have a table and I want to update it with data that is available in a
different table, the Master table contains a field called RegID and the other table also has RegId - I am bombing out with this UPDATE QUERY :

UPDATE trainee
SET tra_HomePhone = phone$.EPhoneH,
tra_areaHomePhone = phone$.EPhoneHA,
tra_workPhone = phone$.EPhoneW,
tra_areaWorkPhone = phone$.EPhoneWA,
tra_postcode = phone$.EAddressHP
WHERE trainee.tra_regId = phone$.regID

Any reason why this is not working? Thanks in advance for any help.

Anthony

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

Update Not Working In An SQLDataSource

Sep 6, 2006

I'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource"
Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False">
<Fields>
<asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" />
<asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" />
<asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" />
<asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" />
</Fields>
</asp:DetailsView>

<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>"
SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" >
<UpdateParameters>
<asp:Parameter Name="trackName" Type="String" />
<asp:Parameter Name="trackPath" Type="String" />
<asp:Parameter Name="lyrics" Type="String" />
<asp:Parameter Name="pk_trackID" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.

View 7 Replies View Related

UPDATE Not Working In Gridview

Sep 26, 2006

I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" > <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx

View 4 Replies View Related

UPDATE Command Not Working

Feb 13, 2007

i am using visual web developer 2005 and SQL Express 2005 and VB as the code behindi have a table called orderdetail and i want to update the fromdesignstatus field from 0 to 1 in one of the rows containing order_id = 2so i am using the following coding in button click event Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = '1' WHERE order_id = '2'" End Sub but the field is not updatedi do not know where i have gone wrong in my coding. i am sure that my database connection string is correctplease help me

View 1 Replies View Related

Update In Trigger Not Working

Sep 24, 2007

Hi,
I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:
CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05
I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.

View 2 Replies View Related

Update Query Is Not Working

Sep 24, 2007

Hi,I have three tables

Time_Sheet

Pin_Code

P_Date

Day_Status

Primary Key

Primary Key



Leave

P_Number

Leave_Code

Start_Date

End_Date

Primary Key



Employees

P_Number

Pin_Code

More>>

Primary Key

Primary Key

     I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave  I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka

View 2 Replies View Related

C# Update Function. Where AND Where Not Working.

Jan 29, 2008

Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE)
{int vendorid = Convert.ToInt32(VEE.VendorevalVendor);
int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter();
SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int;
pmvendorid.Value = vendorid;
pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int;
pmuserid.Value = userid;
cmd.Parameters.Add(pmvendorid);
cmd.Parameters.Add(pmuserid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

}

View 3 Replies View Related

Why Is My Update Command Not Working

Feb 18, 2008

hi, i am tryuing to use the gridview as means for the user to be able to edit delete and update columns of the database. however when it is run in the browser it allows the user to edit the fields but when i click on the update button it throws an error. can someone please offer me advice on how i can sort this problem out or provide me with any examples as i cant see what the error is. i used the option in edit columns which allows you to specify you want update delete etc controls added to the gridview. how can i make it so it supports updating? thank you
Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NotSupportedException: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

View 2 Replies View Related

Update In DetailsView Not Working

Jun 9, 2008

Hi,
Can anyone tell me why my Update attempts are not working? Here is my code:
<body>
<form id="form1" runat="server">
<div>
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
AutoGenerateEditButton="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
DefaultMode="Edit">
<Fields>
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Fields>
</asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringListings %>"
SelectCommand="SELECT [City] FROM [Listings]"
UpdateCommand="UPDATE [Listings] Set [City]=@City WHERE [ListingID]=@ListingGuid ">
<UpdateParameters>
<asp:Parameter Name="City" />
<asp:Parameter Name ="ListingGuid" />
</UpdateParameters>
</asp:SqlDataSource>

<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
</div></form>
</body>
And here is my code behindProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ListingGuid = Request.QueryString("GUID")
End SubProtected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated
Label1.Text = "updated"
Label2.Text = ListingGuidEnd Sub
Please let me know what I am doing wrong?

View 3 Replies View Related

Update In Gridview Not Working

Jan 5, 2006

I may have posted this in the wrong forum before, but i am trying to update a table in SQL2000 from asp.net2.0 gridview. I follow all the recommended ways and it doesnt update the row. Please help
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None">

.......misc code.........
UpdateCommand="UPDATE vacationrequest SET hrapprvl = @hrapprvl, notes = @notes WHERE ([ID] = @original_ID)">
<SelectParameters>
<asp:ControlParameter ControlID="HiddenField1" Name="StartDate" PropertyName="Value"
Type="DateTime" />
<asp:Parameter DefaultValue="false" Name="hrapprvl" Type="Boolean" />
</SelectParameters>

<UpdateParameters>
<asp:Parameter Type=Boolean Name="hrapprvl" />
<asp:Parameter type=String Name="notes" />
<asp:Parameter Name="original_ID"/>

View 1 Replies View Related

HELP--UPDATE STATEMENT NOT WORKING

Mar 21, 2000

Hi,

I am trying to write an update query to update rows in one table.

Structure of Table1:

SocialSecurityNumber Varchar(9) -- Primary Key
Name Varchar(30)

Structure of Table2 :

SocialSecurityNumber Varchar(9)
Name Varchar(30)

Table 1 contains:

Row1: "123456789" Sally
Row2: "999999999" Bill
Row3: "333333333" Alex

Table 2 contains:

Row1: "123456789" <NULL>
Row2: "123456789" <NULL>

Basically I want to update the name column in Table 2 (based on the SocialSecurityNumber column) so that after
the update Table 2 will contain:

Row1: "123456789" Sally
Row2: "123456789" Sally
------------------------------------------------------------
First I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Then I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name

Again this works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Finally I tried:

UPDATE Table2, Table1
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This also did not work.
------------------------------------------------------------
Is there any way to write this update statement without cursors?

Thanks.

View 1 Replies View Related

Update Method Not Working

Oct 27, 2004

UPDATE support, support_temp SET support.cat_id = support_temp.cat_id_new WHERE support.cat_id = support_temp.cat_id_old

Works with MySQL 4.0.20 but does not work with 3.23.58. What can i do to get that code to work with MySQL 3.23?

View 1 Replies View Related

Insert/Update Not Working

Jun 18, 2008

Hi there

I've amended a table to include some extra columns to track when changes are made. Next step is to amend the stored procedure that updates that table when the changes are made.

I amended an existing stored proc to include CreateTS, CreateID, ModifyTS, ModifyID. Unfortunately, the INSERT and UPDATE aren't working for the new columns.

Am fairly new to this, so not sure why it's not working? Code is below:

DECLARE @ThisBSB VarChar(6)
DECLARE @intCount int
DECLARE @intInserted int
DECLARE @intUpdated int

SET @intInserted = 0
SET @intUpdated = 0

-- fields from New Table

DECLARE curBSB CURSOR
FOR
SELECT Replace(bsbnumber,'-','')
FROM ztblBSBText (nolock)

OPEN curBSB

FETCH NEXT FROM curBSB
INTO @ThisBSB

WHILE @@FETCH_STATUS = 0
BEGIN

--Print @ThisBSB

-- See if this BSB Already Exists
SELECT @Intcount = Count(*)
FROM tblBankBSB (nolock)
WHERE BSBcode = @ThisBSB

IF @intCount = 0
BEGIN

-- Insert New Record
--Print 'Insert: ' + @ThisBSB
INSERT INTO tblBankBSB
([BSBCode]
,[BankID]
,[BranchNumber]
,[BranchName]
,[CountryID]
,[Address]
,[Suburb]
,[StateID]
,[StateCode]
,[State]
,[PostcodeID]
,[Postcode]
,[StatusID]
,[TransferedToBSB]
,[CreateID]
,[CreateTS]
,[ModifyID]
,[ModifyTS])
SELECT @ThisBSB
,tblBank.BankID
,Cast(Right(bsbnumber,3) AS Int)
,ztblBSBText.BSBName
,1
,ztblBSBText.Address
,ztblBSBText.Suburb
,tblState.StateId
,Null
,ztblBSBText.State
,Null
,ztblBSBText.Postcode
,1
,Null
,Null
,Null
,@UserContactID
,getDate()
FROM ztblBSBText
INNER JOIN tblBank (nolock) on ztblBSBText.Mnemonic = tblBank.BankCode
INNER JOIN tblState (nolock) on ztblBSBText.State = tblState.State
WHERE tblState.StatusID = 1
AND tblState.CountryID = 1
AND Replace(bsbnumber,'-','') = @ThisBSB

SET @intInserted = @intInserted + 1
END

ELSE
BEGIN

-- See If Closed since last time this was run, and if so, update
SELECT @intCount = Count(*)
FROM ztblBSBText
INNER JOIN tblBankBSB (nolock) ON Replace(ztblBSBText.bsbnumber,'-','') = tblBankBSB.BSBCode
WHERE Replace(bsbnumber,'-','') = @ThisBSB
AND ztblBSBText.BSBName = 'Closed'
AND tblBankBSB.BranchName Not Like '%Closed%'

IF @intCount > 0
BEGIN

--Print 'Update: ' + @ThisBSB
UPDATE tblBankBSB
SET tblBankBSB.StatusID = 0
,tblBankBSB.BranchName = tblBankBSB.BranchName + ' - Closed'
,tblBankBSB.TransferedToBSB = (SELECT replace(substring(address, 14,7),'-','')
FROM ztblBSBText
WHERE Replace(ztblBSBText.bsbnumber,'-','') = @ThisBSB)
,tblBankBSB.ModifyID = @UserContactID
,tblBankBSB.ModifyTS = getDate()
WHERE BSBCode = @ThisBSB

SET @intUpdated = @intUpdated + 1
END

END

FETCH NEXT FROM curBSB
INTO @ThisBSB

END

CLOSE curBSB
DEALLOCATE curBSB

_____________________________
"Nihil est incertius volgo." - Cicero

View 2 Replies View Related

Update Statement Not Working

Oct 2, 2007

I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?

UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]

SET [LaborBudget_USD] = Week1

FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN

[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON

Labor.Location_Code = Budget.Location_Code

JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON

Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND

Date.FiscalPeriodOfYear = Labor.FiscalPeriodOfYear

WHERE Labor.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,1,4) AS NVARCHAR(50)) AND

Budget.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,4,1) AS NVARCHAR(50)) AND

Date.FiscalWeekofYear = 1

View 6 Replies View Related

Update Statement Not Working

Apr 11, 2006

I've got the following update statement:

UPDATE ISSUE_ACTIONS
SET BAE_FLAG = 2
WHERE IA_ISSUE_NO = 399
AND IA_SEQUENCE = 20

The fields BAE_FLAG, IA_ISSUE_NO, and IA_SEQUENCE are all of the type int.

When I run this code inside of my windows app (C#),

cmd3.CommandText = "UPDATE ISSUE_ACTIONS " +
"SET BAE_FLAG = 2 " +
"WHERE IA_ISSUE_NO = 437 " +
"AND IA_SEQUENCE = 13";

try
{
cmd3.ExecuteNonQuery();
}
catch (Exception e)
{
throw (e);
}

I get a timeout error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

But when I run the SAME statement from Query Analyzer, it executes without a problem.

Has anyone run into this issue before? How do I get around this?

The CommandTimeout property of cmd3 is set to the default because this is not a complex query and should not take more than .5 seconds to execute.

View 5 Replies View Related

Update With Inner Join Is Not Working

Mar 31, 2008

Hi

Please some one help me with this
I have a storedproc

ALTER PROCEDURE [dbo].[usp_updateCustomerList]

(@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Address1 varchar(35),
@Address2 varchar(35),
@Address3 varchar(35),
@Address4 varchar(35),
@OrderAmount money,
@OrderStatus varchar(6))

as

UPDATE OrderDetails SET Customer.FirstName=@FirstName,Customer.LastName=@LastName,Customer.Address1=@Address1,Custome.Address2=@Address2,Customer.Address3=@Address3,Customer.Address4=@Address4,
OrderDetails.OrderAmount=@OrderAmount,OrderDetails.Status=@Status
From OrderDetails
INNER JOIN Customer
ON OrderDetails.CustomerNumber = Customer.CustomerNumber
where OrderDetails.CustomerID = @CustomerID

When iam executing the stored procedure iam getting error

The multi-part identifier "Customer.FirstName" could not be bound.

Please someone help me with this.

I want to update two tables so ia joining the two tables with customer number which in both the tables.

and customerID is only in OrderDetails table.

so someone please help me with this.

thanks
Ramya

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