Trim And Update Not Working As Expected

Feb 25, 2006

I was hoping that the Trim function inside the update command, cmdUpdate.Parameters.Add("@doc_num", Trim(txtDocNum.Text))
, would deal with any leading and trailing spaces, but it does not seem
to be doing anything at all. The value from the textbox still arrives
in the database table with leading spaces!!



Am I doing something wrong?

View 7 Replies


ADVERTISEMENT

Encrypt Not Working As Expected

Oct 18, 2007

Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.


select *

from [dbo].[dmTable]

WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'



select *

from [dbo].[dmTable]

where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')

View 1 Replies View Related

RMO - FrequencyType Not Working As Expected

Oct 9, 2006

Hi,

In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM. 

Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.

Is there something missing in this code? Is this a SQL Server bug?

    // Set the required properties for the trans publication snapshot job.
    TransPublication tpublication = new TransPublication();
    tpublication.ConnectionContext = conn;
    tpublication.Name = publicationName;
    tpublication.DatabaseName = publicationDbName;
    tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily;
    tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001);
    tpublication.SnapshotSchedule.ActiveStartDate = 20051101;
    string newString = "025800";
    tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString);
    tpublication.Create();

 

View 3 Replies View Related

Transactions And Checkpoints Not Working As Expected

May 2, 2007

I have a package that has a container containing multiple DF Tasks.

The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.

This works as expected.



However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.

However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.

This is not the functionality I see. The package restarts from the failed task within the container every time.



According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.



A previous forum message encountered the same issue it appears:

See SSIS Checkpoints 04 Dec 2006.



This is an extract from it:

"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.

In short, I felt that check points were not aware of transactions.

So, I ended up with this setting and it works for me:

Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.

That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."



While this makes sense to me it is not the same properties that the SSIS book has that work.

Additionally, this didn't work for me either !!

I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.



The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.

View 1 Replies View Related

Report Viewer Links Not Working As Expected

Nov 20, 2007



Hi all,

Heres the setup...

We have an asp application that runs the reportserver URL for the selected report, passing it through parameters. This opens the report viewer and the report runs.

The problem im having is that one report is not working as expected. When the report is run from report manager, everything works fine. The links do what they're meant to (they link to other reports passing through parameters). When the report is run from our asp application with the report viewer, the links fail.... they dont pass through the correct values or sometimes dont pass through a value at all.

Were the report viewer and report manager applications developed seperately?

Any help would be appreciated.

Thanks.

View 5 Replies View Related

FTP Task Not Working As Expected - File Not Downloading

Feb 11, 2008

I have a package with an FTP task in the Control Flow. Nothing complicated, its configured to download a file from FTP with overwrite true, and the get and download paths in variables. Once this step completes it goes to a sequence container that does stuff with the file, that part works fine.

The problem is if i run the package in debug mode using Visual Studio, everything works perfectly (even if run over and over). The problem occurs if i use my "driver" application to try and execute the package. All my driver does is use C# code to create an Application object, set the PackagePassword, LoadPackage based on the path and then .Execute().

Here is when the strangeness begins:. If the file exists in the destination path, then the FTP task fires. If the file does not exist (lets say i manually delete it after its being downloaded before using the IDE) the FTP task dose not fire and my Sequence Container fails to use the fail, since it is of course missing.

Monitoring the FTP server that i am running locally i can see commands firing when the file exists, but when the file is killed it never even tries to connect to my server. Very odd.

Am i missing something? Why would this work from the IDE but never from my driver?

Any help would be appreciated. Please let me know if any additional detail is required.


Dmitry
http://blog.lyalin.com

View 6 Replies View Related

Challenging Search Task Is Not Working As Expected

Sep 14, 2007



Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------


table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------


Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================


select * from @table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @table1 ta

JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)



)
===============================================================================

script to create tables
============================================================================

DECLARE @table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @table1 (

searchword

) VALUES ( '%.txt' )



DECLARE @table2 TABLE (

testname VARCHAR(255)

)



INSERT INTO @table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @table2 (

testname

) VALUES ( 'readme.txt' )



INSERT INTO @table2 (testname

) VALUES ('README.TXT' )





INSERT INTO @table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @table2

DECLARE @table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )






===================================================================================





View 4 Replies View Related

Parent/Child Packages And Transaction Option Not Working As Expected

Dec 18, 2007

Has anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.

The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.

So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored



Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored



for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.

View 7 Replies View Related

UPDATE Using TRIM

Jan 3, 2007

hi guys,

i need to update a column by trimming the right end. i tried many things but its not working. can anyone tell me?

Regards,
David

View 12 Replies View Related

Sql Server 2005 Full Text Search Not Working As Expected On Production Server

Jan 28, 2008

I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows,  the full text search works perfect, but as the number of rows increases,  the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)       

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

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

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

SQLDataSource Update Using Parameters Not Working

Jul 10, 2006

I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast.  And then firing off the update command that contains that parameter from a button.  But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes.  All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list.  But it doesn't even change anything.  It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc.  It just won't change the data from the page when I try to.
 
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Any help is much appreciated.

View 4 Replies View Related

New 2005 Syntax With - Update Not Working

Oct 26, 2007

I have tried the following, the update part i snot working. Any idea why? alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
 
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
 
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
 
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
Update FeaturedClassifiedsSET FeaturedDisplayedCount = FeaturedDisplayedCount+1
Where
Row between
@startRowIndex And @startRowIndex+@NumRows-1
END
 
 I have tried function too for this, but function can not update table I guess.... Can I call stored procedure for each column? How?
 I thought the code above would work? What am I missing?

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

I Cant Figure Out Why This Update Isnt Working

Dec 11, 2007

 i dont know if it is just because im tired or what. im trying to do a update one this table here is the stored procedure im usingALTER PROCEDURE Snake.UpdateSPlits
@name nvarchar(50),
@split nvarchar(50)
AS
Update accounts
Set split_id = @split
Where name = @name

RETURN Here is what im using to call it  Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Me.SqlDataSource1.UpdateParameters.Clear()

Dim name As String = Me.GridView1.SelectedRow.Cells(0).Text
Dim Split As String = Me.GridView1.SelectedRow.Cells(1).Text

Dim pname As New Parameter("name", TypeCode.String, name)
Me.SqlDataSource1.UpdateParameters.Add(pname)

Dim psplit As New Parameter("split", TypeCode.String, Split)
Me.SqlDataSource1.UpdateParameters.Add(psplit)

Me.SqlDataSource1.Update()
End Sub  I keep getting one of 2 errors they areObject reference not set to an instance of an object. orone that says i had to many aurgements any idea what im doing wrong?    

View 7 Replies View Related







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