INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)
SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1
FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON
PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR]
AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
WHERE PLAN_DEMAND.BOD_INDEX IS NULL
When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number  [code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
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?
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.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?
UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]
SET [LaborBudget_USD] = Week1
FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN
[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON
Labor.Location_Code = Budget.Location_Code
JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON
Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND
I followed an example using the AdventureWorks database to set up a simple messaging test on one database:
Code Block -- We will use adventure works as the sample database USE AdventureWorks GO -- First, we need to create a message type. Note that our message type is -- very simple and allowed any type of content CREATE MESSAGE TYPE JobRequest VALIDATION = NONE GO -- Once the message type has been created, we need to create a contract -- that specifies who can send what types of messages CREATE CONTRACT JobRequestor (JobRequest SENT BY INITIATOR) GO -- The communication is between two endpoints. Thus, we need two queues to -- hold messages CREATE QUEUE RequestorQueue CREATE QUEUE ReceiverQueue GO -- Create the required services and bind them to be above created queues CREATE SERVICE Requestor ON QUEUE RequestorQueue CREATE SERVICE Receiver ON QUEUE ReceiverQueue (JobRequestor) GO -- At this point, we can begin the conversation between the two services by -- sending messages DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @message NVARCHAR(100) BEGIN BEGIN TRANSACTION; BEGIN DIALOG @conversationHandle FROM SERVICE Requestor TO SERVICE 'Receiver' ON CONTRACT JobRequestor WITH ENCRYPTION=OFF, LIFETIME= 600; -- Send a message on the conversation SET @message = N'Hello, World'; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE JobRequest (@message) COMMIT TRANSACTION END GO -- Receive a message from the queue RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue -- Cleanup DROP SERVICE Sender DROP SERVICE Receiver DROP QUEUE SenderQueue DROP QUEUE ReceiverQueue DROP CONTRACT HelloContract DROP MESSAGE TYPE HelloMessage GO
This all works fine but if I run the section that creates the message and then copy the RECEIVE section to a new query window and execute it, nothing is returned. If I run the RECEIVE section within the same query window it returns the 'Hello World' message as expected. I am new to Service Broker and so am assuming that I am missing something obvious!!
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
myCommand2.CommandText = "INSERT INTO testimonials(name,email,testimonial,approved,time) VALUES ('" + exp.escString(txtName.Text) + "','" + exp.escString(txtEmail.Text) + "','" + txtTestimonial.Text + "','" + false + "','" + DateTime.Now + "')"; ok the form has a field txtTestimonial.Text these strings work when i submit them thoughthats all folksthat"s (double quotes) that\"sthat\"s these failthat's that's that\'s that\'s tried up to 7 just to make sure Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'. I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind thatI tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess. Any help would be greatly appreciated. Jim
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
can somebody explain to me why the below update fails to update one row and updates the entire table?
Code:
UPDATE addlist SET add_s = 1 WHERE EXISTS (SELECT a.add_s, a.email_address, e.public_name FROM add a, edit e WHERE a.email_address = e.email_address and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
Hi, I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
Nothing is being inserted into the database. Â My code is below:Line 12: add_friend_source.InsertCommand = "INSERT INTO Friends (UserID, UserName, FriendName, AddedOn, IP) VALUES (@UserID, @UserName, @FriendName, @AddedOn, @IP)"Line 13: detailsview_addfriend.DataSource = add_friend_sourceLine 14: add_friend_source.Insert()Line 15: End Sub
What am I doing wrong here? I want to insert the current date into Companies.EmailDate given the Company ID ( C_ID ) error message: Incorrect syntax near the keyword 'WHERE' PROCEDURE EmailSentDate @CID intASINSERT INTO tblCompanies ( EmailDate )VALUES(GetDate())WHERE Companies.C_ID = @CID RETURN
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
Hey people im just wondering if someone could help me out with this Insert query im doing from one of the learn asp videos. I have a table called EquipmentBooking and it contains the following fields <teachingSession, int,> <staff, char(5),> <equipment, varchar(15),> <bookedOn, datetime,> <bookedFor, datetime,> now im doing the following Insert statement in Visual Studio 2005 when the submit button is clicked but all I get is the catched error exception and I just can working out why. Can someone help? heres the code im using Dim WebTimetableDataSource As New SqlDataSource()WebTimetableDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("WebTimetableConnectionString").ToString() WebTimetableDataSource.InsertCommandType = SqlDataSourceCommandType.Text WebTimetableDataSource.InsertCommand = "INSERT INTO EquipmentBooking (teachingSession, staff, equipment, bookedOn, bookedFor) VALUES (@TeachingDropDown, @StaffDropDown, @EquipmentDropDown, @DateTimeStamp, @DateTextBox)"WebTimetableDataSource.InsertParameters.Add("TeachingDropDown", TeachingDropDown.Text) WebTimetableDataSource.InsertParameters.Add("StaffDropDown", StaffDropDown.Text)WebTimetableDataSource.InsertParameters.Add("EquipmentDropDown", EquipmentDropDown.Text) WebTimetableDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now())WebTimetableDataSource.InsertParameters.Add("DateTextBox", DateTime.Now()) Dim rowsAffected As Integer = 0 Try rowsAffected = WebTimetableDataSource.Insert()Catch ex As Exception Server.Transfer("problem.aspx") Finally WebTimetableDataSource = Nothing End Try If rowsAffected <> 1 ThenServer.Transfer("confirmation.aspx") End If
This is a real head ache. Nothing I do to add a record to my SQL2k Database wil work.
I'm logged into it as "sa".
I've Tried Stored Procedures:
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As New SqlCommand("AddLender", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100) parameterUserName.Value = userName myCommand.Parameters.Add(parameterUserName)
Dim parameterName As New SqlParameter("@Name", SqlDbType.NVarChar, 100) parameterName.Value = name myCommand.Parameters.Add(parameterName)
Dim parameterCompany As New SqlParameter("@Company", SqlDbType.NVarChar, 100) parameterCompany.Value = Company myCommand.Parameters.Add(parameterCompany)
Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 100) parameterEmail.Value = email myCommand.Parameters.Add(parameterEmail)
Dim parameterContact As New SqlParameter("@Contact", SqlDbType.NVarChar, 100) parameterContact.Value = contact myCommand.Parameters.Add(parameterContact)
Dim parameterPhone As New SqlParameter("@Phone", SqlDbType.NVarChar, 100) parameterPhone.Value = Phone myCommand.Parameters.Add(parameterPhone)
Dim parameterFax As New SqlParameter("@Fax", SqlDbType.NVarChar, 100) parameterFax.Value = Fax myCommand.Parameters.Add(parameterFax)
CREATE PROCEDURE AddLender ( @Username nvarchar(100), @ModuleID int, @Email nvarchar(100), @Name nvarchar(100), @Rep nvarchar(250), @Phone nvarchar(250), @Fax nvarchar(250), @City nvarchar (100), @State nvarchar(100), @ItemID int OUTPUT ) AS INSERT INTO Lenders ( Email, Name, Rep, Phone, Fax, CIty, State ) VALUES ( @Email, @Name, @Rep, @Phone, @Fax, @City, @state
) SELECT @ItemID = @@Identity
GO
I get no Errors... I've run SQLProfiller and I don;t even see it run...
I also tried the method..
Dim strSql As String Dim objDataSet As New DataSet() Dim objConnection As OleDbConnection Dim objAdapter As OleDbDataAdapter
strSql = "Select ItemId,ModuleId,Name,Rep, Email,Phone,Fax,City,State,Address From Portal_Lenders;"
objConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionStringOledb")) objAdapter = New OleDbDataAdapter(strSql, objConnection)
objAdapter.Fill(objDataSet, "Lenders")
Dim objtable As DataTable Dim objNewRow As DataRow
I have a situation. The userID running the Stored Proc is assigned Bulk-Admin privileges . Program creates a temp# table and bulk inserts a text file. The process runs fine running as Sysadmin . However , it fails if run with that UserID with following error
"The current user is not the database or object owner of table '#Temp'. Cannot perform SET operation."
Hello, I have a stored procedure that updates my table from values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case. Here is my code, where am I going wrong?
ALTER PROCEDURE [dbo].[spUploadUser] (@userid varchar(10), @username varchar(50), @userstatus varchar(20)) AS BEGIN SET NOCOUNT ON; DECLARE @ERROR_STATE INT; BEGIN TRANSACTION INSERT INTO userprofile (uid, uname, ustatus) VALUES @userid, @username, @userstatus; SELECT @ERROR_STATE = @@ERROR; IF (@ERROR_STATE <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END ELSE COMMIT TRANSACTION END
I've amended a table to include some extra columns to track when changes are made. Next step is to amend the stored procedure that updates that table when the changes are made.
I amended an existing stored proc to include CreateTS, CreateID, ModifyTS, ModifyID. Unfortunately, the INSERT and UPDATE aren't working for the new columns.
Am fairly new to this, so not sure why it's not working? Code is below:
DECLARE @ThisBSB VarChar(6) DECLARE @intCount int DECLARE @intInserted int DECLARE @intUpdated int
SET @intInserted = 0 SET @intUpdated = 0
-- fields from New Table
DECLARE curBSB CURSOR FOR SELECT Replace(bsbnumber,'-','') FROM ztblBSBText (nolock)
OPEN curBSB
FETCH NEXT FROM curBSB INTO @ThisBSB
WHILE @@FETCH_STATUS = 0 BEGIN
--Print @ThisBSB
-- See if this BSB Already Exists SELECT @Intcount = Count(*) FROM tblBankBSB (nolock) WHERE BSBcode = @ThisBSB
IF @intCount = 0 BEGIN
-- Insert New Record --Print 'Insert: ' + @ThisBSB INSERT INTO tblBankBSB ([BSBCode] ,[BankID] ,[BranchNumber] ,[BranchName] ,[CountryID] ,[Address] ,[Suburb] ,[StateID] ,[StateCode] ,[State] ,[PostcodeID] ,[Postcode] ,[StatusID] ,[TransferedToBSB] ,[CreateID] ,[CreateTS] ,[ModifyID] ,[ModifyTS]) SELECT @ThisBSB ,tblBank.BankID ,Cast(Right(bsbnumber,3) AS Int) ,ztblBSBText.BSBName ,1 ,ztblBSBText.Address ,ztblBSBText.Suburb ,tblState.StateId ,Null ,ztblBSBText.State ,Null ,ztblBSBText.Postcode ,1 ,Null ,Null ,Null ,@UserContactID ,getDate() FROM ztblBSBText INNER JOIN tblBank (nolock) on ztblBSBText.Mnemonic = tblBank.BankCode INNER JOIN tblState (nolock) on ztblBSBText.State = tblState.State WHERE tblState.StatusID = 1 AND tblState.CountryID = 1 AND Replace(bsbnumber,'-','') = @ThisBSB
SET @intInserted = @intInserted + 1 END
ELSE BEGIN
-- See If Closed since last time this was run, and if so, update SELECT @intCount = Count(*) FROM ztblBSBText INNER JOIN tblBankBSB (nolock) ON Replace(ztblBSBText.bsbnumber,'-','') = tblBankBSB.BSBCode WHERE Replace(bsbnumber,'-','') = @ThisBSB AND ztblBSBText.BSBName = 'Closed' AND tblBankBSB.BranchName Not Like '%Closed%'
IF @intCount > 0 BEGIN
--Print 'Update: ' + @ThisBSB UPDATE tblBankBSB SET tblBankBSB.StatusID = 0 ,tblBankBSB.BranchName = tblBankBSB.BranchName + ' - Closed' ,tblBankBSB.TransferedToBSB = (SELECT replace(substring(address, 14,7),'-','') FROM ztblBSBText WHERE Replace(ztblBSBText.bsbnumber,'-','') = @ThisBSB) ,tblBankBSB.ModifyID = @UserContactID ,tblBankBSB.ModifyTS = getDate() WHERE BSBCode = @ThisBSB
SET @intUpdated = @intUpdated + 1 END
END
FETCH NEXT FROM curBSB INTO @ThisBSB
END
CLOSE curBSB DEALLOCATE curBSB
_____________________________ "Nihil est incertius volgo." - Cicero
