I need to update the string in a longtext field. NewString = "This is a 'test'"
I can't figure out how to use an escape character to identify the string with " ' " and then also include the " ' " character inside the string.
Can anybody help me?
Thanks,
Alex
Hi, I'm trying to store large strings to a database, so am using thetext field type (LongText). I have used this before when storing thehtml of a webpage, and was able to store more than 255 characters byusing just a normal update sql statement. Now I'm trying to store thebody of research papers, and must be doing something different, as Ican only store 255 characters.Can someone explain why SQL Server doesn't like what I am doing -should I be using the WriteText / UpdateText function? If so, pleaseexplain by example how I would do that, and why doing that works.Thanks so much,Iain
View 5 Replies View RelatedHi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedHow do I post the currentdatetime (now) into a DateTime field in SQLserver using a formview? I tried a couple different techniques, but had problems with both:
1. TRY ONE: I created a date time field on the SQL server named entrydate. I can not figure out how to post the current datetime into it, without the user inputting it into a field.
2. Second TRY: I tried to change the datetime field on the sql server to a datetime stamp field, but it puts in a binary value. Then I can't read it correctly, and my delete functions don't work.
Thanks Ed
I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
In a SQL Server Db, what is the maximum amount of data a field can hold?
I am using right now nvarchar with a length of 4000.
Is there another setting to allow even more in a field?
Thanks all,
Zath
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
Hello,I wonder how I can insert a string which contains #, as # is a specialcharacters in sqlThanks
View 1 Replies View RelatedHiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
View 1 Replies View RelatedI was wondering what everyone felt about the fomats in characterfields where the front end application accepts anything.I wouldn't want a customer table where the customer name was lowercase on one, upper on another and who knows on the third.If character fields are not consistent, then formatting will have tohappen every time someone access the data for reporting - as anexample ...Thanks,Craig
View 1 Replies View Related---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C public string GetUserName(long UserId)
{
try
{
string userName;
DiscussionDB data = new DiscussionDB();
List paramlist = new List();
paramlist.Add(data.CreateParameter("@UserId", UserId));
paramlist.Add(data.CreateParameter("@UserName", "", ParameterDirection.Output, DbType.String, 20));
data.ExecuteNonQuery("dbo.Discussion_User_Name", ref paramlist);
userName = paramlist[1].Value.ToString();
return userName;
}
catch
{
throw;
}
}
ALTER PROCEDURE [dbo].[Discussion_User_Name]
@UserId bigint,
@UserName varchar(20) output
AS
SET NOCOUNT ON
SET @UserName = (Select [Name] from Discussion_Member WHERE UserID = @UserID)
if (@UserName is null or @UserName = '')
BEGIN
SET @UserName = (Select UserName from Membership_User WHERE UserID = @UserID)
END
I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this
convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2)))
Problem is with a date value of 081649
I get 08/16/2049 instead of 1949, where did I goof
Hi,
I am getting a date string that is contained in a character field (char(20)).
An example of the data is as follows:
2005-09-20121315001
it is in the format YYYY-MM-DDHHMMSSMMM.
I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon
i.e. to get it to insert into a datetime field the data has to be in the following format
2005-09-20 13:15:18.001
YYYY-MM-DD HH:MM:SS.MMM
Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date,
Thanks in advance
Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.
I have one table and this field is character field with save data in below.
Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank
if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record?
The result expect:
0
0
0
3
4
0
0
I try this query but wrong message :
select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test
Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks
How to modify values in a SQL Database. In the table PRDDEmpmaster there is a field called email_address, and for those fields that have a value I need to change that value to add { } opening/closing "curly braces" to that field.
For example,
RPARSONS@YAHOO.COM becomes
{RPARSONS@YAHOO.COM} . Nulls/empty fields remain as is.
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.
So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
I am getting a data loaded in a DB table to a character field padded from the right with hexadecimal zeros 0x00. How to get rid of it?
Thank you.
Hi,I'm new at asp .net and have a problem. I have a page where the logged in user can add another user to his list of friends. To do this, I have a page where all the users are listed by using a datalist, and I have a form with an invisible field which passes the value of <%#DataBinder.Eval(Container.DataItem, "UserName")%> in post when the user clicks on "add as a friend" so that in the next page I can get that value and add it as well as the username of the logged in user to the table friendships. This is not working, can anyone help please? The code of the page with the datalist is:<script language="VB" runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) ' Session("username") = User.Identity.Name Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select u.*,f.buddyId as userNameIfFriend from aspnet_Users u left outer join aspnet_friendship f ON u.UserId=f.buddyId AND f.userId=@Param1 where IsAnonymous='False' and u.UserId<>@Param1", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: <form id="teste" name="teste" method="post" action="add_buddy.aspx"> <input name="UserId" type="hidden" id="UserId" value="<%#DataBinder.Eval(Container.DataItem, "UserName")%>" /> </form> <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<a href='add_buddy.aspx'>Add as buddy</a> ", "Already buddy")%> </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList></body></asp:Content>The code of the page where the user is redirected to add a friend and where I want to make the insert is: <script runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() ' Declaring variables Dim UserId As String ' A Function to check if some field entered by user is empty ' Receiving values from Form UserId = (Request.Form("UserId")) Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("insert @Param2, @Param1 into aspnet_friendship.userId, aspnet_friendship.buddyId", MyConnection) MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand.SelectCommand.Parameters.AddWithValue("@Param2", UserId) ' Creating Connection Object and opening the database Dim DS As DataSet DS = New DataSet() MyCommand.Fill(DS, "aspnet_friendship") ' Done. Close the connection End Sub </script> I am not sure if sending the information in a form in post to another page and executing the query there is the best option or if I can do it all within
the if statement. I appreciate any help, since I am having difficulties in solving this.Thank you.
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
[Code] .....
I want to insert a value which has the UTF-8 encoding into a field of the database which has the "text" data Type
but it saves like this : " ?????????????? "
I would be thankful if u tell me how should I save it ?
here is the SqlComand I wrote :
string comand = " Insert into Table1 ( title , Body ) values ( ' " + textBox1.text + " ' , ' " + textBox2.text + " ' ) " ;
Table1
=============
title nvarchar(1000)
body text
I have 3x6 textbox lay out, QTY, Descp, Cost, with submit button with followin code...., insert function has too many function, anyone know how to work thiis type of data entery?? Thanks in advanc.Dim Quntityx() As Integer = {txtQ1.Text, txtQ2.Text, txtQ3.Text, txtQ4.Text, txtQ5.Text, txtQ6.Text}
Dim xDex() As String = {txtD1.Text, txtD2.Text, txtD3.Text, txtD4.Text, txtD5.Text, txtD6.Text}Dim xcost() As Decimal = {txtC1.Text, txtC2.Text, txtC3.Text, txtC4.Text, txtC5.Text, txtC6.Text}
Dim i As IntegerFor i = 0 To 5
itemInput.InsertParameters.Add("RequestN", RequestN)itemInput.InsertParameters.Add("Description", xDex(i))
itemInput.InsertParameters.Add("Cost", xcost(i))itemInput.InsertParameters.Add("Quantity", Quntityx(i))
itemInput.Insert(i)
Next i
What I am trying to do is move data from a staging table into a live environment and then update the staging table AFTER the row has move (and not errored). There does not seem to be a reliable method for doing this.
Any assistance would be appreciated.
Hi,
Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.
Regds,
Anu
I have a package that used to work fine, but after I imported it into a different existing solution and tried to run it, it always stalls during the data flow task at the very end.
It will run through and process all the rows and insert them into the destination except for the last chunk. The task blocks eventually all turn green as well, but then it never proceeds to the next task after the data flow. Looking at the Progress tab for the data flow task, I get the following:
Progress: Pre-Execute - 100 percent complete
[DTS.Pipeline] Information: Execute phase is beginning.
[DTS.Pipeline] Information: Post Execute phase is beginning.
Progress: Post Execute - 0 percent complete
Then it just stays at 0 percent for Post Execute. The program doesn't hang or anything, it just doesn't progress at all. I also just checked and I run the package from its original solution and it is now exhibiting the same behavior as well.
Any ideas what might be causing this or something to do to try and figure it out or at least get more information about what might be causing it?
Thanks.
I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000 BC
3 995-000-000 CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last