Trigger Update Syntax

Dec 18, 2007

I€™ve developed a trigger for SQL 2000 that works great in my test environment, but is a bit inconsistent in my production environment. The goal of this trigger is to find and update the row that was just entered in the OCNTACT2 table. It takes the highest integer value from CONTACT2.UPRONUM (yes, it€™s an nvarchar field), increments it by one, then updates the CONTACT2.UPRONUM field for the newly inserted row.
Does anyone see anything wrong with this trigger? Thank you for reading.
CREATE TRIGGER Update_UPRONUM_For_Webgrabber
ON CONTACT2
AFTER INSERT
AS
BEGIN
SET ROWCOUNT 1
UPDATE CONTACT2
SET CONTACT2.UPRONUM = CAST(C.tempColumn AS int) + 1
FROM CONTACT2 CROSS JOIN
(Select top 1 cast(upronum as int) as tempColumn from CONTACT2 AS CONTACT2_1 WHERE
(UPRONUM NOT LIKE '%[,]%') AND (UPRONUM NOT LIKE '%[a-z]%') AND (UPRONUM NOT LIKE '%[A-Z]%')
order by cast(upronum as int) desc) AS C
WHERE (CONTACT2.UPRONUM IS NULL OR CONTACT2.UPRONUM = '') AND (CONTACT2.UCOMPBY = 'WEB')
AND (CONTACT2.UWEBDATE > '12/13/2007')
SET ROWCOUNT 0
END

View 9 Replies


ADVERTISEMENT

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Trigger Syntax

Feb 2, 2007

I have a basic knowledge on trigger coding but I am not sure how to code the trigger to copy  data (from another table) that keyed off field A (of tableA) only if fieldB (of tableA)=999
Trigger on TableA: field A, field B (only if  fieldB=999)
TableB: colA, colB,colC,colD, colE, colF
TableC: dfC, dfD
if TableA rows updated and meet the condition(fieldB=999) , copy colC, colD fo tableC WHERE colA of (tableB) = fieldA (of tableA)
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER abc
ON TableA AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;Insert TableC (colC, colD)Select tableA.fieldA From Inserted tableA Inner Join TableB ON tableB.colA = tableA.fieldAWhere tableA.fieldB = 999
END
GO
 is this correctly coded? If not please show me your correction.
Thanks

View 5 Replies View Related

Syntax Trigger Help

Jan 30, 2008

This Continues a question from previous trigger advice question

Is there no global way to grab all columns for a row
I tried using * like you would with select. But it failed.
I have been unable to find an example or command that shows
if this possible. The all show you have to type out your
fields and in my case that would take time. The below
does not work. Does anyone have a advice on this, Thanx.

INSERT INTO Archive ()
Select i.*


This section of the trigger does work.
SET IDENTITY_INSERT Archive ON
INSERT INTO Archive (grid, name, address, state, zip, arc)
SELECT i.grid, i.name, i.address, i.state, i.zip, i.arc
fromInserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1

View 2 Replies View Related

Disable Trigger Syntax?

Sep 4, 2007





Code Snippet
CREATE PROCEDURE Staff_NoteGroup_insert
(

@staffID AS int,
@owner AS int,
@subject AS varchar(256),
@message AS varchar(512),
@date_add AS DateTime
)
AS
BEGIN TRANSACTION SERIALIZABLE

DECLARE @id as int
SELECT @id = (SELECT MAX(id) FROM Staff_NoteGroup) + 1

IF @id IS NULL

SET @id = 1

INSERT INTO Staff_NoteGroup VALUES(@id, @staffID, @owner, @subject, GETDATE(), GETDATE())

IF @@error <> 0 BEGIN

ROLLBACK
RETURN
END

DISABLE TRIGGER Staff_Note_insert ON DATABASE
INSERT INTO Staff_Note VALUES(1, @id, @owner, @message, @date_add)
ENABLE TRIGGER Staff_Note_insert ON DATABASE

IF @@error <> 0 BEGIN

ROLLBACK
RETURN
END
COMMIT
GO






Below is the error message.


Msg 156, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 26

Incorrect syntax near the keyword 'TRIGGER'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 28

Incorrect syntax near 'ENABLE'.

Msg 102, Level 15, State 1, Procedure Staff_NoteGroup_insert, Line 33

Incorrect syntax near 'COMMIT'.


Where is the problem?
I'm using sql server 2005

Thanks,
Max

View 6 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

T-SQL (SS2K8) :: Syntax Error With Trigger

May 9, 2014

I have a syntax error...

[code = "sql"]CREATE TRIGGER Trigger_20321 ON FACT_CUST_GRP_ICM_MO
AFTER DELETE
AS
/* DELETE trigger on FACT_CUST_GRP_ICM_MO */
/* default body for Trigger_20321 */

[Code]...

Msg 102, Level 15, State 1, Procedure Trigger_20321, Line 21
Incorrect syntax near ')'.

The error is in line: raiserror (@errno, @errmsg)

View 1 Replies View Related

SQL 2012 :: Create Trigger Syntax

May 30, 2014

ALTER TRIGGER [dbo].[Trigger1]
ON [dbo].[Table1] with execute as SELF
AFTER INSERT

[code]....

I am trying to create a trigger so every time a entry is made on a table, and the Colum1 is 'entry', it starts a job. But the users running the inserts do not have permission to Start jobs so I need to make it run as a super user. Where do i put the syntax in here? I Have tried Execute as login 'superuser' before the exec statement but it errors on the principal not being valid

View 1 Replies View Related

Update Syntax For Same Table Update

Aug 7, 2003

How do I write an update query to update a column in TabA with the information from other records in TabA?

View 2 Replies View Related

Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Update Syntax..

Sep 7, 2005

Hi (again)

I know that it's very simple question but since I know that many gurus are here, I want to ask abt it.

Ssql = "Update Holder set PID='temp_PID',CMSN=sMSN,CSN=sMSN1 where HID= " & temp_HID

when i run that sql from VB, it gives error as INVALID COLUMN name(sMSN/sMSN1).., it is for both sMSN and sMSN1 . But both of these sMSN and sMSN1 are numeric in the table. so I think I don't need for quotes.

pls tell me what is the correct syntax for it?

View 6 Replies View Related

Update Trigger To Update Another Table

Dec 17, 2001

I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)

create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as

if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end

View 1 Replies View Related

UPDATE Trigger Issue When Using UPDATE

May 30, 2008

I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.

Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating

ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)

View 7 Replies View Related

Update Trigger - Update Query

Jul 20, 2005

Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

INSTEAD OF UPDATE Trigger To Hold Update

Apr 3, 2008



hi!

i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.

i want to avoid the trigger launching for the last column (UpdatedOn).

how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.

another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)

thank you in advance.

View 8 Replies View Related

UPDATE Syntax Error

Apr 10, 2007

Hello
I am trying to update a column containing URL's and include the "www." which had previously been omitted on many URL's in the column. But I get an error when trying to UPDATE
I have tried:
UPDATE table_nameSET URL = http://www.a2zdom.com/*WHERE URL = http://a2zdom.com/*
I have tried and left out the http: and also the /* but nothing works.  Is this type of update not possible?
Thanks

View 7 Replies View Related

Update Syntax Error

Jun 12, 2007

           
string cmdTxt = "Update penberry_SubjectName set SubjectLeaderId IN (
SELECT userid FROM aspnet_users WHERE username = @subjectLeaderName)
where SubjectCode = @subjectCode";

            SqlConnection sqlconn = new SqlConnection(sqlConnStr);
            SqlCommand sqlCmd = new SqlCommand(cmdTxt, sqlconn);

           
sqlCmd.Parameters.AddWithValue("@subjectLeaderName", subjectLeaderName);
            sqlCmd.Parameters.AddWithValue("@subjectCode", subjectCode);

            sqlconn.Open();
            sqlCmd.ExecuteNonQuery(); hi guys i got this error from my program.Can anyone help me out?

View 9 Replies View Related

Update Command Syntax ...

Feb 3, 2008

Hi here's a bit of code. What am I doing wrong here?  Visual Studio isn't even accepting the Set word on line 56. It deletes it everytime.  What am I doing wrong here? Why is Visual studio putting the parenthese around the table name in 55? I generated an update query for my Websitetableadapter. Here it is:
UPDATE [tblWebSite] SET [Rating] = @Rating, WHERE (([WebSiteID] = @Original_WebSiteID))
How do I use this to update the Rating column after I've done my calculation below?

1    Imports RatingsTableAdapters2    3    4    Partial Class admin_ratings5        Inherits System.Web.UI.Page6    7        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load8            Dim I As Integer = 09            Dim J As Integer = 010           Dim Rating As Integer11           Dim Rate As Decimal12           Dim tblwebsiteAdapter As New tblWebSiteTableAdapter13           Dim tblWebsite As ratings.tblWebSiteDataTable14           tblWebsite = tblwebsiteAdapter.GetData()15           For Each tblwebsiteRow As ratings.tblWebSiteRow In tblWebsite16               Rate = 017               Dim tblLinkAdapter As New tblLinkTableAdapter18               Dim tblLink As ratings.tblLinkDataTable19               Dim tblLinkTot As ratings.tblLinkDataTable20               tblLink = tblLinkAdapter.GetSuccessfulExchanges(tblwebsiteRow.WebSiteID)21               tblLinkTot = tblLinkAdapter.GetTotalLinks(tblwebsiteRow.WebSiteID)22               For Each tbllinkRow As ratings.tblLinkRow In tblLink23                   If tbllinkRow.LinkID < 1 Then24                       I = 0.125                   Else : I = I + 126                   End If27               Next28               If I <> 0 Then29                   For Each tbllinktotrow As ratings.tblLinkRow In tblLinkTot30                       If tbllinktotrow.LinkID < 1 Then31                           J = 0.132                       Else : J = J + 133                       End If34                   Next35               End If36               If I <> 0 And J <> 0 Then37   38                   Rate = I / J39                   If Rate <= 0.3 Then40                       Rate = 041                   End If42                   If Rate <= 0.5 Then43                       Rate = 144                   End If45                   If Rate <= 0.65 Then46                       Rate = 247                   End If48                   If Rate <= 0.75 Then49                       Rate = 350                   End If51               End If52   53               Response.Write(tblwebsiteRow.WebSiteID & " " & tblwebsiteRow.SiteURL & " Rating: " & Rate & "54               I = 055               J = 056               Update(tblWebsite)57               Rating = Rate58               where(tblwebsiteRow.WebSiteID <> 0)59           Next60       End Sub61   End Class 

View 34 Replies View Related

Update Parameters Syntax

May 12, 2008

 Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating        Dim quantity As Integer        Dim weight As Integer        Dim packed As Integer        quantity = CInt(bagsOnPallet.Text)        weight = CInt(lbsInBags.SelectedValue)        packed = weight * quantity        e.Command.Parameters("@packedLbs").Value += packed        e.Command.Parameters("@AvailableLbs").Value -= packed    End Sub    

View 4 Replies View Related

SYNTAX FOR UPDATE PLZZ

Aug 2, 2005

Hi,I have an update statement which works fine in MS ACCESS: "update cbp_prest4 a, cbp_prest3 b set

 a.tempmm_88=b.tempmm,a.tanpay_88=b.tanpay where
 
a.fipstate2=b.fipstate2 and a.fipscty2=b.fipscty2 and a.naiccode=b.naiccode
 
and b.years='88'"
 But somehow when I converted my MS ACCESS database to SQL server database, the above statement is not working. I am getting an error saying that "incorrect syntax at a, line1".Can any help me in this query.Thanks,Ajith

View 1 Replies View Related

Update Syntax Error

Dec 5, 2000

hi, someone please tell me what's wrong with this update statement
It's supposed to get values for employee location from a test table, based on the first name and last name match, and update the outer table. I get this error...

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.



update abs_employee a
set a.office_location =
(select b.Location
from abs_emptest b
where b.lastname = a.last_name and
b.firstname = a.first_name)

View 2 Replies View Related

Update Query Syntax ?

Jul 16, 2004

Is there anything like below in SQL Server 7.0 ?

update (table1 inner join table2 on table1.sno = table2.sno1) set .....

Please advice.

Thanks,
Sam

View 7 Replies View Related

Syntax Error In Update?

Jul 23, 2005

Hi all,I have just written a sql statement where I want two fields updatedfrom another table. The statement like so:update kpidmatterset clientcode,clientname = (select clientcode, clientnamefrom clientconversionwhere (clientcode = kpidmatter.clintcode))where exists(select clientcode, clientnamefrom clientconversionwhere (clientcode = kpidmatter.clintcode))This refuses to work.The statement I based this on worked (below) but only updated onefield (teach me for being too ambitions!). Can anyone see anythingobvious? I know that a DDL and sample data is far more useful but inthis case I think there's a simple syntax problem.I'd be most grateful if someone could spot my error.SamWorking sample:UPDATE kpidmatterSET clientpartner = (SELECT ContactNameFROM newnamesWHERE (feeearnercode = kpidmatter.clientpartnercode))where exists(SELECT ContactNameFROM newnamesWHERE (feeearnercode = kpidmatter.clientpartnercode))

View 6 Replies View Related

Count And Update Syntax Help

Jul 20, 2005

Field Names: NOs Code Code1a UniqueID61 10 888 1062 10 888 1163 10 888 12Logic: If Count(code >1) & Count (Code1a >1)Update the (Nos) to EQUAL the same Value.ALL the Nos for the above examble should be the same value forall three records whether it's 61 for all three records of anyof the other two numbers, it doesn't matter as long as the equal the same value.How can this be done via sql?

View 5 Replies View Related

Trouble With Update Syntax

Jul 20, 2005

I'm new to SQL and can't figure out how to update my table(StoreItemStatus) that contains the current status for items in eachstore (STORE_KEY, ITEM_KEY, STATUS,...).I get updated status info in a table (I'll call it NewInfo) thathas similar fields. NewInfo may contain multiple records for eachStore/Item, but I will just use the latest status. I'm not sure howto update StoreItemStatus using each record of NewInfo. Any advice isgreatly appreciatedThanks,Paul

View 14 Replies View Related

Update Join Syntax From *=

Oct 29, 2007

Team,

Our product was written against an older version of SQL and the non-ANSI standard join operators (*= and =*). Our group is now looking to move our database to SQL Server 2005.

We are now faced with updating a lot of legacy queries that use this method of joins and I was hoping for a shove in the right direction.

I have run across a query similar to the following:
SELECT <many fields go here>
FROM a, b, c, d, e, f, g, h
WHERE a.fld1 *= d.fld1
AND e.fld3 *= h.fld3

The query, as it is with the non-ANSI join operators, returns a single record. For the life of me, I cannot figure out how to format the FROM statement to create the LEFT OUTER JOIN statement(s) to get the query to work.

The SQL Server 2005 books online seems to be lacking in the area of examples where you have multiple joins using different tables; they focus on one table joining against multiple tables instead.

Anyone have any reading suggestions or other ideas on how to get this to work?

Thanks.
Richard

View 13 Replies View Related

New 2005 Syntax With - Update Not Working

Oct 26, 2007

I have tried the following, the update part i snot working. Any idea why? alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
 
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
 
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
 
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
Update FeaturedClassifiedsSET FeaturedDisplayedCount = FeaturedDisplayedCount+1
Where
Row between
@startRowIndex And @startRowIndex+@NumRows-1
END
 
 I have tried function too for this, but function can not update table I guess.... Can I call stored procedure for each column? How?
 I thought the code above would work? What am I missing?

View 3 Replies View Related

Syntax Error With Joined Update

Mar 24, 2008

Hello all,
 Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly.  I think my UPDATE SQL statement is where the problem is.  It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40:
Incorrect syntax near '('.
 1 Dim CustID As Integer
2
3 Dim DbConnection As SqlConnection
4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString)
5 DbConnection.Open()
6 Dim DbCommand As SqlCommand
7
8 If Session("estimateid") = 0 Then
9 Dim DbSqlInsert As String
10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID"
11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
12 Else
13 Dim DbSqlUpdate As String
14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid"
15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection)
16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid"))
17
18 End If
19
20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text)
21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text)
22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text)
23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text)
24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text)
25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text)
26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text)
27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text)
28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text)
29
30
31 If Session("estimateid") = 0 Then
32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar())
33 DbCommand.Dispose()
34 Dim DbSqlInsert As String
35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID"
36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
37 DbCommand.Parameters.AddWithValue("@customerid", CustID)
38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar()))
39 Else
40 DbCommand.ExecuteNonQuery()
41 End If
42
43 DbConnection.Close()
44 DbCommand.Dispose()
 

View 3 Replies View Related

Syntax Error In UPDATE Statement.

May 2, 2008

Hi forum, im converting some code and have an issue with th following code!!! many thanks for good advice, Paul
Line 55:     SQLa = "UPDATE counted SET " & y & " = " & intClick & ", total = " & intTotal & " WHERE ID = " & RSpc.Fields("pc").ValueLine 56:     Conn.Execute(SQLa)
System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement.
Dim SQLa As String 

View 8 Replies View Related

Syntax Error In UPDATE Statement.

Apr 13, 2004

I have place a lblmessage.text = ex.Message to trace what happen and it show me a syntax error in update happen. I don't know whats wrong with my syntax. Can someone help me on this?

<%@ Page Language = "vb" Debug="true" %>
<%@ import namespace= "system.data" %>
<%@ import namespace= "system.data.oledb" %>
<script runat="server">

'set up connection
dim conn as new oledbconnection _
("provider = microsoft.jet.oledb.4.0;" & _
"data source = c:aspnetdataanking.mdb")




sub page_load(sender as object, e as eventargs)
if not page.ispostback then
filldatagrid()
end if
end sub




sub submit (sender as object, e as eventargs)
'insert new data
dim i,j as integer
dim params(7) as string
dim strtext as string
dim blngo as boolean = true

j = 0

for i = 0 to addpanel.controls.count -1
if addpanel.controls(i).gettype is _
gettype (textbox) then
strtext = ctype(addpanel.controls(i), _
textbox).text
if strtext <> "" then
params(j) = strtext
else
blngo = false
lblmessage.text = lblmessage.text & _
"you forgot to enter a value for " & _
addpanel.controls (i).id & "<p>"
lblmessage.style ("forecolor")= "red"
end if
j=j+1
end if
next

if not blngo then
exit sub
end if

dim strsql as string = "INSERT INTO tblusers " & _
"(firstname, lastname, address, city, state, " & _
"zip, phone) values (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'," & _
"'" & params(3) & "'," & _
"'" & params(4) & "'," & _
"'" & params(5) & "'," & _
"'" & params(6) & "')"

executestatement(strsql)
filldatagrid()
end sub




sub dgdata_edit (sender as object, e as datagridcommandeventargs)
filldatagrid(e.item.itemindex)
end sub




sub dgdata_delete (sender as object, e as datagridcommandeventargs)
dim strsql as string = "DELETE FROM tblusers " & _
"WHERE userid = " & e.item.itemindex + 1

executestatement(strsql)
filldatagrid()
end sub




sub dgdata_update (sender as object, e as datagridcommandeventargs)
if updatedatastore(e) then
filldatagrid(-1)
end if
end sub



sub dgdata_cancel (sender as object, e as datagridcommandeventargs)
filldatagrid(-1)
end sub



sub dgdata_pageindexchanged (sender as object, e as datagridpagechangedeventargs)
dgdata.databind()
end sub



function updatedatastore (e as datagridcommandeventargs) as boolean
dim i,j as integer
dim params(7) as string
dim strtext as string
dim blngo as boolean = true

j = 0

for i =1 to e.item.cells.count - 3
strtext = ctype(e.item.cells(i).controls(0), _
textbox).text
if strtext <> "" then
params(j) = strtext
blngo = true
j= j+1
else
blngo = false
lblmessage.text = lblmessage.text & _
"you forgot to enter a value<p>"
end if
next

if not blngo then
return false
exit function
end if

dim strsql as string = "update tblusers SET " & _
"Firstname.value = '" & params(0) & "'," & _
"lastname.value = '" & params(1) & "'," & _
"address.value = '" & params(2) & "'," & _
"city.value = '" & params(3) & "'," & _
"state.value = '" & params(4) & "'," & _
"zip.value = '" & params(5) & "'," & _
"phone.value = '" & params(6) & "'," & _
" WHERE Userid = " & ctype(e.item.cells(0). _
controls(1), label).text

executestatement (strsql)
return blngo
end function

sub filldatagrid (optional editindex as integer = -1)
' open connection
dim objcmd as new oledbcommand _
("select * from tblusers", conn)
dim objreader as oledbdatareader

try
objcmd.connection.open ()
objreader = objcmd.executereader()
catch ex as exception
lblmessage.text = "error retrieving from the " & _
"database."
end try

dgdata.datasource = objreader
if not editindex.equals(nothing) then
dgdata.edititemindex = editindex
end if

dgdata.databind()
objreader.close
objcmd.connection.close()

end sub

function executestatement (strsql)
dim objcmd as new oledbcommand(strsql, conn)

try
objcmd.connection.open()
objcmd.executenonquery()
catch ex as exception
lblmessage.text = ex.message
end try

objcmd.connection.close()
end function
</script>

<html><body>
<asp:label id= "lblmessage" runat ="server" />
<form runat= "server">
<asp:datagrid id = "dgdata" runat ="server"
bordercolor = "black"
gridlines="vertical"
cellpadding ="4"
cellspacing="0"
width = "100%"
autogeneratecolumns = "False"
ondeletecommand = "dgdata_delete"
oneditcommand = "dgdata_edit"
oncancelcommand = "dgdata_cancel"
onupdatecommand = "dgdata_update"
onpageindexchanged= "dgdata_pageindexchanged"
font-names = "arial"
font-size="8pt"
showfooter = "true"
headerstyle-backcolor= "#cccc99"
footerstyle-backcolor= "#cccc99"
itemstyle-backcolor= "#ffffff"
alternatingitemstyle-backcolor="#cccccc">

<columns>
<asp:templatecolumn headertext="id">
<itemtemplate>
<asp:label id = "name" runat = "server"
text = '<%# container.dataitem("userid")%>' />
</itemtemplate>
</asp:templatecolumn>

<asp:boundcolumn headertext = "firstname"
datafield = "firstname" />

<asp:boundcolumn headertext = "lastname"
datafield = "lastname" />

<asp:boundcolumn headertext = "address"
datafield = "address" />

<asp:boundcolumn headertext = "city"
datafield = "city" />

<asp:boundcolumn headertext = "state"
datafield = "state" />

<asp:boundcolumn headertext ="zip"
datafield = "zip" />

<asp:boundcolumn headertext ="phone"
datafield = "phone" />

<asp:editcommandcolumn
edittext="Edit"
canceltext="cancel"
updatetext="update"
headertext = "edit"/>

<asp:buttoncolumn headertext = "delete?" text = "X"
commandname = "delete"
buttontype = "pushbutton" />

</columns>
</asp:datagrid><p>

<asp:panel id= "addpanel" runat = "server">
<table>
<tr>
<td width ="100" valign = "top">
first and last name:
</td>
<td width ="300" valign = "top">
<asp:textbox id= "tbfname" runat = "server"/>
<asp:textbox id= "tblname" runat = "server"/>
</td>
</tr>
<tr>
<td valign = "top">address:</td>
<td valign= "top">
<asp:textbox id= "tbaddress" runat = "server"/>
</td>
</tr>
<tr>
<td valign = "top">city, state, zip:</td>
<td valign= "top">
<asp:textbox id= "tbcity" runat = "server"/>
<asp:textbox id= "tbstate" runat = "server"/>
<asp:textbox id= "tbzip" runat = "server"
size=5 />
</td>
</tr>
<tr>
<td valign= "top">phone:</td>
<td valign= "top">
<asp:textbox id= "tbphone" runat = "server"
size = 11/><p>
</td>
</tr>
<tr>
<td colspan = "2" valign = "top" allign = "right">
<asp:button id = "btsubmit" runat ="server" text ="add"
onclick = "submit" />
</td>
</tr>
</table>
</asp:panel>
</form>
</body></html>

View 14 Replies View Related

Running Update Syntax At Set Time

Feb 17, 2006

Hi

Is it possible for me to run an update syntax at a certain time say midnight for example?

I'm trying to update a bit field in my table (which acts as a checkbox in my Access front end), but only if three date fields are before todays date. The dates in question are in two other tables.

If so how would I go about doing this?

Thanks

View 3 Replies View Related







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