adding to text

If I have a varchar field, I can easily add to it. For instance
select 'Name: ' + fname as fname from Customers.

But what if I have a text field instead of varchar?

select 'Summary: ' + summary as Summary from Customers wont work at all.
Is there a way to accomplish this?TEXT columns really ought to be manipulated on the client, not the server. There are a number of reasons for this, most of which are design and performance issues.

If you really must manipulate a TEXT column on the server, you can use the UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx) statment, but I'll forewarn you that it is rather ugly.

You really ought to handle this on the client if you can't make the column a VARCHAR instead of a TEXT column.

-PatPsql

Adding date to filename in report subscription

My company sends reports on a daily basis to our customers. Now I want to save all the sent reports on disc with the date in the filename. I have set up a subscription which daily saves the files where I want them. However, I haven't found a way to add the date easily. I already have a parameter when creating the report, it is called Date. Does anybody know if I can use a parameter or something else?

Thank's

Hello,

Sorry, I don't believe there is a way to modify the filename from a subscription, but you can specify a filename from a Data-Driven Subscription. Do a data-driven subscription to a file share, and just include an extra column in your subscription query to have something like this:

select 'Report or file name here ' + convert(varchar, getdate(), 101) as FileName, ...

Then, when you are setting the delivery extension settings, use this field as your File name.

Hope this helps.

Jarret

Adding Date and zero values to non existent dates

Hi,
I have info about my customers and when they place their orders. I am trying
to get a report that will tell me the sum of their orders for each month fo
r
the last 24 months. The problem I'm having is that certain customers don't
have order in every month so I'm only able to query on what's there.
How can I create a table or a view that would return every months in the
last 24 months with the sum of their orders for each month and 0 for months
that had no orders?
Thanks in advance.Read this for some ideas:
http://www.aspfaq.com/show.asp?id=2519
"Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
> Hi,
> I have info about my customers and when they place their orders. I am
> trying
> to get a report that will tell me the sum of their orders for each month
> for
> the last 24 months. The problem I'm having is that certain customers don't
> have order in every month so I'm only able to query on what's there.
> How can I create a table or a view that would return every months in the
> last 24 months with the sum of their orders for each month and 0 for
> months
> that had no orders?
> Thanks in advance.|||Thanks, I think this will help... Merci Beaucoup!
"Raymond D'Anjou" wrote:

> Read this for some ideas:
> http://www.aspfaq.com/show.asp?id=2519
> "Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
> news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
>
>

Adding data to more than one table

Hi there,

I am currently setting up a registration system where customers can registers their details and the details of the product, using ASP.net and MS SQL.

There is a column called customerID in the Custoemrs table, and a column of the same name in the Products table, so that I can have relationships between the tables.

For obvious reasons (ie. people that quit half-way through), I want to hold all the information until the end. The ID in the Customer table is unique, and auto-increasing, and therefore not assigned until the data enters the database.

However, I wish to submit information to the Products table at the same time, but what shall I put in for the custoemrID (which hasn't yet been assigned)

Thank you in advance for your help,

Nathair

It's a Referential Integrity issue. You need a ForeignKey with Cascading on UPDATE/DELETE to maintain the Referential Integrity on UPDATE/DELETE: take CustomerID column on Customer table as PrimaryKey, and CustomerID on Products table as ForeignKey, you can refer to this link:

http://msdn2.microsoft.com/en-us/library/ms177463.aspx

To maintain the Referential Integrity when INSERT, you can create an INSRET Trigger on the Customer table as following:

create trigger trg_Customer on Customer for insert
as
insert into Products select CustomerId,'myProduct' from inserted
go

You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

|||Thanks for that. UPDATE/DELETE isn't really gonna be an issue.

Normally I hard-core my SQL statement into the actual page - however, this time I'm thinking of using an SP in MS SQL. For both ways, I am unsure on how to incorporate the trigger?

Thanks,
Nathair

Adding an item using limited list in VBA

Hi all,

I am trying to add a new item (City) using a Combo Box in a Customers form. The Form is in Access 2002 and tables are MS SQL Server 2000.
I would like to use a code like one I used when working with Access 2002 tables but without success. If a new City is typed the code asks if
new city will be added. Can someone suggest a version which would work on a MS SQL Server 2000?

Thanks

Dani

PS

Here is the VBA code I use when working against Access 2002 tables.

Dim Response As Integer

Private Sub City_NotInList(NewData As String, Response As Integer)
' Add new City
On Error GoTo City_NotInList_Err

Dim conConnection As ADODB.Connection
Dim StrSQL As String
Dim iAffected As Integer

Set conConnection = CurrentProject.Connection
StrSQL = "INSERT INTO Cities (City) Values " _
& "(" & "'" & NewData & "'" & ");"

If LogMsgBox(Msg("Add new city") & " " & NewData _
& vbCrLf & Msg("Are you sure?"), vbQuestion + vbYesNo, _
Msg("Add new city"), Form.Name, "City_NotOnList", True) = vbYes Then
' Add new city
conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
Response = acDataErrAdded
Else
' No
Response = acDataErrDisplay

End If

' Close ADODB connection
conConnection.Close

Exit_City_NotInList:
Set conConnection = Nothing ' Deassign ADO object
Exit Sub
City_NotInList_Err:
MsgBox Err.Description
Resume Exit_City_NotInListI found where is the proble.