creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

EndWith

Catch ex As Exception

MsgBox(ex.ToString)

EndTry

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>
that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... ?
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>
|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... ?
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>
|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Maybe
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

creating a database on the server

Is there a way to create a brand new database on the server?
Someone gave me the asp code below, but I haven't gotten it to work yet.
Thanks for any help.
<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
response.write "got to here"
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
& "Initial Catalog=test;User Id=sa;Password=;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
response.write "are we heare"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>that creates a Table, not a new DB...which is it you are after? Also, why
are you posting it to these groups? some .Net, some not, some SQL... '
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Is there a way to create a brand new database on the server?
> Someone gave me the asp code below, but I haven't gotten it to work yet.
> Thanks for any help.
> <%
> Dim strSQL
> Dim cnnTest
> strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
> strSQL = strSQL & "(" & vbCrLf
> strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
> strSQL = strSQL & " constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
> strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
> strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
> strSQL = strSQL & ")" & vbCrLf
> Set cnnTest = Server.CreateObject("ADODB.Connection")
> response.write "got to here"
> cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
> & "Initial Catalog=test;User Id=sa;Password=;" _
> & "Connect Timeout=15;Network Library=dbmssocn;"
> response.write "are we heare"
> cnnTest.Execute strSQL
> cnnTest.Close
> Set cnnTest = Nothing
> %>
>
>|||Hello,
Thanks for your help. I just wanted to create the actual database on the
server, then create a table within the database. I realize the sample code
I sent is probably not the right code.
I posted to several groups I thought seemed related to this question. Maybe
I picked the wrong ones.
Thanks.
Scott Baxter
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
> that creates a Table, not a new DB...which is it you are after? Also, why
> are you posting it to these groups? some .Net, some not, some SQL... '
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
> "Scott Baxter" <sbaxter@.websearchstore.com> wrote in message
> news:ukc9HSbQEHA.3300@.TK2MSFTNGP09.phx.gbl...
>|||You need to create a script, sql statement that does
something like:
create database YourDB
go
use YourDB
go
create table YourTable
(col1 int not null)
-Sue
On Mon, 24 May 2004 11:12:28 -0700, "Scott Baxter"
<sbaxter@.websearchstore.com> wrote:

>Hello,
>Thanks for your help. I just wanted to create the actual database on the
>server, then create a table within the database. I realize the sample code
>I sent is probably not the right code.
>I posted to several groups I thought seemed related to this question. Mayb
e
>I picked the wrong ones.
>Thanks.
>Scott Baxter
>"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
>news:OanW1WbQEHA.252@.TK2MSFTNGP10.phx.gbl...
>

Creating a database

I have a database in Access to keep records for CDs. The tables are as below
Artist
ArtID - 1,2,3...
ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
Genre
GenID - 1,2,3...
Genre - Pop, Rock, Hip Hop, Jazz...
CDType
CDTypeID - 1,2,3...
CDType - Album CD, Single CD, Double Cd, limited edition...
MainCDInformation
LineID - 1,2,3...
Artist - Linked with ArtID
CDTitle - Name of Album
CDType - linked with CDTypeID
Genre - Linked with GenID
Cost - User can enter a cost
Notes - User can enter addtional notes
I am getting conflicting advise whether this approach is correct or not the
way this database has been designed. Would you guys agree to it or not? If
not how would you create it and what rules would you follow?
Thanks in advance"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
>I have a database in Access to keep records for CDs. The tables are as
>below
> Artist
> ArtID - 1,2,3...
> ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
> Genre
> GenID - 1,2,3...
> Genre - Pop, Rock, Hip Hop, Jazz...
> CDType
> CDTypeID - 1,2,3...
> CDType - Album CD, Single CD, Double Cd, limited edition...
> MainCDInformation
> LineID - 1,2,3...
> Artist - Linked with ArtID
> CDTitle - Name of Album
> CDType - linked with CDTypeID
> Genre - Linked with GenID
> Cost - User can enter a cost
> Notes - User can enter addtional notes
> I am getting conflicting advise whether this approach is correct or not
> the
> way this database has been designed. Would you guys agree to it or not? If
> not how would you create it and what rules would you follow?
"Correct" is defined by the user. No schema is ever perfect and there are
frequently times when a schema is compromised in some way to achieve some
objective. Does that make the schema incorrect? If it works for you and
satisifies your requirements, why do you care if others think it is not
"correct"? If you are getting advice about the schema that is useful or
helps meet your requirements in some "better" way, then incorporate it. It
would help to have some examples of this advice to offer meaningful ... umm
... advice.
In any event, a more complete definition of the tables (and how they are
intended to be used) is required before anyone can make useful suggestions.
If you want comments:
* You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
* You appear to have overlooked the fact that an album may have multiple
artists or that an album may have no actual designated artist (insert late
night party-time CD commercial reference here).
* Notes? Your system supports a single user?
* You appear to be focused on music only. What about books or other types
of content?
* Is it not possible to have a limited edition double CD? CDType seems to
be a confusion of mutiple attributes.
If you really want to go nuts, then go visit some audiophile sites - you are
just scratching the surface of music content information. It may help to
explore some online music sites to see what information they provide, how
they organize, and how they link (or reference) related information.
Alternatively, have you considered purchasing this type of software - there
must be hundreds (if not thousands) of music cataloging programs (assuming
that is the purpose of this schema).|||Thanks for your advise. Sorry for the basic format of the table. This was
merely an idea to give you guys rather than having a lengthy page with tables
and fields.
I agree with what you have wrote but the issue is in Access this database is
acceptable. Only 1 user enters the details. For me to retrieve information
lets say about the Artist and the album title i would have a query like
Artist.ArtistName with CDTitle.MainCDInformation
(After the "." is the table name). Now this approach works. When I upgrade
this DB to SQL 2005 - all ok here too. The problem occurs when i create an
application in ASP .Net as the TableAdapter Preview looks correct but when
generating the same information (Artist and Cd Title) and then running this
application I have some data missing from fields that are joined to other
tables and cant figure out why.
Hope this makes some sense if not please let me know. If you wish i can post
a snippet of the SQL code it generates.
Thanks
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:8D19B7CF-EBC4-4894-81D3-1D6E5B19B140@.microsoft.com...
> >I have a database in Access to keep records for CDs. The tables are as
> >below
> >
> > Artist
> > ArtID - 1,2,3...
> > ArtistName - Michael Jackson, Snoop Dogg, Jamiroquai...
> >
> > Genre
> > GenID - 1,2,3...
> > Genre - Pop, Rock, Hip Hop, Jazz...
> >
> > CDType
> > CDTypeID - 1,2,3...
> > CDType - Album CD, Single CD, Double Cd, limited edition...
> >
> > MainCDInformation
> > LineID - 1,2,3...
> > Artist - Linked with ArtID
> > CDTitle - Name of Album
> > CDType - linked with CDTypeID
> > Genre - Linked with GenID
> > Cost - User can enter a cost
> > Notes - User can enter addtional notes
> >
> > I am getting conflicting advise whether this approach is correct or not
> > the
> > way this database has been designed. Would you guys agree to it or not? If
> > not how would you create it and what rules would you follow?
> "Correct" is defined by the user. No schema is ever perfect and there are
> frequently times when a schema is compromised in some way to achieve some
> objective. Does that make the schema incorrect? If it works for you and
> satisifies your requirements, why do you care if others think it is not
> "correct"? If you are getting advice about the schema that is useful or
> helps meet your requirements in some "better" way, then incorporate it. It
> would help to have some examples of this advice to offer meaningful ... umm
> ... advice.
> In any event, a more complete definition of the tables (and how they are
> intended to be used) is required before anyone can make useful suggestions.
> If you want comments:
> * You have focused on a single medium (CD vs DVD, tape, vinyl, etc.).
> * You appear to have overlooked the fact that an album may have multiple
> artists or that an album may have no actual designated artist (insert late
> night party-time CD commercial reference here).
> * Notes? Your system supports a single user?
> * You appear to be focused on music only. What about books or other types
> of content?
> * Is it not possible to have a limited edition double CD? CDType seems to
> be a confusion of mutiple attributes.
> If you really want to go nuts, then go visit some audiophile sites - you are
> just scratching the surface of music content information. It may help to
> explore some online music sites to see what information they provide, how
> they organize, and how they link (or reference) related information.
> Alternatively, have you considered purchasing this type of software - there
> must be hundreds (if not thousands) of music cataloging programs (assuming
> that is the purpose of this schema).
>
>|||> (After the "." is the table name). Now this approach works. When I upgrade
> this DB to SQL 2005 - all ok here too. The problem occurs when i create an
> application in ASP .Net as the TableAdapter Preview looks correct but when
> generating the same information (Artist and Cd Title) and then running
> this
> application I have some data missing from fields that are joined to other
> tables and cant figure out why.
> Hope this makes some sense if not please let me know. If you wish i can
> post
> a snippet of the SQL code it generates.
Not really. You've gone from a relatively simply schema question to an
Access->SQL Server/ASP migration issue. If the actual schema is not an
issue (i.e., it stores what you need and you can get the information you
want from it), then you should probably post details of the actual problem
to a NG that is focused on ASP.|||I thought it wouldnt which is why i wanted to keep this a simple question.
However now you can understand why i was questioning if the DB structure i
had was correct or not and why some say it was incorrect (since it doesnt
show up correctly when the application runs but does otherwise).
I have already asked in an ASP .Net group, which is why i asked here to see
if someone could shed some light here or not :-).
"Scott Morris" wrote:
> > (After the "." is the table name). Now this approach works. When I upgrade
> > this DB to SQL 2005 - all ok here too. The problem occurs when i create an
> > application in ASP .Net as the TableAdapter Preview looks correct but when
> > generating the same information (Artist and Cd Title) and then running
> > this
> > application I have some data missing from fields that are joined to other
> > tables and cant figure out why.
> >
> > Hope this makes some sense if not please let me know. If you wish i can
> > post
> > a snippet of the SQL code it generates.
> Not really. You've gone from a relatively simply schema question to an
> Access->SQL Server/ASP migration issue. If the actual schema is not an
> issue (i.e., it stores what you need and you can get the information you
> want from it), then you should probably post details of the actual problem
> to a NG that is focused on ASP.
>
>|||"Shelly" <Shelly@.discussions.microsoft.com> wrote in message
news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
>I thought it wouldnt which is why i wanted to keep this a simple question.
> However now you can understand why i was questioning if the DB structure i
> had was correct or not and why some say it was incorrect (since it doesnt
> show up correctly when the application runs but does otherwise).
IMO, the clue here is that it "doesnt show up correctly when the application
runs but does otherwise". I think you should give some serious thought to
the code you are using and, to a lesser degree, the queries it generates to
access the database. In your previous post, you also mentioned "joining to
other tables". This also leads me to believe the issue is with the code,
not the schema. The information you previously posted wasn't a complete
schema, so perhaps you left out things needed for your application to work
"correctly" (like primary keys, foreign keys, other tables, etc.).|||I had a feeling you might have said its code related as soon as i clicked
Post on my last response. Its definately not the code as other DB works fine
using this code also i tested the code which was provided with ASP .Net
tutorials.
I can post a detailed database structure if you think it could be something
else. Its a tough one as thsi DB works fine with Access - the code works fine
with other databases and when i generate the code under the TableAdapter i
can view the results and they show up correctly (A TableAdapter is a bit like
creating a query in design mode in SQL - sorry i added this incase you wasnt
aware of what a TableAdapter is - if you do, my apologies)
Thanks
"Scott Morris" wrote:
> "Shelly" <Shelly@.discussions.microsoft.com> wrote in message
> news:BBF6E91D-F447-4384-8995-E0D2AE814619@.microsoft.com...
> >I thought it wouldnt which is why i wanted to keep this a simple question.
> > However now you can understand why i was questioning if the DB structure i
> > had was correct or not and why some say it was incorrect (since it doesnt
> > show up correctly when the application runs but does otherwise).
> IMO, the clue here is that it "doesnt show up correctly when the application
> runs but does otherwise". I think you should give some serious thought to
> the code you are using and, to a lesser degree, the queries it generates to
> access the database. In your previous post, you also mentioned "joining to
> other tables". This also leads me to believe the issue is with the code,
> not the schema. The information you previously posted wasn't a complete
> schema, so perhaps you left out things needed for your application to work
> "correctly" (like primary keys, foreign keys, other tables, etc.).
>
>

creating a data driven hierarchial navigation

Hi need to create navigation that gets populated via database and is role based. below is the sql

CREATETABLE [TopMenu](

[MenuID] [int]IDENTITY(1, 1)NOTNULL,

[Text] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[Description] [varchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentID] [int]NULL,

CONSTRAINT [PK_Menu]PRIMARYKEYCLUSTERED

(

[MenuID]

)ON [PRIMARY]

)ON [PRIMARY]

GO

INSERTINTO TopMENU

Select'Property','Property',NULL

UNION ALLSelect'Portfolio','Portfolio',NULL

UNION ALLSelect'Capital Expenditure','PortfolioCapex', 1

UNION ALLSelect'Depreciation','PortfolioDepreciation', 1

UNION ALLSelect'Condition Audit','PortfolioCondition', 1

UNION ALLSelect'Historical Expenditure','PortfolioHisEx', 1

UNION ALLSelect'Property Details','PropertyDetail', 2

UNION ALLSelect'Assets','PropertyAsset', 2

UNION ALLSelect'Depreciation','PropertyDepreciation', 2

UNION ALLSelect'Capital Expenditure','PropertyExpenditure', 2

UNION ALLSelect'Insurance','PropertyInsurance', 2

UNION ALLSelect'Documents','PropertyDocuments', 2

UNION ALLSelect'Capex','PortfolioCapex', 3

UNION ALLSelect'Graph','PortfolioCapexGraph', 3

UNION ALLSelect'Graph','PortfolioDepreciationGraph', 4

UNION ALLSelect'Current Depreciation','PortfolioDepreciation', 4

UNION ALLSelect'WDV & Depreciation','PortfolioDepreciationWDV', 4

UNION ALLSelect'Assets','PropertyAsset', 8

UNION ALLSelect'Capex','PropertyCapex', 8

UNION ALLSelect'Depreciation','PropertyDepreciation', 8

UNION ALLSelect'Condition','PropertyCondition', 8

UNION ALLSelect'Expenditure','PropertyExpenditure', 8

UNION ALLSelect'Disposed','PropertyDisposed', 8

UNION ALLSelect'Capital Ex','PropertyExpenditure', 10

UNION ALLSelect'Capital Ex Graph','PropertyExpenditureGraph', 10

GO

now clearly, I will have two top level navigation

property and portfolio. both property will have subsequent navigation and those subsequest navigation may or may not have another sets of navigation.

I need to display in a row style not in a drop down style for eg. If I am in portfolio link the navigation structure should be as below

Portfolio

Capital Expenditure | Depreciation | Condition Audit | Historical Expenditure

Capex | Graph

by default Capex should be selected. it should also highlight Portfolio, Capital Expenditure and Capex as Capex's parent is Capital Expenditure and Portfolio

I was thinking of using a 3 level datagrid. could someone please help me I am using asp.net 2.0 web application project with vb.net and sql server 2005

thanks in advance

Have you looked at the menu class available from the toolbar? I know it works with a hierarchical xml file, it might work with a database source also.

If it does, I think it will do what you want UI-wise.

|||I am doing it in webform not windows form. does it support that?|||

foremorecoast:

I am doing it in webform not windows form. does it support that?

This is an asp.net forum, not a windows forms forum. So I wasn't talking about windows forms. :)

As for whether it supports database-supplied values instead of xml files for the menu entries, you'll have to look that up. It's what I would do first if I had to code a solution like that. But I don't, and you do, so you can look it up and tell us about what you found. :)

|||

the problem here is not the datasource type but the logic on how to display second level navigation and third level navigation based on what is clicked. i.e. property or portfolio. I have seen lots of article which shows how to create a data driven menu using javascript downdown but i want second and third navigation to be static based on what is clicked( either property or portfolio).

please help

|||

I am thinking of alternative solution using 3 level datalist but its not working as there is no onclick property so that based on property link or portfolio link click i could display their childrens and based on their children click i could display their sub childs.

can anyone please help me?

|||

Hi! I used below method to implement 3 level navigation. but I now have a problem of maintaining the selected state of the item after postback.

when user logs in I need to somehow set the default page pointing to

Portfolio

Assets

Capex

and at the same time i need to show those three links active.

each page should go to differnt url with parameter. below are my codes. please help.

aspx

<asp:DataList ID="dlParent" runat="server" RepeatDirection="Horizontal" DataKeyField="MenuId" OnSelectedIndexChanged="parentselectedindexchanged" Font-Names="Trebuchet MS" Font-Size="Smaller" ForeColor="#404040">
<ItemTemplate>
<asp:LinkButton ID="lnkParent" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:DataList>
<asp:dataList id="dlChild" runat="server" RepeatDirection="Horizontal" DataKeyField="MenuId" OnSelectedIndexChanged="childselectedindexchanged" Font-Names="Trebuchet MS" Font-Size="Smaller">
<ItemTemplate>
<asp:LinkButton ID="lnkChild" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:dataList>
<asp:dataList id="dlSubChild" runat="server" RepeatDirection="Horizontal" Font-Names="Trebuchet MS" Font-Size="Smaller" >
<ItemTemplate>
<asp:LinkButton ID="lnkSubChild" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:dataList>

code behind
--
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myDal As New clsDAL
Dim dsParent As DataSet
'Dim selParent As Integer = -1
'dlParent.SelectedIndex = selParent
If Not IsPostBack Then

dsParent = myDal.getSubMenuItems(0)

If Not dsParent Is Nothing Then
dlParent.DataSource = dsParent
dlParent.DataBind()
End If

End If
End Sub

Protected Function BuildUrl(ByVal field1 As Integer) As String
Dim baseUrl As String
baseUrl = "menu.aspx"
Return String.Format("{0}?navId={1}", baseUrl, field1)
End Function

Public Sub parentselectedindexchanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mydal As New clsDAL
Dim NavId As Integer
'Dim selChild As Integer = -1
'dlChild.SelectedIndex = selChild
Dim dsChild As New DataSet
Try
NavId = CInt(dlParent.DataKeys(dlParent.SelectedIndex))

dsChild = mydal.getSubMenuItems(NavId)
dlChild.DataSource = dsChild
dlChild.DataBind()
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub

Public Sub childselectedindexchanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mydal As New clsDAL
Dim NavId As Integer
'Dim selSubChild As Integer = -1
'dlSubChild.SelectedIndex = selSubChild
Dim dsSubChild As New DataSet
Try
NavId = CInt(dlChild.DataKeys(dlChild.SelectedIndex))
dsSubChild = mydal.getSubMenuItems(NavId)
dlSubChild.DataSource = dsSubChild
dlSubChild.DataBind()
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub

|||

can someone at least point me to write direction?

|||

You could save the entire object in session cache and restore it with each postback.

Sorry, I don't know how to make it pre-open to a given location.

|||

could you please show me how you will do it as i have never used cache to store the datalist. a code snippet will be of great help.

cheers

Create View in SQL Server with data types

I need to create a view of a sql table, but change the data types. I know
the syntax below is not correct, and can't figure out if it is wrong or if
you just can't do this. I have only created views before with the same data
type.

CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
FROM F0005 AS F0005
WHERE DRSY = '41' AND DRRT = 'S1')

Thanks!!Aren't those JD Edwards column names a drag? I'm surprised you need
UNICODE (nchar and nvarchar instead of char and varchar), I did not
realize that JDE could handle anything beyond EBCDIC or ASCII.

A VIEW is just a SELECT statement inside. The example has an INSERT
statement. A VIEW can NOT have an INSERT statement.

CREATE VIEW does not allow for specifying the data types in the VIEW
column list. It is not part of the syntax.

When I need to control the data type this way I use CONVERT, though
CASE can do the same thing:

CREATE VIEW F0005New
AS
SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'

Roy Harvey
Beacon Falls, CT

On Mon, 14 Aug 2006 15:58:01 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>I need to create a view of a sql table, but change the data types. I know
>the syntax below is not correct, and can't figure out if it is wrong or if
>you just can't do this. I have only created views before with the same data
>type.
>
>CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
>INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
>FROM F0005 AS F0005
>WHERE DRSY = '41' AND DRRT = 'S1')
>
>Thanks!!

|||Roy - thanks so much!!! You are right about the JE Edwards column names!! I
do not enjoy them at all. Regarding the data type - the client already had
it set up - I just have to live with it!! I'll try this and see if it works!

Roy Harvey wrote:

Quote:

Originally Posted by

>Aren't those JD Edwards column names a drag? I'm surprised you need
>UNICODE (nchar and nvarchar instead of char and varchar), I did not
>realize that JDE could handle anything beyond EBCDIC or ASCII.
>
>A VIEW is just a SELECT statement inside. The example has an INSERT
>statement. A VIEW can NOT have an INSERT statement.
>
>CREATE VIEW does not allow for specifying the data types in the VIEW
>column list. It is not part of the syntax.
>
>When I need to control the data type this way I use CONVERT, though
>CASE can do the same thing:
>
>CREATE VIEW F0005New
>AS
>SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'
>
>Roy Harvey
>Beacon Falls, CT
>

Quote:

Originally Posted by

>>I need to create a view of a sql table, but change the data types. I know
>>the syntax below is not correct, and can't figure out if it is wrong or if

>[quoted text clipped - 7 lines]

Quote:

Originally Posted by

>>
>>Thanks!!

|||Roy - it worked! Thanks for your help.

cognosqueen wrote:

Quote:

Originally Posted by

>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>do not enjoy them at all. Regarding the data type - the client already had
>it set up - I just have to live with it!! I'll try this and see if it works!
>

Quote:

Originally Posted by

>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>
>>>Thanks!!

|||You are welcome, and good luck! My experience with importing JD
Edwards data into a SQL Server data warehouse was quite painful, not
because of F0005 which in our case was quite clean, but the invoice
data that came in lacked any unique key. I hope your life is simpler
than mine was.

Roy

On Mon, 14 Aug 2006 18:37:37 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>Roy - it worked! Thanks for your help.
>
>cognosqueen wrote:

Quote:

Originally Posted by

>>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>>do not enjoy them at all. Regarding the data type - the client already had
>>it set up - I just have to live with it!! I'll try this and see if it works!
>>

Quote:

Originally Posted by

>>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

>>>>
>>>>Thanks!!

Create view

hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))

if i want to create a view
how ?
thank you!CREATE VIEW myviewname AS
select statement goes here

how hard did you try to look up the CREATE VIEW syntax?

:) :)|||hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))

if i want to create a view
how ?
thank you!

Create View <ViewName>
as
(Your Sql Statement)

Regards
Subramanyam.|||Create View <ViewName>
as
(Your Sql Statement)good one

looks a lot like post #2, though

:)|||Good answer.

Appears similar to post #2, however.|||Good answer.

Appears similar to post #2, however.that's funny, your post looks a lot like post #4

;) ;)