I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I have an application running off an Access database. Im trying to convert the application to a server-client architecture and thus moving everything to SQL Server 2000. I have read alot of articles about ways to accomplish that but I've still yet to decide on which approach is the best (best as in robust and scalable).
I pretty much eliminated the ODBC route due to all the layers of translation a request has to go through to reach SQL Server, although this route seems to be the quickes to accomplish.
Mind you I have plenty of time on hand and am willing to re-write the whole thing from scratch if it means a better app.
Now should I go the ADP route and keep Access as the user interface or should I rebuild the whole front End in pure Visual Basic that interacts with SQL Server? Im leaning towards the latter solution.
I haven't read any articles talking about rebuilding the whole app. using VB and SQL Server instead of just using ADP. Why so and which solution do you think is a better solution for a client-server architecture??
Thanks in advance for any replies to my questions.
I would like to simply link two tables that are in two different databases in the SAME server. I know that I could use the replication method (snapshot or merge) but I need a simpler method like the Access link table method.
I have about 30 tabs with same struture. I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that? Thanks JP
Hi guys,I have a simple field in a table in my sql server DB. All i need to dois update a count on it, from 5 to 6, from 6 to 7, so on. A simple counter.Do I have to SELECT the count field once, get the value, do the addingin my program, then do another command to update the count field? Ordoes SQL syntax allow a simple increment function?Thanks!Buck
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong. Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test") Dim cmd As SqlCommand = cn.CreateCommand cn.Open() cmd.CommandText = "cssp_family" cmd.CommandType = CommandType.StoredProcedure cmd.ExecuteNonQuery() cn.closed
I'm just getting started with SSIS and need pointing in the right direction with my first attempt to create a simple transform lookup.
The objective is to copy some columns from a table in one SQL Server 2005 db into another table in a different db on the same server. There are some simple column transforms involved but one column in the target table needs populating from a lookup as a result of a SQL Query.
So far, I have one OLE DB Source object linked to one OLE DB Destination in 'Data Flow'. I've configured the column transforms and these appear ok. However, the problem is that I can't see how to setup the Lookup. I have added a lookup transform object to the Data Flow space, linked from the appropriate OLE DB Source. The simple sql query returns the correct value when previewed. If I try to connect the output of this lookup object with the same OLE DB Destination that is the output from the first column transform, I get a warning message : -
"Cannot create connector. The destination component does not have any available inputs for use in creating a path"
There is column in the destination table available but I don't know how to direct the lookup output to it. I can see this available column by looking at the OLE Destination object properties and clicking 'Mappings'. The other columns are showing the links in the transformation with the destination column intended for the lookup output is not showing any links.
How do I linkup the output from the lookup object to the OLE Destination object? Is it ok to have two inputs into the OLE Destination object (ie. 1. the output from the OLE DB Source; and 2. The output from the lookup object)?
I am developing a asp.net app and I have a long, very long list of data fields. The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So... I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand. I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array. Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do. So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ??? Thank you Marc
Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName ( column_name data_type ) But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey? Hope really for help, because this is a importen thing, and I cant find the answer? :S
A newbie question. I am tearing my hair out trying to work out how in Sql Server 2005 to get a printout (or even better a file I can save that i could incroporate in a wrod document), or both, which shows the structure of all the tables in my database.
I want to list all tables (or selected tables perhaps) , and all columns in those tables, with the attributes of each column (nvarchar(2) etc or decimal(18,5) etc). Just a simple listing of all tables and their columns and the attributes of those columns.
Surely this must be possible with a simple one click operation in Sql Server 2005. I have created a database diagram which gives me part of what I want, but that just shows the tables, relationships, and column names, not the attributes of each column which is what I need as well.
I don't want to have to start installing third party products to do this, and I have no great script writing capabilities. Surely such a basic function is easily acheivable with one or two clicks in Sql Server 2005 from a menu somewhere in sql server mangaement studio?
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL WHILE @@ROWCOUNT > 0 BEGIN SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL END SET rowcount 0
I have one SSIS pkg when I executed this pkg I got following error message
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "column58" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "column58" (250)" failed because truncation occurred, and the truncation row disposition on "output column "column58" (250)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "D:ok_filesmusercm.txt" on data row 1. (SQL Server Import and Export Wizard)
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I am using Flat file as a source & SQL 2005 as Destination.my flat file have "Tab" as a column delimiter and "{CR}-{LF}" as row delimiter and TEXT Qualifier is ["].
Advanced setting options for flatfiles were tried, but didn't work.
For solving this problem i am also using Script Component for column 58.
I am also geting more then 60 warnings of truncation
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
Dim lblock As Boolean chkChecked = lblock strSQL = "UPDATE CLIENTS SET " If blnCompleted = True Then strSQL = strSQL & "COMPLETED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " Else strSQL = strSQL & "LAST_SAVED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " End If strSQL = strSQL & "COMMENTS = '" & FixString(txtcomments.Text) & "' " _ & "WHERE client_ID = " & iclientID & ""I want to put my booleen value lblock to sql too, I probably need value of it, It is checkbox, called chkblock, . how would I include this to update statement database field for that BLOCK =
Hi, i nid help on update statement. I using 03 and a microsoft sql server 2000 database. I use a more simple example of my error. A Northwind Database is use to update the Region table(RegionDescription) User will 1st go in WebForm2.aspx and enter a id, if found will retrieve the data to WebForm1.aspx. User type "1" and retrieve Eastern to TextBox1. User can choose to update the table by typing in a diff word into TextBox1. But when i type any word(e.g East) the page is refresh back to Webform1.aspx with the not updated data and the database is also not updated. Any idea?
WebForm2.aspx.vb Imports System.Data.SqlClient Public Class WebForm2 Inherits System.Web.UI.PageWeb Form Designer Generated Code Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page hereEnd SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Session("id") = TextBox1.Text Response.Redirect("WebForm1.aspx")End Sub End Class
WebForm1.aspx.vb Imports System.Data.SqlClient Public Class WebForm1 Inherits System.Web.UI.Page Web Form Designer Generated CodeDim cnn As New SqlConnection("Data Source=(local); Initial Catalog=Northwind;User ID=******; Password=******") Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here Label1.Text = Session("id") retrieveTitle()End SubSub retrieveTitle() cnn.Open()Dim cmd As New SqlCommand cmd.CommandText = "SELECT * FROM Region WHERE RegionID = '" + Session("id") + "'" cmd.Connection = cnnDim dr As SqlDataReader dr = cmd.ExecuteReader() If dr.Read() Then TextBox1.Text = dr("RegionDescription").ToString End If cnn.Close()End SubSub UpdateTitle(ByVal title As String) cnn.Open()Dim sqlstr As String = "UPDATE Region SET RegionDescription = '" + title + "' WHERE RegionID = '" + Session("id") + "'" Trace.Write(sqlstr)Dim cmd As New SqlCommand(sqlstr, cnn) cmd.ExecuteNonQuery() cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
I have a SQL Table with the following columns ID, Date, Meeting, Venue, Notes What Update statement do i need to update a simple grid view in Visual Studio?I have been experementing but when i click update it updates the whole column insted of the one i was trying to update. Please can you help? Thanks
I need some help. please. Here is what I got. From the webpage I can pull the following data to update a table. update Vehicle set Name='TestUnitName' ,Make='TestMake', Model='TestModel', SoftwareVersion='TestVersion', DynamicChange='1', ProviderID='1', Description='TestDescription', VIN='TestVIN', IMEI='TestSIM', EngineTypeId=' ', Phone=' ', MobilePhoneProviderID='' where VehicleID=64 But I also get the error: "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Vehicle_EngineTypes". The conflict occurred in database "Telemetry", table "dbo.EngineTypes", column 'EngineTypeId'.The statement has been terminated." How do I solve this issue? Actually what I am trying to do is, when EngineTypeId=' ' , I want to set is to NULL and same for the MobilePhoneProviderID. Any help would be appreciated. Thanks in advance.
I need to set a column value where the id is within a string. However, I need to set the column to a default value if the id is not within the string. Hope that was easy to understand!
ie:
This currently works...
SET @strSQL = 'UPDATE tblTest SET Archive = 1 WHERE RecID IN (' + @IDList + ')
If the ID is not in the list then I want that column set to 0. How can I do this?
Hi I use a update sub, the problem is that i got an error, the error is: Syntax error in UPDATE statement. I guess the UPDATE statement is: strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"
Remark:I use Acceess DataBase, exactly the same code works fine in SQL, i just changed the DataBase(From Access to SQL).
Is the problem can be in other place? Thank you very much for your assistance.
Hi, I'm having trouble writing this update statement and was wondering if anyone could help me out :
My database is sort of set up lilke this: There are 3 tables: Orders, OrderDetails and Inventory
Orders has a pk called OrderID. OD has several ProductIDs listed for that OrderID Inventory has 2 fields, the pk InventoryID(which is the same as ProductID) and QOH
So OD kinda looks like this: OrderID ProductID Quantity Cost 192 12 2 $10 192 3 1 $12 192 14 2 $50 193 12 1 $11 .... . ... ...
so what i want to do is take each productID for a specific orderid and decrement the inventory for it by OD.quantity
This is what I was trying
UPDATE Inventory Set QOH = QOH - @qty WHERE Inventory.InventoryID IN ( SELECT ProductID FROM OrderDetails WHERE OrderDetails.OrderID = @OrderID and @qty = OrderDetails.Quantity )
I am a SQL Novice - I have a table called 'users' in which I need to modify the user's email id e.g., from seanu@hotmail.com to seanu@yahoo.com and I have over 150 rows where I should be changing 'domain' name only..
Any help with an Update Statement is highly appreciated..
Is there any way to create a "dynamic" update statement in a stored procedure? Lets say I want to choose whether to update all columns in a table or just one. If I want to update just one column, is there a way to make the other columns keeping their values instead of NULL?
ID Field1 Field2 Field3 Field4 1 x y 100 0 2 xx zz 5 0 3 x y 200 1 4 a b 1 1 5 a b 2 0 6 k L 78 1 7 aa bb 25 1 8 k L 15 0
Now the problem is if I want to update a latest record's(if you go by ID)Field4 with 2 for the records where Field1 + Filed2 is unique, can I do this in a single query?
I can select the unique record by the following query
select Field1,Field2 from myTable group by Field1,Field2 having count(ID)>1
