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 each
store (STORE_KEY, ITEM_KEY, STATUS,...).
I get updated status info in a table (I'll call it NewInfo) that
has similar fields. NewInfo may contain multiple records for each
Store/Item, but I will just use the latest status. I'm not sure how
to update StoreItemStatus using each record of NewInfo. Any advice is
greatly appreciated
Thanks,
Paul
View 14 Replies
ADVERTISEMENT
Apr 12, 2007
I am able to use the following SQL Statement in Access, but when I try to use it in a View or Stored Procedure for SQL Server 2000, it says the function InStr is not a valid function. Would someone be able to assist me with modifying this query to work in SQL Server 2000. This is Spliting a field called EName into two fields called FName and LName.
SELECT EmployeeID, CountryCode, Branch, SSN, Title, HireDate, Status,
RIGHT(EName, LEN(EName) - InStr(EName, [, ]) - 1) AS FName, LEFT(EName, InStr(EName, [, ]) - 1)
AS LName
FROM dbo.Staging_Employees
Thank You,
Wayne
View 5 Replies
View Related
May 9, 2006
Dear Forum,
I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below:
Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’.
Is there a trick to putting in integers in a stored procedure?
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert( @Title varchar(50), @_Date datetime, @Venue varchar(50), @HeadlinerID int(4), @Opener varchar(150), @Doorstime varchar(50), @Showtime varchar(50), @Price varchar(50), @Onsaledate datetime, @Ticketvendor varchar(50), @TicketURL varchar(150), @Description varchar(1000),
)AS
INSERT INTO shows( Title, _Date, Venue, HeadlinerID, Opener, Doorstime, Showtime, Price, Onsaledate, Ticketvendor, TicketURL, Description)VALUES( @Title, @_Date, @Venue, @HeadlinerID, @Opener, @Doorstime, @Showtime, @Price, @Onsaledate, @Ticketvendor, @TicketURL, @Description )GO
View 3 Replies
View Related
Feb 3, 2008
I want to fill in a field whose name is stored in a variable. This code runs, but the field is not filled in afterward. I think I'm doing something wrong:
SET @field = N'bindery'
SET @ordernum = N'SM38948M08'
UPDATE Orders
SET @field = GETDATE() + 5
WHERE ordernum = @ordernum
My problem is related to using the @field variable in the UPDATE query.
How do I fix this?
Thanks!
Brian
View 8 Replies
View Related
Apr 7, 2006
I am trying to Insert or Update a record in MSSQL with a datetime variable which is modified using the DateAdd function.
Basically, I have a table of Coupons which need to expire 'x' days in the future. When I use GetDate() for the Issue Date, I have no problems. But then, when I use DateAdd to return a date in the future, I can not Insert or Update this result into the record.
I get various errors having to do with type mismatch or function not found, etc.
Can you see what I might be doing wrong?
Here's the code snippit:
<%
if(Recordset2.Fields.Item("SerialNo").Value <> "") then UpdateExpire__SerNo = Recordset2.Fields.Item("SerialNo").Value
if(DateAdd("d",2,Now) <> "") then UpdateExpire__XD = DateAdd("d",2,Now)
%>
<%
set UpdateExpire = Server.CreateObject("ADODB.Command")
UpdateExpire.ActiveConnection = MM_FreeVB_STRING
UpdateExpire.CommandText = "UPDATE dbo.Coupon SET ExpireDate = " + Replace(UpdateExpire__XD, "'", "''") + " WHERE SerialNo = " + Replace(UpdateExpire__SerNo, "'", "''") + ""
UpdateExpire.CommandType = 1
UpdateExpire.CommandTimeout = 0
UpdateExpire.Prepared = true
UpdateExpire.Execute()
%>
This produces this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '12'.
And it errors at the UpdateExpire.Execute() line.
The code was generated by Dreamweaver.
Thanks.
Lance
View 1 Replies
View Related
Jul 24, 2007
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written.
So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
View 11 Replies
View Related
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
Mar 6, 2008
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
Apr 25, 2007
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
View 1 Replies
View Related
Dec 13, 2006
Hello
I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.
I have already created the tables with primary keys on each table and foreign keys linking each table to the next.
I tried to delete a row from the parent table and was given this error:
DELETE FROM [dbo].[DomNam]WHERE [DomNam]=N' football '
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.
I tried to insert an alter table query:
ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_ID
REFERENCES dbo.Dom_CatA (Dom_ID)
ON DELETE CASCADE ON UPDATE CASCADE
But on Execute I saw this error:
Error] Incorrect syntax for definition of the 'TABLE' constraint
What is wrong with the above syntax?
Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.
I would be grateful for any advice. Thanks.
View 8 Replies
View Related
Apr 22, 2007
Hi all
My error is as follows: Incorrect syntax near '('.Line 27: acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)Line 28: acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)Line 29: acceptOrDeclineFriendship.Update()Line 30: Line 31: End Sub
Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this:
SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC">This works.
Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests">
<ItemTemplate>
(other stuff like avatar and username etc)
<asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
<asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
</ItemTemplate>
</asp:Repeater>
The code-behind (VB) which deals with this is as follows: Protected Sub Accept_Decline_Friends(ByVal sender As Object, ByVal e As CommandEventArgs)
'retrieve id of requestee and the answer accept/decline
Dim friend_id As String = e.CommandArgument.ToString
Dim answer As String = e.CommandName.ToString
'add the parameters
acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)
acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)
acceptOrDeclineFriendship.Update()
End Sub
Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid)
This relates to a sqlDataSource on my .aspx page like this: <!---- update query when user has accepted the friendship ---->
<asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx %>"
UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @Response) WHERE (FriendID = @UserID) AND (UserID = @FriendID)">
<UpdateParameters>
<asp:ControlParameter Name="UserID" ControlID="userIdValue" />
</UpdateParameters>
</asp:SqlDataSource>
Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to.
I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values.
Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!
View 1 Replies
View Related
Nov 7, 2007
Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot. --------------------------------------------------------------------------------------------------------------------------- public bool Update( string newaccount, string newfullname, string rep, string zip, string comment, string oldaccount, string oldfullname ) { SqlConnection cn = new SqlConnection(_connectionstring); SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn); cmd.Parameters.AddWithValue("@newaccount", newaccount); cmd.Parameters.AddWithValue("@newfullname", newfullname); cmd.Parameters.AddWithValue("@rep", rep); cmd.Parameters.AddWithValue("@zip", zip); cmd.Parameters.AddWithValue("@comments", comment); cmd.Parameters.AddWithValue("@oldaccount", oldaccount); cmd.Parameters.AddWithValue("@oldfullname", oldfullname); using (cn) { cn.Open(); return cmd.ExecuteNonQuery() > 1; } }
View 12 Replies
View Related
May 10, 2004
I have 2 tables, table A and B. Table A has the following fields; Phone (nvchar), Fname (nvchar), Lname (nvchar), DNC (bit). Table B has one field, PhoneNo (nvchar). I would like to update the field DNC in Table A to True(1) if the values Phone in Table A = PhoneNo in Table B.
I tried this syntax but it never updated:
UPDATE dbo.A
SET DNC = 1
WHERE (Phone = 'SELECT MAX dbo.B.PhoneNo FROM dbo.B')
These tables have over 100K records and I would like to make sure it runs through and checks every single record and not just the first 10000.
View 1 Replies
View Related
Nov 24, 2005
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
View 3 Replies
View Related
Jul 20, 2005
The UPDATE table FROM syntax is not supported by Oracle.I am looking for a syntax that is understood by both Oracle and SqlServer.Example:Table1:id name city city_id1 john newyork null2 peter london null3 hans newyork nullTable2:id city23 london24 paris25 newyorkUPDATE table1SET city_id = table2.idFROM table1, table2WHERE table1.city = Table2.cityIf possible I do not want to have two different statements for Oracle andSqlServerPlease do not tell me that these tables are not normalized, it's just anexample!Thanks for any hints.Jan van Veldhuizen
View 8 Replies
View Related
May 3, 2007
Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.
View 15 Replies
View Related
