|
You could soemthing like this, not very clever but it works and you could alway put it in an UDF.
declare @Mob varchar(50)
set @Mob = '0122-1230-123'
set @Mob = REPLACE( @Mob , '-', '')
set @Mob = REPLACE( @Mob , ' ', '')
set @Mob = REPLACE( @Mob , '/', '')
set @Mob = REPLACE( @Mob , 'T', '')
set @Mob = REPLACE( @Mob , '\', '')
SELECT @Mob
Results
--------
01221230123
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Is UDF slow? 
Norman Fung
|
|
|
|
|
Like anything, it depends how you use it.
For example:
SELECT * FROM A INNER JOIN B ON A.SomeValue = dbo.SomeUDF(B.SomeValue) isn't likely to be very quick.
What scenarios did you have in mind?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
No UDF is quick due to it being precompiled on SQL server.
Although I have heard rumours its slower than stored procedures under certain situations; but I've never found any hard evidence to support this.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
I have an sql table with products
each products owns its categorypath in the category field
ex: columns
item | categoryPath
product1 | cat1 > subcat1 > subsubcat2
product2 | cat1 > subcat1 > subsubcat3
product3 | cat2 > subcat2 > subsubcat1
... so on
how can i generate a xml-tree from the column categorypath ?
like:
<node>cat1
<node>subcat1
<node>subsubcat2</node>
</node>
<node>subcat1
<node>subsubcat3</node>
</node>
</node>
...etc.
anyone help, thank you
starlight71 needs help
-- modified at 17:02 Wednesday 26th April, 2006
|
|
|
|
|
We're planning to use SQL 2005 Reporting Services, but first:
1. Is it as buggy as 2000?
2. Formular field and DTS packages: VBScript or C#?
3. Can you deploy reports built for SQL 2000 on SQL 2005?
Thanks in advance.
Norman Fung
|
|
|
|
|
hi,
A problem raised when I changed my Connection string by removing the Integrated Security = SSPI in order to allow multiple users to access the database. The problem is that the connection does not open at all!with any user and I got the following error : System.data.sqlConnetionException and System error nothing else. I'm working on the server machine. and myConnectio string looks like the following Workstation Id = TOTO;DATA SOURCE=TOTO;Initial Catalog= files;MAx Pool Size = 200; user Id = toto;password = foo"
Can anybody help please
|
|
|
|
|
Hi everyone,
I have a small problem which I really need to solve today. I am doing a program which the user needs to log in. Now, when the user clicks the login btn, the username of that particular user is saved in a table called Marks.
Then a new form is opened in which the user can do a small exam and finally submit it. When he clicks the submit btn the mark is saved in the table, in the column called subjectMarks, near the username which was already saved before (in the column userName).
Unfortunately it is working wrong since the username is saved in the first row and then the mark is saved in the 2nd row.
How can I do it?
The following is the code I used in the submit btn:
string query = ("INSERT INTO Marks (" + subjectMarks + ") VALUES ('" + marks + "')");
OleDbCommand myCommand = new OleDbCommand(query);
myCommand.Connection = myConnection;
try
{
myCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
ex.ToString();
}
Thanks
|
|
|
|
|
You need to do an UPDATE not an INSERT. You might want to take steps to prevent SQL Injection Attacks[^]
Also, from the limited amount of information that I have about your data model I have a feeling that it is not normalised properly. You shouldn't need to inject the column name in the way you are doing.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
string query = "UPDATE Marks SET subjectMarks = '" + marks + "' WHERE userName = '" + cUserNameVar + "'"
This assumes that the username is unique in the Marks table, since every record with that user name will be changed to have the new subjectMarks value.
Either that, or you need an ID column - when you INSERT the name into the table from the login btn, get the ID and use that in the WHERE clause of the UPDATE when you put the subjectMarks in later.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
hi
i have had that problem for some time now, where i wish to receive data from another source, format it into a datatable sturcture identical to a data table in my database, and pass said data table to an Oracle database in order to update said data table.
consulting with one of my DBA's i've been refered to the SYS_REFCURSOR object, however i could not find any explenation on it anywhere.
i would very much appriciate someone telling me first if i'm only daydreaming and what i want is impossible, and if it is possible, some way of coding it.
|
|
|
|
|
Hi,
I have a big trouble with connecting a SQL server database... 
The code for connecting SQL server database I used to connect to other SQL server database is as following:
Dim cnReg As SqlConnection
cnReg = New SqlConnection(ConfigurationSettings.AppSettings("MyConnection"))
cnReg.Open()
Dim dsReg As New DataSet
Dim comReg As New SqlCommand
With comReg
.CommandType = CommandType.Text
.CommandText = strSQL
.Connection = cnReg
End With
Dim daReg As New SqlDataAdapter(comReg)
daReg.Fill(dsReg, "Temp")
cnReg.Close()
In Web.config file
<appSettings>
<add key="MyConnection" value="Persist Security Info=False;Data Source=64.119.38.128;User ID=myid; Password=mypassword;Initial Catalog=mydb;" />
</appSettings>
The above code works correctly for my other projects. For my current project, since this hosting company doesn't allow external access SQL server database, I need to use server name, instead of server IP address, for the Data Source, so I change the setting in web.config as following:
<appSettings>
<add key="MyConnection" value="Persist Security Info=False;Data Source=whsql-v09.prod.mesa1.secureserver.net;User ID=myid; Password=mypassword;Initial Catalog=mydb;" />
</appSettings>
When I try to run the application, I got the error:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
I am using .NET Framework 1.1 and have SQL server service manager installed on my testing machine. Also, have McAfee personal Firewall installed.
I think, it's probably because there is something wrong with the setting on my local machine. I have spent the whole day trying to find out the problem, but still couldn't solve it...
Can anybody give me some ideas about how to solve the problem? Any advices and help would be very appreciated!
Thanks,
-- modified at 20:16 Tuesday 25th April, 2006
|
|
|
|
|
Is the Server instance running?
Pictures of the menu available at the drive-thru
|
|
|
|
|
yes, The server instance is running.
I found FAQ from hosting company:
Question:"Can I connect to my database remotely?"
Answer: "No, we do not allow external connections to our database servers."
So I think, it means I have to upload code to the server, then test and debug...
But before the domain name is available, I have no idea how to test it on the server. That's why I want to test and debug on my local machine.
Any suggestions? I would really appreciate it!
Thanks!
|
|
|
|
|
I am using Sql Server 2000 and Visual Studio 2005 and c#. I am have stored procedure which takes some input parameters and it has couple of output parameters
it also returns a Recordset. I am trying to get the value from the Output parameter and also the read the SqlDataReader (which says that it has some rows)
but when I try to access the value in the output parameter it says that 'new' is required to access the data.
varSqlDataReader = SqlCommandObj.ExecuteReader();
How can I get both the Output parameter value back and also the records within the same procedure is this possible
Any sample code would be helpful.
Thanks
netsurf1970
|
|
|
|
|
I have icluded an SQL database file in my application and it now uses sqlexpress to attach that and use that file. It workes good but now I have to deploy sqlexpress 2005 with my application and it requires SP2 for Windows XP. Is there any other way I can use this SQL file without forcing all the customers to install SP2?
Any help is good because I'm desperate.
--Nikola--
modified 7-Dec-20 21:01pm.
|
|
|
|
|
Get them all to install full SQL server 
|
|
|
|
|
I'd love to but for some reason (I don't know why  ) they don't want to. 
--Nikola--
modified 7-Dec-20 21:01pm.
|
|
|
|
|
Apologies for not having a more useful answer. It's very warm in here and i may have been a bit heavy on the coffee today...
|
|
|
|
|
No problem.
--Nikola--
modified 7-Dec-20 21:01pm.
|
|
|
|
|
My O.S is windows 2000.I want to execute a function which is defined in dll.
Let ne know how to execute a function in oracle which is rsiding in a dll.
Looking forward to a reply
Manish
|
|
|
|
|
You reference the DLL in your project, then you can use the classes and other types exposed in the DLL like any other class in .NET framework.
--------------------------------------------------------
My development blog
Q:What does the derived class in C# tell to it's parent?
A:All your base are belong to us!
|
|
|
|
|
Hi all,
I want to create db in sql server 2005. Just i have gone to object explorer and in the database folder. i just right click and said new database, then i got below this error. This is the way to create new database or anyother way. please help me anyone
TITLE: Microsoft SQL Server Management Studio
----------------------------------------
Cannot show requested dialog.
----------------------------------------
ADDITIONAL INFORMATION:
Full-text is not supported on this edition of SQL Server. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1116.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.UnsupportedFeatureException&LinkId=20476
----------------------------------------
BUTTONS:
OK
----------------------------------------
Regards,
siva
|
|
|
|
|
try and create the db using a script
create database......
create table ......
its harder but more fun and more satifiying.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
-- =============================================<br />
-- Create database template<br />
-- =============================================<br />
USE master<br />
GO<br />
<br />
-- Drop the database if it already exists<br />
IF EXISTS (<br />
SELECT name <br />
FROM sys.databases <br />
WHERE name = N'<Database_Name, sysname, Database_Name>'<br />
)<br />
DROP DATABASE <Database_Name, sysname, Database_Name><br />
GO<br />
<br />
CREATE DATABASE <Database_Name, sysname, Database_Name><br />
GO
The above is from the template of the SSMS. Open a new quesry then copy the above into your query wondow. Change Database_name to the name you want for your db.
Pictures of the menu available at the drive-thru
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
