Re: I want to get the create query of existing table - C# Discussion Boards

Re: how to get create query of msaccess table through c#

19-Mar-07 20:21

thank u for u r reply.

Actually i have the tables already. your solution gives me the select query for the table but not the "create query". i want create query of the existing table.

is it posible to generate (create Query) query as we generate in sql server.

sujithkumarsl19-Mar-07 20:34

sujithkumarsl

19-Mar-07 20:34

I think your question is not clear
do you want to create table using query?
What you mean by "Create query" ?

My small attempt...

Re: I want to get the create query of existing table

joerozario19-Mar-07 21:10

Re: I want to get the create query of existing table

19-Mar-07 21:10

I have already created the tables.but i need create query of the existing table.

joerozario19-Mar-07 21:26

Re: I want to get the create query of existing table

19-Mar-07 21:26

i have the tables already.

i want the create table syntax of the existing table.How can i see?

say for example sample is the table that i have created already.

now i want the syntax like this
create table sample(.....);

Where can i go and find this systax in access.

If the search i could get the syntax like this only(Select .......)

But i want the create table syntax.

Russell Jones19-Mar-07 21:56

Re: I want to get the create query of existing table

19-Mar-07 21:56

i think you need a function called createquerydef() on the access database object.

Russ

joerozario19-Mar-07 22:42

Re: I want to get the create query of existing table

19-Mar-07 22:42

createquerydef()
how to get this function i didn't get u r idea.

can u give more explanation. is function available in MsAcess or available in .net

Russell Jones20-Mar-07 3:36

Re: how to get create query of msaccess table through c#

20-Mar-07 3:36

it's a function that is part of the access runtime library. I belive there is a Primary Interop Assembly available for MS Access so you should be able to use the object from c#.

have a look on MSDN for Access PIA and you should find a download.

Russ

V.19-Mar-07 22:57

19-Mar-07 22:57

he means he wants something like
"Create table mytable ID int Not null, somevalue varchar2(10), " etc.

V.
I found a living worth working for, but haven't found work worth living for.

OleDBConnection

zenithmaximus19-Mar-07 17:27

zenithmaximus

19-Mar-07 17:27

Hi all,

Im kinda starter in C#.net, im trying to build a system, first thing's to establish a connection to an MSAccess DB, I want to make my connection public so all forms can use it by just calling it. I'm trying to work on creating a class for it but im having hardtime making it work...

How can I make a public connection in a class and call it in my forms?
Thanks in advance

-::maximus::-

Re: OleDBConnection

sujithkumarsl19-Mar-07 19:16

sujithkumarsl

19-Mar-07 19:16

Try to make a class like "DatabaseManager" to do all the databaseoperations
Please make all the methods as public so that all the classes can access them
You can use the following public methods
1.OpenConnection()
2.CloseConnection()
3.ExcecuteQuery(string query)
4 ExecuteQuery(string query,out Dataset dt)

Just call Excecute query function with a query,and you can managethe open and close connection from this function.

If you expect any output(like search) then you can use the overloaded function
which gives a dataset.

My small attempt...

Re: OleDBConnection

spotl19-Mar-07 22:48

spotl

19-Mar-07 22:48

Hi,

the idea with one class for the hole project is a good one.

You can create a oleDbDataAdpater with the Windows Forms generator.
Then you copy the source and put it into a new class, which is created by your own. Set the modifier to public and now you can use your class in the hole project.

regards

spotl

Anyone that has used SQLExpress as their db?

Glen Harvy19-Mar-07 16:57

19-Mar-07 16:57

Hi,

I am new to SQLExpress (although not SQL) and am about to create a project in C# using it instead of MS Access.

I am a bit confused about some issues and would like advice/guidence on these issues please:

1. In the VS2005 IDE I can view the db, add tables etc manually which avoids the need of moving backwards and forwards with SQL Server Manager which is good.

As I have previously distributed my Access db in a subdirectory of the main program without problems, I would like to think I can do the same with the new .mdf file.

When coding however, the connection string would appear to require it to point to the orginal db in VS2005 Studio as follows:

string dbLocation = @"d:\vs2005 projects\mybookings\mybookings\mybookings.mdf";<br />
string connectionString = "Data Source=.\\SQLExpress;Integrated Security=True;Timeout=60;Initial Catalog = MyBookings;Application Name=SQLExpressTest;AttachDBFilename=" + dbLocation;

If I point the connection string to the copy of the db file in the subdirectory of the main program (like I do with MS Access) then I keep getting connection errors.

The question is - "How will this affect my program when I distribute it as obviously the connection string will point nowhere?"

2. I think I must be missing some basic understanding of use and deployment of SQLExpress enabled programs and would appreciate any references to any online sites you may be aware of. I've looked at the MSDN site and read the information there and no doubt I will eventually master it. However, the above problem concerns me now and I don't wish to start of wrong and have to go back and re-do my code. Unfortunately, almost everything I can find on this subject relates to ASP.Net and vb (which I find difficult to interpret particularly when I am trying to learn the subject - not the language).

TIA.

PS. PLease forgive the xpost but I think in this case it was warranted.

Glen Harvy

Russell Jones19-Mar-07 22:27

Re: Anyone that has used SQLExpress as their db? [modified]

19-Mar-07 22:27

The MSSQL service needs to know about the database for the database to work. I've just looked up the attachDBFileName property and it looks like it should attach the db so all should be well.

http:MSDN article

this article mentions a couple of potential issues:

The db file must be writable.
You might not want to copy in the ldf file as it should create a new one when you first connect.

A problem i had in the past with copying mdf files was that if you haven't detached it before you copy it, it might not be in a consistent state and then it fails to reattach properly

HTH

Russ

PS posting the connection errors you are getting might help.

Glen Harvy19-Mar-07 23:25

19-Mar-07 23:25

Hi,

First of all - thank you for your response.

I have progressed somewhat in that I discovered the following matters.

1. When FIRST accessing the database from my program I need to use the full path name to the .mdf file in my connection string.
2. Subsequent connections need only refer to the database name.

This makes sense to me as the first time I accessed it, the db was included in the SQLExpress Server. Once known to SQLExpress Server I need only refer to the database by name.

I will worry about this more when I come to deploy my project however I am confident enough to see that all I will need to do is install my db into SQLExpress Server on installation of my program and all will then be fine.

BTW I think this is an adverse side affect of using IDE's as I would have thought that by using the VS2005 IDE to create and ammend my SQLExpress db, VS2005 would have "registered" the db with SQLExpress making the connection string almost so simple it's not even needed!

Your thoughts and comments would be appreciated - it's very lonely trying to program in a remote corner of the world. Frown | :(

Thanks for your support,
Regards,

-- modified at 5:51 Tuesday 20th March, 2007

Glen Harvy

joon _vh.20-Mar-07 0:47

joon _vh.

20-Mar-07 0:47

Hang in there Smile | :)

If you attach your DB in your SQL server you normally have no need for the .mdf file, although I can't verify if it's the same in SQLExpress. If it is possible to attach a DB-file to a SQLExpress server, you will only need a connectionstring once your application is properly deployed.

btw I live in Belgium, at least more people know where your country is

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Glen Harvy20-Mar-07 1:38

20-Mar-07 1:38

Thanks,

Actually I figured out how to get it to work by going into SQL Server BUT my main concern was that when I deploy the project there is no way I could expect the end-user to have any idea how to do it "manually".

As you have noted - once I got it attached to SQLExpress Server, I no longer had any deployment worries.

Thanks for your support - it gets pretty darned hard to search the internet for help when you just don't know what to put in your search. Often I get led astray and discover that the solution to the problem that I am reading about is not actually the problem I am having at all Mad | :mad:

Cheers,

Glen Harvy

Russell Jones20-Mar-07 1:02

20-Mar-07 1:02

i would guess that you should already be able to connect by name to the db if you're using the IDE.

create a text file called something.udl. Double click it, select sql server from the provider tab and then put your name / connection credentials etc and select the db from the list it gives you. You can then check the db connection from here and make sure everything is working without your code or the ide being involved.

The cool trick then is to open this file in notepad and there is your functioning connection string. You need to delete the provider section these days, but that string should work as is (apart from password which you can add manually)

HTH

Russ

Glen Harvy20-Mar-07 1:48

20-Mar-07 1:48

Russell Jones wrote:
i would guess that you should already be able to connect by name to the db if you're using the IDE.

I would have thought so as well but no - you need to connect once as far as I can tell with the full path to the db file to get the db into the SQLExpress Server database. At least I had to - someone will prbably tell me that there is an easier way but until they do I will stick to this way - it's not as if I start a new project every day Smile | :)

I was amazed at the .udl file and thanks - did exactly as you said and what a neat trick Smile | :)

Glen Harvy

Glen Harvy22-Mar-07 22:26