Database

Re: MS Access Database Connection with VB.NET

8-Nov-03 16:27

Edza,

not sure, as I'd do it differently in C# ( using:
OleDbConnection = new OleDBConnection(StrConnectionString)

isntead, but have you tried leaving out the "..\", so you get:

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='..\PIB Hiring System.mdb'"

Alternatively, drop the "\", and put the MDB in the RELEASE or DEBUG folder (depending obviously on which you are using).

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='PIB Hiring System.mdb'"

regards,
Pauwl

Pauwl

Mike Dimmick11-Nov-03 10:24

Mike Dimmick

11-Nov-03 10:24

I don't think it is related to the database. You're getting a SecurityException.

You're probably trying to run this code from the network. Currently, the database access code requires Full Trust before it will run, which will only occur if running from the local machine.

Copy your project to a local hard disk and try again.

Re: MS Access Database Connection with VB.NET

Member 59242011-Nov-03 19:20

Member 592420

11-Nov-03 19:20

What you are saying is 100 percent correct, because whenever I open my VB.NET framewok it gives me this kind of an error:

"The project location is not fully trusted by the VB.NET runtime. This is either it is a network share or mapped to a network share not on the local machine. If the output path is under the project location your code will not execute as fully trusted and you may receive unexpected security exceptions. Click Ok to ignore and continue. Click cancel to choose a different project location."

It runs like a dream from my local disk, just as you said.

Thank you Mike, I never thought this error was the cause of all my nightmares. Thank you again mike, you saved 90% of my time.

Converting from SQL server to MS Access

pankajdaga8-Nov-03 12:05

pankajdaga

8-Nov-03 12:05

Hi all,

I know most people want it the other way around.

The application I am developing uses the data that is on an SQL server. However, most of my clients are mobile and do not always have access to that server from outside for security reasons. Basically, what I want to do is have them go to my ASP.NET page and have the option to have the database exported as an access database so that they can use it when they are mobile. Most of them do not have an SQL server installation on their laptops and will never have it. The company provides them with the laptops and they do not have the rights to install anything. I would go for MSDE, but there is no way they will agree to this. It is all too damn beaureucratic! So MS Access is the only option.

So, my question is that is it possible to convert a SQL server database to Access (only the tables and constraints..no views or stored procedure). Is it possible to do it through some custom stored procedure. Any help would be greatly appreciated.

Sincerely,
pankaj

Without struggle, there is no progress

ExecuteScalar with COUNT causes Exception?

Pauwll8-Nov-03 1:21

8-Nov-03 1:21

I'm trying to do the following:

SELECT COUNT(*) FROM table WHERE xyz="a"

and am using ExecuteScalar to do it. This works like a dream for all results >0.
When the resulting set has ZERO entries, an exception is thrown. I would have expected the result from ExecuteScalar to just=0. Instead, the Exception is thrown.

I have 2 problems with this:
1) I don't understand why an exception is being thrown.
2) I can't catch the exact exception (as listed in the documentation), so am just catching them all, but doing nothing smart with it. If anything else goes wrong- I'll never know..

Any ideas what I'm doing wrong?

Pauwl

jeff_martin11-Nov-03 11:54

11-Nov-03 11:54

Can you post some code? I tried to recreate this and had no problem w/ ExecuteScalar returning 0.

Pauwll12-Nov-03 6:00

12-Nov-03 6:00

jeff_martin wrote:
Can you post some code?

Jeff, here it is.
I'm connecting to an MDB. Other connections (SELECT and UPDATE) all work fine.
The funny thing is, when I run it in MS-Access, it comes up with no COUNT row.
If I run it for some other SITENAME, it works fine...

If I do it without the TRY/CATCH, it will bomb out when there should be ZERO as a result.
NOTE: There are NO records in the table that match SITENAME='OFFICE'

================code start====================
Int32 founditems=0;
OleDbConnection conn = new OleDbConnection(s_db_connectionstring);

string counter = "SELECT Count(*) FROM Servers WHERE ((Sitename)='OFFICE')) GROUP BY Servername,SiteName ORDER BY Servername,Sitename;";

OleDbCommand cmd = new OleDbCommand(counter,conn);

try
{
founditems = (Int32) cmd.ExecuteScalar();
}
catch(System.Exception e1)
{
founditems=0;
}

================code end====================

Pauwl

jeff_martin12-Nov-03 7:25

12-Nov-03 7:25

The code looks fine, what is the error message when it bombs out? Perhaps this is a problem with Access? I tested on SQL Server and had no problem. Can you tell us the error message? Maybe that will help.

Pauwll12-Nov-03 16:27

12-Nov-03 16:27

jeff_martin wrote:
The code looks fine, what is the error message when it bombs out? Perhaps this is a problem with Access? I tested on SQL Server and had no problem. Can you tell us the error message? Maybe that will help.

Here it is:
++++++++++
An unhandled exception of type 'System.NullReferenceException' occurred in Application.exe

Additional information: Object reference not set to an instance of an object.
++++++++++

Any ideas? The only way left as far as I can see is to catch this error, and set the variable to zero.

Pauwl

jeff_martin13-Nov-03 3:41

13-Nov-03 3:41

I really don't understand why you are getting that, unless it is some sort of Access problem that doesn't return 0 on counts. If you are worried about other exceptions getting through, you can have multiple catch blocks

Int32 founditems=0;
OleDbConnection conn = new OleDbConnection(s_db_connectionstring);

string counter = "SELECT Count(*) FROM Servers WHERE ((Sitename)='OFFICE')) GROUP BY Servername,SiteName ORDER BY Servername,Sitename;";

OleDbCommand cmd = new OleDbCommand(counter,conn);

try
{
founditems = (Int32) cmd.ExecuteScalar();
}
catch(System.NullReferenceException e1)
{
founditems=0;
}
catch (System.Exception e1)
{
//report other exceptions
}

Pauwll13-Nov-03 5:07

Putting fixed array into DB field

13-Nov-03 5:07

Jeff,

thanks. I'll go for that. Didn't really want to though, as I don't like using exceptions for known states, but as that seems to be the only solution...

I don't think I'll spend more time on it know, but if I do find a solution, I'll post it.

thanks again.

Pauwl

EddieSD647-Nov-03 11:31

EddieSD64

7-Nov-03 11:31

I've got a table that is going to require an array of integers in one
of its fields.

So using C# and .NET, I create a new row, which shows the field to which
I want to write is a System.Object. This is great, because
System.Array ia derived from System.Object, and it copies over nicely.

But, when I try to do the data adapter's Update() with the dataset containing the new row w/ array in field, it fails with unspecified error.

Any thoughts on how to accomplish getting an array into a database field?

Stored Procs, parameters and IN Clause

stephen woolhead7-Nov-03 5:00

stephen woolhead

7-Nov-03 5:00

This question relates to MS-SQL 2000

I would like to write a query such as..

SELECT * FROM Users WHERE id IN @UserIDs

Where @UserIDs is a list of integers passed into the stored proc.

Is this possible? The list of ID's is generated by a user, so I cant use a sub query to get the required list of ID's.

If it is possible, what type do I define the parameter as?

Thanks

Stephen

Re: Stored Procs, parameters and IN Clause

jeff_martin11-Nov-03 12:04

SQL Server does not exist or access denied

11-Nov-03 12:04

There is a way, but I don't recommend it. For one thing, you lose out on one of the key reasons for using a stored proc...the pre-compilation by SQL Server.

Here's how you do it....

create procedure dbo.usp_Test
(
@ids varchar(100)
)
as

declare @sql as varchar(255)
set @sql = 'select * from table where tableid in (' + @ids + ')'
exec(@sql)

GO

then you pass the ids in as a comma delimited string...

exec usp_Test '1000, 1001, 1010'

yyf7-Nov-03 2:50

yyf

7-Nov-03 2:50

I have a problem here. I tried to connect sqlserver as many time as I can in one function, because I want to know about the limitation of connection of sqlserver, btw, the sqlserver is not in my computer, the code is like :

		private void ExecuteCommand_ConnTest_process()<br />
		{<br />
			ArrayList conn = new ArrayList(3000);<br />
			int num = 0;<br />
<br />
			string conn_s = "...";<br />
			conn_s += "Connect Timeout=600;Max Pool Size=1000000;";<br />
<br />
			try<br />
			{<br />
				while ( true ) <br />
				{<br />
					conn.Add(new SqlConnection(conn_s));<br />
					((IDbConnection)conn[num]).Open();<br />
					num ++; <br />
					Thread.Sleep(10);<br />
				}<br />
			}<br />
			catch( InvalidOperationException ioexp )<br />
			{<br />
				StringBuilder buildError = new StringBuilder();<br />
				buildError.Append( "InvalidOperationException thrown when trying to connect "  );<br />
				buildError.Append( ", error given = " + ioexp.Message);<br />
                             MessageBox.Show(builderError.ToString());<br />
			}<br />
			catch ( Exception e ) <br />
			{<br />
				StringBuilder buildError = new StringBuilder();<br />
				buildError.Append( "Exception thrown when trying to connect "  );<br />
				buildError.Append( ", error given = " + e.Message );<br />
<br />
                             MessageBox.Show(builderError.ToString());<br />
			}<br />
			finally<br />
			{<br />
                             MessageBox.Show(builderError.ToString());<br />
			}<br />
		}

After certain number connections, such 6000, it throws an exception:
"SQL Server does not exist or access denied". Then I found I can not access Internet. I have to restart my computer. Why this happened? What's wrong with my code? Could anyone please tell me? Thanks in advance!

Problem with _Connection::Open !

nigs_krec7-Nov-03 1:53

nigs_krec

7-Nov-03 1:53

Hi all,
I am using ADOCE. My requirement is to open a connection to a database (Test.sdf) in the Pocket PC. The problem is _Connection::Open throws an exception "File not found" even though I am giving the right path. My question is, What is the exact format format in which the connection string has to be passed to the _Connection::Open() function ?
Any help would be appreciated.
Regards,
Amit

Managed .NET Embedded Database

Andrew Shapira6-Nov-03 10:11

Andrew Shapira

6-Nov-03 10:11

I'm looking for an embedded .NET database that operates completely in managed code. So far the only one I've found is TurboDB.NET (and I am not sure whether that operates completely in managed code). Are there others?

Mike Dimmick7-Nov-03 2:54

Mike Dimmick

7-Nov-03 2:54

Why do you care? Is there something that Jet can't do that you need to do?

While a wholly-managed application doesn't incur any interop overhead, a P/Invoke call (to a flat API) only has about 10 machine instructions overhead, while a COM Interop call has about 50 machine instructions overhead (source: Managed/Unmanaged Code Interoperability[^]. Some people have reported that ODBC connections from ADO.NET (using System.Data.Odbc classes in Framework 1.1, or Microsoft.Data.Odbc from the download for Framework 1.0) perform better than OLE DB connections.

If you're doing anything serious with the database, though, the interop overhead is likely to be swamped by the cost of actually reading and writing to files, and looking up records in indexes.

If you need your application to be portable across CLI implementations (and across operating systems) then you will have to consider an alternative.

'Pure managed' code on .NET is far less important than for Java, where performance with unmanaged code is reportedly poor.

Andrew Shapira7-Nov-03 10:17

Andrew Shapira

7-Nov-03 10:17

Hi Mike.

I am an experienced programmer and am new to databases. Please be nice.

Maybe a bit more background about the application will help. I am writing a server application that runs on a dedicated host. I don't need to do complicated SQL queries and I'd be perfectly happy to use a custom API instead of SQL queries. Doing SQL queries may be fine too. The main reasons I have been looking at databases is to get atomic transactions so that the database is always in a consistent state. In my situation recovery is also nice although it's less important than atomic transactions. The ability to access the database from multiple threads would be extremely helpful.

Benefits of staying in managed code for this application are type safety, debuggability, and security.

Benefits of embedding the database over using a database server are relative ease of deployment and system management. If performance is better using embedding than a database server, that would be another benefit. As you say though, maybe performance will be about the same either way.

You mentioned Jet.. Is Jet something that can be used directly from my C# application, or does it have to be used with a higher level package like Access? If it can be used directly, is there somewhere I can read about Jet from a non-propaganda standpoint? I looked around some in Google and found mostly Jet error messages and security bulletins. I have been avoiding Access because my understanding is that Access does not perform well with large databases.

Arjan Einbu10-Nov-03 11:01

Arjan Einbu

10-Nov-03 11:01

Maybe Bamboo Prevalence can help you.

See article here[^] for more info...

Have a look at my latest article about Object Prevalence with Bamboo Prevalence.