Closing a connection opened by ADODC - Database Discussion Boards

Illegal Operation15-Aug-05 20:31

15-Aug-05 20:31

I have configured the table with the unique constraints but now I want to catch the error generated by SQL and then display or give it a meaningfull way to handle.

In other words, when the error occurs in SQL, I need to redirect the user to another page.

Illegal Operation

Making Computer Software Talk

Re: SQL Error Handling...

ToddHileHoffer16-Aug-05 10:26

ToddHileHoffer

16-Aug-05 10:26

Declare @intErrorCode int<br />
select @intErrorCode = @@Error<br />
<br />
begin transaction<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert another SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
<br />
IF @intErrorCode = 0<br />
commit transaction<br />
else<br />
rollback transaction<br />
return @intErrorCode

To handel the error in your page use the

Try <br />
Cmd.ExecuteNonQuery<br />
catch ex as sqlclient.sqlexception<br />
'Put code to redirect<br />
End Try

"People who never make mistakes, never do anything."

My blog
http://toddsnotsoamazinglife.blogspot.com/

primary key performance

ppp00114-Aug-05 20:33

ppp001

14-Aug-05 20:33

Hi,

In a relational database, I would to like to know the performance difference between of using a string and an integer as the primary key field for the tabl;e. Will a column field of integer be indexed faster than a string column field ?

Thanks

Re: primary key performance

Colin Angus Mackay14-Aug-05 23:09

Colin Angus Mackay

14-Aug-05 23:09

CraigBurton wrote:
Will a column field of integer be indexed faster than a string column field ?

Yes, simply because the key will be smaller. The smaller the key the faster it can be indexed and retrieved because the index will be smaller overall and it won't have to pull so much data off the disk in order to scan the index.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: primary key performance

ppp00114-Aug-05 23:29

ppp001

14-Aug-05 23:29

Thanks

Besides integer is smaller than string, will implicitly
the indexing of integer is faster than string, if they are of
similar length ?

How about for the retrievel after indexing, should be same ?

Re: primary key performance

Colin Angus Mackay14-Aug-05 23:39

Colin Angus Mackay

14-Aug-05 23:39

If you use a CHAR or NCHAR then I doubt there will be much difference. If you use a VARCHAR or NVARCHAR then there is additional complexity due to the variable lengths possible.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

no item error

Anonymous13-Aug-05 23:37

Anonymous

13-Aug-05 23:37

when i expand the enteprize manager i get the no item error .i increase any time in the option but no advantage... .! i am not the owner of the database .is this is the reason of that error.

Closing a connection opened by ADODC

Maitre Capelo13-Aug-05 2:14

Maitre Capelo

13-Aug-05 2:14

Hi,

I have written a VB6 program that uses a datagrid bound to a ADO data control. I do the following:

Adodc1.ConnectionString = "DSN=" & m_objSentryDB.DSN & ";uid=" & strUserID & ";pwd=" & strPassword
Adodc1.RecordSource = strSQL

Set DataGrid1.DataSource = Adodc1

This is working fine, however, when I want to close the program, how do i CLOSE the connection opened by the ADODC control? I am using a sybase database, and they is basically one ghost connection on the server remaining after each use of the program. I looked for a 'close' method on the ADODC object, but to no avail.

Also, what is the correct way to refresh the grid after the underlying table has changed? I use adocdc1.refresh, but this seams to knock out the display properties of the grid (size, caption etc).

Any ideas?

thanks

Maitre Capelo

ado.net and sql

Micu Valentin12-Aug-05 10:54

Micu Valentin

12-Aug-05 10:54

please anybody knows in c# how can I add new records in joined tables?

Re: ado.net and sql

Colin Angus Mackay14-Aug-05 23:10

Colin Angus Mackay

14-Aug-05 23:10

Micu Valentin wrote:
how can I add new records in joined tables?

You add a row to the parent table first, then add the rows to the child table. The child table is the table with the foreign key to the parent table.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: ado.net and sql

Micu Valentin14-Aug-05 23:25

Micu Valentin

14-Aug-05 23:25

i was talking how to do it by code in c#.
can you help me with an example?
i have 1 table: person_id, person_name
second table: number_id, person_id, telephone no:
the relashion is 1 to many.
how can i add a record that the person_id to be in both tables automatically like in microsoft access?
do i have to use the DataRelation?
waiting

Re: ado.net and sql

Colin Angus Mackay14-Aug-05 23:36

Colin Angus Mackay

14-Aug-05 23:36

I was giving a higher lever overview because you insert/update it as two single tables rather than one joined table. I assumed you already knew how to do that.

Here is a simple example:

// First insert the parent row
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection; // Replace this with your connection object
cmd.CommandText = "INSERT table1(person_id, person_name) VALUES (@person_id, @person_name)";
cmd.Parameters.Add("@person_id", 1);
cmd.Parameters.Add("@person_name", "Joe Bloggs");
cmd.ExecuteNonQuery();

// Now insert the child row
cmd = new SqlCommand();
cmd.Connection = myConnection; // Replace this with your connection object
cmd.CommandText = "INSERT table2(number_id, person_id, telephone_no) VALUES (@number_id, @person_id, @telephone_no)";
cmd.Parameters.Add("@number_id", 10);
cmd.Parameters.Add("@person_id", 1);
cmd.Parameters.Add("@telephone_no", "+44 131 332 4455");
cmd.ExecuteNonQuery();

Does this help?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: ado.net and sql

Micu Valentin15-Aug-05 4:32

Micu Valentin

15-Aug-05 4:32

yes this does help but i wanted to treat them as already in the relationship.
imagine a microsoft access form: in this case I would add only the person_name and telephone numbers without adding the person_id from the child table manualy.in this case it would be copied in the second table automatically due to the relationship.
I want to create an update form like in access.
do you have any ideea?
thanks very much for your fast help.
ex:

person_id 1 (autoseed-i don't have to insert)
person_name micu valentin

number_id 1(autoseed-i don't have to insert)
person_id (to update i have to copy this from the form-this is my problem-he must be copied from the parent table)number 123455

Re: ado.net and sql

Colin Angus Mackay15-Aug-05 4:59

Colin Angus Mackay

15-Aug-05 4:59

You say "link in Access" which suggests you are not using Access. You don't say what you are using so I'll assume SQL Server 2000 (which is the most common database in use on this forum)

You change the SqlCommand.CommandText on the first INSERT to something like this:

INSERT table1 (person_name) VALUES(@person_name); SELECT @@IDENTITY;

And instead of using ExecuteNonQuery() use this:

int personID = cmd.ExecuteScalar()

(You can obviously delete the line that adds the parameter for the person_id.

In the second command, you use the personID you got from the first query for the @person_id parameter.

Does this help?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Record Locking/Updating Datasets

eatwork12-Aug-05 4:59

eatwork

12-Aug-05 4:59

Hi, my question is in regards to record locking and updating datasets in a multiuser environment. We have a database that is used to track items in inventory. This windows application is multiuser and accesses a Microsoft access database. We do not want to use databinding for future maintenance and have been using datasets to populate our user interface. The problem we have noticed is that when two users open the same customer at the same time, one user can delete or modify the data and the other user will not be able to see the updated changes on their application. Is there any way to solve this issue? Thank you

Re: Record Locking/Updating Datasets

ToddHileHoffer12-Aug-05 6:30

ToddHileHoffer

12-Aug-05 6:30

I had a similar issue in an asp.net page. Two users would load the same dataset and whoever saved last would update the database. If the data is not connected there is nothing you can do about it. What I did was implement a save feature that required the user to save the data every two minute. This minimized the problem.

"People who never make mistakes, never do anything."

My blog
http://toddsnotsoamazinglife.blogspot.com/

Re: Record Locking/Updating Datasets

Colin Angus Mackay14-Aug-05 23:18

Colin Angus Mackay

14-Aug-05 23:18

ToddHileHoffer wrote:
What I did was implement a save feature that required the user to save the data every two minute. This minimized the problem.

The solution is that when the user saves the information you check what the user was originally presented with with the current contents of the database, if there is no change then the save operation completes. If there is a change between what is currently in the database with what was in the database then the user is informed of the differences and is offered choices to overwrite, merge or cancel their save operation.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Hiding data in SQL Server/MSDE

D2Necro12-Aug-05 1:24

D2Necro

12-Aug-05 1:24

Hello!

First I have to say that I am a beginner - until some weeks I only had basic SQL knowledge.
Now I am working with the SQL server and I learned a lot about ADO.NET.
My question now is:
How can I hide the data from the customer? Smile | :)

The problem is that we are developing an application and it may be that that the customer would try to manipulate the data if he has the tools to do that.
But we want him only to use our application to work with the data!
So do I have to use the user administration or is there a better strategy how to solve this problem?

Thanks for any help

Re: Hiding data in SQL Server/MSDE

Colin Angus Mackay12-Aug-05 2:40

Colin Angus Mackay

12-Aug-05 2:40

Simple: Don't grant them the rights to manipulate the data. Ensure they log on using a restricted account that only allows them access to the functionality that they need. Your application should use an specific account that grants the application only access to the parts of the database it needs.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Hiding data in SQL Server/MSDE [additional]

Colin Angus Mackay12-Aug-05 2:43

Colin Angus Mackay

12-Aug-05 2:43

As an addition to my first answer. Why are you hiding data from the customer? It is their data. They own it. Why hide it?! If I was your customer and I found out you were hiding MY data from ME I'd be pretty angry.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Hiding data in SQL Server/MSDE [additional]

D2Necro12-Aug-05 3:12

D2Necro

12-Aug-05 3:12

Well, maybe 'hiding' is not the right word. Of course it's the customers data but I want him to use our application to work on it.
If he knows only a little about databases or doesn't understand the concept of the DB he could add redundant records or corrupt the integrity. So I want to be sure that he doesn't come up with the idea "Let's try some UPDATE... or DELETE... statements here."
Otherwise we could get some support calls because of errors in the integrity although it was the customers fault.

Re: Hiding data in SQL Server/MSDE [additional]

Colin Angus Mackay12-Aug-05 3:26

Colin Angus Mackay

12-Aug-05 3:26

If the customer is accessing the database with an account that has db_owner or sysadmin rights then there isn't much you can do. If they are using a restricted account then you can obviously block access to UPDATE, INSERT and DELETE operations.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Hiding data in SQL Server/MSDE

Vasudevan Deepak Kumar15-Aug-05 0:32

Vasudevan Deepak Kumar

15-Aug-05 0:32

D2Necro,

As discussed in other threads, you may not consider hiding data from Customer. After all, it is his data. May be for auditing or other purposes, he may need to have his admin team to review it.

However you may like to protect the data from being inadvertantly modified in a format which may be rendered uncomprehendable for the client or may make the data unusable.

For these reasons, you may encrypt the data using standard keys and use standard stored procedures to access and manipulate the same.

Did this attend to your query?

Vasudevan Deepak Kumar
Personal Web: http://www.lavanyadeepak.tk/
I Blog At: http://deepak.blogdrive.com/

restore database a from f:\x.mdb do you have any adiea

Anonymous12-Aug-05 1:08

Anonymous

12-Aug-05 1:08

hi my dear,
I tried tried to restore the data base from enteprize manager but the permission error apeared.the manager of the host said me you have permission but yuo should restore tnhe database by using restore command from sql query analyser ?is him advice is true?if yes how can i do this,what command should i use?in the below x is the databse that i want to restore and a is the new name ?in query analyser what database must i select in change database?
restore database a from f:\x.mdb

Re: restore database a from f:\x.mdb do you have any adiea

ToddHileHoffer12-Aug-05 6:32

ToddHileHoffer

12-Aug-05 6:32

You can't restore an mdb file. Use the help index in query analyzer to find the sytax for sp_attach_single_file_db

"People who never make mistakes, never do anything."

My blog
http://toddsnotsoamazinglife.blogspot.com/

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.