The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
Hi all, i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e The Following options are not highlighting ------ Generate Insert, Update, Delete statements ------ use optimistic concurrency Plz guide me anyone..... is anything wrong with our VS 2005 software installed? Bilal
I am trying to get this where statement to work.If i just do date >=b_date it works fine, but when i do it WHERE date>=b_date AND date<=e_date it does not give me anything. I have the same Queary in PHP and it works fine is there a better way that i can do this? The data is going into a xsd then used to make a Crystal Report to display the data. The quary works fine without the WHERE in it, so i know its not the Select statment itself.
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
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.
Hello Just having an issue with my code not updating my tables. Fairly new to asp so its frustrating me that its not working Here is my code 1 Protected Sub btnAddBusDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddBusDetails.Click 2 Dim datasource As New SqlDataSource() 3 datasource.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString() 4 5 datasource.UpdateCommand = "Update PrincipalContact set [princName] = @Name, [princPosition] = 'The man', [princContactNumber] = 'At home' Where ([username] = @userName);" 6 datasource.UpdateCommandType 7 datasource.UpdateParameters.Add("userName", My.User.Name) 8 datasource.UpdateParameters.Add("Name", Principal_Name.Text) 9 datasource.UpdateParameters.Add("Position", Principal_Position.Text) 10 datasource.UpdateParameters.Add("Contact", Principal_Contact.Text) 11 datasource.Update() 12 13 14 Dim datasource2 As New SqlDataSource() 15 datasource2.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString() 16 17 datasource2.UpdateCommand = "Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName, [tradingAs] = @tradingAs, [businessDescription] = @businessDescription, [principalAddress] = @principalAddress, [tradingStatus] = @tradingStatus, [numberOfEmployees] = @numberOfEmployees, [locationsByState] = @locationsByState Where ([userName] = @userName);" 18 datasource2.UpdateParameters.Add("userName", My.User.Name) 19 datasource2.UpdateParameters.Add("insolvencyPractitioner", Insolvency_Practitioner.Text) 20 datasource2.UpdateParameters.Add("companyName", Company_Name.Text) 21 datasource2.UpdateParameters.Add("tradingAs", Trading_As.Text) 22 datasource2.UpdateParameters.Add("businessDescription", Description_of_Business.Text) 23 datasource2.UpdateParameters.Add("principalAddress", Principal_Address.Text) 24 datasource2.UpdateParameters.Add("tradingStatus", Trading_status.Text) 25 datasource2.UpdateParameters.Add("numberOfEmployees", Number_of_Employees.Text) 26 datasource2.UpdateParameters.Add("locationsByState", Location_by_state.Text) 27 28 datasource2.Update() 29 30 Response.Redirect("~/Default.aspx") 31 32 33 34 End Sub No errors are occurring and when I replace the parameters with actual text it works.eg. Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName .... to Update Firm set [insolvencyPractitioner] = 'Luke', [companyName] = 'A Company' ..... Not sure why this is happening. Can someone please give me a hand. Thanks
I have a table and I want to update it with data that is available in a different table, the Master table contains a field called RegID and the other table also has RegId - I am bombing out with this UPDATE QUERY :
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
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);
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
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))
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'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource" Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False"> <Fields> <asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" /> <asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" /> <asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" /> <asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" /> </Fields> </asp:DetailsView>
<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>" SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" > <UpdateParameters> <asp:Parameter Name="trackName" Type="String" /> <asp:Parameter Name="trackPath" Type="String" /> <asp:Parameter Name="lyrics" Type="String" /> <asp:Parameter Name="pk_trackID" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.
I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" > <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx
i am using visual web developer 2005 and SQL Express 2005 and VB as the code behindi have a table called orderdetail and i want to update the fromdesignstatus field from 0 to 1 in one of the rows containing order_id = 2so i am using the following coding in button click event Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = '1' WHERE order_id = '2'" End Sub but the field is not updatedi do not know where i have gone wrong in my coding. i am sure that my database connection string is correctplease help me
Hi, I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger: CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05 I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE) {int vendorid = Convert.ToInt32(VEE.VendorevalVendor); int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter(); SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int; pmvendorid.Value = vendorid; pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int; pmuserid.Value = userid; cmd.Parameters.Add(pmvendorid); cmd.Parameters.Add(pmuserid); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
hi, i am tryuing to use the gridview as means for the user to be able to edit delete and update columns of the database. however when it is run in the browser it allows the user to edit the fields but when i click on the update button it throws an error. can someone please offer me advice on how i can sort this problem out or provide me with any examples as i cant see what the error is. i used the option in edit columns which allows you to specify you want update delete etc controls added to the gridview. how can i make it so it supports updating? thank you Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NotSupportedException: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Hi, Can anyone tell me why my Update attempts are not working? Here is my code: <body> <form id="form1" runat="server"> <div> <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1" DefaultMode="Edit"> <Fields> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> </Fields> </asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringListings %>" SelectCommand="SELECT [City] FROM [Listings]" UpdateCommand="UPDATE [Listings] Set [City]=@City WHERE [ListingID]=@ListingGuid "> <UpdateParameters> <asp:Parameter Name="City" /> <asp:Parameter Name ="ListingGuid" /> </UpdateParameters> </asp:SqlDataSource>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> </div></form> </body> And here is my code behindProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ListingGuid = Request.QueryString("GUID") End SubProtected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated Label1.Text = "updated" Label2.Text = ListingGuidEnd Sub Please let me know what I am doing wrong?
I may have posted this in the wrong forum before, but i am trying to update a table in SQL2000 from asp.net2.0 gridview. I follow all the recommended ways and it doesnt update the row. Please help <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None">
