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
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
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
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.
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."
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
Hello,I have been trying to load a delimited data file to SQL Server. Ihave tried both of the options that are available: each time, I getdifferent errors. This is on an eval version of SQL Server 2K, withSP 3a on a Windows XP box.First, I tried to load the data with Bulk Insert. This didn't gothrough as it requires sysadmin/bulkadmin privileges. I am the onlyperson using the SQL Server, and I wanted to grant myself thoseprivileges. But I cannot find them using the Enterprise Manager. AllI see is privileges like datareader, datawriter, etc.Then I tried to use bcp. This doesn't seem to work either as it givesthe following error:ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server doesnot exist or access denied.SQLState = 01000, NativeError = 53Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).child process exited abnormallyAnybody with a solution to make this work? For reference, I am usingthe following bcp command. I can login to the database using theserver/user/password combination with no problem:C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exetestUser.productsIN"C:/Documents and Settings/testUser/products.txt"-f "C:/Documents and Settings/testUser/prodformat.txt"-t "-" -r " "-S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK
Hi, I am tryin to run the store procedure which has bulk insert command.when i run the code,it says you don't have permission to use bulk insert command. This query/storeprocedure is running succesfully in the backend(sqlserver2000). solution is only user in Bulkadmin role has permission for bulk insert .But i don't know how to create one and use it in asp.net. Can anyone help me in this. ..Here is the code. string conn_string1 =@"Data Source=SENTHILTEST;Initial Catalog=EMPLOYEE;Integrated Security=SSPI"; SqlConnection objconn1= new SqlConnection(conn_string1); objconn1.Open(); SqlCommand command1=new SqlCommand("bulkinsert",objconn1); command1.CommandType=CommandType.StoredProcedure; command1.ExecuteNonQuery(); Response.Write("Stored procedure excecuted"); objconn1.Close(); thanks, kar
Hi, In my localmachine everything is working fine. But online only Update, Delete and Select statements are working correctly, while INSERT doesn't work and I got a similar error to this when I try to insert any data into any table: Server Error in '/' Application.
Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated. 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: Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated.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. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 So wat's wrong? Note:1-The Insert statement work correctly in localmachine.2-The application and the sql database are both in remote hosts.3-The application configuration had been checked and is correct.
hi, i am trying to insert my values into the database, however the code i have doesnt seem to work. i have looked at old posts, one suggested to take away my code behind code where the insert method has been written. i did try this but it does not seem to work, can some please help me sort out this problem and advice or examples of what i need to do will be very much appreciated, thank you. the following is the code i have code behind code 'Save vlues into database. If IsPostBack = False ThenDim test As SqlDataSource = New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString() test.InsertCommand = "INSERT INTO [UserQuiz] ([QuizID], [DateTimeComplete], [CorrectAnswerCount], [UserName]) VALUES (@QuizID, @DateTimeComplete, @CorrectAnswerCount, @UserName)"test.InsertParameters.Add("QuizID", Session("QuizID").ToString()) test.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString())test.InsertParameters.Add("CorrectAnswerCount", "12")test.InsertParameters.Add("UserName", User.Identity.Name) test.Insert() End If
when i run the program i get this error Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated. 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: Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
Line 26: test.InsertParameters.Add("UserName", User.Identity.Name) Line 27: Line 28: test.Insert() Line 29: Line 30: End If
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
Hi, The following INSERT query works in all aspects apart from the date value: String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text); The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900"). In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT. What am I doing wrong? Thanks very much. Regards Gary
Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me. My data: “lname�, “fname�, “addr�, “phone�, “lang�
My command: BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt' WITH (FIELDTERMINATOR = '","') The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to: BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt' WITH (FIELDTERMINATOR = '"') But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes?
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million Current record count in t_DTM_DATA_1 - 267 Million Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP] , ,[MAJ] ,[MINR] ,[LOCN] ,[DPT] ,[YEAR] ,[PD] ,[WK] ,[TRDT] ,[SYSTEM] ,[AMOUNT] ,[DESCRIPTION] ,[GROUP] ,[VENDOR] ,[INVOICE] ,[IDAT] ,[PO_NUMBER] ,[DDAT] ,[RCV#] ,[RDAT] ,[RSP] ,[EXPLANATION] ,[UPLOAD_DATE] ,[UPLOAD_USER] ,[UPLOAD_NAME] ,[RELEASE_DATE] ,[RELEASE_USER] ,[RELEASE_NAME] ,[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
It would be great to get some feedback on this, I've wasted nearly the whole day trying to figure it out.
I've created a simple test database called test.sdf. It contains a few tables but no data. My plan is to populate the database programatically; however, the INSERT command does not seem to be working correctly. I can see that the data actually gets added to the database when I add it and iterate over it programatically (using SqlCeCommands), but after the program completes and I look at the Table in "Server Explorer" via VS 2005, the data is no longer there!
Now note that the location of the sdf is in the Emulator's "shared folder." I have read from other posts (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=173589&SiteID=1) that you cannot create a database on emulated "storage card" with PPC 2005 emulators but I've read nothing about modifying a database which exists there. Unfortunately I do not currently have access to a "real" device so I can't test this in any other way.
cmd1.CommandText = "INSERT INTO Customers VALUES ('Joe Schmoe')";
int n = cmd1.ExecuteNonQuery();
cmd1.CommandText = "INSERT INTO Customers VALUES ('Alice Wonderland')";
n = cmd1.ExecuteNonQuery();
SqlCeCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "SELECT * FROM Customers";
SqlCeDataReader rdr = cmd2.ExecuteReader();
//this correctly prints out Joe Schmoe and Alice Wonderland
while (rdr.Read())
{
String str = rdr.GetString(0);
Console.WriteLine(str);
}
rdr.Close();
}
finally
{
conn.Close();
}
}
After running this test function, I open the Customers table in VS2005 and no Customer rows exist despite the fact that the SqlCeDataReader correctly iterated through the previously added 2 customers.
If anyone has examples of pulling the text out of textboxes and passing it to a INSERT statement which then puts the data into a SQL database table please if you could pass this on that would be great.
Hi I have the following Stored Proc which works in SQL Server 6.5 but not in SQL Server 7.0. All this Stored Proc does is Create a temp table, execute the DBCC ShowContig on a table and insert the results of the DBCC into a temp table. What am I missing. Thanks.
The code of the Stored Proc is:
/* This Stored Procedure Creates a temp table. (Step 1) */ /* Initializes a local variable @StirngToBeExecuted with */ /* a DBCC command. (Step 2) */ /* Step 3. The Command is Executed and the results of the */ /* DBCC command is inserted into Temp Table. */ /* Step 4. The results of the Temp table are shown on the */ /* Screen. */
/* This SQL Works Fine in SQL Server Version 6.5 */ /* In SQL Server 7.0 the results of the DBCC command is */ /* NOT getting inserted into the Temp table. WHY??? */
IF EXISTS (SELECT * from sysobjects where id = object_id('dbo.Test_sp') and sysstat & 0xf = 4) drop procedure dbo.Test_sp GO
Hi When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....). Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
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?
I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE [M2Data] GO /****** Object:Â Trigger [dbo].[tblContacts_UTrig]Â Â Â Script Date: 3/11/2014 9:07:13 AM ******/ SET ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET USING (SELECT LGI_GROUPID FROM LOAN_GROUPING WHERE LG_LOANID = 22720 AND LG_ISACTIVE = 1) AS SOURCE
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 have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
