HELP......need To Store Text Field Data Of Up To 40000bytes!!!!!!!
Jul 7, 2005
The problem field is of type ntext, and the length is 16. We've tried changing it to varchar of length of 8000, and this didn't help either. Text datatype didn't work either.When the user pastes a word document or enters more than 11 lines of text, the field in the database goes plank.Here's the subroutine for the Insert. We will change it to a SPROC, but not in the very near future:'Build the Update StringDim strSQLWPSave As StringDim strRQW As String = Request.QueryString("WPPID")Dim strTO As String = TO.Text.ToString()Dim strWS As String = WkScope.Text.ToString()Dim strSD As String = StartDate.Text.ToString()Dim strED As String = EndDate.Text.ToString()Dim strAss As String = Assump.Text.ToString()Dim strBOE As String = BOE.Text.ToString()Dim strConf As String = ConfRat.Text.ToString()Dim str76 As String = Combo76.SelectedItem.Value.ToString()Dim strRDB As String = RevDrivenBy.Text.ToString()Dim strSigComm As String = SigComments.Text.ToString()strSQLWPSave = "Update WP_General_Info SET "strSQLWPSave &= "[TO] = '" & strTO & "', [WkScope] = '" & strWS & "', "strSQLWPSave &= "[Start Date] = '" & strSD & "', [End Date] = '" & strED & "', "strSQLWPSave &= "Assump = '" & strAss & "', BOE = '" & strBOE & "', "strSQLWPSave &= "[ConfRat] = '" & strConf & "', "strSQLWPSave &= "[WPMgrConf] = '" & str76 & "', "strSQLWPSave &= "[Rev Driven By] = '" & strRDB & "', "strSQLWPSave &= "[SigComments] = '" & strSigComm & "' "strSQLWPSave &= "WHERE WP_PId= '" & strRQW & "' "sCon1.Open()Dim cmdSave As New SqlCommand(strSQLWPSave, sCon1)'Try to open DB and execute UpdateTry'cmdDGB.ExecuteNonQuery()Dim NumberUpdated As IntegerNumberUpdated = cmdSave.ExecuteNonQuery'lblStatus.Text = NumberUpdated.ToString'lblStatus.Text &= " Record(s) Updated."Catch ex As ExceptionlblStatus.Text = "2323 Error Updating WP_G_I: "lblStatus.Text &= ex.MessageFinally'If Not (conDGB Is Nothing) Then' conDGB.Close()'End IfEnd TrysCon1.Close() I'm guessing I need to go the route of changing the data type to an image in order to allow users to to copy/paste from word documents. Do I need to go the BLOB route? I've tried to look for KB articles, but everything talks about .bmp or .jpg files.Thanks!
View 5 Replies
ADVERTISEMENT
Jul 2, 2006
Basically i make use of FormView in Web Developer Express Edition to create and store information to the database .... what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament .....this value would be stored within a session string in global condition that allowed any page able to access it .....
View 3 Replies
View Related
Jul 20, 2005
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies
View Related
Jun 2, 2008
Hello Everyone,
I have looked and looked for any information on how to insert data from a db into a text field using a stored procedure. If anyone has any helpful links or suggestions, my sanity would greatly appreciate the help.
Thank you
View 1 Replies
View Related
Dec 6, 2004
I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?
View 2 Replies
View Related
Apr 17, 2007
hey guys... i have some problem here... i am trying to display data out on the text field and these are the codes that i have added... i have a few pages with the same codes but onli this particular page can't work...
when the webpage first load, it will load all the username onto ListBox1... when the user choose a data from ListBox1 and click button3, it will show all the data of that particular user that the user had selected and display them on the text fields provide... but the data could not display the data... is there something wrong with my codings??
These are my coding:
Imports System.Data
Imports System.Data.SqlClient
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Label6.Text = Session("Un")
Dim connSel As New SqlConnection("data source=localhost; initial catalog=Sourcing; integrated security=true")
Dim adapSel As SqlDataAdapter
Dim mySQL As String
mySQL = "select UserName from Users "
adapSel = New SqlDataAdapter(mySQL, connSel)
connSel.Open()
Dim dsSel As New DataSet
adapSel.Fill(dsSel)
ListBox1.DataSource = dsSel
ListBox1.DataTextField = "UserName"
ListBox1.DataValueField = "UserName"
ListBox1.DataBind()
connSel.Close()
End Sub
Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim conn As New SqlConnection("data source=localhost; initial catalog=Sourcing;" & "integrated security=true")
Dim cmd As New SqlCommand("select * from Users where UserName = '" & ListBox1.SelectedValue & "'", conn)
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
Do While dr.Read()
UserTxt.Text = dr("UserName")
PwdTxt.Text = dr("Password")
BranchTxt.Text = dr("Branch")
CountryTxt.Text = dr("Country")
PriTxt.Text = dr("Priority")
Loop
dr.Close()
conn.Close()
End Sub
End Class
View 4 Replies
View Related
Jul 23, 2005
This may be a stupid question but I can't find an easy answer for what Iwant to do. I need a blob data field that can store both binary and textdata like the sql_variant field. I am using vb.net to populate a sql table.I want to store both string and binary data.
View 1 Replies
View Related
Jul 20, 2005
In my database there is a text field type that is used to enter streetaddress. This address could be a few lines long, each line with acarriage return at the end.Is there a way to search for these carriage returns and break out whatis in each line seperately?Thanks.Mike
View 1 Replies
View Related
Mar 12, 2007
AnupG writes "hi,
the most common feature that any RDBMS provides should be that if we want to store the data in the MSSQL tables then the data should be presented in masked/formatted like for eg.the password text in field should be like "*****". I am using SQL 2000 but i unofrtunately i couldn't find any such feature in defining the data type in SQL server 2000 ...?"
View 2 Replies
View Related
Apr 19, 2006
Hello,
I have an Excel spreadsheet that contains 100,000 + rows of data. Two of the columns in the spreadsheet are date fields that are sent over in the yyyymmdd format (no separators such as "-" or "/"). I tried converting these fields into dates using the Data Conversion task in SSIS. This doesn't work as it appears that SSIS cannot map a string in the format of yyyymmdd to a valid date. In lieu of this, I have added a Script Component to my transformation which can take the string and convert it to a date value using some Substring() calls on the source yyyymmdd string.
A few questions for the group ...
1. Is this there a more efficient or "better" way of performing this transformation?
2. I can also use OLE DB Command task in my transformation and make a call that might do something like this: select convert(datetime, convert(varchar(10), [@yyyymmddValue])) but how would this perform related to the Script Component task? My guess is that this would be slower since the transformation would make a database call for every record.
3. Are there any plans for the SQL team to make these types of conversions easier to implement in the future? It seems to me that this is a pretty common scenario for integrating date data.
Thanks!
Brian
View 19 Replies
View Related
Nov 24, 2004
how can i insert a carriage return when i update the field?
say i want to put the following inside a field:
firstline
secondline
how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
thanks
View 3 Replies
View Related
Jan 10, 2008
I can't paste text (directly into table row via enterprise manager) into a varchar (5000) field, truncates after about 960 char. Length of string trying to paste is about 1400 characters including spaces. No special characters (one apostrophe). Error happens intermittently.
SQL server v2000
View 4 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Dec 1, 2007
For a field in a database to accept both text and number what 'Data Type' should I be using, i.e. 23t5
View 1 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
May 15, 2006
Hi,
Im a programmer for an university webportal which uses php and msssql.
When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry.
For example:
http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333
How can I set the text limit to unlimited?
Could it be something else?
Is there a way of splitting an entry to several text fields automatically?
Thanks in advance for any help you can give me,
Chris
View 3 Replies
View Related
Mar 12, 2015
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
View 5 Replies
View Related
Apr 21, 2015
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies
View Related
Nov 19, 2006
Hi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
View 14 Replies
View Related
Jul 20, 2005
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies
View Related
Aug 20, 2006
Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
View 6 Replies
View Related
May 30, 2006
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
View 1 Replies
View Related
Apr 24, 2007
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.
I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)
For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.
<ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
View 2 Replies
View Related
Mar 27, 2008
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €ś(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
View 8 Replies
View Related
May 3, 2004
Hi All
Iam trying to Get a text field value i wrote this code
DECLARE @ptrval varbinary(16)
DECLARE @length bigint
SELECT @ptrval = TEXTPTR(Template), @length = LEN(Template)
FROM #TEMPLATE
READTEXT Template.#TEMPLATE @ptrval 0 @length
but i need to put the result into a text var
is that possible or not and if it possible any one could help me with that
View 1 Replies
View Related
May 9, 2007
Hi everyone,
I'm extremely new to SQL so be nice
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth).
The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset
Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed'
Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16)
SELECT @Dispose = TEXTPTR(DESCRIPTION)
FROM AR_PRIMARY_ASSET
WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed'
So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005.
Code:
DECLARE @Dispose varbinary(16)
DECLARE cursor1 CURSOR FOR
SELECT TEXTPTR(DESCRIPTION)
FROM AR_Primary_Asset
Where AR_Primary_Asset.ASSET_REF in('1','2','3')
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Dispose
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed'
FETCH NEXT FROM cursor1
INTO @Dispose
END
CLOSE cursor1
DEALLOCATE cursor1
But when it was run on our SQL2000 server it gave the following error message
Quote: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way.
Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?
View 4 Replies
View Related
Jul 12, 2014
CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions
Output
Site_noInstructions
20Request PIN then proceed
21Request PIN if wrong request name
22Request PIN allowed to use only numbers
All text instructions start with “Request PIN” but after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text
Desired output
Site_noInstructions
20Request PIN and codeword then proceed
21Request PIN and codeword if wrong request name
22Request PIN and codeword allowed to use only numbers
View 3 Replies
View Related
Feb 7, 2008
Hi,
To store large amount of text which datatype is best whether text or varchar(max).
Thnaks
View 1 Replies
View Related
May 15, 2002
Hi,
does anyone know a way, using native SQL, to store a result of a query in an image field of a certain table.
The case is we have a selfmade replication to communicate with several SQL servers in stores, this replication is over a telephone line. So we collect all the data using SQL statements and store them in a separate table as an image field. This is done know through a Delphi application that streams the resultset to a image field.
Thanks in advance,
View 1 Replies
View Related
Oct 31, 2013
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
View 2 Replies
View Related
Jul 20, 2005
I am creating a document management systems using asp. I have beenresearching the different ways of handling the documents such as using thefile system and storing the path in the db, and actually storing thedocument in the db. I like the idea of storing it in the database muchbetter because I can allow users to manage documents themselves (I alreadyhave the code in place to do it if I decide), having a central system withthe ability to add my own document properties by adding fields to the table,security, and backups. I have found that most think it is better to storethe path due to performance issues and the rate the db can grow. I havelooked at our current system in access and we have a total of 4400 documents(of which probably 25% are in the database but don't actually exist anymorein the file system, one hangup about the file system) since 1988. Thiscomes to about 300 documents added each year. The other thing is the issuewith the size of the db. I don't see a whole lot of difference with thisissue because it is going to take up space in your file system too, althoughthe file system may be more efficient at storing them. I would say that 95%of our docs are under 1 mb in size and done in ms word.The last thing is using full-text search capabilities in SQL Server. I needto be able to search the contents of the field.Is there other issues around storing documents in the db to consider besidesthe above?
View 1 Replies
View Related
Apr 17, 2008
Hello,
In my application, I have a master table that stores information about some other user tables. These other tables are all of different types, that is, the number and type of columns need not match across the user tables.
From the application perspective, logically each of these user tables is nothing but a column field within the master table.
I have seen references about "table" data type in SQL Server. It is possible to create a table that actually has a table data type as its field?
Thank you in advance for your help.
Regards,
Peter
View 10 Replies
View Related
Jun 4, 2008
I have the following Query which gives the output in XML format.
select * from <Table_Name> For XML auto
I wanted to store this output in a .Xml file at my local machine.
I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ?
Thanks,
View 3 Replies
View Related
