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;
I am working on an application to analyse down time on a production line system. The system has about 40 rows inserted per minute. The inserts are coming from about 10 different stations.
I need to a analyse the downtime between each insert from each station. The plan is to copy the data to another database on a different server so as not to affect the live system that is being updated by the production line.
However the initial requirement was to do this at night while the production line was down but now they want the data to be updated every 3 hours which means performing this huge query while the production line is bombarding the DB with inserts.
I am wondering what is the best way of doing this. Is there any way I can limit the abount of processor this proceedure will take.
HiI am using FormView, SQL 2005, VB 2005When I save the contents of Title and Area entry fields , I have lots of spaces added to the end.Title and Area are MultiLine Textboxes and database:varchar(100)I thought I had solved the issue using;TextBox Title = FormView1.FindControl("TitleTextBox") as TextBox;TextBox Area = FormView1.FindControl("AreaTextBox") as TextBox;TitleLenTrim = Title.Text.Trim().ToString();if (TitleLenTrim.Length > 100){TitleLenTrim = TitleLenTrim.Trim().Substring(0, 99);}string AreaLenTrim;AreaLenTrim = Area.Text.Trim().ToString();if (AreaLenTrim.Length > 100){AreaLenTrim = AreaLenTrim.Trim().ToString();} string insertSQL;insertSQL = "INSERT INTO Issue(";insertSQL += "ProjectID, TypeofEntryID, PriorityID ,Title, Area)";insertSQL += "VALUES ( '";insertSQL += ProjectID.Text.ToString() + "', '";insertSQL += EntryTypeID.Text.ToString() + "', '";insertSQL += PriorityID.Text.ToString() + "', '";insertSQL += TitleLenTrim.Trim().ToString() + "', '";insertSQL += AreaLenTrim.Trim().ToString() + "', '";Is there any other way I could remove spaces?Thanks in advance.
Hi, I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.
If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?
The name field in our database stores the name in the below format:
Shields~Joseph A Simmons~Russell G Resig~Benjamin R Lindsey Jr.~Harold H Jordan~Adante D Kerr~Luke D Adkins~Guillermo B Conrad~Brian P
I am trying to separate the last name and first name into 2 fields. I do not want the middle initial or Jr. in the field. I am using the statement below but i am running into an issue with names that have Jr. on them because there is an extra space in the name.
SELECT SubString([Name],CHARINDEX('~',[Name])+1, (CHARINDEX(' ',[Name])-1) - (CHARINDEX('~', [Name])-1)) as FirstName, Left(Name,CHARINDEX('~',Name)-1) as LastName FROM Employee WHERE StrtDate >'7/1/06' and StrtDate < '8/31/06' and Status = 'A' and BirthDate < GetDate() - 7665
I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric
Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?
I need to strip out all alpha chars and spaces in a given field and return only the numbers.
I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).
Any help would be appreciated.
UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter DECLARE @Parameter VARCHAR(8000) SET @Parameter = (select a_notes from dbo.notestuff as notes where a_id = '1')
I am storing formatted data (including spaces and line breaks) in a single field in a table. When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable. When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.
Does anyone know how to make the report preview show the spaces and line breaks?
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
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
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 ?
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 ?
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.
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?
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
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................
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>
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
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?
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.
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.
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.
I have a table with 100,000 addresses and I need to change 'street' to 'st', 'avenue' to 'ave' and 'road' to 'rd'. These words can appear anywhere in the field. example
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
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
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