Showing posts with label enter. Show all posts
Showing posts with label enter. Show all posts

Wednesday, March 28, 2012

incorrect syntax question

i have a table of data that the user can enter into, the data type is set to "text" and has worked in some test so far, but when i type data in '' marks such as :

'text here'


it gives me an incorrect syntax error, is there a way around this? or is the '' charectors invalid? thanks John

A single quote is a string separator. So if your data has single quotes you might have to excape it with double quotes: example:Select'test''s'

|||Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.|||

Mikesdotnetting:

Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.

Agreed.Yes

|||

parameters will stop me getting this error? awesome! thanks for your help John

|||

I have an insert query which gives me a similar error, I cant see why its not working, the error is

A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").

My code is :

Connection.Open();
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES ('@.item')", Connection);
InsertItem.Parameters.Add("@.item", SqlDbType.VarChar).Value = Textbox1.Text;
InsertItem.ExecuteNonQuery();
Connection.Close();
I simply tryed to insert the text string <b>test text</b>

Thanks John

|||

You dont need to put quotes if you are using parameterized queries.

SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES (@.item)", Connection);


|||It's objecting to the fact that you are trying to input html tags. Set ValidateRequest to false in the @.Page directive:http://www.asp.net/learn/whitepapers/request-validation/|||

thats awesome thanks, that answered every question i could come up with! haha John

Wednesday, March 21, 2012

Incorrect Parameter in Desing Mode

WHERE (Cono = @.Company) AND (DATEPART(month, PaymentDate) = @.Month) AND (DATEPART(year, PaymentDate) = @.Year)

If I run the job in preview mode I enter the parameter data as requested and it runs correctly. When I go back to design mode and run the query using the ! (the parameter box pops up with the data I entered in preview mode) I get an error message - The Parameter is incorrect.

I've tried setting the parameters to every combination of string/interger I can think of.

What is happening here?

Try to eliminate parameters one by one (replace with a literal value), so you'll know which parameter causes the error message.|||It has to do with the cono (Company) parameter. Cono is defined as integer. I get the message no matter if I set the parameter value to string or integer.

Sunday, February 19, 2012

In SQL Server XYZ & xyz are treated as same value

Dear All,

In ORACLE, the values 'XYZ' and 'xyz' are treated as different values and I can enter two records with the primary key field having values 'XYZ' and 'xyz'.

But In SQL Server, 'XYZ' and 'xyz' or 'xYz' or 'Xyz' etc are treated as same value. So, when I try to insert 'xyz' after inserting a record with 'XYZ' as value of the primary key field, it gives me the duplicate record error. How to solve this problem?
I want it to work the SQL Server same as Oracle according to our requirement.

Can any one of you Please help me out in this regard?

Regards,
Sat.oracle is case sensitive, by default sql is not...
you can either change your whole installation to be case sensitive
or just make that primary key column case sensitive by running the command below...

alter table tablename
alter column columnname datatype
collate SQL_Latin1_General_Cp1_CS_AS