|
First thing that comes in mind is that you catch the error using WHEN and the print out the values for example using DBMS_OUTPUT package.
|
|
|
|
|
Hi All,
I am creating an application in vb.net 2008 and Sql server 2005
I want to create a shared folder in the Server system, Than can be acceasable from all the Other System.
And This file using all the User Access Rights.
How can I create the Access Rights
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
Do not post the same question in multiple forums. This question is not an appropriate topic for this forum, and will be ignored.
|
|
|
|
|
Hello,
I'm missing something very basic with databinding to a dataset. I have a dataGridView working and updating from the UI correctly. The dataGridView also populates itself correctly from the SQL database. However, changes made in the grid persist in the grid but are not saved back to the database even on the button click. Here is the entire code. The only thing I do in a new project is add a dataGridView and a button in the designer. Then add this code to the Form1.cs file.
public partial class Form1:Form
{
private BindingSource BindingSource1 = new BindingSource();
SqlDataAdapter dataAdapterFinca = null;
DataSet dataSetFinca = null;
public Form1()
{
InitializeComponent();
this.Load += new System.EventHandler(Form1_Load);
}
protected void Form1_Load(object sender,System.EventArgs e)
{
dataGridView1.DataSource = BindingSource1;
GetData();
}
protected void GetData()
{
SqlConnection connection = new SqlConnection("MyConnectionString");
dataSetFinca = new DataSet();
dataSetFinca.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapterFinca = new SqlDataAdapter("select * from Finca",connection);
dataAdapterFinca.Fill(dataSetFinca,"Finca");
BindingSource1.DataSource = dataSetFinca;
BindingSource1.DataMember = "Finca";
}
private void button1_Click(object sender,EventArgs e)
{
if( dataSetFinca.HasChanges() )
{
this.Validate();
this.BindingSource1.EndEdit();
this.Update();
dataSetFinca.AcceptChanges();
dataAdapterFinca.Update( dataSetFinca, "Finca" );
}
}
}
|
|
|
|
|
Since you are calling dataset.AcceptChanges () before DataAdapter.Update (), so the changes are lost. Try this: (sequence of the two statements reversed)
dataAdapterFinca.Update( dataSetFinca, "Finca" );
dataSetFinca.AcceptChanges();
By the way, I also think that DataAdapter.Update () itself calls DataSet.AcceptChanges () after committing the updates, in which case the last statement will not have any effect and you can safely remove it after confirming.
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Thanks Mehroz,
I knew it was something stupid but damn did it take a long time. I kept trying not-enough or too-much. Sure would've helped to have a basic example from MS, but I may as well howl at the moon.
Now I've got the UpdateCommand to deal with. It seems that many people say the way to deal is to implement an UpdateCommand manually in SQL. That seems like it would defeat the purpose...every time I change the database, I'll have to get the changes correct in the SQL and won't know if I got it correct until runtime. Any recommendation?
In any case, thanks for stepping in and keeping some sanity here in Costa Rica.
Brice
|
|
|
|
|
can anyone help mi write a code in sql for a store{supermarket}
|
|
|
|
|
|
I've never seen this before. I'm working on a database where the AutoNumber/Identity ID column is being reset when the given table has no data in it and the Sybase Server (SQL Anywhere v7 to be exact) is restarted.
Anyone have any advise as to why this is happening and how it can be corrected?
Thank you in advance and happy holidays to all.
|
|
|
|
|
Are you doing TRUNCATE on the table? That could be one reason.
"If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, TRUNCATE TABLE resets the next available value for the column"
Refer to: TRUNCATE TABLE statement [^]
|
|
|
|
|
Hi all,
I am Using Sql reporting Services.In that how to format date to dd/mm/yyyy ??
I have given format expression as dd/mm/yyy in properties .but it displays the mm/dd/yyyy format
pls give any solution
modified on Tuesday, December 23, 2008 8:17 AM
|
|
|
|
|
Format expression: dd/MM/yyyy should work. Are you sure that the field data type is datetime and not an string .
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
|
Hi all..
To keep it short - We automatically update a set of tables (lets call it the DATA tables) in a client's database by dumping the raw data into a table (DUMP table) which has in INSERT trigger on it, which will then go and update the various data tables. I'm extending this functionality by adding a SETTING table which will allow clients to specify which fields they want updated. The SETTING table has two fields, an ID for each field in the DATA tables, and a setting field, which can be 0 for never update, 1 for always update, and 2 for only update if the corresponding field is null in the data table.
Some people might complain that this is horrible design - There are reasons for it.
My problem lies with optimization of this query. I get 30000 rows (bout 30-40 columns) at a time in the dump table and the trigger runs for about 40 minutes if it updates blindly, but now I need two extra SELECTs per field - first to check the update setting, then to check if the value is NULL should the setting be 2. So this comes down to more or less (ignore syntax trivialities):
<br />
SELECT @setting = UpdateSetting FROM Setting WHERE ID = @fieldid<br />
IF @setting = 1 OR (@setting = 2 AND IS NULL (SELECT field FROM Data WHERE id = @rowID)<br />
UPDATE Data SET Field = @newfieldval WHERE ID = @rowID<br />
Obviously this query will take AGES to complete, compared to the previous:
<br />
UPDATE Data SET field1 = @newField1Val, <br />
field2 = @newField2Val,<br />
[etc]<br />
...<br />
WHERE id = @rowID<br />
How should I go about optimising this? I am still somewhat novice when it comes to SQL. So far I'm considering building up an update string per row, which should at least cut down the number of updates significantly, but the number of extra selects are still a problem. Ideas? Clarifications needed?
Thanks in advance.
|
|
|
|
|
Would you consider taking the logic that builds up your queries out to a service at the client? This way the service could build up your required updates and then fire them at your database, not taking up large amounts of DB resource, or locking tables.
IMHO it would also be somewhat easier to maintain, as the updates you build up here are going to be quite large? Could make it somewhat more readable...
I know that's not what you're asking for, but i think you'll find it difficult to optimise your update by too much.
|
|
|
|
|
You could try combining the logic to the update. Something like (don't mind the typos):
UPDATE Data SET Field = @newfieldval
WHERE ID = @rowID
AND ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
OR ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
AND IS NULL (SELECT field FROM Data WHERE id = @rowID))) However, few considerations:
- where does the rowid come from, could you eliminate that variable
- as I understood that this is from inside the trigger, what happens if several rows are updated
If you're using SQL Server, I would combine the previous statement with inserted (and perhaps deleted ) table table if possible. That would help to make the whole update process a single batch.
Also don't forget about good indexing.
Hope it helps,
Mika
|
|
|
|
|
I have to write a insert/update command with in a case. IS it possible?
for example
select case(select count(*) from table1 a where a.no=b.no and a.act=b.act)
when 0 then
insert into temp(name,desc,status) values(b.name,b.desc,1)
when 1 then
update temp set name=b.name,desc = b.desc,status=2 where id=b.id
else
insert into temp(name,desc,status) values(b.name,b.desc,3)
end from table2 b
But as case statement accepts only expressions can some one let me know how the above can be modified
|
|
|
|
|
You cannot combine insert and update statements, so you have to execute two separate commands. However you can execute them in a single batch (or write a procedure to wrap them).
|
|
|
|
|
Hi all,
Hope you are doing well. My question is based from MS SQL XSD registering. I know that I can create the schema and register it to use for incoming xml parameters and such. The question is, what if that XSD had references to other XSD files with the "include" attribute? So if I use include to bring in two other xsd files to reference in that one, is MS SQL going to keep up and know that they are in the same namespace? Or would it just crash at parse time because of some missing link?
If you have tried this before please post your experience back, thanks.
|
|
|
|
|
Hello,
Can anyone recommend a nice book for learning Sql Server Integration Services?
Thank you.
modified on Monday, December 22, 2008 9:57 AM
|
|
|
|
|
givi_s wrote: Can anyone recommend a nice book for learning Sql Server Integration Services?
Is it for Lounge.... I think you are in wrong place
-------
Any way you can Read This[^]
|
|
|
|
|
Wow that'll teach you to redirect someone to the lounge, instant 4 univotes.
Actually here is also appropriate for this sort of enquiry.
Do you recommend the book or is it just the first one you found?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using OleDb to write into Access Database. In program, can we write any null value in a column in Access Database?
Thanks.
|
|
|
|
|
hung1984 wrote: In program, can we write any null value in a column in Access Database
No, only System.DBNull .
|
|
|
|
|
For example, is this line allowed?
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20).Value = "";
|
|
|
|