|
So, your .NET code is something like this:
new SqlCommand("INSERT INTO DEVELOPER (OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) VALUES ('1000','\"The Developer Extract XML to you \"as-is.\" asdf makes no representation or warranty to you that the data will be complete, error free, fit for any particular purpose, or provided on an ongoing basis.\nThis information is fsda's .\"','DEVEXTRACT.Toasdfne Networks.082004.xml','Tourmaline Networks','08/01/2004','09/10/2004 21:12:21');"); 
Have you considered using parameterise queries? They even help prevent SQL Injection Attacks[^]
So, if you rewrite your code as:
SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO DEVELOPER "+
"(OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) "+
"VALUES (@objId, @legal, @did, @isv, @month_year, @gmt)");
cmd.Parameters.Add("@objId", "1000");
cmd.Parameters.Add("@legal", "\"The Developer Extract XML to you "+
"\"as-is.\" asdf makes no representation or warranty to you that "+
"the data will be complete, error free, fit for any particular "+
"purpose, or provided on an ongoing basis.\nThis information is "+
"fsda's .\"");
cmd.Parameters.Add(<small>... Hopefully you get the idea ....</small>);
If you use parameters then you don't have to worry about formatting characters that SQL Server recognises messing up the text you are trying to insert.
Also, could I interest you in the Benefits of Stored Procedures[^]??
Does this help?
Do you want to know more?
|
|
|
|
|
I want to keep it like it was though... I don't want to use "parameterised queries" just yet...
do you know why if I leave my code the same with the ' in it it dosn't work and then when I take the ' out it works fine?
thanks for your time..
/\ |_ E X E GG
|
|
|
|
|
eggie5 wrote:
do you know why if I leave my code the same with the ' in it it dosn't work and then when I take the ' out it works fine?
Because SQL Server is interpreting the apostrophe as a string delimiter.
eggie5 wrote:
I don't want to use "parameterised queries" just yet...
Out of curiosity, why? Of course it is up to you, but if you don't could you let me know the systems you are working on so I don't entrust my credit card details to them.
Do you want to know more?
|
|
|
|
|
oh yeah... so can I go like this??? \' ? will that work?
I'm just messing around with the sql server... i'm not doing anything important.
/\ |_ E X E GG
|
|
|
|
|
You would double up the apostrophe. E.g.
INSERT MyTable(MyColumn)
VALUES('It''s a lovely day.');
SELECT * FROM MyTable; The result is
MyColumn
-----------------------------------------------------------
It's a lovely day.
Do you want to know more?
|
|
|
|
|
Hi,
I would like to allow users of my ADO.NET application to browse through a list of the available databases on a given SQL server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this?
TIA,
Royce
|
|
|
|
|
|
I want my client app connecting to an external server with IP 1.1.1.1 for example. The database is called "WORK"
I tried to use URI
Uri url = new Uri("http://1.1.1.1/);<br />
connDB = new SqlConnection();<br />
connDB.ConnectionString="data source="+url.Host+" +<br />
"initial catalog=WORK;" +<br />
"user id=me;" +<br />
"pwd=me;" +<br />
"packet size=4096;" +<br />
"persist security info=false;";<br />
connDB.Open();
doesn't work...
Also if I am trying to access the local server db, it fails. Even if data source is (local). How can I access external server's db by IP ?
|
|
|
|
|
|
'SQL Server does not exist or access denied'
If I am right, there only can one instance of SQL Server at the same time ?
|
|
|
|
|
Vector7 wrote:
If I am right, there only can one instance of SQL Server at the same time ?
Actually there can be many (I think up to 16, but I'm not sure).
Suppose the server is called SERVER. The default instance is unnamed and thus is accessed with SERVER. The other instances are named (say SQLSRV1) and it's accessed with SERVER\SQLSRV1.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Try to copare your code and the library of connection string.[^]. Learn from it and let me know if it is working or not.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
I don't know if it may be the case, but try removing that http:// prefix in the URL. That prefix indicates it is HTTP protocol (for web pages) and the server address is only the IP.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hi,
Can someone please help me to format a date in a SQL query coming from SQL Server. I want to format it the way that I like it. Below is what I found in the MSDN library. I want it to display yyyy-mm-dd. Do you this it maybe better to concatenate a string by using YEAR(), MONTH(), and DAY()? Surely there must be a way?
Without century (yy) With century (yyyy) Standard Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM
Thanks
BRENDAN
|
|
|
|
|
SELECT CONVERT(VARCHAR(10),<your datetime value>,20)
|
|
|
|
|
Hi,
I want to query the CASE function in a SQL query.
I have a table field called ContactIsRead. It has a boolean value to determine whether an e-mail has been read or not. When a person open this mail for the first time, it set this field to true, and adds the date to ContactDateRead.
My problem is if the user wants to read this mail again, then I want it to test if the ContactIsRead has been set to true. If not then set it to true and add the current date, if it has been read, then do nothing.
I am working with a normal ASP (not ASP.NET) page that lists all the e-mails as links. Those that have not been read are in bold, while those that have been read are normal text. When a user clicks on the link it take him to a page where he can read it.
I hope it is clear enough. If not, let me know.
Brendan
|
|
|
|
|
Do something like this
select * from
(
select case blah then 1 else 0 end as ContactIsRead from blah blah blah
) mail
where mail.ContactIsRead = 0
You create an alias for the entire query, and then filter on it. The alternative is this:
select case blah then 1 else 0 end as ContactIsRead from blah where (insert same query here that you used in the case statement ).
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
This is a problem better suited for the WHERE clause. A CASE statement would just complicate the matter.
UPDATE
EmailTable
SET
ContactIsRead = 1,
ContactDateRead = DATE()
WHERE
EmailId = 1234 AND
ContactIsRead = 0
In the case where the email had already been read, the UPDATE would result in 0 rows altered.
|
|
|
|
|
I have a Stored Procedure with one input parameter and one
output parameter.
I create a SQLCommand for a Stored Procedure
I add parameter1 with direction input
I add parameter2 with direction output
I add parameter 1 and paramter2 to the Cmd's parameter Collection
I assign an input value to parameter1
I invoke the command's ExecuteReader() and try to
output the result(Parameter2) via response.write
I get a blank?
ps:
1. Procedure works on Query Analyzer
2. At the very end of code I verify two parameter are in
the Paramters collection.
|
|
|
|
|
Try assigning a value to the output parameter and see if it changes. Make it in/out and change the SP to store the incoming value somewhere so you can see if it's getting there OK. Check your spelling. 
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Christian:
Thanks for the quick reply. Although I coded as an output while in Query Analyzer, it actually stores it as input/ouput, since while retrieving the output you have to read it into a variable.My spelling must be OK, since no exceptions are raised. I will diagnose some more, but I will get there. I just do not want to reinvent the wheel.
Is 'Command.ExecuteReader' the correct way?
The above is invokation is OK for a stored procedure with one input returning a number of rows of result.
mysorian
|
|
|
|
|
Here's a snippet of my code that does what you're trying to do:
SqlCommand cmd = new SqlCommand("SetTrackThumbNail", Connection);<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.Parameters.Add("@thumbNailPath", thumbNailPath);<br />
cmd.Parameters.Add("@artistName", artistName);<br />
cmd.Parameters.Add("@trackID", trackID);<br />
cmd.Parameters.Add("@thumbID", -1);<br />
cmd.Parameters["@thumbID"].Direction = ParameterDirection.Output;<br />
<br />
cmd.ExecuteNonQuery();<br />
<br />
return (int) cmd.Parameters["@thumbID"].Value;
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Christian:
Thanks a lot. Actually, I finally made it work with ExecuteReader()method of the command. What happens actually, is that there is no streaming data and you cannot use the DataReader's Read(). There is nothing to read. However, you may just read out the Output parameter's value. I will try if the ExecuteNonQuery() works as well, as nothing is returned, but everything is contained.
Thanks
jay
|
|
|
|
|
I have a bizzare problem with an SQL query I am trying to build. The problem is, I have a query with a conditional WHERE EXISTS statement that modifies every value in my table, but the SELECT part of the statement returns only the correct, single instance.
This is done in access, in VB, and any help would be greatly appreciated, the queries are listed below.
This select statement returns 1 instance:
SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)
But this update command turns every entry in my table into str_New:
UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE EXISTS (SELECT SubCategory.SubCategoryDesc
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old))
Any ideas why this occurs? It's driving me NUTS!
Cheers
Cata
|
|
|
|
|
Well, the exists clause will return true all the time, so every row is updated.
I don't know enough about the Access subvariant of SQL to provide a definitive solution, but I'd have thought that this would work:
UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)
otherwise, this may work
UPDATE SubCategory
SET SubCategory.SubCategoryDesc = str_New
WHERE SubCategory.CategoryKey IN
(
SELECT SubCategory.CategoryKey
FROM Category
INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey
WHERE ((Category.CategoryDescription)= GetCurrCategor())
AND ((SubCategory.SubCategoryDesc)= str_Old)
)
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
