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);
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
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)
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))
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()
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
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?
'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
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
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
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.
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!
Hi, Here's the code I've used to try and update a new user's IP Address to a Table called Customer who's key field in the UserId: Getting the Exception Error "Incorrect Syntax near'('. " Any ideas? 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(); //This gets the active user if there is someone logged in... Guid UserId = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in user string IPAddress = Request.UserHostAddress.ToString();//This gets the IPAddress of the currently logged in user string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); using (System.Data.SqlClient.SqlConnection con =new System.Data.SqlClient.SqlConnection(cs)) { con.Open(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = con; cmd.CommandType = System.Data.CommandType.Text;
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
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.
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
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
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
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?
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
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?
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
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?
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.
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
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; } }
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.
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
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
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.
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource> And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany" DataTextField="DisplayName" DataValueField="PayrollCompanyID"> </asp:DropDownList> Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code: 1 Sub BindData() 2 3 Dim ds As New DataSet 4 Dim sda As SqlClient.SqlDataAdapter 5 Dim strSQL As String 6 Dim strCon As String 7 8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _ 9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _ 10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)" 11 12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD" 13 14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon) 15 16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString())) 17 18 sda.Fill(ds, "rsrc_Personnel") 19 20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel") 21 dgPersonnel.DataBind() 22 23 End Sub 24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well. What am I missing? Thanks for anyone's help! Cappela07
Hi, I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties Result Set : None
ConnectionType : OLEDB Connection : Connected to a Local Database (DB1) SQLSourceType : Direct Input SQL Statement : exec(?) IsQueryStorePro : False BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
