|
i am using sQL 2000 whtat I want to do is that I want ot create a trigger sot hat every time I import a text file and on every insert it checks for
a. if the imported data has hyphen '-' sign in it
b. If the imported data has correct date
c. if the imported data has values which exists in another table
I have tried it with using Instead of triggers , but I am stuck with the logic that how can I check for all the abovementioed cases before inserting the data
please help me
imran
|
|
|
|
|
a and b can be done using triggers, but I try to stay away from having triggers doing any kind of 'select' which is what I read c to be. Unless you are talking about referential integrity in which case you don't have to do anything about it in a trigger.
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]
|
|
|
|
|
CREATE TRIGGER tgrImporting ON [dbo].[ImportedData]
FOR INSERT
AS
if (exists(select id from inserted where charindex('-',inserted.importedcol)>0))
begin
print 'found hyphen'
end
if (exists(select id from inserted where importeddate = getdate()))
begin
print 'found date'
end
if (exists(select id from inserted where importedcol in(select checkfield from checktable)))
begin
print 'found values in other table'
end
You didn't really mention what you want to do if any of the a,b,c conditions are met...so I just put print statements in the example.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks mate it worked
atleast upto the printing atleast ...
now what if i want to roll back the transaction if it finds any of the errors ..
and i want to completely fail the transaction and roll back to the original state in which the table was ... and if the checks pass than commit transaction
and i actually want to check if the DATE Is in valid format... like isdate() ,, if it passes then commit transaction , if it doesnt then fail the transaction
please bear with me i am just a newbie
sorry to bother u again
or replace them with the appropriate value
thanks again .. u solved my problem
cheers
imran
|
|
|
|
|
thanks mate it worked
atleast upto the printing atleast ...
now what if i want to roll back the transaction if it finds any of the errors ..
and i want to completely fail the transaction and roll back to the original state in which the table was ... and if the checks pass than commit transaction
and i actually want to check if the DATE Is in valid format... like isdate() ,, if it passes then commit transaction , if it doesnt then fail the transaction
please bear with me i am just a newbie
sorry to bother u again
or replace them with the appropriate value
thanks again .. u solved my problem
cheers
imran
|
|
|
|
|
Imran
If you extend this to work with the INSTEAD OF trigger that I mentioned in an earlier post, then you have the solution.
So, insert into the view.
Do your checks in the trigger, and if the data doesn't fail your tests, insert it into the table.
If the data does fail the tests, do nothing (or write to a log - whatever you want to do with the failed data).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hey everyone! I have been scouring the internet for help on an SQL query i need to run and have been trying for a day or two now to no avail! Can any SQL heroes please help me???
My problem seems simple but may not be so! In this example i have two tables, with their respective columns beside in brackets!
1. SERVERS (ID, NAME, LOCATION, OS)
2. SERVERS_VM (PHYSICAL_ID, VIRTUAL_ID)
The columns ID, PHYSICAL_ID and VIRTUAL_ID are all primary keys!
Now consider that the SERVERS table contains lots of servers both physical and virtual each with their own unique primary key labelled ID. While the SERVERS_VM table contains a list of all physical servers that have virtual servers installed on them! So each PHYSICAL_ID in SERVERS_VM might have one or more VIRTUAL_ID's associated with it.
Below i will try to give an example;
SERVERS
-------------------------------------------
ID Name Location o/s
-------------------------------------------
1 Bob12 BobOffice Win Server
23 Ash14 AshOffice Virtual
7 Hank89 Hankoffice Win Server
14 Tom98 Tomoffice Virtual
8 Mark45 Markoffice Win Server
93 Bar123 Baroffice Virtual
SERVERS_VM
---------------------------
PHYSICAL_ID VIRTUAL_ID
---------------------------
7 23
8 14
7 93
Ok so, now not trying to confuse people, but if we see above, Ash14 tom98 and bar123 are virtual servers (os=virtual) located on physical servers (shown in servers_vm)
From SERVERS_VM we can see that server number 7 houses two virtual servers given by their VIRTUAL_ID which is the same as the ID i the SERVERS table! (23 & 93)
So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??
The results should be something like;
---------------------------------------------
ID NAME VIRTUAL_ID
---------------------------------------------
7 Hank89 23
7 Hank89 93
8 Mark45 14
the problem is the fact that there are two primary keys in the SERVERS_VM table, well even though the PHYSICAL_ID repeats in the SERVERS_VM table
this is what i have so far;
SELECT PHYSICAL_ID, VIRTUAL_ID FROM (SELECT ID, NAME FROM SERVERS WHERE OS like '%Virtual%') WHERE SERVERS.ID=SERVERS_VM.VIRTUAL_ID
i have gone to much more depth but dont want to post it up because it is really messy.
Can anyone please Help??????
|
|
|
|
|
I can give you the answer but it sounds like a homework kind of question. So, in my attempt to help you but not give you the answer, here is the magic hint, use Group By, Having and count(*). 
Farhan Noor Qureshi
|
|
|
|
|
Thanks Sir,
Actually im new in a Job and am learnin SQL on my own, so my knowledge of these different commands is limited. Thanks though, anymore hints would be great!
|
|
|
|
|
bazpaul wrote: So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??
select * from servers where id in(select physical_id from servers_vm)
But, the sql to answer your question is different from the results you wanted. To get this:
---------------------------------------------<br />
ID NAME VIRTUAL_ID<br />
---------------------------------------------<br />
7 Hank89 23<br />
7 Hank89 93<br />
8 Mark45 14
You would use something like the following sql:
select s.ID,s.name,p.virtual_id from servers s inner join servers_vm p on s.id = p.physical_id
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want to use a Gridview for a standard CRUD app.
So far I have my delete event wired up but I have a problem with my stored proc. I receive this error everytime i run my debugger and click the delete event. I have a catch event to catch this error.
The SqlParameterCollection only accepts non-null SqlParameter type objects, not Parameter objects.
Here is the code for my deleterow event. Any help is appreciated.
protected void DeleteRow(object sender, GridViewDeleteEventArgs e)
{
try
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["PokerTraxConnectionString"].ToString();
connection.Open();
SqlCommand delCommand = new SqlCommand();
delCommand.Connection = connection;
delCommand.CommandText = "usp_Delete_Loan";
delCommand.CommandType = CommandType.StoredProcedure;
delCommand.Parameters.Add(new Parameter("LoanId"));
delCommand.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(delCommand);
DataSet ds = new DataSet();
GridView1.DataSource = ds;
adapter.Fill(ds);
Cache["Loan"] = ds;
BindPlayerNames();
connection.Close();
connection.Dispose();
delCommand.Dispose();
}
catch (Exception ex)
{
errorMessage.Text = ex.Message;
}
}
|
|
|
|
|
This could be that the parameter was not created completey. In code that I have used I set other values for parameter object.
Generic stored proc calling code:
Dim command As New System.Data.SqlClient.SqlCommand
command.Connection = m_sqlConnection
command.CommandText = strStoredProc
command.CommandType = CommandType.StoredProcedure
' declare the parameter object
Dim param As System.Data.SqlClient.SqlParameter
Dim myEnumerator As System.Collections.IEnumerator = _
parameterArray.GetEnumerator()
Dim pItem As ParameterItem
While myEnumerator.MoveNext()
pItem = CType(myEnumerator.Current, ParameterItem)
' Add a new parameter, get back a reference to it
param = command.Parameters.Add(pItem.Name, pItem.Type)
' set the parameter's direction and value
param.Direction = pItem.Direction
param.Value = pItem.Value
End While
Dim myDataAdapter As New SqlClient.SqlDataAdapter
command.CommandTimeout = iTimeOut
myDataAdapter.SelectCommand = command
myDataAdapter.Fill(ds, "ResultSet")
|
|
|
|
|
Perhaps I should have mentioned I wrote this in C#.net. Thanks for the help though I can kindve determine how this is supposed to work.
|
|
|
|
|
Dear sir,
i have created a form with 3 textbox controls and a button called transfer
I have a table named CustId with the following records:
CID BAL
1 30000
2 50000
Here when i enter any amount (i.e, 3000) in TextBox1 and
enter the CID number(2) in the TextBox3 and click on the transfer button
The record bearing CID as 2 should be updated (the balance in this case will be 50000-3000=47000)
similarly,
if i enter any amount (i.e, 2000) in TextBox2 and
enter the CID number(1) in the TextBox3 and click on the transfer button
The record bearing CID as 1 should be updated (the balance in this case will be 30000-2000=32000)
i have already written the coding for that . But when i try to execute it
it gives a error , that is
"Line 1 Incorrect sytax near = "
Pl see the code below
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace data1
{
///
/// Summary description for Form1.
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.TextBox textBox3;
private System.Windows.Forms.Button button1;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
SqlConnection cn;
SqlCommand dbcm,crcm;
string dbcmstr,crcmstr;
int i1,i2;
SqlTransaction tr;
private void button1_Click(object sender, System.EventArgs e)
{
try
{
cn = new SqlConnection("user id = sa;initial catalog=pubs");
dbcmstr = string.Format("update custdb set bal=bal-{0}where cid={1}",textBox3.Text,textBox1.Text);
crcmstr = string.Format("update custdb set bal=bal+{0}where cid={1}",textBox3.Text,textBox2.Text);
dbcm=new SqlCommand(dbcmstr,cn);
crcm=new SqlCommand(crcmstr,cn);
cn.Open();
tr=cn.BeginTransaction();
dbcm.Transaction=tr;
i1=dbcm.ExecuteNonQuery();
crcm.Transaction=tr;
i2=crcm.ExecuteNonQuery();
if(i1==1 && i2==1)
{
tr.Commit();
MessageBox.Show("sucsesfull");
}
else
{
tr.Rollback();
MessageBox.Show("Failure");
}
cn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
}
}
pl help me out with this
Thank u very much
arun
|
|
|
|
|
Arun Hegde wrote: TextBox1
Arun Hegde wrote: TextBox3
You might want to give more meaningful names to your text boxes. Your code is not maintainable.
dbcmstr = string.Format("update custdb set bal=bal-{0}where cid={1}",textBox3.Text,textBox1.Text);
There is no space betweeen the injected value at {0} and where so the SQL statement will expand to:
update custdb set bal=bal-3000where cid=2
I strongly suggest you look into the subject of SQL Injection Attacks because your code is highly susceptable. SQL Injection Attacks and Some Tips on How to Prevent Them[^]
|
|
|
|
|
If somebody have the form or code pls.send me because I am new in this.
|
|
|
|
|
lopa12 wrote: have the form or code
Sorry, don't have any. Shouldn't be that difficult to do. Use some creativity 
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
Hello there, I'm currently creating an application that will work as a workout journal. One part of this will be reporting your exercices. I will have a list of avaliable exercices which the user can choose one from and fill in the necessary information.
Now, at first I thought it was easy with just two tables. One for each filled in exercice connected to a user and one for all avaliable exercices. However the problem is that while all exercices have a name they differ in other variables. Pushups for example are just repetitions, benchpress is repetition and weight, running is distance and length. Etc etc. How would I solve this in the best way?
Having one table with all avaliable exercices and variables and just having NULL when the field is supposed to be empty? Splitting them up to different tables might be difficult as I would like to add new exercices without having to remake the database if I happened to miss one combination.
Thank you in advance and hope you understood my rambling.
|
|
|
|
|
Christer Claesson wrote: Having one table with all avaliable exercices and variables and just having NULL when the field is supposed to be empty? Splitting them up to different tables might be difficult as I would like to add new exercices without having to remake the database if I happened to miss one combination.
Splitting the information into tables is probably the best way - one table detailing all the variables, one table containg the excercises and another table with the link between the two (one to many relationship).
Hope this helps
Chandra Ram
|
|
|
|
|
Samuel G wrote: trying to connect to mysql database on my hosting account godaddy
Do they allow you to connect remotely to the database?
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
No they don't,
but it is a test page that I have uploaded to my hosting server
Samuelg
-- modified at 2:22 Wednesday 22nd November, 2006
|
|
|
|
|
Samuel G wrote: it is a test page that I have uploaded to my hosting server
Might be an issue on their part. Have you tried contacting their support people?
|
|
|
|
|
I manage to get it right so godaddy does support mysql for asp.net
my connection string was wrong I'v used there IP Address not there server name
ooops my mistake!!
SamuelG
|
|
|
|
|
 The OLE DB provider "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OLEDB\Microsoft.Jet.OL
EDB.4.0" has not been registered.
I've tried this also,
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\viswanatha_c\Desktop\Book1.xls',
NULL,
'Excel 5.0'
Error:
User does not have permission to perform this action.
srinivasintouch
|
|
|
|
|
srinivasintouch wrote: The OLE DB provider "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OLEDB\Microsoft.Jet.OL
EDB.4.0" has not been registered.
Have you fixed this problem?
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
