Re: Programmatically creating a new Access DB - Database Discussion Boards

Mugombi19-Jun-06 21:02

19-Jun-06 21:02

Fellas,

How are you all?

Slight problem, I am developing a huge corporate's sales tracking application, I have decided for there to be a central SQL Server at the HQ and MSDE at branches across the country.

What is the quickest way to allow sales done at branches to be replicated across to the HQ. I think I am doing it the long way in manually checking if record exists and inserting if it doesnt for every table which will be in a dataset object.

Info will also move from HQ down to branches because the db structure is in such a way that all Sales Items can only be added at HQ and downloaded to office as per allocation. I have got a sales table that tracks sale info, a sale items table that tracks the actual items by id of the item which is linked to the items database.

How do I quickly replicate across dbs?

Re: SQL Replications

Frank Kerrigan20-Jun-06 4:49

Frank Kerrigan

20-Jun-06 4:49

Do some research on Merge replication.

Look where you want to go not where you don't want to crash.

Bikers Bible

How to delete all data of a specific column

CandyMe19-Jun-06 19:43

CandyMe

19-Jun-06 19:43

sql server 2005
How do I delete all data of a specific column

Thank you.

Re: How to delete all data of a specific column

rittjc19-Jun-06 20:22

rittjc

19-Jun-06 20:22

Hi Candy,

Try using an update query:

"UPDATE targetTable SET targetTable.targetColumn = NULL"

Where targetTable is the table that contains the column, targetColumn which is the column you want to delete the data from.

Jim

Re: How to delete all data of a specific column

CandyMe19-Jun-06 22:00

CandyMe

19-Jun-06 22:00

Oh yah! Thank you so much. Blush | :O

:->

Programmatically creating a new Access DB [modified]

rittjc19-Jun-06 16:38

rittjc

19-Jun-06 16:38

You can't get there from here...I always found that statement ironic. You hear it a lot when asking directions. It is like the problem I am having in creating a new Access database from ADO.NET programmatically.

First, you need a connection. This connection needs a connection string. The connection string has to have the path of the new database or it gripes about no Data Source. But when you put the new database name and path connection string it complains that the Data Source does not exist.

How do you do this? Any gurus know how to do this?

My plan was to:

OdbcConnection myConn = new OdbcConnection("Provider=blah..blah");
OdbcCommand myCommand = new OdbcCommand("CREATE DATABASE MyDbPath", myConn);

myComm.Open();
myCommand.ExecuteNonQuery();

...

How do you get there from here?

Thanks in advance!

-- modified at 22:48 Monday 19th June, 2006

Re: Programmatically creating a new Access DB

Eric Dahlvang20-Jun-06 3:21

Eric Dahlvang

20-Jun-06 3:21

Intriguing dilemma. I searched a little, and only came up with this:
http://support.microsoft.com/kb/317881/EN-US/[^]

The gist of it is that you need to use Microsoft ADO Ext. 2.7 for DDL and Security.

Build an Access Database
Open a new Visual C# .NET console application.
In Solution Explorer, right-click the References node and select Add Reference.
On the COM tab, select Microsoft ADO Ext. 2.7 for DDL and Security, click Select to add it to the Selected Components, and then click OK.
Delete all of the code from the code window for Class1.cs.
Paste the following code into the code window:

using System;
using ADOX;

namespace ConsoleApplication1
{
	class Class1
	{
		[STAThread]
		static void Main(string[] args)
		{
			ADOX.CatalogClass cat = new ADOX.CatalogClass();

			cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
				   "Data Source=D:\\AccessDB\\NewMDB.mdb;" +
				   "Jet OLEDB:Engine Type=5");

			Console.WriteLine("Database Created Successfully");

			cat = null;

		}
	}
}

Change the path to the new .mdb file as appropriate, and then press F5 to build and run the project.

The new .mdb file will be created in Access 2000 (Jet 4.0) format.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Programmatically creating a new Access DB

rittjc20-Jun-06 3:40

rittjc

20-Jun-06 3:40

Thanks Eric.

That is a very inelegant solution. Looks like I am stuck with using it. Surprizing that there is not a better way. What if the end user does not have ADO 2.7 on his machine?

Great job finding that buddy! It confirms the problem and at least offers a solution, elegant or not.

Thanks again.

conversion

pavan kumar.p19-Jun-06 4:40

pavan kumar.p

19-Jun-06 4:40

how to convert a string to integer of length 4?which function to use?

P.PaVaN KuMaR

Re: conversion

Eric Dahlvang19-Jun-06 5:14

Eric Dahlvang

19-Jun-06 5:14

select convert(int,ColumnName) from TableName

or

select cast(ColumnName as int) from TableName

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: conversion

r.stropek20-Jun-06 23:42

r.stropek

20-Jun-06 23:42

I would suggest to add the isnumeric function if you are not absolutely sure that the string ALWAYS contains valid ints:

select cast( case when isnumeric(ColumnName)=1 then ColumnName else NULL end as int ) from TableName

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: conversion

Eric Dahlvang21-Jun-06 4:44

Eric Dahlvang

21-Jun-06 4:44

r.stropek wrote:
select cast( case when isnumeric(ColumnName)=1 then ColumnName else NULL end as int ) from TableName

Makes sense. Thanks.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

insertquery

pavan kumar.p19-Jun-06 4:37

pavan kumar.p

19-Jun-06 4:37

how to insert a boolean variable into sql table?it needs any conversion?give one example.

P.PaVaN KuMaR

Re: insertquery

Colin Angus Mackay19-Jun-06 5:50

Colin Angus Mackay

19-Jun-06 5:50

Booleans tend to get stored in bit columns. 0=false, 1=true. If you are using parameterised queries then you can sent it a Boolean and it will do the conversion for you.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

CONVERT() Function

NICE TO MEET19-Jun-06 1:05

NICE TO MEET

19-Jun-06 1:05

i want a datetime format as mm/dd/yyyy in dataset i am getting
AcquisitionDate="2005-05-05T00:00:00+05:30"

if i used convert(datetime,AcquisitionDate) function ,the sql is throwing error convertion failed to datetime as character string

any other way to convert this

guide me please

-

Re: CONVERT() Function

vivek-g20-Jun-06 23:45

vivek-g

20-Jun-06 23:45

It is too simple..

try this out...

--> convert(char,getdate(),101) <--function

....

vivek

Best way of searching XML [modified]

__makaveli__19-Jun-06 0:44

__makaveli__

19-Jun-06 0:44

I have a system which uses dynamic forms generated from XML, it saves the results in XML to a seperate table and combines the two on reload but I need a fast way of searching the XML and looking for suggestions, the data is stored like this:

<field name="<fieldname>" value="<somevalue>">

I was thinking of using something like:

SELECT * FROM xmldatatable WHERE xmldata LIKE 'value="%<searchterm>%"';

Would this be acceptable? I would imagine it would start to get very slow once the record numbers start increasing?

(Using PostgreSQL 8.1)

Re: Best way of searching XML

rittjc20-Jun-06 2:38

rittjc

20-Jun-06 2:38

Have you thought about using XPath?

trim in select stmt

bony_baba18-Jun-06 21:27

bony_baba

18-Jun-06 21:27

hi
i am writing a quert to select all the columns from a table just like that
select Eaton.* from Eaton
eaton is my table name but some colums has blank spacesin left and right so i want to eliminate them so i use
select Trim(Eaton.*) from Eaton but its not woking
Whast i do if any body know Please help
Thank in advance

Thanks

Re: trim in select stmt

Reza Raad18-Jun-06 23:34

Reza Raad

18-Jun-06 23:34

Use ltrim or rtrim
as below:

select lTRIM('Eaton.* ')

between (')

how to show date in access table??

areon2518-Jun-06 19:45

areon25

18-Jun-06 19:45

hi all...

i have a problem that how to show a date in access table.. can someone please help me..

areon25

Inserting a Row - Returning the ID

AJ12317-Jun-06 22:40

AJ123

17-Jun-06 22:40

Hi There

This has got to be an easy one... I am coming to ADO.NET from an ADO background, so i appologise if it comes across as totally stupid!

All i want to do is insert a new row into a table and pull back the autonumber primary key assigned to it.

What i don't want to have to do is:

1) Fill a Dataset in order to get a DataTable definition.
2) Use stored procedures.

Anybody know the answer off the top of their head? Guess i really need to buy a good ADO.NET book.

Thanks In Advance
Rich

Re: Inserting a Row - Returning the ID

AJ12317-Jun-06 23:09

AJ123

17-Jun-06 23:09

Found my answer :-

There is a good discussion of this at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp[^]

how to give a name to the result table

TheBeginner7717-Jun-06 22:39

TheBeginner77

17-Jun-06 22:39

this is my query
query="SELECT studenti.Nume, studenti.Prenume, cursuri.denumire_curs, note.nota, note.data " +
"FROM studenti INNER JOIN " +
"(profesori INNER JOIN " +
"(cursuri INNER JOIN [note] ON cursuri.id_curs = note.id_curs) " +
"ON profesori.id_profesor = cursuri.id_profesor) ON studenti.id_stud = note.id_stud " +
"WHERE profesori.user_name = '" + user + "'";
i want to give a name to the result table so i can put it in a DataSet
dadapter.Fill(ds,"table name");
Thanks

Re: how to give a name to the result table

Paul Brower19-Jun-06 4:06

Paul Brower

19-Jun-06 4:06

You don't need to know the name to put the results into a dataset. Just look at the overloads available.

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