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
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
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>
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
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.
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
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 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................
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
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
I have a column being added with VB.net, but I can't figure out the syntax. I get the error No Value given for one or more required parameters. But no clue which one. Below is the code I am attempting.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim conp As String = "SELECT * INTO [Input] FROM [Text;DATABASE=" Dim aCon As String = "W:Glenn-123456VDDDataTest.mdb" Dim scon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" Dim Conn_1 As New OleDbConnection(scon & aCon)
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
