How can I concatenate fields XML in Yukon?

for example:
XML 1: <CAR name="1">
XML 2: <CAR name="2">
XML expected:
<CAR name="1">
<CAR name="2">
Thank.Hello sqlextreme,
declare @.x1 xml,@.x2 xml
set @.x1 = '<CAR name="1"/>'
set @.x2 = '<CAR name="2"/>'
set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
xml)
select @.x1
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||thank you for your Help, Kent Tegels|||Alternatively:
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74179638c8dba6374e0e30@.news.microsoft.com...
> Hello sqlextreme,
> declare @.x1 xml,@.x2 xml
> set @.x1 = '<CAR name="1"/>'
> set @.x2 = '<CAR name="2"/>'
> set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
> xml)
> select @.x1
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

How can I concatenate fields XML in Yukon?

for example:
XML 1: <CAR name="1">
XML 2: <CAR name="2">
XML expected:
<CAR name="1">
<CAR name="2">
Thank.
Hello sqlextreme,
declare @.x1 xml,@.x2 xml
set @.x1 = '<CAR name="1"/>'
set @.x2 = '<CAR name="2"/>'
set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
xml)
select @.x1
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||thank you for your Help, Kent Tegels
|||Alternatively:
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74179638c8dba6374e0e30@.news.microsoft.co m...
> Hello sqlextreme,
> declare @.x1 xml,@.x2 xml
> set @.x1 = '<CAR name="1"/>'
> set @.x2 = '<CAR name="2"/>'
> set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
> xml)
> select @.x1
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

How can I combine three fields together of dif datatypes

I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?

(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)

Select Item_Description&' '& Item_Cost&' '& Is_ActiveAsDescriptionFROM tblItemList

I tried Casting this but same incompatable message.

Select Item_Description&' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) AsDescriptionFROM tblItemList

What I'm hoping to end up with is:

Brake Pedal, $36.00, True

Did you try using the + operator after casting?

Select
Item_Description + ' ' + CAST(Item_Cost AS NVARCHAR) +' '+ CAST(Is_Active AS NVARCHAR) As Description
FROM
tblItemList

What are the datatypes of the above fields?

|||

Ok, Scott, I tried the + again and it did work, however, can you point me in the direction of how to format the money to $36.00 and the bit to True or False. I would like to end up with:

Brake Pedal, $36.00, True

|||

SELECT
Item_Description+', $'+Cast(Item_Costasnvarchar)+', '+ dbo.ufnGetBoolean(Is_Active)
FROM
tblItemList

The ufnGetBoolean function is:

CREATEFUNCTION [dbo].[ufnGetBoolean](@.BitValue [bit])
RETURNSvarchar(5)
AS
-- Returns a string value based on the bit value passed
BEGIN
DECLARE @.retvarchar(5);
SET @.ret='false'

IF(@.BitValue= 1)
SET @.ret='true'

RETURN @.ret

END;

Let me know if that works.

How can I choose a query based on Parameter values

I have 3 parameter fields, last-name, middle-name, first-name
and the view/table of database has just one string combined of all
three(and it is NOT possible to split).
I need to provide search facility with any combination of these three
fields.
I am very new to this environs and would like to know how I can
achieve this.
Do I have to create an SP which checks if each of the fields is NULL
and do accordingly ?
any help will be appreciated
Thanks
BofoIf I understand what you want correctly you could do this:
select * from yourtable where name like '%' + @.FirstName + '%' + @.MiddleName
+ '%' + @.LastName + '%'
The above query doesn't care if a parameter is null, or has a space or a
partial first name, partial lastname etc (I don't know if they are putting
in the names freeform or picking from a listbox). Anyway, that should at
least give you an idea.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bofobofo@.yahoo.com> wrote in message
news:55950c3f.0501271730.6d87c737@.posting.google.com...
>I have 3 parameter fields, last-name, middle-name, first-name
> and the view/table of database has just one string combined of all
> three(and it is NOT possible to split).
> I need to provide search facility with any combination of these three
> fields.
> I am very new to this environs and would like to know how I can
> achieve this.
> Do I have to create an SP which checks if each of the fields is NULL
> and do accordingly ?
> any help will be appreciated
> Thanks
> Bofo|||Hello Bruce,
Thanks for the advice. I have been trying queries in those lines but I
dont get the results.
I get the result only in the case where the Lastname, Middlename and
Firstname match.
for example
I have tried the following:
Name LIKE '%' + @.last + '%' + @.middle + '%' + @.first + '%' --> only
matches if all strings are provided.
Name LIKE '%' + @.last + '%' + @.first + '%' --> matches all with the
last and first
etc..
I can use an OR to consider all possibilities but when i have to
consider the cases when the user gives a single param i will always get
a bunch of results even when the user gives the fullname
For this reason i would like to know if I can put some PL/SQL logic for
diff cases but seems like that is not the way to go as my query is not
being accepted.
Is there any other way I can do this ? a Stored P ? any ideas how to
do it ?
Thanks very much
bofo|||I go it working. Using a stored procedure.
thanks

How can I avoid lots of spaces being added to the end when saving contents of a textbox

Hi

I am using FormView, SQL 2005, VB 2005

When I save the contents of Title and Area entry fields , I have lots of spaces added to the end.

Title and Area are MultiLine Textboxes and database:varchar(100)

I thought I had solved the issue using;

TextBox Title = FormView1.FindControl("TitleTextBox")asTextBox;

TextBox Area = FormView1.FindControl("AreaTextBox")asTextBox;

TitleLenTrim = Title.Text.Trim().ToString();

if (TitleLenTrim.Length > 100)

{

TitleLenTrim = TitleLenTrim.Trim().Substring(0, 99);

}

string AreaLenTrim;

AreaLenTrim = Area.Text.Trim().ToString();

if (AreaLenTrim.Length > 100)

{

AreaLenTrim = AreaLenTrim.Trim().ToString();

}

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Is there any other way I could remove spaces?

Thanks in advance.

My guess is that your columns are defined with data typechar instead ofvarchar. SQL Server will pad char columns with spaces.

Also, you should be using Parameters, not concatenating UI-supplied data directly to SQL statements. This is an insecure practice.

|||

Thankstmorton , you were right, I had declared the two columns as char.

Could you please explain more regarding using Parameters, not concatenating UI-supplied data directly to SQL statements?

Thanks in advance

|||

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Becomes:

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

cmdInsert.Parameters.Add("@.EntryTypeID",SqlDbType.Varchar).Value=EntryTypeID.Text;

...

cmdInsert.ExecuteNonQuery;

(Sorry if my C# syntax isn't exact)

|||

Thanks Motley, It works fine until I try to insert a date.

Previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

Thanks in advance

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

How can I add pictures to my database filed in design view?

I have a database called 'Objects' which has many field. One of its fields is called 'Image' and has a data type image.

I want to add pictures to each one of my records offline, is this possible?

i.e. by copying the address from my C drive such as C:\Documents and Settings\fseyedarabi\My Documents\My Pictures

Hi,

From your description, it seems that you want to upload your local image file to the image field in your database, right?

If so, I suggest that you can refer the FileUpload class, The FileUpload class displays a text box control and a browse button that allow users to select a file on the client and upload it to the Web server. The user specifies the file to upload by entering the full path to the file on the local computer (for example, C:\MyFiles\FileName) in the text box of the control. Alternately, the user can select the file by clicking the Browse button, and then locating it in the Choose File dialog box.

After that, just read the file into binary format and save it into the corresponding data filed of your database.

For more information, see:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.fileupload(VS.80).aspx

Thanks.

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!
"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes of
the data entered, not "n" bytes.

> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type

> Thank you in advance!
>
|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
....

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes o
f
the data entered, not "n" bytes.

> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type

> Thank you in advance!
>|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
...

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes of
the data entered, not "n" bytes.
> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type
> Thank you in advance!
>|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
...

Hourly Average

Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.I believe this will do it.

SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM ....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

>Apologies for the simplicity of the question, but it reflects my
>capabilities! I have the following sample fields coming from different
>tables:
>
>Location
>TimeDate (timestamp)
>Data
>
>I need to return the average of Data per Location per HOUR.
>
>Thanks.

|||Roy:

Thanks for the reply! I have run this and am getting a syntax error on
the second line which says there is incorrect syntax near the '(' . I
played with spacing but could not resolve. Do you have any tips?
Also, is it inappropriate

Roy Harvey wrote:

Quote:

Originally Posted by

I believe this will do it.
>
SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM ....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
>

Quote:

Originally Posted by

Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.

|||I checked the syntax and what I posted seems clean, other than the
lack of the FROM clause. I adapted it to a meaningess example using
an actual table:

SELECT id,
dateadd(hour,datediff(hour,0,crdate),0) as Hourly,
avg(schema_ver) as AvgData
FROM sysobjects
GROUP BY
id,
dateadd(hour,datediff(hour,0,crdate),0)

If you can't find the problem, past the exact code that is not
executing into your reply.

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 11:24:46 -0700, "Compliance" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

>Roy:
>
>Thanks for the reply! I have run this and am getting a syntax error on
>the second line which says there is incorrect syntax near the '(' . I
>played with spacing but could not resolve. Do you have any tips?
>Also, is it inappropriate
>
>Roy Harvey wrote:

Quote:

Originally Posted by

>I believe this will do it.
>>
>SELECT Location,
> dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
> avg(Data) as AvgData
> FROM ....
> GROUP BY
> Location,
> dateadd(hour,datediff(hour,0,TimeDate),0)
>>
>Roy Harvey
>Beacon Falls, CT
>>
>On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
>wrote:
>>

Quote:

Originally Posted by

>Apologies for the simplicity of the question, but it reflects my
>capabilities! I have the following sample fields coming from different
>tables:
>
>Location
>TimeDate (timestamp)
>Data
>
>I need to return the average of Data per Location per HOUR.
>
>Thanks.

|||On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT|||My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:

Quote:

Originally Posted by

>[Error] Script lines: 1-9 --------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Quote:

Originally Posted by

>

So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:

DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName

Thanks again.

Roy Harvey wrote:

Quote:

Originally Posted by

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
>

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
>

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

>
There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT

|||Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.

Thanks Very Much Roy.

Compliance wrote:

Quote:

Originally Posted by

My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:
>

Quote:

Originally Posted by

[Error] Script lines: 1-9 --------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:

Quote:

Originally Posted by

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT

|||Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.

Thanks Very Much Roy.

Compliance wrote:

Quote:

Originally Posted by

My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:
>

Quote:

Originally Posted by

[Error] Script lines: 1-9 --------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:

Quote:

Originally Posted by

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT