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
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?
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?
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
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.
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
Hello Just having an issue with my code not updating my tables. Fairly new to asp so its frustrating me that its not working Here is my code 1 Protected Sub btnAddBusDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddBusDetails.Click 2 Dim datasource As New SqlDataSource() 3 datasource.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString() 4 5 datasource.UpdateCommand = "Update PrincipalContact set [princName] = @Name, [princPosition] = 'The man', [princContactNumber] = 'At home' Where ([username] = @userName);" 6 datasource.UpdateCommandType 7 datasource.UpdateParameters.Add("userName", My.User.Name) 8 datasource.UpdateParameters.Add("Name", Principal_Name.Text) 9 datasource.UpdateParameters.Add("Position", Principal_Position.Text) 10 datasource.UpdateParameters.Add("Contact", Principal_Contact.Text) 11 datasource.Update() 12 13 14 Dim datasource2 As New SqlDataSource() 15 datasource2.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString() 16 17 datasource2.UpdateCommand = "Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName, [tradingAs] = @tradingAs, [businessDescription] = @businessDescription, [principalAddress] = @principalAddress, [tradingStatus] = @tradingStatus, [numberOfEmployees] = @numberOfEmployees, [locationsByState] = @locationsByState Where ([userName] = @userName);" 18 datasource2.UpdateParameters.Add("userName", My.User.Name) 19 datasource2.UpdateParameters.Add("insolvencyPractitioner", Insolvency_Practitioner.Text) 20 datasource2.UpdateParameters.Add("companyName", Company_Name.Text) 21 datasource2.UpdateParameters.Add("tradingAs", Trading_As.Text) 22 datasource2.UpdateParameters.Add("businessDescription", Description_of_Business.Text) 23 datasource2.UpdateParameters.Add("principalAddress", Principal_Address.Text) 24 datasource2.UpdateParameters.Add("tradingStatus", Trading_status.Text) 25 datasource2.UpdateParameters.Add("numberOfEmployees", Number_of_Employees.Text) 26 datasource2.UpdateParameters.Add("locationsByState", Location_by_state.Text) 27 28 datasource2.Update() 29 30 Response.Redirect("~/Default.aspx") 31 32 33 34 End Sub No errors are occurring and when I replace the parameters with actual text it works.eg. Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName .... to Update Firm set [insolvencyPractitioner] = 'Luke', [companyName] = 'A Company' ..... Not sure why this is happening. Can someone please give me a hand. Thanks
I have a table and I want to update it with data that is available in a different table, the Master table contains a field called RegID and the other table also has RegId - I am bombing out with this UPDATE QUERY :
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
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'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource" Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False"> <Fields> <asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" /> <asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" /> <asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" /> <asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" /> </Fields> </asp:DetailsView>
<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>" SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" > <UpdateParameters> <asp:Parameter Name="trackName" Type="String" /> <asp:Parameter Name="trackPath" Type="String" /> <asp:Parameter Name="lyrics" Type="String" /> <asp:Parameter Name="pk_trackID" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.
I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" > <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx
i am using visual web developer 2005 and SQL Express 2005 and VB as the code behindi have a table called orderdetail and i want to update the fromdesignstatus field from 0 to 1 in one of the rows containing order_id = 2so i am using the following coding in button click event Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = '1' WHERE order_id = '2'" End Sub but the field is not updatedi do not know where i have gone wrong in my coding. i am sure that my database connection string is correctplease help me
Hi, I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger: CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05 I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE) {int vendorid = Convert.ToInt32(VEE.VendorevalVendor); int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter(); SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int; pmvendorid.Value = vendorid; pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int; pmuserid.Value = userid; cmd.Parameters.Add(pmvendorid); cmd.Parameters.Add(pmuserid); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
hi, i am tryuing to use the gridview as means for the user to be able to edit delete and update columns of the database. however when it is run in the browser it allows the user to edit the fields but when i click on the update button it throws an error. can someone please offer me advice on how i can sort this problem out or provide me with any examples as i cant see what the error is. i used the option in edit columns which allows you to specify you want update delete etc controls added to the gridview. how can i make it so it supports updating? thank you Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NotSupportedException: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Hi, Can anyone tell me why my Update attempts are not working? Here is my code: <body> <form id="form1" runat="server"> <div> <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1" DefaultMode="Edit"> <Fields> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> </Fields> </asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringListings %>" SelectCommand="SELECT [City] FROM [Listings]" UpdateCommand="UPDATE [Listings] Set [City]=@City WHERE [ListingID]=@ListingGuid "> <UpdateParameters> <asp:Parameter Name="City" /> <asp:Parameter Name ="ListingGuid" /> </UpdateParameters> </asp:SqlDataSource>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> </div></form> </body> And here is my code behindProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ListingGuid = Request.QueryString("GUID") End SubProtected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated Label1.Text = "updated" Label2.Text = ListingGuidEnd Sub Please let me know what I am doing wrong?
I may have posted this in the wrong forum before, but i am trying to update a table in SQL2000 from asp.net2.0 gridview. I follow all the recommended ways and it doesnt update the row. Please help <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None">
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
UPDATE OrderDetails SET Customer.FirstName=@FirstName,Customer.LastName=@LastName,Customer.Address1=@Address1,Custome.Address2=@Address2,Customer.Address3=@Address3,Customer.Address4=@Address4, OrderDetails.OrderAmount=@OrderAmount,OrderDetails.Status=@Status From OrderDetails INNER JOIN Customer ON OrderDetails.CustomerNumber = Customer.CustomerNumber where OrderDetails.CustomerID = @CustomerID
When iam executing the stored procedure iam getting error
The multi-part identifier "Customer.FirstName" could not be bound.
Please someone help me with this.
I want to update two tables so ia joining the two tables with customer number which in both the tables.
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
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."