|
This should work. What error are you getting? (You are missing a "T" at the end of the UPDATE statement. I assume that is a copy/paste error on Code Project).
Does UPDT_DTTM have a unique constraint? If so, are you adding records so fast that you are violating the constraint?
|
|
|
|
|
We had populated the date field using the GetDate() function in SQL during the insert of the record. We were using a datetime type variable in C# to hold the date until we updated the record by calling the SQL statement. When we tried to update the record using the held variable and passing it to the SQL statement to the where clause, it could not find the record to update it.
We resolved the whole issue by changing the way the date field was poulated when it was inserted into the database. We removed the GetDate() function from the SQL and passed a date variable as a string to the datetime type field on the SQL statement. When we updated the record this time, it worked.
Thank you for your help in trying to resolve this.
|
|
|
|
|
If you could show how the procedure is invoked, that might help. How exactly is the date variable passed in the call?
As an aside, logging things like a create_date or update_date, I usually will do that using triggers instead of implementing them within the create or update procedure. It's an extremely subtle difference, but I'd be interested in knowing if others agree or not.
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
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]
|
|
|
|
|
Thanks for replying to the question. We were able to resolve it by changing the SQL insert statement used to write(insert) the record. We removed the GetDate() function and used a string variable to hold the date read. Then we passed it into a datetime type variable in the SQL statement. Without changing the update SQL statement, we were able to update the record.
|
|
|
|
|
I have an existing SQL production database. During new developments, new fields/tables/stored procs etc have been added.
Now I want to merge these changes into the production server (without loosing data).
I could write a program to do it, but is there an easy way?
|
|
|
|
|
We use SQL Compare[^].
The alternative is of course to save a script every time you make a modification, or simply to make the changes using a script in the first place, but that's not really an option for you now.
The only other free alternative - the way we used to do it - is to export the database schema to a file using the Generate Script feature from both the old database and the new, and use a diff tool (such as WinDiff) to compare them. This will at least show what's changed. Then you write another script which uses ALTER DATABASE/ALTER TABLE/etc as appropriate to make the necessary changes.
|
|
|
|
|
hey guys,
How can i get the values oif multiple columns in rules? I was Googling up rules and all i could find was the method to use single column. Let me give you an example:
In an table i have many columns, three of which are X,Y & Z. I want to create a rule that checks if the sum of three columns are greater than 1000. If i do manage creating a rule and bind it to column X - how do i access the value of Y and Z from my Rule. Could some give explain. A peice of code would be really helpful.
Thanks.
|
|
|
|
|
I wanna ask a question bout datareader and dataset.
If i execute 1 Sp with 2 queries in it as
select * from employee
and
select * from Company bothe
this SP's result i execute in dataset as well as datareader at a time. so what o/p it will show in each -datareader and dataset ?
JAX
|
|
|
|
|
Jax_qqq wrote: so what o/p it will show in each -datareader and dataset ?
It will show the same output unless the database changed between runs.
DataReader provides a forward only view of the data. The DataSet is a container that is used by a Data Adapter so that the result set can be accessed in a random access within the application.
|
|
|
|
|
Hi,
I want to run Job using any command prompt utility. Such type of utility is there in SQL Server 2000?
I know about DTSRun.exe is used to run DTS package, but I want to run Job as a whole.
Thanks.
modified 15-Sep-13 3:09am.
|
|
|
|
|
You mean the OSQL command line utilty?
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
How ca i remove spaces in my database ?
my database : visualfoxpro 6.0
CDatabase_Name::CRecordset
(MFC)
col1 = "name"
col2 = "city"
col3 = "country"
tank you in advance !
Bravoone
|
|
|
|
|
How to reset a database's identity value to 0, after deleting all rows from a table.
FIRE
|
|
|
|
|
I don't know the exact way but one workaround is there. You change that column identity to no and again reset it to yes, next time it will start from one.
Best Regards,
Apurva Kaushal
|
|
|
|
|
|
I not sure is there way to show percentage of my sales.
I have done a SQL on showing the total sales and now i want to calculate the percentage of total for each department. Example
TOTAL sales= $100
Vendor1 =$20
Vendor2 =$10
........
how can correctly show another column that will calculate the percentage?? I have try many method but not working... i was thinking to write as
sum(ti.totalsales)/sum(sum(it.totalsales))*100 as precentage
is SQL able to support those complicated calculation? or i have to do it in vb...
---HEre is the basic show the total sales with out percentage...
select d.deptdesc,v.[name],ti.deptcode ,sum(ti.totalsales) as Total from
tot_item ti , dept d, item i, vendor v
where ti.itemcode=i.itemcode and v.vendorid=i.vendorid and
ti.deptcode=d.deptcode
group by v.[name], ti.deptcode, d.deptdesc
ORDER BY v.[name], ti.deptcode ASC
|
|
|
|
|
The sample query you list has both Department and Vendor in it (you could get departments listed more than once if they use different vendors). You are trying to get a percentage of total for each department but your sample result set lists no percentages and values are listed by vendor. Are you sure you know what you really need?
Given your 2nd line and assuming you are using SQL Server, first calculate a good total sales per department.
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
Now write a query that calculates total sales.
SELECT
SUM(ti.totalsales) AS FullTotal
FROM
tot_item ti
Now put them together
SELECT
dep.deptdesc,
(dep.DeptTotal /
(SELECT
SUM(ti.totalsales)
FROM
tot_item ti)) * 100 AS PercentOfSales
FROM
(SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)) AS dep
ORDER BY
deptdesc
Note: I am using a more modern form of the JOIN syntax. There are issues with expressing the JOIN in the WHERE clause. You can make this a lot cleaner using variables in a stored proc.
|
|
|
|
|
Sorry - forgot the GROUP BY clause in the aggregate:
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
GROUP BY
deptdesc
This will also need to be modified in the final query.
|
|
|
|
|
You can try something like this:
DECLARE @TempTotal int
SELECT @TempTotal=sum(ti.totalsales) from ti
select d.deptdesc,v.[name],ti.deptcode ,(sum(ti.totalsales)/@TempTotal) as Total
from
tot_item ti,
dept d,
item i,
vendor v
where
ti.itemcode=i.itemcode and
v.vendorid=i.vendorid and
ti.deptcode=d.deptcode
group by v.[name], ti.deptcode, d.deptdesc
ORDER BY v.[name], ti.deptcode ASC
|
|
|
|
|
Hey all,
I'll open with an example:
Lets say I have a table of students...
I can represent each line in the table as a class, which I'll write.
This Student class would contain all of the fields in the table as properties, and maybe a few more functions.
So now lets say I have another table of classes, and I want to write a complex query, which takes information from both tables.
How can I represent this kind of stuff as classes?
Thanks in advance,
Shy.
|
|
|
|
|
It could become something like your Class object having a collection of Student class objects.
|
|
|
|
|
Hi,
Is there any inbuilt function in SQL 2005 which checks whether referential integrity exist in any other table for an particular master record?
Regards,
Vipul Mehta
Sr. Software Engineer
NIIT Technologies Ltd
|
|
|
|
|
sp_helpconstraint stored procedure returns the constraints on the table.
Ex:
sp_helpconstraint ele_Examination
|
|
|
|
|
how to insert 3 webforms controls data to a sql database in asp.net Just posted ?
hi
i have 3 webforms.In first webform we have 2 textbox ,one is user id and other is password.then there is a continue button.page go to second webform.
in second we have two textbox.one is username and age.and again we have continue button.page go towebform3.again we have 2 textbox,one is use hobbies and user favorite food,here we have submit button.
When we clik submit button,it should get inserted in one table in database.
After clicking submit button he felt that he has to chage some thing(for eg change usrid or password) ,he goes to firstpage from third page and changes then he goes to third page and click submit and now it should be upadated.
thank you.
bye
md riyasath ali
-- modified at 1:20 Monday 25th September, 2006
|
|
|
|
|
riyasath wrote: Expecting anser very quickly
Code Project members do not respond nicely to statements like this.
|
|
|
|