|
Hello. Does anyone know how to concatenate two text strings in MS SQL data base. I have two tables with ntext data type fields and I need to concatenate this two and write to the therd ntext field. The sign '+' doesn't work for ntext format. I couldn't find anything about it in SQL book online. Please help. Is there any solution?
My best regards
|
|
|
|
|
Hi there. I understand that concatenation is not support with ntext data types at all. If the amount of text isn't too much, you could try converting it to a varchar datatype, and concatenate that... something like:
convert(varchar(4000), myNText1) + convert(varchar(4000), myNText2) Of course, if your ntext fields are that small, maybe you should use varchars anyway.
|
|
|
|
|
The problem is that strings are longer then 6000 symbols so I have to use "ntext" data type... 
My best regards
|
|
|
|
|
I've found a solution to the problem. There is "UPDATETEXT" expression in T-SQL. It works with pointers so it doesn't look at the length og strings. More info in SQL Book online. There are examples as well.
My best regards
|
|
|
|
|
I think I have installed MSDE 2000 on my pc.
I set SAPWD="123456" and INSTANCENAME="test". After installation, in the services, there are MSSQL$TEST and SQLAgent$TEST exiting and I have started both of them. But I can not see Server and Services available in SQL Server services Manager. So I can not use MSDE.
Do you think I install it successfully? If yes, how can I use the MSDE?
Thank you very much!!!
vigorous
|
|
|
|
|
Your service manager is probably pointing at "\\LocalMachine"
The problem is that you specified an instance name. Since you did this instead of installing a default instance, you can't connect to the instance using just the machine name, you have to provide the instance name, as well.
Open the service manager, and in the Server box, type \\LocalMachine\test. That is how you will have to refer to the instance that you installed.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
I type \\LocalMachine\test, but the message is "The network path was not found". Do you know how to confirm whether MSDE 2000 has been installed successfully or not?
Thanks
vigorous
|
|
|
|
|
What operating system?
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Windows 2000 professional
vigorous
|
|
|
|
|
In VS .Net, on the left side, in the Server Explorer fly-out, add a new server. Give your hostname, and you will be able to browse down to it in the SQLServer tree.
"For all of our languages, we cannot communicate" - Christy Moore, Natives
|
|
|
|
|
AKAIK, the service manager is not installed with MSDE (though other posts seem to disagree). The main interface is the command line OSQL utilitiy, which can be accessed using start->run-> osql -U sa -P 123456
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
How do I access oleadapter and dataset that is on form1 from form2. Also, how do I access a variable that I made public on Form1 from form2, do I have to use "global"?
|
|
|
|
|
You can create a static instance in some data holder class. You can also make it a public member of your Form1 class, but (although I don't know particulars) this is usually bad design.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
I have a web app that executes pivot tables/cross tab queries in SQL Server. All is working fince except I am having to use global temp tables, ##temp. If 2 users access the same stored procedure it causes problems because of the global temp tables. Does anyone have a work around or a solution that will create cross tab queries efficiently without having to use global temp table or use a cursor to build a query string to exec on?
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi Michael. Can you use a non-global temp table #temp?
|
|
|
|
|
No, because I am using the data in another proc that does the pivot.
I initially did it the hard way by creating a cursor to create the SQL string, but have since been using the following method. It is the best I have found, http://www.sqlteam.com/item.asp?ItemID=2955. Also, I stuck with SQL 7 for now. Not sure if declaring a table as a variable would be helpful in SQL 2K either.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi there Michael. Ok, I think I see. The crosstab proc you pointed to is one I've used before too... and you can't just substitute a local temp table #pivot for the global ##pivot - it doesn't work (scope issues when executing the dynamically created sql statement perhaps?)
It does seem though that there could be a solution using a local #temp table... I'm just not seeing it right now.
Or maybe you could use a global temp table with a name ##pivot_xxx, where xxx is a number you pull from a crosstab_rowCounter table. Maybe for each call to crosstab, start by inserting a row into crosstab_rowCounter - this table could have an identity field that you retrieve to serve as the xxx in the ##pivot_xxx naming convention. At the end of the crosstab procedure, then remove the row from crosstab_rowCounter. Do you think that might work?
|
|
|
|
|
Insert into Names(Name) values('FirstName,I,LastName');
it has 3 commas, but SQL server doesn't accept commas.
Does anyone have any idea?
|
|
|
|
|
SQL server does inserts comma in the table.
Check the following query :
Insert into Names(Name) values(FirstName + ',' + I + ',' + LastName');
Om Prakash
|
|
|
|
|
Another cheap trick if you have characters in code is to do a cast with the ascii value:
string apos = (char)39;
But yeah, I can't see why you wouldn't be able to insert a comma - it must be syntactical.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Insert into Names(Name) values('FirstName,I,LastName);
it has 3 commas, but SQL server doesn't accept commas.
Does anyone have any idea?
|
|
|
|
|
I think you're a bit confused with the syntax of the insert command (Im presuming that your table 'Names' has more than one column ... )
INSERT INTO table_name
VALUES (value1, value2,....)
or
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
here's a link to a handy ref site :
http://www.w3schools.com/sql/sql_insert.asp[^]
'G'
|
|
|
|
|
If you're saying you need to insert FirstName, I, Lastname into one column and separate them with commas then this is how you should do it:
INSERT INTO Names(Name) VALUES (FirstName + ',' + I + ',' + LastName)
This should insert the name into the database like this: 'John,M,Smith'
Hope that helps
Edbert P.
Sydney, Australia.
|
|
|
|
|
i'm new to the .net enviroment, and i'm trying to make my web application store to an access database .
my database fields are:
E-Name
E-Email
StartDate
EndDate
Reason
S-Name
S-Email
Approved
ApprovedBy
ApprovedDate
my textboxes that correspoond are
tbName
tbEmail
tbStart
tbEnd
tbReason
tbSupervisor
tbPermission
tbApproved
tbapprDate
all help would be greatly appreciated.
also, the database key is ID which is set to autonumber. i also need to query and retrieve that number.
Thanks greatly.
Dustin
Dustin Wehler
"Sometimes you win, sometimes you lose, sometimes it rains...think about it."
- Ebby Calvin "Nuke" LaLoosh in Bull Durham
|
|
|
|
|
Hi there, Dustin. I would recommend you look at some tutorials to get you started with ASP.NET and database handling. The QuickStart tutorials[^] are a good place to start. In particular, you'll want to read the tutorial "Server-Side Data Access" from the QuickStarts.
You'll start to familiarize yourself with the data objects that you'll need. In a nutshell, you'll find them in the System.Data and System.Data.OleDb namespaces (look for these in the .NET SDK documentation.) You'll use an OleDbConnection object to establish a connection to your access .mdb file. You'll then create an OleDbCommand object to represent your insert statement. Use OleDbParameter objects with the OleDbCommand to apply the values from your textboxes, and use the ExecuteNonQuery method to issue the command to the database. When selecting records you'll likely use a DataSet object or perhaps an OleDataReader
Here is a very raw example of a page that performs an insert to an Access database (in C#):
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" %>
<script runat="server">
void btnSubmit_Click(object o, EventArgs e)
{
const string kCONNECT_STRING =
"Provider=Microsoft.Jet.OLEDB.4.0; "
+ @"Data Source=c:\inetpub\wwwroot\tests\test.mdb";
const string kINSERT_STATEMENT =
"INSERT INTO [MyTable] ([E-Name], [E-Email]) "
+ " Values (@pName , @pEmail) ";
OleDbConnection con = null;
OleDbCommand cmd = null;
OleDbParameter pName = null;
OleDbParameter pEmail = null;
int insertedID = -1;
try
{
con = new OleDbConnection(kCONNECT_STRING);
con.Open();
cmd = new OleDbCommand(kINSERT_STATEMENT, con);
pName = new OleDbParameter("@pName", DbType.String);
pName.Value = tbName.Text;
cmd.Parameters.Add(pName);
pEmail = new OleDbParameter("@pEmail", DbType.String);
pEmail.Value = tbEmail.Text;
cmd.Parameters.Add(pEmail);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
insertedID = (int)cmd.ExecuteScalar();
Response.Write(
string.Format("Record has been inserted and assigned ID# {0}."
,insertedID) );
}
catch (Exception x)
{
Response.Write("There has been an error. ");
Response.Write(x.Message);
}
finally
{
if (cmd != null) cmd.Dispose();
if (con != null) con.Dispose();
}
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h3>Insert into Access</h3>
<table>
<tr>
<td>Name</td>
<td><asp:TextBox id="tbName" runat="server" /></td>
</tr>
<tr>
<td>Email</td>
<td><asp:TextBox id="tbEmail" runat="server" /></td>
</tr>
</table>
<asp:Button id="btnSubmit" runat="server" text="Insert"
onClick="btnSubmit_Click" />
</form>
</body>
</html>Another way to go about it is to use the OleDbDataAdapter object - there happens to be a good article on MSDN[^] that discusses how to retrieve the autonumber value after an insert, and their sample code uses OleDbDataAdapter. You may want to go in that direction.
One final word: Access is a good single-user desktop database. It is not as good as other RDBMS (for a variety of reasons) when it comes to multi-user web applications. SQL Server or Oracle may be better choices if you have the $$$. If cost is an issue, you may want to investigate using MSDE - a lighter-weight SQL Server from Microsoft, or perhaps even MySQL.
I hope this helps.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
