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?
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?
Hi, I am populating a dataset in .net with output from sql 2005 database. One of the columns in the table is a 'varchar(max)' type. This dataset is then converted to XML using WriteXml and written to a .xml document. But due to the presence of invalid characters, this process errors out. Is there any way using which these invalid characters can be replaced at the database level itself when querying on the table? The error that is produced is as follows: '', hexadecimal value 0x1C, is an invalid character. Line 32201, position 924.
When we are getting data in a table and we want to replace characters with other characters. For example, We have a table with a street address, and there are numerous ascii character values we want to review and replace if they exist. We were looking at using a table with 2 columns, 1 containing each ascii character value the other it's preferred replacement value. Then updating the street address searching through each ascii character and replacing it if needed. Currently, we are running it through a looping process searching each individual address for each ascii character, and updating it.
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
************* 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?
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?
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
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.
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
Hi everybody, I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
I'm entering a Selection record for a partiuclar lotID, Once entered, I need to obtain its SelectionID then use it to update a another field within that record. Here's what I've been doing... --insert values into a testchangeorders table INSERT INTO testchangeorders VALUES (2,3,3,3,1,'red',0,5) --Find the SelectionsID of the last record created for that partiuclar LotID SELECT MAX (SelectionsID) FROM testchangeorders WHERE LotID = 2 --Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID UPDATE testchangeorders SET UniqueID = ('3-' & SelectionID WHERE SelectionsID = SELECT MAX (SelectionsID) AND LotID = 2)
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
Hi I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox this is the parameter definition:<asp:ControlParameter ControlID="tbComment" Name="Comment" PropertyName="Text" Type="String" /> Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database. I use a SqlDataSource with automatic generated script.
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).
I have a VB.NET program that displays the time extracted from a SQL Server database datetime datatype by way of a User-defined scalar function I created. However, sometimes information is entered into the system through the program that does not have a time-- only a date. SQL Server automatically assigns a time of 12:00 AM to these values (since they're a datetime). Is there any way to detect when this happens in my user-defined scalar function so that when I try to extract time values, I can instead return a message/time of my choice? I would rather not assume that all 12:00 AM values are automatically inserted by SQL Server since this might not actually be the case.
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?
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
Im trying to add a new rcord to my db on a button click usign the following code
'data adapter Dim dAdapt1 As New SqlClient.SqlDataAdapter 'create a command object Dim objCommand As New SqlClient.SqlCommand 'command builder Dim builderT As SqlClient.SqlCommandBuilder 'connection string Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True" 'dataset Dim dsT As DataSet Private Sub connect() 'connection objCommand.Connection = New SqlClient.SqlConnection(cnStr) 'associating the builder with the data adapter builderT = New SqlClient.SqlCommandBuilder(dAdapt1) 'opening the connection objCommand.Connection.Open() 'query string Dim query As String = "SELECT * from StudentPlayground..Employees" 'setting the select command dAdapt1.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection) 'dataset dsT = New DataSet("Trainee Listings") dAdapt1.Fill(dsT, "Employees") End Sub Private Sub BindData() connect() DataBind() End Sub Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click Dim empID As Integer = CType(FindControl("TextBox8"), TextBox).Text BindData() Dim firstName As String = CType(FindControl("TextBox1"), TextBox).Text BindData() Dim lastName As String = CType(FindControl("TextBox2"), TextBox).Text BindData() Dim location As String = CType(FindControl("TextBox3"), TextBox).Text BindData() Dim termDate As Date = CType(FindControl("TextBox4"), TextBox).Text BindData() Dim hireDate As Date = CType(FindControl("TextBox7"), TextBox).Text BindData() Dim dept As String = CType(FindControl("TextBox5"), TextBox).Text BindData() Dim super As String = CType(FindControl("TextBox6"), TextBox).Text BindData() Dim newRow As DataRow = dsT.Tables("Employees").NewRow newRow.BeginEdit() newRow.Item(0) = empID newRow.Item(1) = firstName newRow.Item(2) = lastName newRow.Item(3) = location newRow.Item(4) = hireDate newRow.Item(5) = termDate newRow.Item(6) = dept newRow.Item(7) = super newRow.EndEdit()
'do the update Dim insertStr As String = "INSERT INTO Employees" + _ "(EmployeeID,FirstName,LatName,Location,HireDate,TerminationDate,Supervisor)" + _ "VALUES (empID,firstName,lastName,location,hireDate,termDate,dept,super)" Dim insertCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(insertStr, objCommand.Connection) dAdapt1.InsertCommand() = insertCmd
dAdapt1.Update(dsT, "Employees") 'Dim insertCmd As new SqlClient.SqlCommand = (builderT.GetInsertCommand()).ToString()) 'dAdapt1.InsertCommand = New SqlClient.SqlCommand(insertCmd.ToString(), objCommand.Connection) BindData() objCommand.Connection.Close() objCommand.Connection.Dispose() End Sub
im not sure wats going wrong because the record is not being added. Please help!!
Hi again, In ASP.net, is there any elegant way to handle a set of time inserts from a form when the 2nd time is past midnight? Specifically, I have a form with 2 textboxes on it (startTime and endTime) that are set up to accept time values (using AJAX MaskedEditExtender for formatting/validation - pretty cool). This data is posted to a sub that enters the data into a table (T_Details). However, I've noticed that the data inserted as part of the record (SQL field is smalldatetime) doesn't take into account the fact that a time value past 23:59:59 in the "endTime" textbox is a time on the next day - it simply rolls to an A.M. date for the same day as the date for the pre-midnight value from the "startTime" textbox. I'm sure that I can simply do some conditional coding and modify the date if necessary but is there a better way to do it? Thanks as always...this forum is a great resource
Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like dropdownlist.items.findbyvalue(), it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks
hi Actually in my project we need to validate the mailid entered by the user not simple validation ,i need to validate wheather the mailid exists .Ex: ravishankar@yahoo.com entered by user whether this mailid is existed in the yahoo or not i,e we want wheather it exists or not................ Please if any one know the solution please send to me .............
'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?
I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.
So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.