I thought this would work. I use this code to update another table in another page. In this page, after someone has cast their vote for an article, the article db is updated for the current rating. It reads all the votes so far and takes and average and is supposed to write this number to the article database. There are 2 columns. ArticleRating is a decimal and is the average and ArticleReaders is the total number of votes.
The response.write in lines 3 and 4 are the correct numbers appearing in my upper left corner of the page. They are not getting written to the db. Can someone help me understand why this is the case? 1
2 Public Function UpdateRating(ByVal ArticleID As Integer, ByVal ArticleRating As Decimal, ByVal Votes As Integer) As Boolean
3 Response.Write("Values: " & ArticleRating)
4 Response.Write("Votes: " & Votes)
5
6 Dim con As New SqlConnection(DataFuncs.GetConnectionString)
7 Const sSQL As String = "UPDATE tblArticle SET ArticleRating = @ArticleRating, ArticleReaders=@Votes WHERE ArticleID = @ArticleID"
8 Dim xSqlCommand As SqlCommand = New SqlCommand(sSQL, con)
9 Try
10 xSqlCommand.Parameters.Add("@Rating", Data.SqlDbType.Decimal)
11 xSqlCommand.Parameters("@Rating").Value = Rating
12 xSqlCommand.Parameters.Add("@Article", Data.SqlDbType.Decimal)
13 xSqlCommand.Parameters("@ArticleID").Value = ArticleID
14 con.Open()
15 xSqlCommand.ExecuteNonQuery()
16 con.Close()
17 Return True
18 Catch ex As Exception
19 Return False
20 Throw ex
21 Finally
22 xSqlCommand.Dispose()
23 con.Dispose()
24 End Try
25 End Function
I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.
The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.
Could this be due to a table-locking issue? Any suggestions?
I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.
The same package works fine when the target is on Oracle database or a SQL Server database.
For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.
Any suggestion regarding this would be really helpful.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
Hello, I am trying to update a few records in my table everything seems to work fine when I hit the submit button. But it does not acutally end up writing to the database... Can someone tell me if they see anything wrong with what I have for code? public void SaveData(){//declare sql connectionSqlConnection SQLConn = new SqlConnection();SQLConn.ConnectionString = base.m_CharterBase.GetSiteKey("EDCCConnectString");SqlCommand Cmd = new SqlCommand();//Open Connections SQLConn.Open();//Set command propertiesCmd.Connection = SQLConn;Cmd.CommandText ="Declare @ID as Int " +"UPDATE [TD_FSCL_PayPeriod] " + "Set [Quota] = @Quota, [LastChangeDate] = getdate(), [LastChangeID] = @LastChangeID, [LastChangeBy] = @LastChangeBy " + " where [ID] = @ID"; //Set parameter valueSqlParameter parmQuota = new SqlParameter();parmQuota.DbType = DbType.Int16;parmQuota.ParameterName = "@Quota";parmQuota.Value = txtQuota.Text; SqlParameter parmLastChangeID = new SqlParameter();parmLastChangeID.DbType = DbType.Int32;parmLastChangeID.ParameterName = "@LastChangeID";parmLastChangeID.Value = Master.curUser.User_ID;SqlParameter parmLastChangeBy = new SqlParameter();parmLastChangeBy.DbType = DbType.String;parmLastChangeBy.ParameterName = "@LastChangeBy";parmLastChangeBy.Value = Master.curUser.Domain_Login; //Add to parameters collectionCmd.Parameters.Add(parmQuota);Cmd.Parameters.Add(parmLastChangeID);Cmd.Parameters.Add(parmLastChangeBy); //Fire the queryCmd.ExecuteNonQuery();//Close ConnectionSQLConn.Close();} protected void btnSubmit_click(Object sender, EventArgs e){// run save method and then clear methodSaveData();clear_page();}
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
I have a database on a server that im trying to update using asp.net. I tested this on another server and everything worked perfectly. The test system was set up where the website is hosted. But the live system is placed on a different server than the webpage. Does that make a difference? Test System Client -> Website/database Live System: Client -> website -> database I created a user account to use when accessing the database on the live system because i read the double hop causes problems. By using that account i can access and view the data. But whenever i update it nothing happens. NO ERROR either. It works perfectly but does nothing. Anyone have any ideas please?!?!?!
Hi all, just need a LITTLE help here. I am very close, I know I am, but just can't seem to fit the last piece in on this. I have a page that shows current data for a customer and allows you to make changes to the data, then I have a button to push to update the system. If I hard-code in a value in place of '@Pf_Value' it will update that value, so I know my where is working, just SOMETHING missing in syntax on the set statement or something wrong on my cmd.parameters statement. Any help would be great!!Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click Dim sql As String = "update CustomerPOFlexField set [Pf_Value] = @Pf_Value where Pf_property = 'Attrib1' and Pf_CustomerNo = @CustomerNo"Dim newc As String = NewCustomer.Text Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataConnectionString").ConnectionString)Dim cmd As New SqlCommand(sql, conn) cmd.Parameters.AddWithValue("@CustomerNo", DropDownlist1.SelectedValue)cmd.Parameters.Add(New SqlParameter("@Pf_Value", TextBox2.Text)) conn.Open() cmd.ExecuteNonQuery() End Using End Sub Thanks, Randy
Hi all, Im working in VB web application.Im having some values in some textbox and trying to update it using update command and its not working for me... I used ExecuteNonQuery statement after that. The alert message after that is working for me. But the values i changed in textbox in not updating in my database. What could be wrong? cid = lbcid.Text cname = txtname.Text contactname = txtconame.Text tele = Val(txttele.Text) mob = Val(txtmob.Text) email = txtemail.Text genmess = txtgen.Text cmd = New SqlCommand("Update CompanyDetails set CompanyName='" & cname & "',ContactName='" & contactname & "',Telephone=" & tele & ",Mobile=" & mob & ",Generalmess='" & genmess & "',Email='" & email & "'where CompanyId= '" & cid & "'", con)
I'm using SQL Server Management Studio to do an extremely simple table update (I thought). I wish to fill a newly added table field with a value, however I didn't do this before... What I do is:
Im looking for example code to make a sql update... I want to use command.Parameters and variables from text boxes and i'm unsure how to do this... Please help. This code below doesn't work but it is an example of what i've been working with.. <code> { string conn = string.Empty; ConnectionStringsSection connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection; if (connectionStringsSection != null) { ConnectionStringSettingsCollection connectStrings = connectionStringsSection.ConnectionStrings; ConnectionStringSettings connString = connectStrings["whowantsConnectionString"]; conn = connString.ConnectionString; using (SqlConnection connection = new SqlConnection(conn)) using (SqlCommand command = new SqlCommand("UPDATE users SET currentscore=5)", connection)) { updateCommand.Parameters.Add("@currentscore", SQLServerDbType.numeric, 18, "currentscore"); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } }</code>
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
Here I have the following command Dim dtNow As DateTime = DateTime.NowSqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=dtnow Where [PK]=@PK"What I was ttrying to accomplish was , in my grid view, when someone clicks update, it would automatically set LW to true and set LWD to today's date. No user intervention required. However, I figured the above script would not work. What would I have to do to make LWD = dtnow? I do not want to give the user the option to update anything.
Hello, I'm using a Gridview to display a list of servers. Each server has a column in a table called "Enabled." I want to create a button that is supposed to toggle the value called "Enabled." I am able to make the button either to set Enabled to true or false, but I don't know how to make it toggle. Here is the command:UpdateCommand="UPDATE [ServerStatus] SET [Enabled] = 1 WHERE [ServerName] = @ServerName" The button is a buttonfield in the gridview:<asp:ButtonField ButtonType="Button" CommandName="Update" HeaderText="Toggle" ShowHeader="True" Text="Toggle" /> Does anyone know the syntax, or a way to make the button set Enabled to true when it is false, and false when it is set to true?
Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe
hello all..,i have problem in update sqldatasource, my code like that:me.sqldatasource.updateparameter(index).defaultvalue=valueme.sqldatasource.update()it can not update data, why?but if i use insert or delete like:me.sqldatasource.insertparameter(index).defaultvalue=valueme.sqldatasource.insert()me.sqldatasource.deleteparameter(index).defaultvalue=valueme.sqldatasource.delete()it can work for insert and delete data...can anyone give me update command code in sqldatasouce? plsss...thx...
I have the code as fallows to update my SQL data ;Sub Kaydet(ByVal TickerKod As String, ByVal op1 As Double, ByVal op2 As Double, ByVal op3 As Double, ByVal op4 As Double, ByVal op5 As Double, ByVal op6 As Double, ByVal op7 As Double, ByVal mov1 As Double, ByVal mov2 As Double) 'Dim nop1 As Decimal = FormatNumber(Replace(op1, ",", "."), 2)Dim mysource As New SqlDataSource mysource.ConnectionString = ("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Stock.mdf;Integrated Security=True;User Instance=True")mysource.UpdateCommand = ("UPDATE StockParametre SET OPT1 = " & FormatNumber(op1, 2) & ", OPT2 = " & FormatNumber(op2, 2) & ", OPT3 = " & FormatNumber(op3, 2) & ", OPT4 = " & FormatNumber(op4, 2) & ", OPT5 = " & FormatNumber(op5, 2) & ", OPT6 = " & FormatNumber(op6, 2) & ", OPT7 = " & FormatNumber(op7, 2) & ", MOVY = " & FormatNumber(mov1, 2) & ", MOVD = " & FormatNumber(mov2, 2) & " WHERE (Stock = '" & TickerKod & "')") mysource.Update()Response.Write(mysource.UpdateCommand & "<br>") End Sub When I call this code inside of Virtual Web Developer it functions perfect and updates the data..There is no problerm But when I call it like http:// .......myip/updatedata.aspx then I have the error " System.Data.SqlClient.SqlException: Incorrect Syntax near 32 I see that 32 is the value inside the update command like SET OPT1=107,32, OPT2=25,00, ........and so on Where do I make wrong ? Thanks
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, 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 i make form to update some data in table the code:protected void Update_Click(object sender, EventArgs e) {SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=AinShams;Integrated Security=True"); con.Open();SqlCommand cmd = con.CreateCommand(); int iid = int.Parse(DropDownList1.SelectedValue);string name2 = DropDownList1.SelectedItem.Text; string des2 = txt_dsc.Text;string loc2 = txt_loc.Text; int act=-1;if (active_check.Checked == true) { act = 1; } else { act = 0; }int yearsc = int.Parse(txt_yc.Text); int pr = -1;if (prep_ck.Checked == true) { pr = 1; } else { pr = 0; } cmd.CommandText = "update Faculties set FacultyName=" +name2+ ",FacultyDescription=" + des2 + ",FacultyLocation=" + loc2 + ",FacultyActive='" + act + "',FacultyYearsCount='" + yearsc + "',FacultyIsPrep='" + pr + "' where FacultyId='" + iid + "'"; //cmd.CommandText = "update Faculties set FacultyName=@name,FacultyDescription=@des,FacultyLocation=@loc,FacultyActive=@act,FacultyYearsCount=@years,FacultyIsPrep=@p where FacultyId=@iid"; // cmd.CommandText = "update Faculties set FacultyDescription=" + des2 + " where FacultyId='" + iid + "'"; //cmd.Parameters.AddWithValue(@name, name); //cmd.Parameters.AddWithValue(@des, des); //cmd.Parameters.AddWithValue(@loc, loc ); //cmd.Parameters.AddWithValue(@act, act); //cmd.Parameters.AddWithValue(@years, yearsc); //cmd.Parameters.AddWithValue(@p , pr); //cmd.Parameters.AddWithValue(@iid, iid ); cmd.ExecuteNonQuery(); con.Close(); }
but it give mw exception: Server Error in '/try' Application.
Invalid column name 'sara'.Invalid column name 'mayada'.Invalid column name 'mmmmmm'. 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.Data.SqlClient.SqlException: Invalid column name 'sara'.Invalid column name 'mayada'.Invalid column name 'mmmmmm'.Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %>or:2) Add the following section to the configuration file of your application:<configuration> <system.web> <compilation debug="true"/> </system.web></configuration>Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace:
Hi I and using gridview. And binding the data in the code behind.I need to use update command in code behind. How do I achieve this? I protected void lookUP (object sender, EventArgs e) { string strSql, strConn;
System.Text.StringBuilder whereClause = new System.Text.StringBuilder();
as Update Problemset CompanyName = @CompanyName,Firstname = @Firstname,Lastname = @Lastname,Address = @Address,PostCode = @Postcode,City = @City,Phone = @Phone,Cutype = @Cutype,ProDescript = @ProDescript,Sol = @Sol,Email = @Email where ProblemID = @ProblemID when I test the querry exec UpdateProblem10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@ht.com' what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data. Please help. I have set the ProblemID as the Primary key.
I wrote a sproc which does four things: 1. It looks at an option master to see if the record exists before inserting a new one 2. If the record is not there it inserts the optino record 3. Once the record is inserted I have to run a CASE statement on the record to determine its level 4. Once the level is determined, the record needs to be updated with the correct level. 1-3 work fine (when run without the update command). However, even though I set a criteria, UPDATE still updates and not the one records. Any idea why? set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)
BEGIN SELECT ' This option already exists in your Option Master' END ELSE BEGIN
--if the option record option does not exist, insert it
--once the option record is inserted, case it to find the its level (1-9) --update the record with the approciate level.
UPDATE Optionmaster SET optionlevel (
SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID > '0' THEN '1' END AS OptionLevel --provides the option level required to update the record FROM optionmaster WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)) --even through I specify the above criteria, it upates all records.
Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe
Hello All I m trying to update a table whose col name will be read from another table. For e.g. Table1 gives the result: 'emp1', 1, 'John' 'emp2', 2, 'Mike' Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2' I need to write one Update Statement which will handle all the cases. I tried Update Table2 set @VariableName = ....... but didnt work... How can i do that ?
Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select). Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command. How do I get the link button to trigger the update command? Thanks, Mark
1. I read from a source namely SOURCE. SOURCE is defined like
CREATE TABLE SOURCE
( f1 int, f2 int, f3 int, id int)
, where f1 is unique and id is always null after reading.
2. pass it to a Slow Change Dimension control to separate the new records and changed records
Then for new records,
3. link to a Derived Column control to add in some new columns
4. link to a OLE DB Command control to update field id before write to destination
In Step 4, I use the command :
INSERT INTO global_id_pool
SELECT MAX(id) + 1
FROM global_id_pool
UPDATE SOURCE
SET id =
(SELECT MAX(id) FROM global_id_pool)
WHERE f1 = ?
Using the command, the parameter can't be recognised with an error reported in BIDS. So eventually I had to change the query to the following to make it work
UPDATE SOURCE
SET id =
(SELECT MAX(id) + 1 FROM global_id_pool)
INSERT INTO global_id_pool
SELECT MAX(id) + 1
FROM global_id_pool
I don't mind the parameter can't be added. But after running, I found id field in the table after the OLE DB Command control is still NULL while the field in the SOURCE table in database is updated. So it seems the OLE DB Command worked on database but the data in memory cache wasn't affected.
So I just wonder if there is way to UPDATE the cache in OLE DB Command control. Many thanks for any help.
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 an update command with 7 parameters, but at run time the order to the parameters gets mixed up. I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,? I then named each of the parameters and set their sources. The parameters are like this (samepl name, then source, then type): A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update. This is how they looked (Parameter index, paramter name): [0] A[1] B[2] D[3] E[4] F[5] G[6] C It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same. I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters> No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work. Any ideas as to why this would happen or how to fix it? (I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)