|
For the first problem it looks like I missed the line:
cmd.CommandType = CommandType.StoredProcedure You need to place this between creating the cmd object and the cmd.ExecuteNonQuery.
chodicimrkev wrote:
It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why?
Ah! Just realised that RAISERROR doesn't like strings being concatenated inside it. Weird!?!
Anyway, try
DECLARE @error_message varchar(256);
SET @error_message='The user '+@UserName+' already exists.';
RAISERROR(@error_message, 16, 1);
Hopefully it should work out now.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I take my hat off to you!!! Your better help than MSDN. Thanks a lot, it works definitely. I am going to change all in my app to stored procedures, it's more clear than I did before. Thanks!
|
|
|
|
|
chodicimrkev wrote:
I take my hat off to you!!! Your better help than MSDN.
I'm not sure about that, I have to admit I've had 8 years to get used to the style. But thanks for the compliment, it is very welcome.
Have a great weekend. 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Another programmer converted to using stored procedures... you're an evil genius! 
my blog
|
|
|
|
|
Steven Campbell wrote:
Another programmer converted to using stored procedures... you're an evil genius!
 Mwwaaa haaa haa haa haaaaaaa!!
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Just wanted to say that I thought your reply here was really good. I agree - I would never build a database that didn't work through stored procedures. I was shocked to find out that MySQL does not support them, I immediately stopped considering it when I found out.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Christian Graus wrote:
I thought your reply here was really good
Thanks.
Christian Graus wrote:
I was shocked to find out that MySQL does not support them
Really! I heard also that it doesn't support transactions either (although there is supposed to be some sort of plug in that gives this functionality). That is the biggest showstopper for me.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
I am porting an enterprise level 3-tier C++ application to C# with a tight timeline. I am confused with the database jargon in .net Can somebody advice me on which objects/wizards can satisfy following requirements:
1. Compatibility with multiple database vendor (Oracle and MS SQL to start with)
2. Transactional control
3. Ability to fetch XML from query results
4. Ability to operate on views and execute stored procedures
Can some expert guide me?
|
|
|
|
|
1. .NET provides classes optimises for MS SQL, but also has a generic set of classes that I believe can connect to anything that is generally connectable to
2. Why don't you control transactions in stored procedures ?
3. The SQLCommand class has an ExecuteXMLReader method that should do this
4. The SQLCommand class does this all, you just give it the stored procedure name, tell it that it's a stored procedure and add to the parameters collection. Make sure any parameters that are not just 'in' parameters get set to whatever they are, or they will never come back to you.
Overall, I recommend buying the ADO.NET book from Microsoft, it answers all these questions and is a good reference to have.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Hi,
I have a Main Form, with controls, which are dynamically being populated from the database on FormLoad Event. There is a button called "Run Query", when I click this button, I want the result of the query to be displayed in a "Child Form" in a DataGrid, with sorting and paging facility and also a column in the "data grid", which allows me to edit the selected records.
Any working examples or help is appreciated.
Thanks
abhi
|
|
|
|
|
Hi,
Im trying to connect to an sql server through a PDA. When I use the PDA over a LAN network, it works absolutely fine, but when I try to connect to the sql server through Wi-Lan using the same PDA, I get an error saying that "Could not find assembly resource". Can anyone tell me the reason?
One thing that while switching from the LAN network to the Wi-Lan network, my IP address changes, so do I need to restart the Sql Server? Cant figure out the actual cause!
|
|
|
|
|
Try to read the information from here[^]. 
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hello
I am working on very simple SQL query but the resultset is not what I am waiting for.
cmdSelectConfigurations.CommandText = "SELECT Config_Number, Config_Type, " +
"Model_Serie, Serial_Number, Install_Customer_Nb, Install_Customer_Name, " +
"Install_Postcode, Install_City, Star_Zone, Install_Date " +
"FROM Configurations WHERE Config_Type like @Config_Type";
cmdSelectConfigurations.Parameters.Add(new SqlParameter(
"@Config_Type", SqlDbType.Char, 10, "Config_Type"));
cmdSelectConfigurations.Parameters["@Config_Type"].Value = @"%" + Search.ConfigType + @"%";
the character '%' seems to not be understanding, it seems to be understanding like '_'.
It considerates only one charactere before and after the value.
The query is OK when I put "'%" + AValue + "%'" directly in the Command text.
but it is always wrong when I try to use a parameter.
Thank you for your help
|
|
|
|
|
Just a WAG (Wild Ass Guess )
Change to
cmdSelectConfigurations.Parameters["Config_Type"].Value = "%" + Search.ConfigType + "%";
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
I have a feeling that if the millions of man hours wasted every year by geeks trying to get various video and sound cards working under Linux were put into some useful endeavor we'd have solved world hunger, we'd have peace and aids would be no more. [JOS poster]
|
|
|
|
|
I have tried all possibilities to write the value of the parameter.
Finally,I have changed the size of the parameter from 10 (the size of the field in the table to 12), and now the query is OK!
I am not sure that is a good solution, so maybe I will do a stocked procedure...
|
|
|
|
|
YomYom wrote:
cmdSelectConfigurations.Parameters["@Config_Type"].Value = @"%" + Search.ConfigType + @"%";
<code>
cmdSelectConfigurations.Parameters["@Config_Type"].Value = @"'''%" + Search.ConfigType + @"%'''";</code>
"Creating tomorrow's legacy systems today.....
.... One CRISIS at a time!" -- Unknown
"If you build it....
.....BUGS will come!" -JB
this.Dispose();
"A Bug is a piece of <sup> code </sup> that knows whatz its purpose"
|
|
|
|
|
I am working on a product where depending on some metadata associated with a table column my presentation of the table contents changes. For example a user may say a column is bold and in the reports it should be printed bold.
If the table schema is fixed I could have done this by maintaining another table which maps a column with its display attributes. Problem with this scheme is both my table structure and the display attributes are user configurable.
So I think the best solution will be if I could make use of any built in database functionality to store the metadata. For example MS SQL Server has a description associated with each table column, which I can (hopefully) manipulate programmatically.
I would like to know is there a universal way to store and retrieve metadata about a database table column?
|
|
|
|
|
You would have to maintain your own metadata. You could, for ease, link it to the existing metadata.
You could create your own metadata table with this schema:
CREATE TABLE MyMetaData
(id int,
colid smallint,
is_bold bit....) -- Add your own metadata as needed
The primary key is on id & colid
id is the iditifier from the sysobjects table (which contains a list of the tables umong other things)
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Yes, I think this would work. Thanks a lot, man.
This is a better solution than my original idea, as it is extensible. The only minor problem it would have - if users delete columns from their tables, the corresponding entries will unnecessarily remain in MyMetaData table. But this is a negligible problem.
Anybody else has any better/easier solution?
|
|
|
|
|
I have 2 select statements and both of them return a number, i need to add the result of both of them
Does any one know HOW-TO do this...
Glenn Guzman
San Jose, Costa Rica
|
|
|
|
|
SELECT A.A+B.B
FROM (SELECT 1 as A) AS A
INNER JOIN (SELECT 2 as B) AS B ON 1=1
For this to work each subquery (the selects inside the brackets) must return exactly one row only.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
U can try something like
create view vwtest as
select A.X S from Table A where <condition>
union
select B.X S from Table B where <condition>
select sum(S) from vwtest
cool man
|
|
|
|
|
Hi all,
anyone know if there is a way to convert an SQL Server script (i think it's TSQL?) to an Oracle script (i think its PL/SQL)?
Converting it by hand is giving me a royal headache.
Thanx in advance.
Jubjub
If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson
|
|
|
|
|
I am working on ADO databound dialog where i am calling a database which has 2 tables , in which table 1 is called as ADO and i like to call a frist field of table 2 into the same ADO dailog as a dropdown (Combobox control)which i have already called for frist table.
Please Help in this 
|
|
|
|
|
Problem:
Password text is shown in ConnectionString property of ADODB.Connection when using an OLEDB provider and specifying Persist Security Info=false.
Applies to:
ADO, OLE DB Provider
Description:
According to MSDN documentation: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg04_97g9.asp
If Persist Security Info is set to False at the time the data source is initialized, the data source cannot persist sensitive authentication information. Furthermore, a call to a property that contains sensitive authentication information, such as a password, returns a default value instead of the actual password.
After the data source has been uninitialized, sensitive information that was set when the data source was initialized with the Persist Security Info property set to False still cannot be obtained from the Properties collection or by persisting the uninitialized data source object. However, new properties set after the data source object has been uninitialized can be persisted or obtained if Persist Security Info is set to True.
Before the data source is initialized for the first time, sensitive information can be obtained from the Properties collection, and can be persisted, regardless of the setting of the Persist Security Info property. Therefore, sensitive applications should avoid passing uninitialized data source objects.
However, password text is shown in the ConnectionString property after an error occurs or when asking for ConnectionString after the ADO Connection has been closed.
The following code shows how to reproduce the problem:
private sub tryConnection()
Dim adoConn As ADODB.Connection
Dim sSql As String
Dim sConnectionString as String
Dim rstConsulta As ADODB.Recordset
Dim pwd As String
On Error GoTo ErrorHandler
Set adoConn = New ADODB.Connection
Set rstConsulta = New ADODB.Recordset
‘//Any SQL statement
sSql = "select Count(*) from K_User " '//Change this for a valid statement
'//Declares the connection using Persist Security Info=false
sConnectionString = "Provider=SQLOLEDB;Data Source=localhost; User ID=acdev;” & _
“Password=acdev; Initial Catalog=Keyadmin40; Persist Security Info=false;"
adoConn.Open (sConnectionString)
pwd = adoConn.Properties("password") '//pwd is blank, this is ok
MsgBox adoConn.ConnectionString '//password text is not shown, this is ok
rstConsulta.Open sSql, adoConn '//Executes ok
adoConn.Close '//Closes connection
‘//If the next line is uncommented, the message box will not show the password text.
‘//pwd = adoConn.Properties("password") '//pwd is blank, this is ok
MsgBox adoConn.ConnectionString ‘// The password text is shown, which is really bad.
Set adoConn = Nothing
Exit Sub
ErrorHandler:
'//If an error occurs, for example the table name on the sSql string
'//does not exist
MsgBox Err.Description
MsgBox adoConn.ConnectionString '//password text is not shown, this is ok
adoConn.Close
MsgBox adoConn.ConnectionString '//password text is SHOWN, this is really bad
End Sub
Conclusion:
Password text is shown after the data source object has been uninitialized
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
