Preventing Invalid Data From Being Entered
Sep 24, 2006
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?
Any one any ideas on the cleanest way to achieve this?
Thanks
View 1 Replies
ADVERTISEMENT
Sep 24, 2006
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email with the required info?
Any one any ideas?
Thanks
View 3 Replies
View Related
Jul 20, 2005
Someone entered a lot of incorrect data into our SQL 2000 database. Isthere any way to determine who made the changes?Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
Hai ,I created a table with primary key clustered. I have entered the datathru E.Manager . If a close the table and open it again , Ii shows therows with the (default) ascending order. Is, there any way to get therows in the user entered order(neither asc or dec order)With ThanksRaghu
View 2 Replies
View Related
Jul 31, 2013
I have a table where the designer allowed time reporting for 8 different types of activity on each timecard, then stored the hours by activity in 8 separate columns in the database. Example:
Table A.
Column Headers: Employee|Date|Phase1Hours|Phase2Hours, etc...
Data: Fred Jones| 7/15/13 |3|3, etc...
The problem is there is no way based on this structure to get an employee's hours for the day in columnar form.
To get this data into columnar form I have used Select queries with Union All, for instance:
SELECT Employee, Date, Phase1
FROM Table A
UNION ALL
SELECT Employee, Date, Phase2
FROM Table A
,etc.....
Query is running slow (I am guessing it is because the same table is repeatedly being accessed).
View 4 Replies
View Related
Jun 25, 2007
I have a procedure that allows a user to enter two dates to run a query. I would like to display those dates on the generated report underneath the title. "5-01-07 to 5-31-07" How would I do this?
View 3 Replies
View Related
Jul 6, 2007
Hi Guys,
I'm having a problem with some data in our database, basically a web app is storing data into the DB and then recalling it to display to a user. The problem I am having is that for one particular function the DB is causing the app to fail, the app code works for 95% of the data inserted into the DB by the users but it is failing on a few records.. I'v gone through the data manually checking for funny characters or spaces or anything else which is different from the other records, but everything seems to be in order. I doubt very much that this is a system app code problem as the code is working perfectly for all the other records...
Can anyone advise me on what else I can check.. really stuck on this one guys
Thanks Gurj
View 3 Replies
View Related
Apr 17, 2007
Hello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
View 3 Replies
View Related
Jan 16, 2007
I have a schema/user called mstp who has tables in another schema/user. How do I prevent the mstp user from seeing those tables/data that do not belong to their schema?
example:
database xxxx has tables som.employees and mstp.employees The mstp user should only allowed access to mstp.employees and nothing else.
thanks.
View 2 Replies
View Related
Jun 6, 2005
Hi..
I am trying to add new record to any table in my database.
But I can't.
I am tryting to add a value (1500 character) and it gives the message below
"The value you entered is not consistent with the data type of the column."
I have tried alot.
The result is my columns in my table accept maximum 900 character.
But the type of my column I try to add a value is text..
I am too angry with SQL
Please help
View 10 Replies
View Related
Feb 3, 2001
I keep getting this error:
'The value you entered is not consistent with the data type or length of the column'
when trying to enter data into a feild, the feild type is char and the length
is 100 i'm entering text 3 words long but no where near a 100 characters long
any one know why this is happening?
View 1 Replies
View Related
Mar 4, 2015
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
truncate table [stg].[Nationality_1_Leaf]
insert into [stg].[Nationality_1_Leaf](importType, ImportStatus_id, BatchTag, Name)
values(1, 0, 'some batch' , 'American' )
select * From [stg].[Nationality_1_Leaf]
exec stg.udp_Nationality_1_Leaf 'VERSION_1', 1, 'some batch'
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
View 13 Replies
View Related
Dec 10, 2006
Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks
View 9 Replies
View Related
Mar 28, 2008
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View 10 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
Nov 28, 2006
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
View 2 Replies
View Related
Apr 21, 2008
Hello,
I've got the following query:
SELECT zA."ID" AS fA_A
, zA."TEXT" AS fA_B
, (
SELECT COUNT(zC."ID")
FROM Test."Booking" AS zC
) AS fA_E
FROM Test."Stack" AS zA
WHERE zA."ID" = ?
With this query I call:
- SQLPrepare -> SQL_SUCCESS=0
- SQLNumParams -> SQL_SUCCESS=0, pcpar = 1
- SQLDescribeParam( 1 ) -> SQL_ERROR=-1, [Microsoft][ODBC SQL Server Driver]Invalid parameter number", "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"
Is there a problem with this calling sequence or this query? Or is this a problem of SQL Server?
Regards
Markus
View 7 Replies
View Related
Jan 8, 2005
************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags (without the
spaces inside). Always include such tags when
including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************
Hi
Probably a dumb question but I'm doing an insert into a table with an identity field. How do I get the key back straight after I add it?
This is how I do the addition
SQLString = "Insert into users (username,password,status,campus, ulevel ) values ( @username, @password, @status, @campus, @ulevel)"
cmdInsert = new SQLCommand(SQLString, conn)
cmdInsert.Parameters.Add( "@username", _username)
cmdInsert.Parameters.Add( "@password", md5Hasher.ComputeHash(encoder.GetBytes(_password)))
cmdInsert.Parameters.Add( "@status", _status)
cmdInsert.Parameters.Add( "@campus", _campus)
cmdInsert.Parameters.Add( "@ulevel", _ulevel )
conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close()
but about now I need the primary key just generated.
Any ideas?
Thanks
ABold
View 5 Replies
View Related
Dec 10, 2007
Hello,
I'm trying to migrate a table from Oracle over to SQL Server. About half way through the migration, I get an error saying Invalid Data Value for 'SERVICE_START_DATE' source.
I'm trying to locate the value in Oracle that is causing the error but I'm having trouble finding it.
This is the code I'm using for the transformation:
if isdate(DTSSource("SERVICE_START_DATE")) and DTSSource("SERVICE_START_DATE") > "1/1/2000" and DTSSource("SERVICE_START_DATE") < "1/1/2020" then
DTSDestination("SERVICE_START_DATE") = DTSSource("SERVICE_START_DATE")
else
DTSDestination("SERVICE_START_DATE") = Null
end if
I can add on error resume next to the code but I would like to find the culprit.
Thanks.
View 4 Replies
View Related
Mar 18, 2008
First we need to run all packages for initial loading and then we need to run all packages every night.
But inside all packages we need to mark some records as invalid data.
How to deal with all these invalid data after data migration?
If I reject to transfer these data, I will get reference error during next data migration loading.
View 4 Replies
View Related
Jul 13, 2007
We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)
My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.
col1 col2 col3 col4
1 1 1 1
1 -1 -1 1
-1 1 -1 1
-1 1 -1 1
I would want my % distribution query/ssis result to return:
col1 col2 col3 col4
.5 .25 .75 0
I do not want to do a select/ssis process for each column. Any way to do this all in one shot?
View 3 Replies
View Related
Aug 20, 1999
Is there a way to get the last ROWGUIDCOL entered into a table? I don't believe that doing a SELECT MAX will work, and we really need a way to do this. Does anyone have a nifty way to do this?
TIA...
View 4 Replies
View Related
Aug 8, 2007
im trying to summarize all records except the last one entered.
ex
-column001-
1
2
3
4
5
6
the output should be "15" (1+2+3+4+5) is this possible?
One thing i tryed is to put in autodate and time and then count them and leaveout the newest one. Well i can't make it work...
Thx for all help
View 4 Replies
View Related
Apr 28, 2006
What is wrong with this select statement?
SELECT lastName + ", " + firstName + " " + middleName as Name
FROM [users]
WHERE ([usrID] = 100)
I kept getting this error:
Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Help is appreciated.
View 6 Replies
View Related
Apr 28, 2006
What is wrong with this select statement:
SELECT lastName + ", " + firstname + " " + middleName + " " + maidenName as Name
FROM users
WHERE userID = 100;
I kept getting this error:
Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Help is appreciated.
View 7 Replies
View Related
Oct 23, 2005
Kudos to y'all experts out there. I kinda needed your help. I'm trying to run a query...
SELECT a.AUF_POS AS Pos, c.ZL_STR AS Panel, a.POS_TEXT AS Description, a.BREITE AS W1, a.HOEHE
AS H1, a.BREITE2 AS W2, a.HOEHE2 AS H2, SUM(b.ANZ) AS Qty, SUM(b.LIEFER_ANZ) AS Dlvd,
SUM(b.RG_ANZ) AS Inv, (a.BREITE*a.HOEHE/CAST(1000000 AS NUMERIC)) AS UnitSQM,
(a.BREITE*a.HOEHE*SUM(b.ANZ)/CAST(1000000 AS NUMERIC)) as TotPosSQM
FROM liorder..LIORDER.AUF_POS a INNER JOIN liorder..LIORDER.AUF_STAT b ON a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS INNER JOIN liorder..LIORDER.AUF_TEXT c ON a.AUF_NR = c.AUF_NR AND
b.AUF_POS = c.AUF_POS
WHERE (c.ZL_MOD = 0) AND (b.AUF_NR = '86260')
GROUP BY a.AUF_POS, a.POS_TEXT, a.BREITE, a.BREITE2, a.HOEHE, a.HOEHE2, a.SFORM_NR, c.ZL_STR
...and I keep getting this error: Invalid operator for data type. Operator equals multiply, type equals nvarchar. I've tried every possible CAST and CONVERT but I just can't make it work. I'm pretty sure that the data types for the columns I mentioned on the mathematical equation are all numeric. Please help...
View 1 Replies
View Related
Jul 4, 2007
1 ALTER PROCEDURE links_GetAllLinks
2 @startRowIndex INT,
3 @maximumRows INT,
4 @sortExpression nvarchar(50)
5 AS
6
7
8
9
10
11 SET @startRowIndex = @startRowIndex + 1
12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END
13 -- populate the table CAST(getdate() as int)
14
15
16 IF @sortExpression LIKE ''
17 BEGIN
18 SET @sortExpression = 'links_Links.link_date'
19 END
20 DECLARE @sql nvarchar(4000)
21 SET @sql = 'Declare @Links TABLE
22 (RowNumber INT,
23 link_id INT,
24 cat_id INT,
25 cat_title VARCHAR(100),
26 sub_id INT,
27 sub_name VARCHAR(100),
28 link_aproved BIT,
29 link_name VARCHAR(100),
30 link_url varchar(100),
31 link_desc varchar(500),
32 link_date datetime,
33 link_rating INT,
34 link_ratingscount INT,
35 link_ratingvalue INT,
36 link_poster uniqueidentifier,
37 link_comments INT,
38 hit_date datetime);INSERT INTO @Links
39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date
40 FROM links_Links INNER JOIN
41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN
42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id
43 WHERE links_Links.link_aproved = 1;SELECT * from @Links
44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1'
45
46
47
48 EXEC sp_executesql @sql
49
50
51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
View 4 Replies
View Related
Mar 25, 2003
Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance .... j
View 3 Replies
View Related
Jan 21, 2007
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add
db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);
Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie
My C# update method in the DAL (still working on the code)
private static bool Update(AvatarImageInfo avatarImage)
{
//Invoke a SQL command and return true if the update was successful.
db.ExecuteNonQuery("syl_AvatarImageUpdate",
avatarImage.AvatarImageID,
avatarImage.DateAdded,
avatarImage.ImageName,
avatarImage.ImagePath,
avatarImage.IsApproved);
return true;
}
I am using stored procedures to access the data in the database.
My update stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
@AvatarImageIDint,
@DateAddeddatetime,
@ImageNamenvarchar(64),
@ImagePathnvarchar(64),
@IsApprovedbit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
UPDATE[syl_AvatarImages]
SET
[DateAdded]= @DateAdded,
[ImageName]= @ImageName,
[ImagePath]= @ImagePath,
[IsApproved]= @IsApproved
WHERE[AvatarImageID] = @AvatarImageID
RETURN
END TRY
BEGIN CATCH
--Execute LogError SP
EXECUTE [dbo].[syl_LogError];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
View 3 Replies
View Related
Nov 20, 2007
Hi every one
I want to get the currently entered or updated record in the database table by using SQL Query or stored procedure.
Thanx in advance
Take care
Bye
View 3 Replies
View Related
Oct 22, 2005
My form has an optional field for date entry. If user did not enter anything then how can I still maintain null value in the table in an update operation? Thanks for advice.
View 14 Replies
View Related
Apr 23, 2008
I'm writing my first .net app in VB.net. I can connect to my database, but get the following error when I try to set a label value. (My code is listed below) What am I missing?
Exception Details: System.InvalidOperationException: Invalid attempt to read when no data is present.Source Error:
Line 19:
Line 20: reader = comm.ExecuteReader()
Line 21: EmployeesLabel.Text = reader.Item("tkinit")
Line 22:
Line 23: reader.Close()
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim conn As SqlConnectionDim comm As SqlCommand
Dim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("IntegratedDataConnectionString").ConnectionString
conn = New SqlConnection(connectionString)
comm = New SqlCommand("Select top 1 tkinit from tEliteTimeKeep", conn)
conn.Open()
reader = comm.ExecuteReader()EmployeesLabel.Text = reader.Item("tkinit")
reader.Close()
conn.Close()
End Sub
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server"><title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="EmployeesLabel" runat="server" ></asp:Label>
</div></form>
</body>
</html>
View 3 Replies
View Related
May 16, 2008
My query actually returns rows but when I run, I get this error "Invalid attempt to read when no data is present". It says that the datareader does not return any rows.
Please help.
Thanks in advance
Sangita
View 5 Replies
View Related
