|
You have to use either Parameters in the update command.
But the best practice is to pass the users input
to a "STORED PROCEDURE" and let the SP do the job.
Regards,
Arun Kumar.A
|
|
|
|
|
The problem is that ' ends your quotes for your string value. In fact, if you typed this "value' GO drop database mydatabase GO", you'd find that the SQL after the ' GO is going to execute. You need to read the articles on SQL injection attacks that exist here on CP.
The short answer is to replace ' with ''. But, please read the article, your code is not safe.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Can you recommend a good one?
/\ |_ E X E GG
|
|
|
|
|
This[^] is the best article for it on CP.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello,
VS 2005
I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.
This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)
However, I have to change this column to a DateTime without destroying the data.
Is there any easy way to write some script or use studio management to change this.
Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.
The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.
I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Any suggestions would be most grateful,
Thanks,
Steve
|
|
|
|
|
It's most likely that the data is being interpreted in US English (MM/dd/yyyy) format.
You could try using a query window (right-click the database and select New Query) and using the following
SET LANGUAGE British
ALTER TABLE table
ALTER COLUMN column datetime where table is the name of the table and column the name of the column to alter.
If that doesn't work, try adding a new column of type datetime and then using an UPDATE statement with SET newcolumn = CONVERT( datetime, oldcolumn, 103 ) to do the conversion, then drop the old column.
|
|
|
|
|
Hi guys,
I have a website that works with an Access DB,
now, I've moved it to a computer that has "only"
office 2007, and when the site tries to open a connection
to the DB, I get :
'Could not find installable ISAM.' message
Anyone ?
Regards,
Tzumer Edo.
|
|
|
|
|
|
Hello,
VS 2005
I am developing the database application. This is a live database and is being used by the customer.
I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.
There are already over a 1000 rows in this table. When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:
"The value for column 'DateStarted' in table 'IncidentTask' is DBNull."
The method for inserting is:
Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted
What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:
UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)
Many thanks for any suggestions,
Steve
|
|
|
|
|
Or you could specify a default value for the date and total hours in the alter table script that adds the new columns...the DateStarted should probably not allow nulls in the first place.
Alter table [IncidentTask] add Datestart Datetime DEFAULT 20050101 NOT NULL, TotalHours INT DEFAULT 0 NOT NULL
|
|
|
|
|
|
Can I declare variables in MS Access same as we can do in T-SQL.
Example:
declare @i as Integer<br />
set @i=1;<br />
<br />
select tablename.ColName where Col.ID=@i
I Love SQL
|
|
|
|
|
VBA is used in MS Access so this usage is not possible.
Regards
KP
|
|
|
|
|
Thanks , I will try to find another solution without using t-sql in ms access.
Regards
I Love SQL
|
|
|
|
|
Access doesn't support true stored procedures like this. Anything you do like this has to be implemented in your own application code.
|
|
|
|
|
Access doesn't support T-SQL, not only stored procedures.
|
|
|
|
|
SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblTimeCardmain.carddate,
dbo.tblTimeCardmain.reportingtime, dbo.tblTimeCardmain.leavingtime, dbo.tblsalarydetails.Whrs,
dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid FROM dbo.tblEmployeeMaster INNER JOIN
dbo.tblTimeCardmain ON dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid AND
dbo.tblEmployeeMaster.projectId = dbo.tblTimeCardmain.projectid INNER JOIN
dbo.tblsalarydetails ON dbo.tblTimeCardmain.empid = dbo.tblsalarydetails.EmpId INNER JOIN
dbo.tblMonthHourDetails ON dbo.tblTimeCardmain.empid = dbo.tblMonthHourDetails.EmpId AND
dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate AND
dbo.tblTimeCardmain.projectid = dbo.tblMonthHourDetails.ProjectId
WHERE (dbo.tblTimeCardmain.carddate = CONVERT(DATETIME, '2007-06-04 00:00:00', 101)) AND
(dbo.tblTimeCardmain.app1 = 1) AND (dbo.tblTimeCardmain.projectid = 100)
Hi i amn't well in sqlserver so hlp me...,
See the above query in that i am checkng "dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"...,
if both dates are equal then those dates cant display how to do that?
same time it want to check other conditions also...,
Plz hlp me...,
Mag
|
|
|
|
|
the table structure is not fully understood.
however, from remove join on date ...
"dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"
and use it in where with not equal
"dbo.tblTimeCardmain.carddate <> dbo.tblMonthHourDetails.SDate"
check whether is solves
Regards
KP
|
|
|
|
|
Hi all,
I need to know if this is possible, and if it is, whats would a stored proc like that look like.
What I'm looking for is a query that the stored proc will run, which will like the following :
SELECT * FROM myTable <br />
WHERE<br />
(<br />
SELECT COUNT(*) FROM myOtherTable<br />
WHERE (myOTID = @param1 OR<br />
myOTID = @param2 OR<br />
myOTID = @param3 OR [could me more ORs]) AND (myOtherTable.myID= myTable.myID)<br />
) = 3
Any help would be much appreciated...
Jubjub
"If you're too careful, your whole life can become a f---in' grind." - Mike McD ( Rounders)
|
|
|
|
|
Yes, it is possible.
There are 3 ways to do this as mentioned here :
Click Here[^]
Regards,
Arun Kumar.A
|
|
|
|
|
Thanx arul, that helps a lot.
That helps more than a lot. I tweaked the code that Mark J. Miller wrote in that article to suit my needs and it works a treat. I appreciate the help Arul.
Looks like I have malfunctions with my web surfing abilities .
-- modified at 20:38 Saturday 2nd June, 2007
"If you're too careful, your whole life can become a f---in' grind." - Mike McD ( Rounders)
|
|
|
|
|
I've built some strongly typed data sets, they work pretty good, but they didn't do everything I wanted them to do.
So I make a class Library and called it my DAL(Data access layer) in it, I had the data adapters, and would do some processing and then make some calls to the db through the functions set in the strongly typed datasets.
The whole system seems to be working pretty good, but now I have someone else working with me on the page stuff. At first he created his own instance of the data adapters and bypassed my DAL.
Does anyone know a good way of preventing the DAL from being bypassed? I don't know if there's a way to change the scope of a strongly typed data set so that only one class could access it.
Kris
|
|
|
|
|
Drathmar wrote: Does anyone know a good way of preventing the DAL from being bypassed?
It is called discipline.
|
|
|
|
|
Colin Angus Mackay wrote: Drathmar wrote:
Does anyone know a good way of preventing the DAL from being bypassed?
It is called discipline.
Sometimes the short answers are the best
|
|
|
|
|
im using this query
select date from table where date='5/7/2007'
it returns '0' rows, but when I run this follwoing query it returns 2 rows
select date from table where date='5/7/2007 1:30:00 PM'
i don't want to search with time how can i do that?
i even try this
select date from table where date='5/7/2007%'
but it gives error as it cant convert smalldatetime to string any solution.
|
|
|
|