|
Simple answer, no you can't. You can compare the old and new values within the trigger though if that helps. See my reply to your other question.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi
i have
table1 (name[unique], address, phone)
table2 (name[unique], occupation )
in my situation i am updating table2 name field value through procedure.
so i want to create a trigger which change the relevant name field value of table1
using sql server 2000
please help me
Rajeev Kr. Sharma
VRI Software Pvt.Ltd.
New Delhi India
HumOnline.com
Stay Connected
|
|
|
|
|
You can get the old value of the name from the deleted table and the new value of the name from the inserted record within your trigger. The deleted and inserted tables are special tables within a trigger, but if you are always updating using a stored prcoedure why not just update both atbles within a transaction. This will enaure referentail integrity too.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i am getting this error when i an trying to read data from trhe access database
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission
private void ProcessData()
{
while (true)
{
string dt = System.DateTime.Now.Date.ToString();
string[] dt1 = dt.Split();
string todays_date = dt1[0];
string strHtml = "you are late logged in";
OdbcConnection con = new OdbcConnection("DSN=target1");
string query = "SELECT DISTINCT EmpID FROM Employee";
con.Open();
OdbcCommand cd = new OdbcCommand(query,con);
OdbcDataReader dar = cd.ExecuteReader();
while (dar.Read())
{
int employee_id = (int)dar["EMPID"];
//string query2 = "";
try
{
string email = (string)dar["email_id"];
MailSender mail = new MailSender();
mail.SendMail(strHtml, "+ email +", "LateLogin");
}
catch (Exception)
{
}
}
//finally
//{
// //you can change the number of milliseconds according to your wish
// Thread.Sleep(10000);
//}
}
}
|
|
|
|
|
Hello everyone,
I have a Categories Table which has a self join, FK ParentCategoryID. I've provided a search facility with wild card search. Now the problem comes when I you search such that a only child category matches the criteria. In that case when I try to bind it with the TreeView it doesnt show anything as my function tries to insert rows from the 1st level till the end.
I was thinking if I can make out a query somehow that can give the parents of the matched child category all the way up to the first level, it can solve my problem. But the question is how???
Can any one give me some suggestions, guidelines on that
Thanks
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Hi - I'm wasting a lot of time trying to get connection right to Sybase and didn't manage to get the combination/configuration right.. very nice if someone can help here!
In my sql.ini file under here C:\...\Sybase\ocnt1252\ini:
... many entries
[UATMACHINE]
somesrv.data.com,4300
somesrv.data.com,4300
... many more entries ...
Now,
connection string = "Provider=Sybase ASE OLE DB Provider;Data Source=UATMACHINE;Server Name=somesrv.data.com,4300;Database=MyDB;User ID=johdoe;Password=xxx;"
http://www.carlprothman.net/Default.aspx?tabid=86[^]
And when I stepped through the code, it failed on oConn.Open with this erro:
[System.InvalidOperationException] = {"The 'Sybase ASE OLE DB Provider' provider is not registered on the local machine."}
Also tried this:
"Provider=Sybase.ASEOLEDBProvider;Server Name=somesrv.data.com,4300;Initial Catalog=MyDB;User ID=johdoe;Password=xxx;"
http://www.devlist.com/ConnectionStringsPage.aspx[^][^]
Same error.
These reference seems to be suggesting I should:
(a) Use ASE ADO.NET Data Provider (i.e. Provider=ASAProv.90) instead
(b) download ASE ADO.NET Data Provider
REF[^]
(c) Instead of generic "new DbConnection()" I'm using now (trying to abstract away DAL), I should be using OleDbConnection new OleDbConnection[^]
Actually this is quite inconvenient as my connection code looks like this:
<br />
DbProviderFactory oDbProviderFactory = CDataUtil.GetConnectionFactory("System.Data.OleDb");<br />
DbConnection oConn = oDbProviderFactory.CreateConnection();<br />
oConn.ConnectionString = m_Cmd.ConnectionString;
(i.e. I'm using dotnet 2.0)
One more problem,
What's Open Server 15.0?? From isql path (client installed on my workstation already working) C:\...\Sybase\ocnt1252\ini seems to suggest we're using Sysbase version 12.5? Open Server 15.0 compatible?[^]
I am not sure if I have time to read the ebook[^] just so I can open a connection!
And, here's a nice/short 32 pages intro to dotnet+Sybase by Ashish Mahajan (but fell short in providing link to download:
download.sybase.com/presentation/2003_presentations/ASE135.ppt
This seems to be telling me that ASE ADO.NET Data Provider is NOT FREE... I don't want to go through admin process and would simply like to stay with M$ Ole DB instead.
http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit[^]
devy
modified on Saturday, February 16, 2008 12:19 AM
|
|
|
|
|
Hi Devvvy,
i have a legacy code in VB.Net 1.1 and Sybase ASE 12.5, system is working fine as i took over from previous developer. Now client want to upgrade to ASE 15.7. we created a totally new environment and installed DB and ASE Client on new machines.
I was getting Connection Error while connecting with new DB.
I modified connection string in web.config
Previous:
<add key="connectionString"
value="Provider=Sybase.ASEOLEDBProvider.2;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
New:
<add key="connectionString"
value="Provider=ASEOLEDB;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
Login works fine however just after Login when system tries to run any other query in database I get following error message:
[Message Class: 14][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 924] [ASEOLEDB]Database 'RLDB' is already open and can only have one user at a time.
[Message Class: 11][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 4001][ASEOLEDB]Cannot open default database 'RLDB'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5704][ASEOLEDB]Changed client character set setting to 'iso_1'.
[Message Class: 10][Message State: 2][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5701][ASEOLEDB]Changed database context to 'master'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5703][ASEOLEDB]Changed language setting to 'us_english'.
I am also getting similar error if i try to open both SYBASE CENTRAL and SQL ADVANTAGE at the same time.
any help will be welcome.
|
|
|
|
|
Good afternoon all.
the developers on my team are having an ongoing discussion about null values being represented as another value where applicable. The reason for this discussion is we are going through a system re-write and we need to know the best way to do the following in SQL server 2005 and ASP.NET
Meaning, due to the nature of our business, we can have situations where numeric or date values can actually be represented as a character value. For example, if a numeric mortgage rate is not present on a mortgage application document, the field is considered to be "not applicable". However the data type for the mortgage rate field is numeric in the SQL database.
The argument here is how do we store the "not applicable" for a null numeric field? We have talked about having child tables to store the field name and its character field representation while keeping the null in the parent table with its true datatype.
We have also discussed having an XML field in the parent table to store the XML character representation for that field. So for example if the mortgage rate for a record is "not applicable" null, we would store null in the Mortgage_Rate field in the database, but store <mortgage_rate>Not Applicable in the XML field for that record.
Does anyone have any other types of recommendations for storing this kind of data?
Thanks for any tips in advanced.
- AGD
Software Developer
|
|
|
|
|
hi guys... i have a table that references itself!
so i came up with the following query
SELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30
FROM TimeCategories tc1
JOIN TimeCategories tc2
ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI
that returnes only 1 / 5 rows because only the returned row has a value in TCat_AlternateCategory_SI the rest of the entries has NULL which is suposed to be like that
any ideas or sugestions on how to get them all returned (incl rows with NULL in TCat_AlternateCategory_SI)?
thanx
[UPDATE]
i now have this as my query
SELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30
FROM TimeCategories tc1
JOIN TimeCategories tc2
ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI
UNION
SELECT TCat_ID_SI, TCat_Name_VC30, TCat_Rate_D, TCat_Normal_B, 'N/A'
FROM TimeCategories
WHERE TCat_AlternateCategory_SI = null
but now the line WHERE TCat_AlternateCategory_SI = null isnt working!
any sugestions?
thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
modified on Friday, February 15, 2008 9:57 AM
|
|
|
|
|
Replace "JOIN" in your first query with "LEFT OUTER JOIN".
|
|
|
|
|
it worked!
thanx man, o u one
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
Hi
I want to add a extra lump of text to a varchar field within my sql server database without first having to fetch the data
i want to do something like the below, but its not working
update mytable set col1=col1+'Sometext'
where id=1
Can any advise
ta
Simon
|
|
|
|
|
si_69 wrote: update mytable set col1=col1+'Sometext'
where id=1
I believe that this as a query should work. 
si_69 wrote: but its not working
Do you get any error message?
What happens when you try to execute a select query to check for the data?
select * from mytable where id=1
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
It seems that you have to increase the length of your field
|
|
|
|
|
Hi guys
is there any way that i can monitor connections to a sql database?
thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
SQL Server 2000: In Enterprise Manager's tree, go to your server, Management, Current Activity, Process Info. To update the view right-click Current Activity and select Refresh.
SQL Server 2005: In Management Studio, in the Object Explorer tree go to Management and double-click Activity Monitor. You can filter to only show the database you're interested in.
From a command-line tool or Query Analyzer, run the sp_who2 stored procedure.
If you want to make your applications easier to pick out, you can influence the value in the ProgramName column by using the Application Name parameter in the connection string. If you don't set this your .NET applications will simply show as '.Net SqlClient Data Provider'. Applications using OLE DB (incl. ADO) or ODBC use the product name from the resources of the main EXE, if I recall correctly.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
thanx a mill dude!
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
hi guys
this is strange, hav a look at the following code...
for (int i = 0; i < reader.FieldCount; i++)
{
MessageBox.Show("" + reader[i].ToString());
}
txtName.Text = reader[1].ToString();
txtPassword.Text = reader[2].ToString();
txtConfirmPassword.Text = reader[3].ToString();
the messagebox displays the correct data, but when i get to txtName.Text = reader[1].ToString() i get a null reference exception.
what is up with that?
Thanx
[Sollution]
all that was happening in a 2nd constructor of the form. i never did InitializeComponent();!!!
so the null reference was the text box that hasnt been created yet 
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
modified on Friday, February 15, 2008 4:57 AM
|
|
|
|
|
Good Morning All
i have the Following View
<br />
ALTER VIEW [dbo].[VW_Toi_Deeds_Vuyiswa]<br />
<br />
AS<br />
<br />
SELECT ID, PROPERTY_KEY, LIS_KEY, CASE <br />
WHEN Isnumeric(substring(FUNCTION_KEY,6,4)) = 1 --If Its Numeric on last digit of the <br />
<br />
--GEOSS part(4 Digits) <br />
<br />
AND SUBSTRING(FUNCTION_KEY, 6, 1) = 0 --Making sure that there is a Digit After the Funckey,and its "0"<br />
<br />
THEN SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />
<br />
ELSE FUNCTION_KEY <br />
<br />
END AS FUNCTION_KEY<br />
<br />
, TOWN_NAME, PARCEL_NUMBER, PORTION_NUMBER, FARM_NAME, REGISTRATION_DIVISION, FARM_NUMBER, FARM_PORTION,<br />
SECTIONAL_SCHEME_NAME, SECTIONAL_SCHEME_NUMBER, SECTIONAL_SCHEME_UNIT_NUMBER, BUYER_NAME, BUYER_ID, <br />
REGISTRATION_DATE, PURCHASE_DATE, PURCHASE_PRICE, <br />
REASON_NO_PRICE, NEW_TITLE_DEED_NUMBER, OLD_TITLE_DEED_NUMBER,<br />
STATUS, SHARES,DATE_CAPTURED,HISTORY_ID<br />
FROM DEEDS.DEEDS_ADMIN.TSHWANE_OWNER_INFORMATION<br />
<br />
This View Extract Data from a tabe Tshwane_Owner_Information table, and i dont understand why the certain part of Concatinaing use substring 7,3, because the view in this case, it goes to a field named "Functionkey" and in that Fied there will be character dataype that looks like this
"GEOSS001" or "GEOSS0001" as you can see some have 4 digits after the GEOSS string other have 3, and now the First part of the String is "GEOSS"
And as you can see above its been concatinated with the Digits.
<br />
SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />
ok, the First part i understand and we are Concatinating the String "GEOSS"+ Something like "0001" or "001", but now, the Substring says move to 7th Posistion and start counting 3 from the Current Posistion, dont you think it missed the Digit? is it not Supposed to be like this ?
<br />
SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 6, 4)<br />
Correct me if am Wrong, we have been unable to get some Records through this view, I did not write it, i found it there, am trying to get it fixed.
Help
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi folks,
Actually in my table i have (lru_name,part_no) as the primary keys. But the problem that i am facing is i have some values in part_no like'121232 with 2323' ,'121212 or 12123' and '121212 & 12121' ,this words like with ,or ,& is creating problems as the database in not able to take the part_no values . So what could be the problem.Please help
skd
|
|
|
|
|
in the stored procedure (sql server 2000) can i check whether the value is a string or it is a decimal.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Welcome back Sonia
One suggestion:
You can use the IsNumeric function to check if it is a number.
You can then use CharIndex to find if there is a '.' in the number.
You always pass failure on the way to success.
|
|
|
|
|
SQL Server:
Is there a way to extract a value/result when you exec a sql string?
DECLARE @PaperCost money<br />
declare @PaperCategories nvarchar(30)<br />
set @PaperCategories = '3,7'<br />
<br />
DECLARE @sqlcmd varchar(255)<br />
<br />
select @sqlcmd = ' SELECT sum(amounts) FROM tbl_CostInfo<br />
where CatID in (' + RTRIM(@PaperCategories) + ') '<br />
<br />
SELECT @PaperCost = exec( @sqlcmd )
This sql block will reside inside of a stored proc, and the idea is to have a string passed in for the category values ('3,7' in this example) and return the corresponding cost.
However, sql doesn't like the last line. Is there a way to extract the resulting value from an exec command?
|
|
|
|
|
Why not pass the categories as XML, use OpenXML to turn them into data, and do a normal query ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You could try:
create table #temp1 (
SumAmounts money
)
set @SQL = 'SELECT sum(amounts) FROM tbl_CostInfo .....'
exec(@SQL)
select @PaperCost = SumAmounts from #temp1
drop table #temp1 However, a better way of acheiving this would be to use a UDF to turn your @PaperCategories string into a list of values. The resulting SQL would look a bit like:
select @PaperCost = SUM(Amounts) from tbl_CostInfo
where CatId in (
select Element from dbo.udf_split(@PaperCategories))You should be able to find the source code for a similar UDF using google.
Regards
Andy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
