|
How can I build this query for date input also:
str1 = string.Format("INSERT INTO Table1(ERROR, STATION, TIME) VALUES ({0},{1}, CONVERT(DATETIME, '2009-09-09 00:00:00', 102))",x,y) ;
I want that the date will be variable
thankss
|
|
|
|
|
You are injecting values into the SQL statement. This will compromise security of your application. See SQL Injection Attacks and tips on how to prevent them[^]. If I recall this was mentioned to you before[^].
By solving the security problem, you also solve the problem of formatting dates. Part of the solution to the security problem is to use parameters. This then removes the need for you to worry about how to format dates in SQL because you can pass a DateTime object as a parameter.
string sql = "INSERT INTO Table1(Error, Station, Time) VALUES (@error, @station, @time)"
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@error", x);
cmd.Parameters.Add("@station", y);
cmd.Patarmters.Add("@time", someDateTimeObject);
|
|
|
|
|
thanks,
I'm doing my first step in SQL so I'm trying
to do the simple things first...
|
|
|
|
|
Hi,
I didn't know where I should post this, but because it is ADO.NET, it would be the best place (but I have posted in the C# forum also).
Anyway, I have a question, I have created a datasource to the northwind database and created a simple windows form. With the first name, last name textboxes etc. and to select the employees I use the listbox with the employees last names in it. I have added an the TextBox_Validate Event to each textbox with the code:
<br />
private void TextBox_Validated(object sender, EventArgs e)<br />
{<br />
employeesTableAdapter.Update(northwndDataSet1.Employees);<br />
<br />
employeesTableAdapter.Fill(northwndDataSet1.Employees);<br />
}
Yet, when I change a name, and then tab or click somewhere else, it doesn't update the database with the new name.
What am I doing wrong?
Thanks,
Any reply is appreciated.
|
|
|
|
|
Hi - I added a new table. Tested on two different databases. One of the database had db_owner role checked for this user and inserting into this table worked. The other database didn't have this option checked. By default this option is not checked for our database.
Is there a way I could set permission on table level, just for this table?
thanks
|
|
|
|
|
In my stored proc, changed a DML "truncate" to use "delete" - it is now working.
|
|
|
|
|
Hi,
I want to know,How to know Store Procedure Input and Output parameters.
What I want.
When I pass any Store Procedure Name.it's return that Store Procedure Input and Output parameters.
Help me out
it's very arrgent.
Thanks
|
|
|
|
|
|
Hi,
I am using DateTime data type in my SQL Server 2000 to store the date.
When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
Kindly help me.
Regards,
Jay 
|
|
|
|
|
Hi
Use This
select
Convert(varchar(12),DateTime,101) AS DateTime
from
Student
this will work for you
|
|
|
|
|
Jay_se wrote: When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
It is considered bad practice to store dates as strings. SQL Server has a DATETIME column type that you can use that is culture indepenent.
It is better to render the date in a culture specific way in the application's presentation layer. When a DATETIME column is read into a .NET application you get a .NET DateTime object. You can use its ToString() method to define exactly how you want the date to be formatted. e.g.
myDate.ToString("dd/MM/yyyy");
A quick reference off the top of my head:
d: day
dd: day with leading zero
h: hour (12)
hh: hour with leading zero (12)
H: hour (24)
HH: hour with leading zero (24)
m: minute
mm: minute with leading zero
M: month
MM: month with leading zero
MMM: short month name
MMMM: long month name
yy: short year
yyyy: long year with century
|
|
|
|
|
Thanks Mr.Colin,
Its very much helpful for me to implement in efficient way.
Regards,
Jay
|
|
|
|
|
Hello,
I am trying to connect to a sql server 2005.
Under the surface server configuration - remote connections i have set it for named pipes and tcp/ip. Is there something else i should do with the configuration?
I have also turned of the firewall, in case that has caused a problem.
my connection string is below.
<br />
Try<br />
cnn.ConnectionString = "data source=1.1.1.1,1433\ssd01;Network Library=DBMSSOCN;database=serviceAcc; user id=; pwd=;persist security info=True; encrypt=yes"<br />
<br />
Catch ex As Exception<br />
Console.WriteLine(ex.Message)<br />
End Try<br />
I have changed this a few time and the error message:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
Thanks in advance,
Steve
|
|
|
|
|
Hi,
It seems that you are using named instance. In such a case you should check what is the port of that named instance in order to point your connection string correctly.
For example, if your port number is 1550 you should specify the connection string as follows:
Network Library=dbmssocn;Data Source=127.0.0.1,1550;...
Hope it helps,
Uri
|
|
|
|
|
Using SQL Server (2005), and performing an INNER JOIN, let's say...
SELECT * FROM Employee E INNER JOIN Address A ON E.EmployeeId=A.EmployeeId WHERE E.Department='Sales'
It has occurred to me to wonder; mightn't it be more efficient to write that as...
SELECT * FROM Employee E INNER JOIN Address A ON E.Department='Sales' AND
E.EmployeeId=A.EmployeeId
But then I think; surely the engine is smart enough to optimize something so simple if it makes a difference.
I've tried executing a few of my joins both ways, and there's little difference between the two forms.
Can anyone here tell me whether or not such an optimization is performed? Or worthwhile?
|
|
|
|
|
I'd write it this way instead
select e.*
from employee e
where e.department = 'Sales'
and exists ( select a.EmployeeId
from Address a
where e.EmployeeId = a.EmployeeId
)
;
I've always found using the 'exists' clause with a second query to be more performant than trying to use a join (even though logically they are equivalent).
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Ah, right, but that doesn't perform the join.
|
|
|
|
|
The two statements should be the same. Check the Execution Plan (CTRL + M).
|
|
|
|
|
That only seems to show the indices and such involved, which are of course the same.
My interest is more concerned with how big a temporary table gets generated by the query.
The first form may generate a very large temporary table and then pare it down based on the WHERE clause,
whereas the second form should generate a much smaller temporary table to begin with.
Look at it this way: what would you do if asked, "make a list of all the addresses for all the employees and give me the ones for the sales personnel"? Wouldn't you simply ask only the sales personnel and call it good?
|
|
|
|
|
That is where the cost estimator comes in. The query optimizer will choose the same plan either way based on the cost. If the table statistics (or the lack thereof) show that it will be more effective to filter the results from the employee table first then join to addresses it will do that in both cases. However, if the number of rows in the address table is less than the number of employees in the sales department then the optimizer may decide to pull all the rows from the address table and use them to filter the results from the employee table and then apply the filter on department.
The question is simply: how will the query optimizer interpret the statement based on the data provided by the cost estimator? Unless you enforce join order by using a join hint, the query optimizer will choose the best join order for the plan based on the cost data. If you want to enforce join order, then you will effectively tie the optimizer's hands and bend it to your will.
But no matter what you do the temporary table from the employee table will only ever contain the rows from the sales department. The statement is only parsed in the order you write it, then it becomes a data structure used by the query optimizer which is why, in this case it doesn't matter weither your filter is contained w/in the WHERE clause or the JOIN clause.
If you use SET STATISTICS IO ON you will see that the logical IO is the same either way, combine this with the execution plan of both queries and that tells me that there is no difference between the two statements at all.
|
|
|
|
|
plz guys
i need a help
how to put sql server instances running on the local area network in a combo or a list in vb.net 2003 and not in 2005.
i can put them in 2005 but in 2003 i can't
and i need it badly
plz plz plz
-- modified at 6:52 Thursday 4th January, 2007
|
|
|
|
|
|
Hi
I am learing ADO at the moment have just created a simple application to display & update the data in access data base. I have used data connection wizard to connect to the database. I dragged the table on to the form from the datasource viewer to create a simple datagrid along with navigator control. But now the problem is that when i add the new data in the datagrid and try to save it using the default save button on the navigator window, The changes just dosent get saved. Can anyone tell me why?. I am using C# 2005 express edition. Below it the details code that is created automatically for save item click
private void addressDataBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
{
this.Validate();
this.addressDataBindingSource.EndEdit();
this.addressDataTableAdapter.Update(this.addressBookDataSet.AddressData);
}
Thanks
Irfan
I am still learning
|
|
|
|
|
Verify that the event handler has been registered.
You can either :
1) set a break point in the addressDataBindingNavigatorSaveItem_Click_1 method and make sure the method is being called
2) or, look at the properties of the save button in the designer window under events. Make sure the click event has the method name as the property value.
If you try #1 and the break point isn't hit, you'll need to try #2.
|
|
|
|
|
Hi
I have check both the point. They are fine. I have tried dubugging it to make sure the on click is called & it is. It goes through the update method fine but for some reason just dosent save it
I am still learning
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
