Need To Create Custom Function For Enterting Automated Database Records
May 12, 2007
Hi All,
I need help in creating a function in VB for my ASP.NET application where I want to add records to database on the first day of every month.
I have got no idea about what I have to do for achieving this goal.
Its basically for a customer based application where Interest will be paid into customers' account and I need to implement this for every customer on 1st day of every month
The thing I am not sure about is how can I get the application to add a record for each customer on the first day of each month, i.e. how can I get the application to check that its 1st day of month and then the application adds records automatically for each customer based on my specified rules.
If any of you could help me with this, I'll really appreciate it.
Thank you.
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
I'm relatively new to Reporting Services and .Net development so bear with me if I've missed something obvious. I've set up Reporting Services to run under forms authentication successfully. I want to filter the data (both within Report Builder Models and .rdl files) to the company the user belongs to.
To avoid joining the users table to all other tables on the companyID I'm wondering if it's possible to create a custom funciton which could use the UserID to lookup the companyID and then pass this through as a parameter, and have this "GetCompanyID" function available when building Models and/or reports...???
When building a model the Information Functions available are GETUSERID and GETUSERCULTURE. Is it possible to modify how these work and/or create additional functions here???
Hi everyone, this is is my first post, so please reply and help. I'm working on a project right now that uses asp 2.0 and SQL server 2005 express edition. This is a general idea of the project. In our company some of us receive ECO notifications (engineering change orders) for our products and we need to implement these to the test scripts that are on the production floor. So the project is about entering the new ECO into a database which will send an automatic notification to our test team. When they receive the notification they will have to sign in to the website and introduce their login and password to sign off the ECO (Following some checkpoints already defined by me, for example, Area ready, Test script modification necessary, new firmware introduction, comments, etc...) but I also need to record WHO and WHEN sign that ECO. We have 3 different test areas in our factory: Electrical, Functional and Systems, so all THREE areas must be signed off in order to the ECO go to a IMPLEMENTED state (at this point i need to send a new email saying that the eco has been implemented in all three areas). So far I've completed the following things: -users validation (logins, areas) -New custom entry form for the ECOs and automatic email notification (part of what I did is described below). Dim ECODataSource As New SqlDataSource()ECODataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ECO_ICSConnectionString1").ToString()
ECODataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure ECODataSource.InsertCommand = "EcoNew" ECODataSource.InsertParameters.Add("EcoNumber", EcoNumberTextBox.Text) ECODataSource.InsertParameters.Add("EcoDescription", EcoDescriptionTextBox.Text) ECODataSource.InsertParameters.Add("EcoMandatory", EcoMandatoryDropDownList.Text) -Depending on which test area is the the engineering from, I can filter the ECOs and just shows the ones that their test area is pending. (using GridView) But I'm stuck right now when the engineers have to sign the ECO for their test areas. I was able to use the Gridview and DetailsView to EDIT most of the things that I need. But there are somethings that I don't like: 1. When using the EDIT option on Gridview or Detailsview, all fields can be edited including ECO number, description and mandatory, which I don't want them to change. If I set those columns to read only, when editing that row again. It gives me an error that says that the ECOnumber can't be NULL, but if I remove these 3 columns the Engineer will not know which ECO they have sign. They are only going to be able to see the EcoId, which doesn't say much. 2. Also I saw that I wasn't able to do is to enter the USER login and CURRENT system date and time automatically. I don't want them to manually enter the date and their login manually. 3. Finally, when the last area signs the ECO, I want to update that record and set a flag that tells me that the ECO has been completed. So what I really want is to create some sort of form (textboxes, labels, checkboxes, etc.) that will UPDATE the selected ECO from the gridview for instance. So when I select the row from the GridView, It will show the data (Econumber, description and mandatory as READ ONLY) and use the rest of the things as INPUT for the engineer to complete. At the end an "update button" and when I click it, It will enter/update the data on that specific row, but including the time and user login as well. Also to check if the other 2 areas have signed and if so, change the ECOReadiness flag to 1 and send the email. Is there a code like the one I used above to do this ? Or if you think there a better way to do this, I'll be very glad to hear it. I'm new using sql and asp, so If i'm asking some dumb questions please forgive me. .
Here's my table definition for your reference: EcoId - primary key. EcoNumber EcoDescription EcoMandatory EcoReadiness <- Flag for the entire ECO, when ALL 3 areas have signed, this will be 1. ATE < - Flag for Electrical area. ATEscripts < - Just a Yes/no input. ATEengineer <- user login ATEdatetimestamp <- Date.Now() FAT < - Flag for functional. FATscripts FATengineer FATdatetimestamp SYSTEMS < - Flag for systems. SYSTEMSscripts SYSTEMSengineer SYSTEMSdatetimestamp
Hello,Our QA team have running a lot of test scripts (for automated regressiontesting), they run them on the different databases (Oracle/MS SQL).Several of those tests are dependent on the current date/time. In order tobeable to use them efficiently, we changed the current date/time on the QAdatabase server to a specific date/time before starting the scripts, so weare sure the test scripts always run in the same environment.Resetting the date/time of the database server gives us more and moreproblems (OS problems, backup/ virusscan, ...).It is possible to fix the problem with SYSDATE function on Oracle by settingFIXED_DATE init parameter.Is it possible to 'change' the current date/time on 'database' level,instead of on OS level for MSSQL2000?Do you know other means to do such things?Thanks in advance,Konstantin
I was directed to post this here. I am thinking of creating a script that generates another script for creating/dropping tables, indeces, FK, views, stored procedures..etc in the correct sequence in a specific database. I accept the fact this script is dangerous (like if I ran it on production server). But it sure will be very helpful when moving our SQL objects from development to qa and then to production. Is this worth it? Have you see anything that generates such a thing using sys.objects,sys.indexes,sys.foreign_keys etc?
I am working on the build of a product where we build all software needed for the various servers automatically (web, SQL, SSAS etc.). Of course, all the standard builds work fine.
However, when building the SSIS project from the command-line, the binDeployment sub-folder is not created and therefore no SSISDeploymentManifest file is created. Even if the binDeployment sub-folder is present then SSISDeploymentManifest file is not created.
The command-line I am using is "C:Program FilesMicrosoft Visual Studio 8Common7IDEdevenv.exe" MyPackages.dtproj /build Release /project MyPackages
Of course, opening the project in Visual Studio and pressing build does indeed create the SSISDeploymentManifest, so the project is configured correctly.
We need a fully automated build, so can anyone help?
I have a database (actually several of them) on a production server. I want to be able to create an automatic process so every evening the production database is copied to a different server that runs staging sites.
I know that this is possible through database mirroring, but I don't want the database to be constantly mirrored. I would prefer to have a script that ran every night that just make an exact copy over to the staging server.
Is there any way to automate this, possibly through the copy database wizard? Does anyone have any good suggestions as to how I can make this work? If not, does anyone know of a good straight forward article on database mirroring that will get me started quick?
Hi all, i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .
the environment: - MsSql Server 2000 - Suns App-Server Glasfish - Java (J2EE) - Hibernate
the requirements: 1.) Some tables should be synchronized and redundant (the same data in both databases)
2.) If the maindatabase is changing the schema the archivedatabse must do the same changes.
3.) The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.
4.) If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.
I don't find a way to realise this ... can some one give me a hint?
... Sorry for my bad english, i hope i explained my problem good enough.
Hi folks,I got a script which restores a database. It works fineif it is running in my Query Analyzer.It fails when I put this script in an automated schedule using theSQL agent.This is my scriptRESTORE DATABASE [RestoreTest]FROM DISK = N'E:sqlbakRestoreTest.BAK'WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERYand this is the error message from the scheduler (Sorry its in German)Executing as User dbo. Exclusiv access to database not possiblebecauseit is in use (which is not).. Rest may be clear ;-))Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank istnicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.[SQLSTATE 42000] (Fehler 3013). Fehler bei SchrittDo you have any suggestion to me ?
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work? All help is greatly appreciated!
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?
hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev] GO /****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008 Date To: 24/02/2008 Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name -------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John 2 2/22/2008 4:16:45 PM Dean 3 2/23/2008 4:16:45 PM Rick 4 2/24/2008 4:16:45 PM Van
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
I've gone through all the steps, and when I finally drag the custom task I've made into the control flow, I get the error
"Failed to create the task. ... Cannot create a task with the name "SSISExportToExcel.ExcelExport.ExcelExport, SSISExcelExport, ... Verify the name is correct"
Here's the beginning of my class... any help would be appreciated...
Namespace ExcelExport <DtsTask(DisplayName:="Excel Export Task", _ Description:="Exports a SQL query results to an Excel Document")> _ Public Class ExcelExport Inherits Task ....
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String Dim ss As Integer = seconds Mod 60 Dim mm As Integer = (seconds - ss) / 60 Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss) End Function
Can anyone help me out? I am not that familiar with VB.
I created a SSIS log provider derived from LogProviderBase, it's appear in my VS2005 and work fine, the only one problem is, there is not a dropdown list of connections in the SSIS Log Configuration column, how can I add it?
I'm building a custom component and UI and am a bit confused on where I need to create and/or set custom column properties?
My UI will have a datagrid with three columns: 1) a check box to select a column for use by the component, 2) the input column name, and 3) a "differentiator" checkbox that indicates an extra property about some of the columns that have the first column checkbox checked (For example, my component may be using five input columns, but three of those need to be used in a slightly different way.)
The problem is, I don't understand when or where I'm supposed to create the custom property for the input column. SetUsageType is where I've been thinking, but I don't know if I'm supposed to be creating it for an input column or a virtual input column. I'd appreciate any guidance.
heres my problem, since I migrated to SQL-Server 2005, I was able to use the Row_Number() Over Method to make my Custom Paging Stored Procedure better. But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure. What I want to know is: Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction??? heres my stored procedure: SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum, morerecords = (Select Count(Ad_Id) From Ads) FROM Ads) as testWHERE RowNum Between 11 AND 20 The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me. Thank for your time.
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. Â This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar ( wk_Date INT IDENTITY NOT NULL, FullDate DATETIME NOT NULL, WeekDayName VARCHAR(9) NOT NULL, IsWorkday varchar(20) NOT NULL )
Below is code I am using to create a heatmap in SSRS. THe code seems to be working well except for one glitch. Sometimes numbers in the "middle of the pack" are white. Obviously they should be one the of colors listed below. Why numbers would be white when I declared only white for IsNothing.Â
 Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String     Dim colours As String() = New String() {"White","DarkGreen","Green","ForestGreen","LimeGreen","YellowGreen","Yellow","Gold","Orange","DarkOrange","OrangeRed","Red"}         If IsNothing(textBoxValue) = True Then       Return colours(0)
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
