If I could explain the problem..........

In SQL database we need to concatenate 2 fields to display in one and turn them into an email address in the following format

joe.bloggs@.company.co.uk

They are the following: forename & surname

The expression will require a . to be added between the forename & surname and @.company.co.uk at the end.

Anyone help...... ?This should work:

SELECT forename || '.' || surname || '@.company.co.uk' AS [name]
FROM [table]
[WHERE ...];

If Exists problem

I want to enter a website address into a TextBox and on AutoPostBack check to see if the entry is LIKE any in a SQL 2000 database tblLinkAddresses. If it is not - add it to the table and if it is display the LIKE matches.

Where do I start?

I am stupid - got the if it matches problem solved - just need to know how to add a textbox entry to a database table on a button click.

|||

SqlConnection _sqlconnection=new SqlConnection('place your connectionstring')

_sqlconnection.Open();

SqlCommand _cmd=new SqlCommand('your stored procedurename ',_sqlconnection);

_cmd.CommandType=CommandType.Storedprocedure;

_cmd.Parameter.Add(new SqlParameter('@.parametername',SqlDBType.Varchar,50));

_cmd.Parameter["@.parametername"].value=Text1.Text;

_cmd.ExecuteNonQuery()

identity

i am using sql server mobile

I have an insert statement with select @.@.identity:
commandString = "insert into Location (Address,Suburb,State) values('627 beach rd', 'rose bay', 'NSW');SELECT @.@.IDENTITY"

I execute the insert statement as below:
command.CommandText = commandString;
id = command.ExecuteNonQuery();

I get the following error:
Error: There was an error parsing the query. [ Token line number = 1,Token line offset = 137,Token in error = SELECT ]
Source: SQL Server 2005 Mobile Edition ADO.NET Data Provider

this statement works ok in sql mobile query analyzer

does anybody know where i am going wrong

do i need an output parameter

thanks
adamTry ...

SELECT SCOPE_IDENTITY() AS LocationID

...instead...I haven't worked with SQL Mobile, but you should rarely use @.@.IDENTITY. @.@.IDENTITY will give you the last identity generated in the database by any session. If there's a trigger or other user session that generates an IDENTITY for another table or the same table, you will get the wrong value. SCOPE_IDENTITY() gives you the last IDENTITY value generated by your session. (see BOL for more details about the difference.)

Also, look into the use of a simi-colon between the statements. I've seen that throw wierd errors. You might replace that with a CRLF instead. You can determine whether the semi-colon or the @.@.identity is causing the issue by replacing the @.@.identity with a one...(ie...SELECT 1 AS LocationID)

You might also need the (AS LocationID) in the statement for ADO to assign a column name to the return value, but not sure...

Lastly, as a matter of good form, you might consider placing this whole thing into a simple stored proc.

CREATE PROCEDURE dbo.LocationInsert (
@.Address VARCHAR(255),
@.Suburb VARCHAR(255),
@.State VARCHAR(255) )

AS

BEGIN

INSERT INTO dbo.Location (
Address,
Suburb,
State )
VALUES (
@.Address,
@.Suburb,
@.State )

SELECT SCOPE_IDENTITY() AS LocationID

END

Then, simply call the stored proc with the following....

commandString = "EXEC dbo.LocationInsert @.Address='627 beach rd', @.Suburb='rose bay', @.State='NSW'"

Good luck...

g2|||Setting aside issue of SCOPE_IDENTITY vs. @.@.IDENTITY, the problem you are seeing is a limitation of SQL Mobile.

This is a piece of SQL Server 2005 Mobile Books Online (http://msdn2.microsoft.com/en-us/library/ms173053(en-us,SQL.90).aspx)

Queries that can typically be run on SQL Server Mobile can also run on Microsoft SQL Server; however, many of the features of Transact-SQL are absent from SQL Server Mobile, and only a single SQL statement can be executed in a command.

Stored procedures are not allowed either (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=104249).

To Greg: semicolons are required on Mobile to separate statements, so CRLF won't help in this case (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=92581).

Regards,
Boris.