Gridview Date Field Not Updating

Jan 31, 2008

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


ADVERTISEMENT

Want To Hide The Time From The Date Field In GridView In ASP.Net With SQL

Mar 30, 2007

I m using ASP.NET 2005 with VB,C#.
I m using GridView to display the data.

My problem is with the database field date.
I want to use only the date and the fields are manufacturing date and expiry date of some items.
But,in the gridview it displays the time which is by default same for all the entries which 12:00A.M.

I have used date picker for designing.

What should be the validation for showing only the date and not the time ??
How to hide the time from the gridview.
I m using SQL Server 2005 to store database where i have selected the datatype as date/time.

View 1 Replies View Related

Updating Database Date Field Results In Date Value Of 01/01/1900

Jun 18, 2007

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.

View 1 Replies View Related

Updating Date Field

Aug 7, 2005

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 4 Replies View Related

Need Help With Updating A Gridview From Code-behind

May 8, 2007

Hi,
I've got a gridview that displays some (but not all) columns from a sql database table. One of the columns that is NOT displayed is the table's primary key column. I'm putting together this row updating sub based on an article I found on the 'Net. Here's the code I've added for the GridView's edit routine (based on the article):Protected Sub gvPersonnelType_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles gvPersonnelType.RowEditing
gvPersonnelType.EditIndex = e.NewEditIndex
gvPersonnelTypeBindData()
End Sub

Protected Sub gvPersonnelType_RowCancellingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
gvPersonnelType.EditIndex = -1
gvPersonnelTypeBindData()
End Sub

Protected Sub gvPersonnelType_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
'Dim PersonnelTypeID As Integer'This is not displayed in the gridview, but is the primary key in the database
Dim LaborForceTypeID As Integer
Dim DefaultPayRate, DefaultPieceRate, Rebill As Decimal'These are money datatype in the database
Dim DefaultAdminCostPercent, MarkUp As Double 'These are float datatype in the database'Don't know if the following lines properly "capture" the values in the textboxes
LaborForceTypeID = Integer.Parse(gvPersonnelType.Rows(e.RowIndex).Cells(0).Text)
DefaultPayRate = CType(gvPersonnelType.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text
DefaultPieceRate = CType(gvPersonnelType.Rows(e.RowIndex).Cells(2).Controls(0), TextBox).Text
DefaultAdminCostPercent = CType(gvPersonnelType.Rows(e.RowIndex).Cells(3).Controls(0), TextBox).Text
Rebill = CType(gvPersonnelType.Rows(e.RowIndex).Cells(4).Controls(0), TextBox).Text
MarkUp = CType(gvPersonnelType.Rows(e.RowIndex).Cells(5).Controls(0), TextBox).Text

Dim gvSqlConn = New SqlConnection("DATABASECONNECTION")

gvSqlConn.open()
Dim UpdatePersonnelTypeCmd As New SqlCommand("UPDATE rsrc_PersonnelType SET LaborForceTypeID = @LaborForceTypeID, " & _
"DefaultPayRate = @DefaultPayRate, DefaultPieceRate = @DefaultPieceRate, " & _
"DefaultAdminCostPercent = @DefaultAdminCostPercent, Rebill = @Rebill, Markup = @Markup", gvSqlConn)

'Sql doesn't know what row to update: NEED WHERE STATEMENT!!!

UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@LaborForceTypeID", LaborForceTypeID))
UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@DefaultPayRate", DefaultPayRate))
UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@DefaultPieceRate", DefaultPieceRate))
UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@DefaultAdminCostPercent", DefaultAdminCostPercent))
UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@Rebill", Rebill))
UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@Markup", MarkUp))

UpdatePersonnelTypeCmd.ExecuteNonQuery()
gvSqlConn.close()

gvPersonnelType.EditIndex = -1
gvPersonnelTypeBindData()

End Sub
 As you can see, I've added some notes in the RowUpdating sub. I don't understand how to use the PersonnelTypeID (the database table's primary key) for the WHERE clause to update the correct row in the database.
As for the GridView itself, it's populated from the code-behind as well (as you can see from the call to "gvPersonnelTypeBindData()") and the SELECT statement used to populate the gridview doesn't use the PersonnelTypeID column either.
I'd appreciate it if someone could point me in the right direction to get this RowUpdating sub working.
Thanks!

View 5 Replies View Related

Deleting And Updating From Gridview

Nov 10, 2007

Hi,I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"        DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @TransactionID AND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @Itemid) " It gives the error that Quantityavailable is not a SET type?Thanks if you can suggest a remedy! Jon 

View 2 Replies View Related

SqlDataSource Not Updating On Editing GridView

Mar 4, 2007

Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards

==========================================================
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="QuoteItemID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" CausesValidation="False" /> <asp:BoundField DataField="QuoteItemID" HeaderText="QuoteItemID" InsertVisible="False" ReadOnly="True" SortExpression="QuoteItemID" /> <asp:BoundField DataField="QuoteID" HeaderText="QuoteID" SortExpression="QuoteID" /> <asp:BoundField DataField="ChargeCodeID" HeaderText="ChargeCodeID" SortExpression="ChargeCodeID" /> <asp:BoundField DataField="RateItemAmount" HeaderText="RateItemAmount" SortExpression="RateItemAmount" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ratesdb %>" DeleteCommand="DELETE FROM [Quote_item] WHERE [QuoteItemID] = @QuoteItemID" InsertCommand="INSERT INTO [Quote_item] ([QuoteID], [ChargeCodeID], [RateItemAmount]) VALUES (@QuoteID, @ChargeCodeID, @RateItemAmount)" SelectCommand="SELECT * FROM [Quote_item]" UpdateCommand="UPDATE [Quote_item] SET [QuoteID] = @QuoteID, [ChargeCodeID] = @ChargeCodeID, [RateItemAmount] = @RateItemAmount WHERE [QuoteItemID] = @QuoteItemID" ConflictDetection="CompareAllValues"> <DeleteParameters> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> <asp:Parameter Name="QuoteItemID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="QuoteID" Type="Int32" /> <asp:Parameter Name="ChargeCodeID" Type="Int32" /> <asp:Parameter Name="RateItemAmount" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource>

View 6 Replies View Related

Gridview - Updating Is Not Supported By Data Source 'SqlDataSourceGridView' Unless UpdateCommand Is Specified

May 13, 2008

 Hi all,I have a gridview that bound to a SqlDataSource called SqlDataSourceGridView. I have enabled Edit in my GridView, but I do all the updating in code behind with stored procedure (using onRowUpdating). Now each time when I click "Update", the update went through and all the data got updated, but I received this error: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.NotSupportedException:
Updating is not supported by data source 'SqlDataSourceGridView' unless
UpdateCommand is specified. What do I need to do to fix this problem?Thanks a lot. 

View 6 Replies View Related

Gridview - SqlDatasource - How Do You Get All Values For A Like Text Parameter Or A Boolean Field When Filtering?

Mar 18, 2008

I have a gridview connected to a sqldatasource, and it works pretty good.  It gives me the subsets of the information that I need.  But, I really want to let them choose all the companies and/or any status.  What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working.  And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24

 

View 3 Replies View Related

Trying To Return An Upcoming Expiring Date Using SQL/Gridview

Dec 21, 2007

Hello....
I'm trying to return only values that will be "expiring" between the current date and 60 days from the current date. (The data is populated in a GridView)
So far my thought process is:Show [AllTableData] WHERE [ExpirationDate] >= (today's date) AND [ExpirationDate] <= (today's date +60days)
 A.) Am I on the right track?
and
B.) What is the proper syntax for the "today's date" and "today's date +60days"
 TIA!-Scott

View 2 Replies View Related

Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.

Jun 12, 2008

Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having.   Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class  I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks! 

View 2 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

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

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

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.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007



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 1 Replies View Related

Updating Fact Table Field From Source Table Field

Apr 11, 2008




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 6 Replies View Related

Updating A Field Of Type Bit

Aug 4, 2004

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 4 Replies View Related

Updating 3 Characters In A Field

Mar 5, 2002

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

Updating A Text Field

Apr 27, 2000

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 1 Replies View Related

Need Help Updating Part Of A Field.

Aug 28, 1999

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 1 Replies View Related

Updating A Field With A Triggers

Mar 25, 2004

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

Updating Field But Not Filename

Jan 22, 2007

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 19 Replies View Related

Updating A Text Field

Feb 2, 2007

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 4 Replies View Related

Updating Field In Table

Apr 24, 2008

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 11 Replies View Related

Need Help Updating One Field In Every Table

Sep 14, 2007

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 4 Replies View Related

Create Date Field From Substring Of Text Field

Jul 20, 2005

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

View 2 Replies View Related

Need Help With Sqldatasource And Updating A Field In Sql 2005

Nov 19, 2007

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 1 Replies View Related

Error Updating A DateTime Field

Oct 11, 2005

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 7 Replies View Related

Problem Updating Varchar Field

Nov 29, 2001

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 1 Replies View Related

Automatic Updating Of Datetime Field

Jun 15, 2004

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 1 Replies View Related

Updating Time Of Smalldatetime Field

Apr 19, 2007

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 7 Replies View Related

Help - Updating A Field In Query Analyzer

Mar 12, 2004

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

Updating A Field With Info From Other Fields

May 8, 2008

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 5 Replies View Related







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