STORED PROC To UPDATE Ntext Field -- PLEASE!
Jul 20, 2005
Hi, I've been reading all sorts of info on the ntext field. I need
this to store xml documents in sql server via a stored proc.
Because of its size, I apparently can not use SET (as in UPDATE)
therefore I'm trying to do an INSERT of the row with this field (after
deleting the old row).
CREATE PROCEDURE dbo.UpdateXmlWF
(
@varWO varchar(50)
@strWF ntext
@varCust varchar(50)
@varAssy varchar(50)
)
AS
INSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES
(@varWO, @varCust, @varAssy, @strWF)
I'm using MSDE so I can't tell what's wrong...it just won't save the
proc.
PLEASE HELP!
Thanks, Kathy
View 2 Replies
ADVERTISEMENT
Mar 22, 2008
Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate]
(
@ID int,
@Field1 nvarchar(50),
@Field2 nvarchar(50)=null,
@Field3 nvarchar(50)=null,
@Field4 nvarchar(50)
)
AS
UPDATE MyTable
SET Field1 = @Field1,
if (@Field2<>null) Field2 = @Field2,
if (@Field2<>null) Field3 = @Field3,
Field4 = @Field4
WHERE ID = @ID
I saw example before but i can't remember where.
Thank you in advance
View 4 Replies
View Related
Aug 17, 2004
Hi All,
I will be doing stress test for my app.
Loading thousands of records to the DB through bulk insert.
There's one field NText which I have left NULL because it will be hard to gen dummy flat file to it.
I have another table which has the Ntext Value which i will want to copy and duplicate to the other table.
what is the way to do it?
simply said i want to update a record with NULL value from one table with NText field with the value from another table..
View 1 Replies
View Related
Jul 20, 2005
Hi, I've read conflicting articles on updating an ntext field in acolumn.My ntext field will exceed 8,000 characters (typically twice that size-- but just a text string).One article (I think from MicroSoft) said you could NOT use ntext inan UPDATE statement, but I've seen examples from other people usingit...but don't know if it's related to the size/characters issue.Is this true or not?Thanks very much...Kathy
View 2 Replies
View Related
Oct 24, 2007
Hello everybody,
i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how
to get a pointer to the data and how to read from it.
But i want to copy the whole data.
Does anybody know how to do that?
Thanks in advance
A.Gempp
-------------------------------
I'm using SQL Server 2000
View 4 Replies
View Related
Apr 9, 2008
I have a third party application with a ntext field that I need to parse the data out of. The data looks like this:
<xmlF><FNumber type="int">2421</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate</xmlF>
The fieldname is Data and the tablename is ProcessData
Again, this looks like xml, but the field type is ntext. I would like to create a view displaying the parsed data in fields. How would I go about parsing the data?
Thanks.
View 12 Replies
View Related
Jul 4, 2007
Hi
My SP to update a value type ntext don't work, ALTER PROCEDURE UpdateMultiContentFullDescriptionByID
(@ContentID int,
@FullDescription ntext)
AS
UPDATE MultifunctionalContent
SET FullDescription = @FullDescription
WHERE ContentID = @ContentID
RETURNPublic Sub UpdateMultiContentFullDescriptionByID(ByVal ContentID As String, ByVal FullDescription As String)
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(Const.ConnectionString)
Dim myCommand As SqlCommand = New SqlCommand("UpdateMultiContentFullDescriptionByID", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add an input parameter and supply a value for it
myCommand.Parameters.Add("@ContentID", SqlDbType.Int, 4)
myCommand.Parameters("@ContentID").Value = ContentID
' Add an input parameter and supply a value for it
myCommand.Parameters.Add("@FullDescription", SqlDbType.NText, 8000)
myCommand.Parameters("@FullDescription").Value = FullDescription
' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub Then how to ...
View 2 Replies
View Related
Sep 28, 2005
Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
View 3 Replies
View Related
Jul 23, 2006
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)
/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN
View 2 Replies
View Related
Jul 12, 2007
Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
View 10 Replies
View Related
Jan 12, 2005
I'm trying to run a UPDATE stored proc to allow my users to update records that are in a datagrid. What the update proc looks like is as follows:
Proc sp_UpdateRecords
@fname nvarchar(30), @lname nvarchar(30), @address1 nvarchar(50), @address2 nvarchar(50), @CITY nvarchar(33), @ST nvarchar(10), @ZIP_OUT nvarchar(5), @ZIP4_OUT nvarchar(4), @home_phone nvarchar(22), @autonumber int
AS
UPDATE NCOA20040603 SET fname=@fname, lname=@lname, address1=@address1, address2=@address2, CITY=@CITY, ST=@ST, ZIP_OUT=@ZIP_OUT, ZIP4_OUT=@ZIP4_OUT, home_phone=@home_phone
WHERE autonumber=@autonumber
The message I'm getting is as follows:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
What could be the problem
Any ideas are appriciated -- Thanks in advance
RB
View 5 Replies
View Related
Nov 6, 1998
Referencing the sample stored procedure
below that updates table 'this_table' containing
3 INT columns: 'key', 'col2', 'col3'.
How do I revise the stored procedure so that *** I do not
have to specify ALL the columns *** to update just one column?
e.g. if I want to update @col3 to 9 (exec sp_update_table, @col3 = 9),
how can I set col2 to its existing value?
create proc sp_update_table
@key int,
@col2 int,
@col3 int
as
update this_table
set col2 = @col2,
col3 = @col3
where key = @key
go
View 1 Replies
View Related
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies
View Related
Jul 11, 2005
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies
View Related
Dec 3, 2003
I have a stored procedure that updates about a dozen rows.
I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.
Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.
Many thanks for any guidance.
Simon
View 5 Replies
View Related
Jan 31, 2005
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
Here is the syntax for my stored proc.
CREATE PROC updateResults2
@id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20)
AS
UPDATE Results
SET address1 = @address1,
address2 = @address2,
CITY = @CITY,
ST = @ST,
ZIP_OUT = @ZIP_OUT,
ZIP4_OUT = @ZIP4_OUT,
home_phone = @home_phone,
NEWPhone = @NEWPhone
GO
As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!!
Thanks in advance.
RB
View 3 Replies
View Related
Nov 5, 2006
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ----------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
go
ALTER PROCEDURE [dbo].[sp_UpdatePersonalization]
@p_id nvarchar(50),
@cust_num varchar(50),
--@publication varchar(25),
@full_name varchar(500),
@email varchar(200),
@web_address varchar(300),
@include_web bit,
@ReturnAddressYN varchar(1),
@include_email bit,
@job_title varchar(500),
@company_name varchar(500),
@tagline varchar(1000),
@phone1 varchar(30),
@phone2 varchar(30),
@phone3 varchar(30),
@phone4 varchar(30),
@phoneext1 varchar(10),
@phoneext2 varchar(10),
@phoneext3 varchar(10),
@phoneext4 varchar(10),
@phonedesc1 varchar(20),
@phonedesc2 varchar(20),
@phonedesc3 varchar(20),
@phonedesc4 varchar(20),
@company_mail_address varchar(500),
@masthead varchar(250),
@verbiage_page1 varchar(1400),
@verbiage_page4 varchar(2650),
@inc_bbb bit,
@inc_ehl bit,
@inc_eho bit,
@inc_rl bit,
@p_comments varchar(500),
@p_update_flag varchar(10)
--@frequency varchar(50),
--@mail varchar(50),
--@fold varchar(50),
--@contact varchar(50),
-- do not add this on the update! @date_added,
AS
declare @last_updated datetime
select @last_updated=GETDATE()
declare @set_update bit
--if @p_update_flag='False'
--select @set_update = 1
--else
--select @set_update = 0
if @p_update_flag='False'
INSERT INTO pers_main_arch
(
p_id,
cust_num,
publication,
full_name,
email,
web_address,
include_web,
include_email,
job_title,
company_name,
tagline,
phone1,
phone2,
phone3,
phone4,
phoneext1,
phoneext2,
phoneext3,
phoneext4,
phonedesc1,
phonedesc2,
phonedesc3,
phonedesc4,
company_mail_address,
masthead,
verbiage_page1,
verbiage_page4,
inc_bbb,
inc_ehl,
inc_eho,
inc_rl,
p_comments,
frequency,
mail,
fold,
contact,
date_added,
last_updated,
start_month,
ReturnAddressYN
)
SELECT
pm.p_id,
pm.cust_num,
pm.publication,
pm.full_name,
pm.email,
pm.web_address,
pm.include_web,
pm.include_email,
pm.job_title,
pm.company_name,
pm.tagline,
pm.phone1,
pm.phone2,
pm.phone3,
pm.phone4,
pm.phoneext1,
pm.phoneext2,
pm.phoneext3,
pm.phoneext4,
pm.phonedesc1,
pm.phonedesc2,
pm.phonedesc3,
pm.phonedesc4,
pm.company_mail_address,
pm.masthead,
pm.verbiage_page1,
pm.verbiage_page4,
pm.inc_bbb,
pm.inc_ehl,
pm.inc_eho,
pm.inc_rl,
pm.p_comments,
pm.frequency,
pm.mail,
pm.fold,
pm.contact,
pm.date_added,
pm.last_updated,
pm.start_month,
pm.ReturnAddressYN
FROM pers_main pm
WHERE pm.cust_num = @cust_num
if @p_update_flag='True' OR @p_update_flag='False' OR @p_update_flag IS NULL OR @p_update_flag=''
UPDATE pers_main SET
--cust_num=@cust_num,
--publication=@publication,
full_name=@full_name,
email=@email,
web_address=@web_address,
include_web=@include_web,
include_email=@include_email,
job_title=@job_title,
company_name=@company_name,
tagline=@tagline,
phone1=@phone1,
phone2=@phone2,
phone3=@phone3,
phone4=@phone4,
phoneext1=@phoneext1,
phoneext2=@phoneext2,
phoneext3=@phoneext3,
phoneext4=@phoneext4,
phonedesc1=@phonedesc1,
phonedesc2=@phonedesc2,
phonedesc3=@phonedesc3,
phonedesc4=@phonedesc4,
company_mail_address=@company_mail_address,
masthead=@masthead,
verbiage_page1=@verbiage_page1,
verbiage_page4=@verbiage_page4,
inc_bbb=@inc_bbb,
inc_ehl=@inc_ehl,
inc_eho=@inc_eho,
inc_rl=@inc_rl,
p_comments=@p_comments,
--frequency=@frequency,
--mail=@mail,
--fold=@fold,
--contact=@contact,
--date_added,
last_updated=@last_updated,
updated_flag=1,
ReturnAddressYN=@ReturnAddressYN
WHERE cust_num=@cust_num
------------------------------------------ code ----------------------------------------------
View 1 Replies
View Related
Jul 1, 2004
I'd like to be able to update an Analysis Services cube through a stored proc.
Currently I can:
- Make a DTS package that updates the cube
- run xp_cmdshell which runs dtsrun which runs the DTS package.
That is messy, easily broken, and hard to get good error info when an error occurs. Is there a better route?
View 1 Replies
View Related
Nov 8, 2005
For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.
I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.
To trick the sorting I use a calculated field:
ORDER BY RSort - where Rsort is:
convert(varchar(4), RD.response) as RSort
It works but put a high load on the SQL server when the number of responses increases.
I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.
What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:
View 3 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Dec 13, 2004
I need to update a field with the code below, is it possible to do this with a table join? The Query Analyzer is giving me an error at "Join".
An example:
(ManufSerNo is a field in table ASSETS,
Status is a field in table HISTORY)
GO
CREATE PROCEDURE sp_ChngeAssetStatus
@ManufSerNochar(10),
@Statusvarchar(1)
AS
UPDATE HISTORY
SET Status = @Status
JOIN ASSETS
ON HISTORY.AssetID = ASSETS.AssetID
WHERE ManufSerNo = @ManufSerNo
--ELSE
--RAISERROR ('Sorry, but the Asset ID specified does not exist. Record not updated.', 10 , 1)
GO
View 2 Replies
View Related
Jan 15, 2004
Hello All,
Maybe a stupid question but I'm new to the db admin work so please bear with me.
I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.
My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.
After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).
I'm not sure what I'm missing here to get this to work the way it did in Access.
View 11 Replies
View Related
Jul 20, 2005
I am trying to view all the ntext from a profiler trace. The data istruncated at 256 and I am not sure why... The max length is 1820 viathis command:select max(datalength(textdata)) from "monitor forms usage"where textdata like '%gforms%' .I then issueset textsize 8000select (textdata) from "monitor forms usage" where textdata like'%gforms%' and datalength(textdata) >1800and still only 256 is returned. this is true even if I redirect theoutput to a file.Any ideas on how a humble man like me can see all of the data.Mike--Posted via http://dbforums.com
View 2 Replies
View Related
Jan 29, 2008
I'm trying to parse an ntext field that in my SQL View contains an invoice comment in order to be able to group on parts of the comment. I have two problems--one, the syntax to do this, and two, the best way to deal with the parts that I want.
The comment is like: "standard text ABCDE : $99.99" but can have multiple "ABCDE"s, e.g. "standard text ABCDE FGH IJKL $999.99" and I found some that had duplicates like "standard text standard text...".
I want to be able to report in SSRS 2005 by grouping the "ABCDE", "FGH", "IJKL" items.
Any ideas? Please be specific as I'm still learning.
View 1 Replies
View Related
Dec 20, 2006
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
View 1 Replies
View Related
Jul 22, 2002
The title says it all. I've used ALTER DATABASE, and ALTER TABLE...ALTER COLUMN to change all my character fields from 'Compatibility_42_409_30003' to the default I want ('Latin1_General_CI_AI') on databases converted from SQL7. Now I just have these ntext fields to change...
Al
View 1 Replies
View Related
Feb 27, 2006
Hi,
Can anybody tell me how to append data to ntext field?
Joydeep
View 9 Replies
View Related
Jun 16, 2006
I need help on replace char in ntext data type
Here is the example data
<qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off
20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree
</qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh
I want result look like this
First Column:The AE Understands what conditions the Account Manager is allowed to sign-off.
Second Column: Strongly Disagree Disagree Agree Strongly Agree
This is what i had so far
Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion
my result:
The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.
I have problem with replace '%20' and how to make the second column.
Any Help?
Thanks
Shan
View 1 Replies
View Related
Sep 9, 2006
My Stored Proc runs through a loop and concats the contents of each field into one big nvarchar. Procedure works fine on a smaller scale but now it is being implemented on a very large table and the results of the sequel overflow the nvarchar limits. I looked into using text and ntext but both cannot be declared locally. Does anyone know how I can work aroudn this limitation?
Summary:The problem is that the temporary variable I am using (nvarchar) is too small to contain the robust size that the SQL is concating into it. The final field it winds up in is a text field and will be able to handle the amount of data, its just getting the data there is the issue..... Your thoughts please....
View 7 Replies
View Related
Oct 15, 2006
I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.
View 8 Replies
View Related
Jun 20, 2007
Hello,
I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.
I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.
any ideas really appriciated.
Thanks
Ram
View 4 Replies
View Related
May 24, 2002
I am having an interesting error. I have a bunch of data that I am updating to a field in my sql server 7.0 database. It updates the data, however only puts the first 64,999 characters into the field. The datatype is ntext. From what I understand, ntext datatype can hold much more than 65,000 characters. Am I right in this? The data I'm putting into the field is html tags/text. I've tried several different data sets. I'm updating the data using Coldfusion/SQL commands.
Here is my SQL syntax:
UPDATE htmltest
SET html_offline = '#form.html#'
WHERE htmlid = #form.htmlid#
View 1 Replies
View Related
Apr 25, 2008
Has anyone seen this issue before? We are running a SQL CE 3.5 database on a windows desktop. A couple of our tables have ntext fields. When we do an insert the statement updates the value for all rows, not just the one that was added. I can easily repro this with some of the online samples too. Try the following:
SqlCeConnection conn = new SqlCeConnection(_sConn);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";
SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);
SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);
paramName.Value = "Name1";
paramBlob.Value = "Name1 Memo";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";
SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);
SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);
paramName.Value = "Name2";
paramBlob.Value = "Name2 Memo";
cmd.ExecuteNonQuery();
After the second execution the blob column in both rows will have the value 'Name2 Memo'.
This is obviously a huge problem for us and would appreciate it if someone can explain what is happening. Seems like a bug but would like to be certain before I go the support route.
View 1 Replies
View Related
