When I try to run these queries in my stored procedure by hardcoding officeids, they run fine, but when I use the parameter @officeid and then try to run the procedure by using getEmployeeInfo_proc '001' I get no results. Can anyone see what I could be doing wrong? Thanks Here is my code:
1 SET QUOTED_IDENTIFIER ON 2 GO 3 SET ANSI_NULLS ON 4 GO 5 6 7 ALTER PROCEDURE getEmployeeInfo_proc (@officeid varchar(10))AS 8 select distinct 9 a.emplid, a.name,a.last_name, a.first_name, a.status, a.officeid, 10 b.deptid_child_node, b.dept_child_descr, a.job_descr, 11 d.officename 12 from employeeinfo_vie a, 13 dept_all_nodes_tbl b, 14 office_tbl d 15 where 16 a.status in ('A','L','P') and 17 a.officeid = d.officeid and 18 a.deptid = b.deptid_child_node and 19 a.officeid = '@officeid' 20 21 UNION 22 23 select distinct 24 e.emplid, e.name, e.last_name, e.first_name, e.status, e.officeid, 25 b.deptid_child_node, b.dept_child_descr, f.job_descr, 26 d.officename 27 from phone_admin_tbl e, 28 dept_all_nodes_tbl b, 29 office_tbl d, 30 jobs_tbl f 31 where 32 e.status in ('A','L','P') and 33 e.officeid = d.officeid and 34 e.deptid = b.deptid_child_node and 35 e.job_code = f.job_code and 36 e.officeid = '@officeid' 37 order by name 38 39 return 40 41 GO 42 SET QUOTED_IDENTIFIER OFF 43 GO 44 SET ANSI_NULLS ON 45 GO 46 47 48 49 50
I'm having trouble with this stored procedure, it gets down to the insert and then times out. Can anyone see why the insert might be wrong? I did check and at least one of the workshops is open and the @Status is showing it as Open on the client.ALTER PROCEDURE [dbo].[AddNewWorkshopAssigned]
SET @Status = (SELECT CASE WHEN (tblWorkshop.Workshop_Status) = 'Canceled' THEN 'Canceled' WHEN (tblWorkshop.Workshop_Size - COALESCE(COUNT(tblWorkshopsAttended.client_ID),0)) >= 1 THEN 'Open' ELSE 'Closed' END AS Current_Status FROM tblWorkshop INNER JOIN tblWorkshopsAttended ON tblWorkshop.Workshop_ID = tblWorkshopsAttended.workshop_id WHERE tblWorkshopsAttended.Workshop_ID=@Workshop_ID AND tblWorkshopsAttended.Wait_list = 0 GROUP BY tblWorkshop.Workshop_ID,tblWorkshop.Workshop_Size,tblWorkshop.Workshop_Status)
--Change Workshop Status if NOT canceled. If @Status <> 'Canceled' UPDATE tblWorkshop SET Workshop_Status=@Status WHERE tblWorkshop.Workshop_ID=@Workshop_ID
IF @Status = 'Open'
INSERT INTO tblWorkshopsAttended (Workshop_ID,Client_ID,Wait_list,DateEntered,EnteredBy)
I'm trying to use the "sp_columns" stored procedure to pull in some information about a table in my db, but I'm continuing to get the same error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_columns' expects parameter '@table_name', which was not supplied.
I'm not sure why, but I've re-written my code several times and can't figure out just why this error is happening. Here's a snippet of the code:
Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String Dim con As New OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection")) Dim com As New OdbcCommand("sp_columns", con) com.CommandType = CommandType.StoredProcedure
Dim param As New OdbcParameter("@table_name", table) param.OdbcType = OdbcType.VarChar com.Parameters.Add(param)
param = New OdbcParameter("@column_name", columnName) param.OdbcType = OdbcType.VarChar com.Parameters.Add(param)
Dim dr As OdbcDataReader Dim name As String
con.Open() dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR While dr.Read name = dr("TYPE_NAME") End While dr.Close() con.Close()
Dear Forum, I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below: Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’. Is there a trick to putting in integers in a stored procedure?
Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event: Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC" 'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection Dim conn As SqlConnection = New SqlConnection(connString) ' Create command
Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"
Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer
groupID = return_value.Value
If groupID < 0 Then MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If
conn.Close()
End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here @UserName nvarchar(50) = N'', @Password nvarchar(50) = N'' ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists' SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist' RETURN (-1)ENDEND All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom
I am having a bit of trouble with a stored procedure on the SQL Server that my web host is running. The stored procedure I have created for testing is a simple SELECT statement: SELECT * FROM table This code works fine with the query tool in Sqlwebadmin. But using that same code from my ASP.NET page doesn't work, it reports the error "Invalid object name 'table'". So I read a bit more about stored procedures (newbie to this) and came to the conslusion that I need to write database.dbo.table instead. But after changing the code to SELECT * FROM database.dbo.table, I get the "Invalid object name"-error in Sqlwebadmin too. The name of the database contains a "-", so I write the statements as SELECT * FROM [database].[dbo].[table]. Any suggestions what is wrong with the code? I have tried it locally with WebMatrix and MSDE before I uploaded it to the web host and it works fine locally, without specifying database.dbo.
Hello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao CREATE PROCEDURE dbo.SendRenewalEmail @ID INT AS BEGIN exec dbo.sp_send_cdosysmail_htm @From = 'yosemite.sam@acme.com', @To = 'road.runner@acme.com', @Cc = null, @BCC = null, @Subject = 'Order ' + @ID + 'Renewal Reminder', @Body = 'Hello roadrunner....' ENDGO
I created a stored procedure (see snippet below) and the owner is "dbo". I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication. I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd). I opened up the dataset in Designer so I could get to the table adapter. I selected the table adapter and went to the properties panel. I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?
If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?
Any help will be greatly appreciated!
Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory ( @DirectoryID int ) AS SET NOCOUNT ON DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)
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.
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?
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, i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do: i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview: [<<Prev] [Selected Month] [Next>>] the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads). i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year. i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing. i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
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.
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
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.
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.
Hello,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..
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
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.
I've got a weird one here. I'm running a DTS package on SQL Server2005. It copies a bunch of stored procedures. I renamed them on theoriginating server and ran the DTS again.The came over with the old name and code!I deleted the DTS and built it from scratch, and the same thinghappened.I ran SELECT * FROM sys.objects where type = 'P' on the source serverand the names were correctI'm explicitly checking which sp to copy rather than using Copy all. Ican see the sp namesI've deleted and recreated the sp on the source server using scriptsI've checked the source server nameI've Refreshed everywhereNothing worksWhy is up_Department_GetAllBySchool trying to be be pulled over whenit doesn't exist?Why is up_Department_GetBySchool not being pulled over when it doesexist?I've heard that SQL 2005 pre-SP2 has a problem where renaming anobject that has a text definition (like sprocs, functions, triggers,views) doesn't update the definition. So if you pull that objectdefinition and run it into your new database, it will use the originalscript, which has the original name.I ransp_helptext 'up_Department_GetBySchool'and checked the CREATE statement at the top. Sure enough, it had theold textI asked our NetAdmin to install SP2 on our server. Then I ransp_refreshsqlmodule 'up_Department_GetForSchool'and got this error:Invalid object name 'up_Department_GetAllForSchool'.So even the code which was supposed to fix it, doesn't. Has anyoneelse had this problem, and managed to fix it?--John Hunter
Hi, I have a problem, it is that when I try to update a record in my SQL server database, it is not updated and I recieve no error messages. This is the code behind the update button. The stored procedure is "sqlupdate".
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString Dim con As New SqlConnection(ListingConnection) Dim cmd As New SqlCommand("sqlupdate", con) cmd.CommandType = CommandType.StoredProcedure Dim id As SqlParameter = cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier) id.Direction = ParameterDirection.Input id.Value = ListingID
Dim PlaceName As SqlParameter = cmd.Parameters.Add("@PlaceName", SqlDbType.VarChar) PlaceName.Direction = ParameterDirection.Input PlaceName.Value = PlaceNameTB.Text
Dim Location As SqlParameter = cmd.Parameters.Add("@Location", SqlDbType.VarChar) Location.Direction = ParameterDirection.Input Location.Value = LocationTB.Text
Dim PropertyType As SqlParameter = cmd.Parameters.Add("@PropertyType", SqlDbType.VarChar) PropertyType.Direction = ParameterDirection.Input PropertyType.Value = PropertyTypeTB.Text
Dim Description As SqlParameter = cmd.Parameters.Add("@Description", SqlDbType.VarChar) Description.Direction = ParameterDirection.Input Description.Value = DescriptionTB.Text
Try con.Open() cmd.ExecuteNonQuery() con.Close() Catch ex As Exception
I am having trouble updating my database. I have seen a couple of other people had the same problem. Two answers I had found and tried did not work. The updating if newer I had already came across in a step by step class room tutorial written by a college professor and to make sure the save procedure came after the update procedure. I did need to make this last change but its still not updating. Any further advice on this subject would be appreciated. .
I have dataadapter and dataset that reads/writes to SQL tables. I can read. I can create "new" records. However, I have not been able to master the "updating" of an existing row. Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below. The code I using is below. I don't get error, but changes do not get written to SQL dbase. For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row. thanks to any who can help
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´m tring to create a stored procedure in SQLSERVER 2000 that receives as input parameter a code from some table. I would like to return (as output) in this stored procedure all information from this table that was linked to this code.
Example: in other words, to explaim better I would like to transforme the following SQL to stored procedured:
SELECT * FROM VENDEDOR WHERE codVendedor = '20'
considering that in this table 'VENDEDOR' we have: cod, name, address, ..... from VENDEDOR.
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
It seems that srv_paramsetoutput function always returns FAIL if type of the output parameter is LOB (varchar(max),nvarchar(max), varbinary(max)). Is there any way to return LOB parameter by an extended procedure?
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
