Our DB has a large amount of tables, and whenever we update a field variable (in this case a varchar length) which we did in nearly all of the tables, we then have to go through and update the stored procedures as well. Is there a way to do this automatically, or can this only be done manually.
View 2 RepliesIs it possible to get SQL Server to automatically update the table/field names in your stored procedures when changing those table/field names in the database?
View 2 Replies View RelatedThis is a general question regarding the use of view and storedprocedures. I'm fairly new to databases and SQL.I've created a SQL database using an Access Data Project ("ADP") andI'm satified with the table structure. I've moved on to building somefront ends for our users.I'm running into situations where I want subreports to be built fromqueries [views or stored procedures-I don't know which to use so I usethe term query] that are dependent on the values in other controls.I've played with stored procedures and I've figured out how to sendcriteria to a stored procedure and then dynamically change the recordsource of a subreport.However, I'm running into cases where I can't add records to theresults of a stored procedure. The table I'm running a stored procedureon has five fields: (1) Primary Key for each record, (2) FundID that'sa primary key in another table, (3) CompanyID that's a primary key inanother table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute2 of the (Fund/Company).The stored procedure filters the set of Fund/Companies based on aFundID from a form. I can update this stored procedure. However, forusers, they would like to see the Fund Name from the table that hasunique FundIDs. As soon as I include that into the stored procedure, Ican no longer add records.My questions are many:1. Is there a primer online that discusses the theory behind myquestion? Recordsets, updatability, working with recordsets in forms?2. What are some best practices for developing subreports, combo boxes,list boxes, etc. where the data is dependent on the values in a control3. I'm struggling with the best ways to grab objects on a form. If I'mon the main form I'm comfortable working with theMe.__object__.__sub-oject routine. However, if I'm in one subform whereI need another subform to change based on the record I'm in, I feelthat my code to get at the subform is very klunky..forms.main form name.sub form name.form.record sourceI don't even know how I figured out the "form" part before recordsource. Again, are there some basic rules or guides about navigatingthrough forms in VBA?4. Should I be developing front ends in some other environment?I know it's a lot, but all the advice from the newsgroups seems topresuppose some knowledge about how ADP, ADO, ODBC..blah blah and Ican't seem to find any documents about ADP and SQL.
View 3 Replies View Related
I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.
I have performed the process manually running the stored procedures, providing the values directly and everything works fine.
I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.
One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.
This has become rather frustrating and would appreciate any assistance.
Thanks.
I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.
My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.
I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true
I've tried two approaches... one utilizes my stored procedure....
bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}
Then I tried a direct insert...
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}
Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.
Thanks for whatever help you can offer...
Jim-
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I have a batch input system consisting of two tables which I've simplified below.
(Batch Table)BatchID IntBatchTotal Decimal(18.2)
(BatchTran Table)BatchTranID IntBatchTranHeaderID Int This links to BatchID BatchTranValue Decimal(18.2)BatchTranAccountNo Int
When the batch is complete I want to move the details from the Batch files and transfer them to the relevant Accounts files.
(Account Table)AccountNoID IntAccountTotal Decimal(18.2)
(AccountTran Table)AccountTranBatchID IntAccountTranBatchRef IntAccountTranAmount Decimal(18.2)
I want to be able to run a stored procedure which selects all Batches and Transactions with a reference passed to the stored procedure (@BatchID) and create the entries in the AccountTran Table
INSERT INTO AccountTran(AccountTranBatchID, AccountTranBatchRef, AccountTranAmount) SELECT BatchTranId, BatchTranHeaderID, BatchTranValue FROM BatchTran WHERE BatchTranHeaderID=@BatchID
No problems so far, the details are created perfectly.
What I want to do next is to take the values in BatchTranValue (which have been passed into AccountTranAmount) and add it to AccountTotal field
My question is can this be completed in one stored procedure if so can you guide me as to how I achieve this?
Many thanks
Using VS.Net 2003 and C#
Why won't this update the table?
public bool changeVisibility( string Id, bool visibility )
{
using( SqlConnection conn = new SqlConnection( @SQLHelper.CONN_STRING ) )
{
using( SqlCommand command = new SqlCommand( "changeVisibility", conn ) )
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add( "@Id", Id );
command.Parameters.Add( "@visibility", visibility );
<snip>
And then the stored procedure:
CREATE PROCEDURE dbo.changeVisibility
@Id VARCHAR(20), @visibility BIT
ASBEGIN BEGIN TRAN UPDATE courseStructure SET visible = @visibility WHERE Id = @Id
COMMIT TRAN
ENDGO
The @visibility seems to be the problem.
I set it in the code as boolean. And it goes into the sp as a BIT.
This should work but doesn't.
If I change the SET visible = 0 it works ok, but not with @visibility.
Thanks,
Zath
Hi all.
First post on here, so please excuse any ignorant parts of my question :)
We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).
Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.
To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.
Could anyone point me to some useful information on other changes to look out for (within the SP's) ??
Thanks in advance for your help, and for taking the time to read this :beer:
Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN SET @Tstamp = @CHANGED_Tstamp RAISERROR('This survey has already been updated since you opened this record',16,1) RETURN 14 ENDELSE
BEGIN
SELECT * FROM tblSurvey WHERE Constructor_ID = @Constructor_ID AND --Contractor_ID = @Contractor_ID AND Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND IsAbortiveCall = @AbortiveCall IF @@ROWCOUNT>0 SET @ActionDone = 'Update'
UPDATE tblSurvey SET Constructor_ID = @Constructor_ID , SurveyorName_ID = @SurveyorName_ID , Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) , IsAbortiveCall = @AbortiveCall , Note = @Notes WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin exec dhoc_ChangeLog_Insert 'tblSurvey', @Survey_ID, @User_ID, @ActionDone, 'Main Details', @Survey_ID
end else BEGIN RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1) RETURN 10 END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1, dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code, dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID, dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2, dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name, dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date, dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID, LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address, dbo.tblProperty.TenureFROM dbo.tblPropertyType RIGHT OUTER JOIN dbo.tblProperty LEFT OUTER JOIN dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
Hello,I'm working on a grant management database for a project in my databases class and I'm having some issues updating grants into the database.Here is my situation:How this page works is, it gets a query string from a search_grant.aspx page. In this query string, it gets the grant ID of the grant the user wants to edit. If the grant id is a valid grant, it then, upon page_load in C#:1.) Creates an Sql connection set to a viewGrant stored procedure,2.) Adds in all the necessary parameters as output variables3.) Sets private members declared inside of the partial class to those values it gets from the stored procedure4.) Sets textbox controls on the page to those values5.) Displays the page with all the populated data from the stored procedureThat part works fine. I was having an issue where clicking the update button would not grab the new values that the user input into the textboxes. I later realized that the Page_Load code was being re-executed BEFORE the button was being clicked (kind of dumb but...whatever). To fix it, I placed all of the code to do the above statements inside of a: if (!Page.IsPostBack){ // Do code here}That works fine. The problem, however, is that it's STILL not updating. The stored procedure works just fine inside of the management studio, but not in the ASP Page. The code is similar to that of my new_grant.aspx page, which creates a grant into the database. Why that works and this doesn't, I don't know. Even when I hard code the values into the parameters in C#, it's not updating the data! There are no errors that are being returned, so this has really boggled my mind.Any help is greatly appreciated! Here is some sample code of what I'm doing:protected void Update_button_Click(object sender, EventArgs e){ // Create SQL connection to update Grant string ConnectionString = "connection string which works fine"; SqlConnection sqlConnection2 = new SqlConnection(); try { sqlConnection2.ConnectionString = ConnectionString; sqlConnection2.Open(); } catch (Exception Ex) { if (sqlConnection2 != null) { sqlConnection2.Dispose(); } SQLErrorLabel.Text = Ex.Message; SQLErrorLabel.Visible = true; return; } // ------------------ Update values into database ------------------- // Create the statement to use on the database SqlCommand editGrant = new SqlCommand("editGrant", sqlConnection2); editGrant.CommandType = CommandType.StoredProcedure; editGrant.Connection = sqlConnection2; // Set our values for each variable GrantName = GrantName_input.Text; ProjectDescription = ProjDesBox.Text; ReportingYear = Int32.Parse(ReportYearBox.SelectedItem.ToString()); ActivityStarted = Activity_Date.Text; DateSubmitted = Date_Submitted.Text; Audit = chkAudit.Checked; TypeID = Type_ID_input.SelectedValue; FunderID = Funder_List.SelectedValue; StatusID = Status_ID_input.SelectedValue; AcademicDepartmentID = AcademicID_List.SelectedValue; PIID = PI_List.SelectedValue; ContractNumber = txtContractNum.Text; ESUAccountNumber = txtESUAccountNum.Text; AmountAwarded = txtAmount.Text; AwardDate = Award_Date.Text; DateContractSigned = txtDateSigned.Text; ReportingNotes = ReportingNotesbox.Text; NotesNotes = GrantNotesbox.Text; string ReportingTimestamp = DateTime.Now.ToString(); string ReportingWho = Membership.GetUser().ToString(); string NotesTimestamp = DateTime.Now.ToString(); string NotesWho = Membership.GetUser().ToString(); #region insertParams // Add our parameters that SQL will be using editGrant.Parameters.AddWithValue("@GrantID", GID); editGrant.Parameters["@GrantID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@GrantName", GrantName); editGrant.Parameters["@GrantName"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ProjectDescription", ProjectDescription); editGrant.Parameters["@ProjectDescription"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingYear", ReportingYear); editGrant.Parameters["@ReportingYear"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ActivityStarted", ActivityStarted); editGrant.Parameters["@ActivityStarted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateSubmitted", DateSubmitted); editGrant.Parameters["@DateSubmitted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@Audit", Audit); editGrant.Parameters["@Audit"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@TypeID", TypeID); editGrant.Parameters["@TypeID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@StatusID", StatusID); editGrant.Parameters["@StatusID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@FunderID", FunderID); editGrant.Parameters["@FunderID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AcademicDepartmentID", AcademicDepartmentID); editGrant.Parameters["@AcademicDepartmentID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@PIID", PIID); editGrant.Parameters["@PIID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ContractNumber", ContractNumber); editGrant.Parameters["@ContractNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ESUAccountNumber", ESUAccountNumber); editGrant.Parameters["@ESUAccountNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AmountAwarded", AmountAwarded); editGrant.Parameters["@AmountAwarded"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AwardDate", AwardDate); editGrant.Parameters["@AwardDate"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateContractSigned", DateContractSigned); editGrant.Parameters["@DateContractSigned"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingNotes", ReportingNotes); editGrant.Parameters["@ReportingNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingTimestamp", ReportingTimestamp); editGrant.Parameters["@ReportingTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingWho", ReportingWho); editGrant.Parameters["@ReportingWho"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesNotes", NotesNotes); editGrant.Parameters["@NotesNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesTimestamp", NotesTimestamp); editGrant.Parameters["@NotesTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesWho", NotesWho); editGrant.Parameters["@NotesWho"].Direction = ParameterDirection.Input; #endregion // Execute the UPDATE statement to Grant editGrant.ExecuteNonQuery(); sqlConnection2.Close(); sqlConnection2.Dispose(); Response.Redirect("editedGrant.aspx?id=" + GrantIDBox.Text);}EDIT: Problem Solved! Problem was the that the GrantID wasn't being properly set.
View 6 Replies View Relatedhello im trying to update the notification by yes or no using stored procedure when the email is sent to the user. the sending o email is being accomplish using SendEmail Class. the send email class will return success and error if success or error the stored procedure will run and update the notification.
My question is how can i get the return value of that SendEmail class and how can i connect those two task?
Please Help..
I have a table Patient_Registration with columns Patient_ID,Registration_Date,RENEWAL_DATE_D
After billing This table will get updated every time , when there is any new patient registered or any old patient got renewed there account.
For New patient registration the service code is 1.
and for renewed patient the service code is 293.
CREATE PROCEDURE [dbo].[SP_UPDATE_PATIENT_REGISTRATION_RENEW_DATE]
@PID BIGINT
AS
BEGIN
UPDATE PATIENT_REGISTRATION SET RENEWAL_DATE_D = (SELECT top 1 BD.BILL_DATE_D
[Code] .....
I have an SSE system/database with a WinForms (VB.NET) application front end... i am just wondering how I can update a stored procedure in the local SSE database from my application?
This is a real big issue for us and our automated deployment.
Any help would be great!!!!!!
ward0093
Hi guys,
I have a table with following columns and records.
Empid Empname Phone Flag
14 Rajan 2143 116 Balan 4321 122 Nalini 3456 023 Ganesh 9543 0
Now i need to create a stored procedure which will convert the flag values to vice versa since it is a bit datatype. That is if execute the stored procedure it should convert all the flag values to 1 if it zero and zero's to 1. How? Pls provide me the full coding for the stored procedure.
Thanx.
If I run a update stored procedure on my SQLServer It work Fine.
But When I try it in my VB code, it's just do nothing not even an error message.
What I've got to do for being able to Update SQLTable with a stored procedure?
That's my VB code:
Dim objConnect As SqlConnection
Dim strConnect As String = System.Configuration.ConfigurationSettings.AppSettings("StringConn")
objConnect = New SqlConnection(strConnect)
Dim objCommand As New SqlCommand("internUpdate", objConnect)
objCommand.CommandType = CommandType.StoredProcedure
Try
Dim objParam As SqlParameter
objParam = objCommand.Parameters.Add("Id", SqlDbType.Int)
objParam.Direction = ParameterDirection.Input
objParam.Value = InternIDValue
objParam = objCommand.Parameters.Add("Address", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Address.Value.Trim()
objParam = objCommand.Parameters.Add("City", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = City.Value.Trim()
objParam = objCommand.Parameters.Add("ProvinceCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myProvince.SelectedValue
objParam = objCommand.Parameters.Add("PostalCode", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = PostalCode.Value.Trim()
objParam = objCommand.Parameters.Add("Phone", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone1.Value.Trim()
objParam = objCommand.Parameters.Add("Phone2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone2.Value.Trim()
objParam = objCommand.Parameters.Add("Email", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress1.Value.Trim()
objParam = objCommand.Parameters.Add("Email2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress2.Value.Trim()
objParam = objCommand.Parameters.Add("EmploymentStatusCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myStatus.SelectedValue
objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()
Catch ex As Exception
Exit Sub
End Try
Thanks!!
G'day everyone...
I have two tables: Category and Product.
CategoryID is the Foreign Key of Product table.
I want to update those tables at the same time using stored procedure.
It will work like this.
If I update the Display column of Category table to have the value of 0, it will also update any records in the Product table -that has the same CategoryID- to have the value of 0 in its Display column.
I just read something about trigger and bit gives me idea that I might use it for this case, but don't know how to write the SQL.
Can anyone please help me...
Thanks...
I'm using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case 'JobID'). Butwhat's happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 13 Replies View RelatedHello,I am having a problem when using access xp as a frontend for sql server2000.I have been trying to update a number of stored procedures (Just simpleadding fields etc) which results in access crashing with event ID 1000 and1001.Does anyone have any ideas as to what could be the problem?Thanks in advance..
View 3 Replies View Relatedi have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
Hi All,
I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen
ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int)
AS
BEGIN
UPDATE dbo.PaymentDeleted
SET EmpID_WhoDeleted = @EmpID_WhoDeleted
WHERE PmtDeletedID = @PmtDeletedID
UPDATE dbo.PaymentDeleted
SET Reason_Deleted = @Reason_Deleted
WHERE PmtDeletedID = @PmtDeletedID
END
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Me.Validate()
If Me.IsValid Then
Dim DR As SqlClient.SqlDataReader
Dim strPmtID As String
strPmtID = lblPmtIDDel.Text
Dim MySQL As String
MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'"
Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1)
SqlConnection1.Open()
DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
SqlConnection1.Close()
Dim strDeletePmt As String
strDeletePmt = lblPmtIDDel.Text
Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Dim myParam As SqlClient.SqlParameter
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int))
myParam.Direction = ParameterDirection.Input
myParam.Value = lblPmtIDDel.Text
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@EmpID_WhoDeleted", SqlDbType.VarChar))
myParam.Value = txtEmpIDDelete.Text
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@Reason_Deleted", SqlDbType.VarChar))
myParam.Value = txtDeleteComments.Text
SqlConnection1.Open()
cmd.ExecuteNonQuery()
SqlConnection1.Close()
End If
Response.Redirect("PaymentVerification.aspx")
End Sub
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
Thanks in advance,
Dimeji