Hi all,
I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.
calculate age:CREATE PROCEDURE selectage ASSELECT Age = datediff(yy, birthday, getdate()) + case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0 else -1endFROM nopaymy ? is how do fix this problem:I add inWHERE (((Age)>= @age1 And (Age)<= @age2)) and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?
Currently I add a new stored procedure in Enterprise Manager by right-clicking Stored Procedure and clicking New Stored Procedure then copy/paste text of procedure into window displayed. Whenever we set up new customer, this process is repeated several times to get all of our stored procedures loaded. Is there a way to automate this process? Thanks
Hi , I am trying to add users,rules,roles and administrators to my website, of course on my local server everything was ok, but now I can't login with my users on the server. Basicly I created users and rules on my comp, and copied the App_data DB file to the rootapp_data that is on the server(Using godaddy). I understand that if the data is on the server it is no longer uses the DB in the rootapp_data , but only the asp tables which are on MSSQL database. I need help in copying the data from the data base in the App_data folder to the hosting database. I couldn't find the answers in the godaddy website help nor how to use their stored procedures to insert all the above. (if I need to use the stored procedures please explain how) Thanks very much for your help
Hi!!! I have a database called "test_DB" i have one table and some data on it. I uploaded the website and created an SQL Server account to upload the database. Can you please help me to do that task. Is there any step by step directions to do it and what files i need to add ( like ".mdf" or others...) I appreciate your help!!! Thanks...
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?
I am getting the below error Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53 Ambiguous column name 'REINS_TYPE_CD'.
below is the query i changed.
ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION] @RECON_TRNSCTN_ID int=NULL, @RECON_ITEM_ID int=NULL, @DIVISION_ID int=NULL, @REINS_TYPE_CD int=NULL, @TRANSACTION_NO char(10)=NULL, @TRANSACTION_NAME varchar(100)=NULL, @REF_NUMBER varchar(20)=NULL, @POLICY_CRS_REF_NO varchar(20)=NULL, @GL_DTFrom datetime=NULL, @GL_DTTo datetime=NULL, @TRANSACTION_CD int=NULL, @AMOUNT money=NULL, @FILE_STATUS_CD int=NULL, @TRNSCTN_OWR_CD int=NULL, @ISSUE_CD int=NULL, @SUPP_ISSUE_CD int=NULL, @IRC_CLASSIFICATION_CD int=NULL, @UNDER_90_DAYS money=NULL, @OVER_90_DAYS money=NULL, @AGING_DAYS_CNT int=NULL, @VOCHER_NO varchar(50)=NULL, @LOADED_DTFrom datetime=NULL, @LOADED_DTTo datetime=NULL, @SPUsageMode TINYINT = 0 -- This should be the last parameter
AS
------------------------------------------------------------------------------- -- SP Usage Audit Info -- DO NOT REMOVE -- All Stored Procedure code MUST be placed between Section 1 and 2 of -- SP Usage Audit code ------------------------------------------------------------------------------- -- SP Usaged Section 1 - Declare ------------------------------------------------------------------------------- DECLARE @SPStartTime DATETIME SELECT @SPStartTime = GETDATE() DECLARE @SPEndTime DATETIME DECLARE @AuditCount INT -------------------------------------------------------------------------------
FROM AIGNET.dbo.RECON_TRANSACTION AS RE INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD WHERE (@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID) AND (@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID) AND (@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID) AND (@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD) AND (@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO) AND (@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME) AND (@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER) AND (@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO) AND ((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo)) AND (@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD) AND (@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT) AND (@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD) AND (@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD) AND (@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD) AND (@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD) AND (@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD) AND (@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS) AND (@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS) AND (@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT) AND (@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO) AND ((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))
End
------------------------------------------------------------------------------- -- SP Usage Audit Info -- DO NOT REMOVE -- SP Usage Section 2 - INSERT Audit Info ------------------------------------------------------------------------------- SELECT @AuditCount = @SPUsageMode + (SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK) WHERE Parameter = 'SPUsageByPass')
------------------------------------------------------------------------------- -- Absolutely NO Stored Procedure code written beyond this point -------------------------------------------------------------------------------
I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[spMyRequests] -- Add the parameters for the stored procedure here
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int EXEC@return_value = [dbo].[EP_Conterbalances] @Start_Date_For_Totals_Date = N'20120831', @EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
Contacts ------------- UserId - int, the UserId of the user who has the contact ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this? ASPX.CS file protected void Page_Load(object sender, EventArgs e) { SqlConnection con = null;
con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; da.Fill(ds);
In trying to upgrade my sql express install from express to developer edition, I ended up having to delete all things express from my computer and do a clean install of developer edition (as i installed developer edition initially over sql express but this didnt seem to work; express edition still appeared in add remove programs etc). Anyway so i blew away the install and have a clean install of developer edition. Now when I create a new website in Visual Studio 2005 and try to add a database to the project, it gives me an error stating that i need to have sql server express 2005 installed to function properly... Do i need to have express AND developer edition of sql installed to do this? I would think developer edition of sql would enable me this functionality...http://img277.imageshack.us/img277/4228/sqlerrorie2.jpgThanks!It is also worth noting that I get the dreaded remote connection error when trying to connect to a db that I used to be able to connect to when I was using sql express (the aspnetdb database that is created when you user their membership stuff)"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "However, I did the following successfully:1. create a database using the sql management studio2. added a table and some data to it3. threw a dataset into the website4. added a gridview to a page in the website, pulling data from the dataset5. and the website runs successfully populating the page with data...fyi: My sql services: http://img158.imageshack.us/img158/2020/sqlserviceshj6.jpg
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 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.
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)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.
When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.
Can anyone help me determine what to look for or what I might be doing wrong?
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddToReport] (@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300), @ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS SET NOCOUNT ON DECLARE @Dept varchar(50) DECLARE @err int
BEGIN TRANSACTION
IF @ReportName IS NULL BEGIN RETURN -1 END ELSE BEGIN IF @DeptCode IS NOT NULL BEGIN SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','') END SET @err = @@ERROR
INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID) VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID); IF @err<>0 BEGIN ROLLBACK TRANSACTION RETURN @err END END COMMIT TRANSACTION Given below is the relevant codebehind. This is how the values are initialized: Dim newManager As New ListItem newManager.Text = "Choose a Manager" newManager.Value = 0 projectManagerDDL.Items.Add(newManager)
Dim newDept As New ListItem newDept.Text = "Choose a Department" newDept.Value = "" deptCodeDDL.Items.Add(newDept)
Dim newID As New ListItem newID.Text = "Choose a Project" newID.Value = "" projIDDDL.Items.Add(newID)
Dim newStatus As New ListItem newStatus.Text = "Choose a Status" newStatus.Value = 0 projectStatusDDL.Items.Add(newStatus)
Dim newDateRequestedMonth As New ListItem newDateRequestedMonth.Text = "Month" newDateRequestedMonth.Value = 0 dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)
Dim newDateRequestedDay As New ListItem newDateRequestedDay.Text = "Day" newDateRequestedDay.Value = 0 dateRequestedDayDDL.Items.Add(newDateRequestedDay)
Dim newDateRequestedYear As New ListItem newDateRequestedYear.Text = "Year" newDateRequestedYear.Value = 0 dateRequestedYearDDL.Items.Add(newDateRequestedYear)
Dim newDueDateMonth As New ListItem newDueDateMonth.Text = "Month" newDueDateMonth.Value = 0 dueDateMonthDDL.Items.Add(newDueDateMonth)
Dim newDueDateDay As New ListItem newDueDateDay.Text = "Day" newDueDateDay.Value = 0 dueDateDayDDL.Items.Add(newDueDateDay)
Dim newDueDateYear As New ListItem newDueDateYear.Text = "Year" newDueDateYear.Value = 0 dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code: Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim connString As String Dim con As SqlConnection
Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = con
If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text End If
If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value End If
If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value End If
If (typeRBL.SelectedItem.Value <> "All") Then cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value End If
If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text End If
If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value) End If
If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then Dim dateRequested As New DateTime dateRequested = Nothing Else Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue) If (dateRequested) <> Nothing Then cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested End If End If
If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then Dim dueDate As New DateTime dueDate = Nothing Else Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue) If (dueDate) <> Nothing Then cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate End If End If
If (projectStatusDDL.SelectedItem.Value) <> 0 Then cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value) End If
Try con.Open() cmd.ExecuteNonQuery() Response.Write("Report Saved") Catch ex As Exception Response.Write(ex) Finally con.Close() con.Dispose()
End Try
Catch ex As ApplicationException Response.Write("Could not load the database") End Try
End Sub The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects parameter '@ProjID', which was not supplied. at System.Data.SqlClient.SqlConnection.OnError... etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects parameter '@DeptCode', which was not supplied. at System.Data.SqlClient.SqlConnection.OnError... etc and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks.
Hi... Am working with asp.net with vb for a MusicProject.. In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button.... Here He can insert those songs into a new playlist or update the earlier.. the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing *************************************************************************************** CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON DECLARE @IDENT INT IF @PLAYLISTTYPE = 'N' BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID ) SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E' BEGIN SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME END DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item) INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END GO *************************************************************************************** Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist.. Please help me out Thanks in Advance, Madhavi
(6th attempt to post this on the forum - problems with the forum?)
Hello
Assume that you have SQL Server 2005 Standard Edition installed but only the Database Services and you patched it with SP2. Now assume that you need to install all the other components like Notification Services and Reporting Services, what are the correct steps to follow?
I tried this once this failed miserably and ended up in completely having to uninstall SQL Server 2005 and completely reinstalling it. Luckily for me this was a demo machine.
I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out
REATE PROCEDURE InsertTerms AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName], [Pictures])
SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS FROM EmployeeGamingLicense AS a WHERE a.STATUS = 'TERMINATED' IF @@Error <> '0' RETURN
