In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else
End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
I Want to write a single SP which inserts and updates.
CREATE PROCEDURE sp_NewPassword @UserName nvarchar(50), @OldPassword nvarchar(50), @NewPassword nvarchar(50) AS BEGIN INSERT INTO RGH_ChangePassword(vUserName, vNewPassword) VALUES(@UserName, @NewPassword) UPDATE RGH_LoginMaster SET vPassword=@NewPassword WHERE vUserName=@vUserName and vPassword=@OldPassword END
When i tried to create above sp it gives me an error saying
Msg 137, Level 15, State 2, Procedure sp_NewPassword, Line 8 Must declare the scalar variable "@vUserName".
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd)
'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
A problem has come up in designing a Web page to maintain a small reference table in SQL Server 2000 (9 columns, about 25 records).
I tried to design the Web page to allow INSERT and UPDATE operations using a single page that posts back to itself. The page contains a set of empty form fields for a new record, followed by a set of filled-in form field for each row in the table. The form fields for existing records are given a unique name based on the field name concatenated with the primary key value for that row.
If I set up the page to INSERT only, it works properly. But when I add the fields for existing records, the INSERT operation malfunctions. Specifically, anytime a set of existing fields for a particular column is added to the page, the INSERT will no longer work properly for that column. This is true for all fields except the primary key field. It always INSERTs correctly. I tried adding only some columns to the set of existing form fields. In that case, the INSERT operation added the correct values for the fields that were not listed in the existing records section, but failed for the others.
I am using the INSERT INTO syntax for that operation and the recordset .Update syntax for the edits. I tried using the recordset .AddNew/.Update syntax for the insert, but it exhibited the same problems. The column data types contain smallint, bit, nvarchar, and ntext types.
I know that the correct values are being put into the INSERT statement. I also tried renaming the INSERT form fields to be totally different than the names of the existing record fields. But the problem comes back no matter what.
If necessary, I can split the logic so that inserts and updates are handled by different pages. But I would like to make this work if possible. If a reader knows why SQL Server is causing this problem, any help would be greatly appreciated.
1.First i need to update the row if the status column is 0 to 1 2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId 3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ?
1.First i need to update the row if the status column is 0 to 1 2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId 3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced
What I am attempting to do here is check to see if a record exists in a table, if so, I will update some fields, if not, I will insert a new record into the table. This is what I have so far, I was hoping someone could let me know if any of these elements are unnecessary. SELECT [IP] FROM [Table]if (strIP == @IP){ UPDATE [Table] SET [Column = value++]} else{ INSERT INTO [Table] (IP) VALUES (@IP) } I obviously left out parameters and whatnot, I am mainly concerned with the logic here. My apologies if this is something simple.
In one statement (or whatever is most efficient if not possible), how do I update the rows in table A (based on a where clause), and insert into table B, the data from those same number of rows.
UPDATE table A by settting branch = 'ETC' where department IS NULL INSERT INTO table b the rows found in the above UPDATE statement
Note: 1.To find the rows UPDATED in the first statement, it is not sufficient to search by branch='ETC', as that will encompass more rows. 2. I do not want to use triggers as these statements will be executed once in the entire process
Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do i have a table like this Field1 Field2 Field3 FieldType1 FieldType2 FieldType3 1-when there is no data for a given combination of Field2 and Field3,i need to do "insert".i.e the very first time and only time 2-Second time,when there is already a row created for a given combination of Field2 and Field3,i would do the update onwards from there on. At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure CREATE PROCEDURE dbo.StoredProcedure ( @intField1 int, @intField2 int, @intField3 int, @intFieldValue int , @evalFieldName varchar(4) )So i am trying something like CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue) END similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing stored procedure to insert/update for each individual fields..thanks a lot
Using ms sql 2000I have 2 tables.I have a table which has information regarding a computer scan. Eachrecord in this table has a column called MAC which is the unique ID foreach Scan. The table in question holds the various scan results ofevery scan from different computers. I have an insert statement thatworks however I am having troulbe getting and update statement out ofit, not sure if I'm using the correct method to insert and thats why orif I'm just missing something. Anyway the scan results is stored as anXML document(@iTree) so I have a temp table that holds the releventinfo from that. Here is my Insert statement for the temporary table.INSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')Now here is the insert statement for the table I am having troublewith.INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, #temp.ID, #temp.ParentID,tblScanAttribute.ScanAttributeID, tblScan.ScanID,#temp.scanattribute, DateCreated = getdate(),LastModified =getdate()FROM tblScan, tblScanAttribute JOIN #temp ONtblScanAttribute.Name =#temp.NameIf there is a way to do this without the temporary table that would begreat, but I haven't figured a way around it yet, if anyone has anyideas that would be great, thanks.
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
Using MS SQL 2000I have a stored procedure that processes an XML file generated from anAudit program. The XML looks somewhat like this:<ComputerScan><scanheader><ScanDate>somedate&time</ScanDate><UniqueID>MAC address</UniqueID></scanheader><computer><ComputerName>RyanPC</ComputerName></computer><scans><scan ID = "1.0" Section= "Basic Overview"><scanattributes><scanattribute ID="1.0.0.0" ParentID=""Name="NetworkDomian">MSHOMe</scanattribute>scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....This is the Update portion of the sproc....CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)ASDECLARE @iTree intDECLARE @assetid intDECLARE @scanid intDECLARE @MAC nvarchar(50)CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Namenvarchar(50), scanattribute nvarchar(50))/* SET NOCOUNT ON */EXEC sp_xml_preparedocument @iTree OUTPUT, @docINSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))IF EXISTS(select MAC from tblAsset where MAC = @MAC)BEGINUPDATE tblAsset set DatelastScanned = (select ScanDate fromopenxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScan set ScanDate = (select ScanDate fromopenxml(@iTree,'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =getdate()FROM tblScanDetail INNER JOIN #tempON (tblScanDetail.GUIID = #temp.ID ANDtblScanDetail.GUIParentID =#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)WHERE MAC = @MAC!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERTSTATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUSSCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXISTYET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, b.ID, b.ParentID,tblScanAttribute.ScanAttributeID,@scanid, b.scanattribute, DateCreated = getdate(), LastModified =getdate()FROM tblScanDetail LEFT OUTER JOIN #temp a ON(tblScanDetail.GUIID =a.ID AND tblScanDetail.GUIParentID = a.ParentID ANDtblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN#temp b ON tblScanAttribute.Name = b.NameWHERE (tblScanDetail.GUIID IS NULL ANDtblScanDetail.GUIParentID ISNULL AND tblScanDetail.AttributeValue IS NULL)ENDELSEBEGINHere are a few table defintions to maybe help out a little too...if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAssetGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblAsset]GOCREATE TABLE [dbo].[tblAsset] ([AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[AssetTypeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DatelastScanned] [smalldatetime] NULL ,[NextScanDate] [smalldatetime] NULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NULL) ON [PRIMARY]GO-----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblScan]GOCREATE TABLE [dbo].[tblScan] ([ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetID] [int] NULL ,[ScanDate] [smalldatetime] NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblScanDetail]GOCREATE TABLE [dbo].[tblScanDetail] ([ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOTNULL ,[ScanID] [int] NULL ,[ScanAttributeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[GUIParentID] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[AttributeValue] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO------------------------------------------------------------My problem is that Insert statement that follows the update intotblScanDetail, for some reason it just seems to insert everything twiceif the update is performed. Not sure what I did wrong but any helpwould be appreciated. Thanks in advance.
Hi AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Im using VS2008, visual basic and SQL Server express.
General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?
I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.
Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature' on/off or not.
I hope thats clear
Also, could someone point me to documentation that explains each parameter in the connection string
Hello All I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully
Soo the question is: When creating a SQLDataSource in the wizard you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??
thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane... (0 row(s) affected) (1 row(s) affected) How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect) (1 row(s) affected) If anyone has any ideas where to look next, I'd appreciate it. Thanks
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.
How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??
e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??
1st query Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value
2nd query Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4 where agfield5=Value Group By joinfield1,joinfield2
Does anyone know how should I write the sql for getting the following result?
Original Table like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 3 06/12/02 2 06/12/02 3 06/12/03 2 06/12/03 3 -------------------------------
Curernt SQL:
When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.
Example, SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2 ------------------------------- [WorkDay][AgentCode] 06/12/02 2 06/12/03 2 -------------------------------
I would like to know the agent is in the specified date. The expected result like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 NULL 06/12/02 2 06/12/03 2 -------------------------------
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
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
