Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Updating A Field Of Type Bit


Hi,


I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??








CREATE PROCEDURE PublishSchedule


(


@SiteCode smallint,


@YearMonth int


)


AS





DECLARE @Active bit


IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 )


BEGIN


UPDATE CabsSchedule


SET Active=1


WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth


END


GO











Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Informix Date Type Field To SQL Server Datetime Field Error
 

I am trying to drag data from Informix to Sql Server.  When I kick off the package
using an OLE DB Source and a SQL Server Destination,  I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
 
I tried a couple of things:
 
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
 
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
 
What options do I have that will work?

View Replies !   View Related
SQL Problem Updating A Column Of Type DECIMAL(11,4)
This problem is strictly an SQL problem, and I am about to lose my mind over it.  I have a table named Inventory that has a column named PartialQty which is of type DECIMAL(11,4).  I have shortened my query for simplicity, but here it is.
DECLARE @pqty1  DECIMAL(11,4)
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1 UPDATE Inventory   SET PartialQty = 6.5513   WHERE InventoryID = @invID
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1
Before this query is ran, the value in PartialQty is 1.2345.  If I run this 2 times in a row, this is the output I get:
RUN 1:
1.2345
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
6.0000
 
RUN 2:
6.0000
(1 row(s) affected)
6.5513
 
I don't understand 2 things.  One is why the first run displays (1 row(s) affected) 4 times even though there is only one record with that InventoryID.
And the other thing I don't understand is why the first time I run it, it loses all of the values after the decimal point. 
Please help,
Scott

View Replies !   View Related
Updating Fact Table Field From Source Table Field
 
 

Hi,

 
I have source table , fact table and four dim. tables , I  have to update a field in fact table from source table.
 
How can I do it?
 
thanks...

View Replies !   View Related
Updating Date Field
All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid.
Dim sDate As DateTime
sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure.....
Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read
Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name
command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num    VARCHAR(256),@date    SMALLDATETIME,@contact    VARCHAR(256),@notes    VARCHAR(8000),@media    VARCHAR(256) ASBEGIN DECLARE @errCode   INT
BEGIN TRAN
  -- UPDATE THE RECORDS  UPDATE dbo.tblData  SET    fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media  WHERE fldNum = @num     
<sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath

View Replies !   View Related
Updating A Field With A Triggers
How would i update a field with a triggers to set the column password to something else if the password column = <NULL> on a update.

thank you very much.

View Replies !   View Related
Need Help Updating Part Of A Field.
Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0.
My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long.
Any help would be appreciated.

View Replies !   View Related
Updating A Text Field
I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it.
How can I get it done ?

Thanks
Pete

View Replies !   View Related
Updating 3 Characters In A Field
I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?

Thanks in advance.

View Replies !   View Related
Updating A Text Field
I have a complex issue that has several steps.

Question 1.
I need to be able to update the following path in the database, a text field

[Info] NumAttachments=1 [Attachments] Attachment1=65513|C:Program FilesHEATHEATSelfServiceattachmentsWinter019997.htm

I need to update the path only to H:2007|( Winter019997.htm)Filename as in database.

Question 2
This needs to be done automatically so when attachments are being added this updates. How can I do this?

Question 3
I have the attachments saved in this location C:Program Files etc… I need them moved to another location on the network. How can I do this?

View Replies !   View Related
Updating Field But Not Filename
I want to change the following field in the database - text field.

Want it to be like this,

C:ProgramFilesHEATHEATSelfServiceattachmentsWinter019997.htm

want it to be H:2007Winter019997.htm

My aim is to move the files from C: to H: drive and update the database to reflect this. Thanks

Thsi is my code;

declare @str varchar(1000)
UPDATE heatgen
set @str = 'c:Program FilesHEATHEATSelfServiceattachmentsWinter019997.htm'
select reverse(@str)
select charindex('', reverse(@str))
select right(@str, charindex('', reverse(@str)))
select 'H:2007' + right(@str, charindex('', reverse(@str)))

SET gdetail = 'H:2007' + RIGHT(gdetail, CHARINDEX('', REVERSE(gdetail)))

The field is text I get error

Line 9: Incorrect syntax near '='.

Thanks.

View Replies !   View Related
Updating Field In Table
I have 3 tables and 1 view. Which are:
TOWNLAND_GEOREFERENCE_POLYGON
PlanningPointLocation
paflarea
VIEW_paapplic
 
The View paaplic has 100 records and I have to do the below for all 1000 records individually.
I have to update the field TP_Total in the TOWNLAND_GEOREFERENCE_POLYGON table depending on what is in the fields in the tables.
I am writing the below code but am unsure if this is going to achieve what I want.
 
BEGIN
Select file_number, land_use_code, pluse1_code
From VIEW_paapplic
END
 
BEGIN
If pluse1_code = 'A' Then
Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number
 
Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + 1
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number
 
Else If pluse1_code = 'C' Then
Select Count(*) As TempCount From table paflarea
Where fk_paapplicfile_nu = file_number
 
Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number
 
Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + TempCount
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number
END
 
Anyone any ideas?
Jmccon

View Replies !   View Related
Need Help Updating One Field In Every Table
ok, i am trying to update a database at work for a product we are developing.
 
i need to run this command
update <tablenamehere> set value = Replace(value, 'GeoLynxAO_Henrico', 'GeoLynx')
on every tabe in the database

is there a simple way to do this while pulling the table names out of information_schema.tables?
 
i have searched using google and been unable to find anything so far.  the db server is running sql server express 2005 and i'm doing this from sql server management studio express
 
i really don't want to have to type the update statement by hand for 90+ tables................

View Replies !   View Related
Gridview Date Field Not Updating
Hi - Once again I've been looking at this forever and not able to see the problem.  Have a grid table everything updates except the training date field.  That get's wiped out each time - no matter if something is in it or not.  Everything else updates correctly.
Here's the code: 
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><Columns><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True"><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:BoundField><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:TemplateField>
<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>" ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"UpdateCommand="UPDATE [trainingLog] SET  [trainingDate] = @trainingDate WHERE [eventID] = ?" >
<UpdateParameters><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>

View Replies !   View Related
Need Help With Sqldatasource And Updating A Field In Sql 2005
I am experiencing some wacky errors here. While trying to update a field that does not allow nulls and the default value is set to '', I keep receiving an exception error that:
 Cannot insert the value NULL into column 'image_name', table 'DB_123871.dbo.tWebBlogs'; column does not allow nulls. UPDATE fails. The statement has been terminated.
 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlGetBlogs" DataKeyNames="article_id" AutoGenerateColumns="False" CssClass="GridView" CellPadding="4" HorizontalAlign="Center" Width="875px">
<Columns>
<asp:CommandField CancelImageUrl="~/images/Cancel.gif" EditImageUrl="~/images/Edit.gif"
UpdateImageUrl="~/images/Update.gif" ButtonType="Image" HeaderText="Edit" ShowEditButton="True">
</asp:CommandField>
<asp:BoundField DataField="article_id" HeaderText="ID" ReadOnly="True" />
<asp:TemplateField HeaderText="Artilce Header">
<EditItemTemplate>
<asp:TextBox ID="ArticleHeaderTxt" runat="server" Text='<%# Bind("article_header") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleHeaderTxtReq" runat="server" ControlToValidate="ArticleHeaderTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Header Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleHeaderLbl" runat="server" Text='<%# Eval("article_header") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Description">
<EditItemTemplate>
<asp:TextBox ID="ArticleDescriptionTxt" runat="server" Text='<%# Bind("article_description") %>' Width="325" Rows="8" TextMode="MultiLine"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleDescTxtReq" runat="server" ControlToValidate="ArticleDescriptionTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Description Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleDescriptionLbl" runat="server" Text='<%# Eval("article_description") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Link Short Text">
<EditItemTemplate>
<asp:TextBox ID="ArticleLinkTxt" runat="server" Text='<%# Bind("short_link_text") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkTxtReq" runat="server" ControlToValidate="ArticleLinkTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link Text Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleLinkLbl" runat="server" Text='<%# Eval("short_link_text") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Link">
<EditItemTemplate>
<asp:TextBox ID="ArticleLink" runat="server" Text='<%# Bind("article_link") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkReq" runat="server" ControlToValidate="ArticleLink" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link URL Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:HyperLink ID="ArticleLinkLnk" runat="server" CssClass="LinkNormal" NavigateUrl='<%# Eval("article_link") %>'
Target="_blank" Text="View Link"></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image Name">
<EditItemTemplate>
<asp:TextBox ID="Image1Txt" runat="server" Text='<%# Bind("image_name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Image ID="Image1Img" runat="server" AlternateText='<%# Eval("image_name") %>' ImageUrl='<%# Eval("image_name", "~/Blogs/Images/Thumbs/{0}") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Entered">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Active?">
<EditItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Bind("active") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Eval("active") %>' Enabled="false" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<asp:ImageButton ID="DeleteBtn" runat="server" AlternateText="Delete Record" CommandName="Delete"
ImageUrl="~/images/Delete.gif" OnClientClick="return confirm('Are you sure you want to delete this blog?');" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle HorizontalAlign="Left"></RowStyle>
<EditRowStyle Font-Bold="False"></EditRowStyle>
<HeaderStyle CssClass="GridViewHeader" HorizontalAlign="Left"></HeaderStyle>
<AlternatingRowStyle CssClass="GridViewAltRow"></AlternatingRowStyle>
</asp:GridView>
</td>
</tr>
<tr>
<td>
<div style="text-align: center;"><asp:Label ID="recordset_lbl" runat="server" CssClass="HelpTextNormal"></asp:Label></div>
</td>
</tr>
</table>
</div>

<asp:SqlDataSource ID="SqlGetBlogs" runat="server" ConnectionString="<%$ connectionStrings:dbconn1 %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT article_id, article_header, article_description, image_name, short_link_text, article_link, article_date_entered, active FROM tWebBlogs ORDER BY article_date_entered DESC"
SelectCommandType="Text"
UpdateCommand="UPDATE tWebBlogs SET article_header=@article_header, article_description=@article_description, image_name=@image_name, short_link_text=@short_link_text, article_link=@article_link, active=@active WHERE article_id=@article_id"
UpdateCommandType="Text"
DeleteCommandType="Text"
DeleteCommand="DELETE tWebBlogs WHERE article_id=@article_id">
<DeleteParameters>
<asp:Parameter Name="article_id" />
</DeleteParameters>
</asp:SqlDataSource> 
 Please help! I am stumped!

View Replies !   View Related
Error Updating A DateTime Field
Hi, I'm having trouble updating a DateTime field in my SQL database.  Here is what I'm trying to do....I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable.  Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. ").  There is a ton more than this but here are the relevant snippets:<code>Dim CompDate As DateTimeDim aComm As SQLCommand Dim aReader As SQLDataReader Dim bSQL,bConn As String bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39) bConn = serverStuff aConn = New SQLConnection(bConn) aComm = New SQLCommand(bSQL,aConn) aConn.Open() result = aComm.ExecuteReader() 'fills controls with data While result.Read()    CompDate = result("CompleteDate")    PreviousMarkoutStatus.Text = result("StatusOfMarkout") End While result.Close() aConn.Close()sSqlCmd ="Update OneCallTickets CompleteDate=@CompleteDate, StatusOfMarkout=@StatusOfMarkout WHERE TicketFileName=@TicketFileName" dim SqlCon as New SqlConnection(serverStuff) dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon) If Flag1List.SelectedItem.Value = "No Change" Then    SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35))    SqlCmd.Parameters("@Flag1").Value = PreviousMarkoutStatus.Text    SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8))    SqlCmd.Parameters("@CompleteDate").Value = CompDateElse   SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35))    SqlCmd.Parameters("@Flag1").Value = CurrentStatus.Text    SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8))    SqlCmd.Parameters("@CompleteDate").Value = Today()End IfSqlCon.Open() SqlCmd.ExecuteNonQuery() SqlCon.Close()</code>Can anybody help me with this?  Thanks a bunch

View Replies !   View Related
Incrementing Or Updating A TIMESTAMP Field.
Hi,I'm in the process of implementing a multi-user system containing anadjacency list (tree structure). I'm using a TIMESTAMP field on eachrecord in the adjacency list in order to tell when a node has been changedsince the last read. Sometimes though, it is useful to flag a "parent" (orall ancestors or a node) as being changed if any of its children have. Isthere any way I can force an update to the parent TIMESTAMP field withoutactually modifying any of the other fields in the record? Something likethis (assuming I have a field called [Timestamp]):UPDATE Adjacency SET [Timestamp] = [Timestamp] + 1 WHERE ID = @_In_IDNow, will the timestamp be incremented here by 1, or will SQL server getconfused as in theory it must "update the timestamp after updating thetimestamp" for this record?Thanks,Robin

View Replies !   View Related
Updating A Text Field In SQL Server
Hi,I have a website using a SQL Server database to store all of it's data.Retrieving data and writing basic data back is fine, however, when i goto update one table that has a text field in it the update fails ifthe amount of data being passed to the text field is too large.Is there a way around this or a particular update i should be using?Any information would be greatly appreciated.CheersBj

View Replies !   View Related
Updating A Field For Multiple Records
Dear all,I need to update one field in a table for a given record and visit number.Example below is how the table looks -SID VISIT DLCO101 0 12101 1 16102 0 18102 2 10103 1 12103 2 14Here is how I would like it to look. The changes are the starred items.SID VISIT DLCO101 0 14*101 1 16102 0 18102 2 16*103 1 12*103 2 14I know it is an UPDATE statement, but I am not sure how to use it when Ineed to update more than one record.Thanks for the help in advance.Jeff

View Replies !   View Related
Automatic Updating Of Datetime Field
I need to automatically update a datetime field for a record to the current time whenever the record is updated.

create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

One option would be to use an instead of update trigger.

create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go

update t set value='dick' where id=1 and name='fred'
go

Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.

Any ideas?

View Replies !   View Related
Updating Time Of Smalldatetime Field
How does one update the time part of a smalldatetime field...?
2006-11-16 20:12:00 ---> 2006-11-16 16:30:00
2005-06-01 18:19:00 ---> 2005-06-01 16:30:00

I have tried using the datepart but I'm doing something incorrectly.

thanks,

Jonathan

View Replies !   View Related
Help - Updating A Field In Query Analyzer
Hi All,

I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:

USE DBMyDatabase

UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld

SELECT Pk, IDField
FROM TblToBeUpdated

What am I doing wrong? The error code I get is:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.

Thanks.

Henry

View Replies !   View Related
Problem Updating Varchar Field
Hiya!
I'm having a problem updating a varchar(4000) column in Enterprise Manager when I open the table and try to update one particular row directly. Most of my rows have approx. 100-500 characters in this column, and I can update all of them them, but one row which has 1976 characters in this varchar column doesn't let me change data by typing directly into this column, although it will let me update other columns in the same row. Altogether, my row size has a max. of approx. 5000, below the 8060 limit, so I doubt that's the problem. What Is?

Thanks,
Sarah

View Replies !   View Related
Updating A Field With Info From Other Fields
I am trying to make a field that has info from othe fields

Table = Page0
Fields = LName, FName, SS

I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234

I want to update Folder = Smith_John_1234

View Replies !   View Related
Error In Updating Field Thru Stor_proc
 

I'm trying to update 'password' field in a table thru a stored procedure which is called from my program.

I'm passing the values for password and the key fields.

While debugging I can see the values in each parameter, but I get an error that the 'value for password was not passed'.

Here's the program snippet for calling the stored proc.

All three parameters are 'varchar' type

---------------------------------------------------------------------------

dbConn.Open();

dbConn.CreateParameters(3);

dbConn.AddParameters(0, Pwd, newPwd);

dbConn.AddParameters(1, Login, pwd_Login);

dbConn.AddParameters(2, IdNum, pwd_IdNum);

result = dbMgr.ExecuteNonQuery(CommandType.StoredProcedure, SP_UPDPWD);

--------------------------------------------------------------------------------------------

EXEC @return_value = [dbo].[usp_UsrMstUpdPwdParaPwdEmailCaseNum]

@Pwd = N'3Sg7vqowIBRdfgdfgrgdjykFTjTFt5hfHfhFtFghzIG1haWRliBuYW1lPw==',

@Login = N'xyz@abc.com',

@IdNumber = N'00009'

 

--------------------------------------------------------------------------------------------

What am I missing?

View Replies !   View Related
Common(?) Problem For Updating A Text Field!
 Hello, I have an aspx page with a text box. The user may enter a large portion of text including symbols like ' or ". On form submitting I would like a database (text or varchar(MAX) ) field to be updated with the value of the text box...The query that I have created for this purpose is the following: "string query ="UPDATE Article SET A_Content='" + content + "' WHERE A_Id=" + id; As I said before the user may (and in most cases must) enter special characters like ' (example: bla bla blah it's very good bla bla blah).The problem is that ' or " close the query string (SQL) and interpreter things that I am reffering to a field's name instead of the fields value (I think) resulting the following error:The identifier that starts with...is too long.Maximum length is 128.Unclosed quotation mark after the character string ...I think that it must be a very common problem with many solutions but for some reason I cant find anything on the web.What is the common practice to overcome the problem?Is there a function that might transform text into SQL acceptable text?Should I create methods for Replacing ' with other characters like ^^^ (text can be quite big...)?Thanx for any suggestions! 

View Replies !   View Related
Updating A Field Inserts Lots Of Spaces
I have a database setup and the fields setup as nchar(200).
Now, when I update a row using the code below it inserts the text but then seems to fill the rest of the field with spaces. i.e. if the text is only 10 characters, mssql seems to put 190 characters on the end of it to make 200.
 Is there any reason how I can stop this/1 // Create new command
2 comm = new SqlCommand(
3 "UPDATE Pages SET PageBody=@PageBody, " +
4 "PageMetaTitle=@PageMetaTitle, PageMetaDesc=@PageMetaDesc, PageMetaKeywords=@PageMetaKeywords " +
5 "WHERE PageID=@PageID", conn);
6
7 // Add command parameters
8 comm.Parameters.Add("@PageID", System.Data.SqlDbType.Int);
9 comm.Parameters["@PageID"].Value = idTextBox.Text;
10 comm.Parameters.Add("@PageBody", System.Data.SqlDbType.NVarChar);
11 comm.Parameters["@PageBody"].Value = contentTextBox.Text;
12 comm.Parameters.Add("@PageMetaTitle", System.Data.SqlDbType.NVarChar);
13 comm.Parameters["@PageMetaTitle"].Value = titleTextBox.Text;
14 comm.Parameters.Add("@PageMetaDesc", System.Data.SqlDbType.NVarChar);
15 comm.Parameters["@PageMetaDesc"].Value = descriptionTextBox.Text;
16 comm.Parameters.Add("@PageMetaKeywords", System.Data.SqlDbType.NVarChar);
17 comm.Parameters["@PageMetaKeywords"].Value = keywordsTextBox.Text;
 

View Replies !   View Related
Updating The Image Field In SQL Server Via DataSet
Hello,I am trying to update the Image type field named as BlobData in sqlServer 2000. I capture the record in a data set to have the schema and try to update the BlobData field of type Image by assigning it a value of buffer as below. but my assignment seems to be wrong and generates an error saying Object reference not set.
Code=========================
Dim fileBuffer(contentLength) As Byte
 
Dim attachmentFile As HttpPostedFile = Me.fileUpload_fu.PostedFile
 
attachmentFile.InputStream.Read(fileBuffer, 0, contentLength)
 
Dim cn As SqlClient.SqlConnection
 
Try
      Dim sSQL As String
      Dim cmd As SqlClient.SqlCommand
      Dim da As SqlClient.SqlDataAdapter
      Dim ds As System.Data.DataSet = New DataSet
 
      cn = New SqlClient.SqlConnection(myconnectionString)
 
cn.Open()
 
sSQL = "SELECT * FROM Attachment WHERE ID = " & attachmentRecordID
                           
cmd = New SqlClient.SqlCommand(sSQL, cn)
 
da = New SqlClient.SqlDataAdapter(cmd)
 
da.Fill(ds)
If (ds.Tables(0).Rows.Count = 1) Then
 
      ' ======================================
' ERROR GENERATED HERE
' ======================================
 
ds.Tables("Attachment").Rows(0).Item("BlobData") = fileBuffer
' ====================================== 
 
da.Update(ds, "Attachment")
 
Return True
Else
Return False
End If
 
Catch ex As Exception
Me.error_lbl.Text = ex.Message
            Return False
Finally
cn.Close()
End Try
==========================
Can anyone please help this one out.
Cheers.Imran.

View Replies !   View Related
Updating Field Based On Record Count
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.


Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc


All help is greatly appreciated.

View Replies !   View Related
SQL - Updating Empty Text Field Is Enterring -1
I'm trying to do a simple update like I have done countless timesbefore.However, when I update the empty fields in this table it places a -1into the field rather than the enterred value.It is indexed with duplicates allowed. It says that zero lenght isset to yes.What can I do about this?if Request.form("ouserid") <> "" Thenvar2 = " and userid = '" & Request.form("ouserid") & "' "Elsevar2 = " and userid is null "End ifif Request.form("orepid") <> "" Thenvar1 = " repid = '" & Request.form("orepid") & "'"Elsevar1 = " repid is null "End if"Update [Website Settings] set userid = '" & REquest.form("userid") &"' and repid = '" & Request.form("repid") & "' where " & var1 & var2& var3Thanks!

View Replies !   View Related
Inserting / Updating LongText Field Using WriteText?
Hi, I'm trying to store large strings to a database, so am using thetext field type (LongText). I have used this before when storing thehtml of a webpage, and was able to store more than 255 characters byusing just a normal update sql statement. Now I'm trying to store thebody of research papers, and must be doing something different, as Ican only store 255 characters.Can someone explain why SQL Server doesn't like what I am doing -should I be using the WriteText / UpdateText function? If so, pleaseexplain by example how I would do that, and why doing that works.Thanks so much,Iain

View Replies !   View Related
Updating Varchar Field In SQL Server 2000
I would like to update a varchar field with an update statement that appends information to what is currently stored in the field, for example in the Statement field I may currently have a name (Mark) and I want to add a unique identifier to the end of the name so that it may look like, Markq1572, is there a way to do this in an update statement?

View Replies !   View Related
Best Practice For Updating Single Field In N Rows
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance

View Replies !   View Related
Alternative Way Of Updating Text Field That Has An Apostrophe
Hi ....currently I am using two single quotes to update text that has an apostrophe.....i was wondering if there is any other way of doing this more efficiently....

for ex: if I have to add the text ...'The prism's uses are....' ......I just add two single quotes after the 'm'in the database modification page and I get the required result of a single quote....can u let me know if there is some other way to do this....

Thanks a lot in advance.....

View Replies !   View Related
Updating Single Quote In Varchar Field
hi!
I am working on a database where i have to update a few things.The following is the table

AgentId Disposition
(VarChar) (Varchar)

Agnt112 Don't Call
Agnt113 Answer
Agnt114 Don't Call

Already there are single quotes in the Disposion Column.
now my Query is:-

Update table1 set Disposition='donot call again' where
disposition='don't call'

now this throws a error as there is a single quote in the column.
Kindly help
thnx.

View Replies !   View Related
Updating Fact Field From Source Table....
 

Hi,

 
I have source table , fact table and four dim. tables , I  have to update a field in fact table from source table.
 
How can I do it?
 
thanks...

View Replies !   View Related
Problem Updating Null Text Field To 256 Characters
If I create a row with a nullable text column whose initial value is null and then update the column with a value that is exactly 256 characters long, the value remains null. Once I update the column to any other value (including null), it works as expected. I have not yet seen a way around this.

I am working in 6.5 SP3.

View Replies !   View Related
Updating Only A Small Part Of A Text String In A Field
Hello all,

I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:

Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.

I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.

It is also important to note that the rest of the body of the emails stored here is different depending on the email.

So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.

Thanks for the help in advance!

View Replies !   View Related
Help On Updating A Field In A Table With The Field Content Of Another Table
HI everybody need help on this..

I have two tables below

table1

country countryid

africa ___
usa ___
italy ___
Spain ___

table2

countryid country name

1 africa
2 germany
3 italy
4 usa


I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

THE RESULT WOULD BE

country countryid

africa 1
usa 4
italy 3
spain 0

thanks

View Replies !   View Related
About SQL Field Type
Hi FriendsI have question.I connected oracle and MS access tables(via Delphi7).I wanna get type of field in indicated table. For example if it isinteger or boolean e.t.cPlease tell me the SQL function or whatever that retrievs type of field.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
What Should B The Field Type
I'm using Microsoft SQL Server 2000 on windows 2000 Professional OS.

I want to make a field having capacity of 100,000 characters. What datatype should be best for this requirement.

View Replies !   View Related
What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?
Hi,
What is the difference updating a null value  to char/varchar type column

 versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?
 
Example:
 
Table 1 : tCountry  - Name varchar(80) nullable
Table 2 :tState      - Name char(2) nullable
Table 3 :tCountryDetails  - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View Replies !   View Related
Sum Of A Field That Has A Type Varchar
Hi guys
        I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message.
I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
 
Thanks
-Sarah
Select SUM(Convert(int, calculatedValue))
from monitor.dbo.monHistory
where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
 
This is the error message I recieve:
Conversion failed when converting the varchar value '274.2' to data type int.
 

View Replies !   View Related
Duration Data Field Type
Hi, I want to store a time duration such as 1:30 (mm:ss), 1:00, or 1:23 in a SQL 2005 database.  What is the best data field type for this data?  DateTime or TimeStamp?  Thanks 

View Replies !   View Related
SQL Datetime Field Type Command Help??
I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.

View Replies !   View Related
Save SQL XML Field Type To A Xml File
Hi, I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file? Thanks Bones 

View Replies !   View Related
Contains Keyword For Integer Field Type
I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.Something like a CONTAINS keyword...If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved