creating tables

I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is

Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()

Next

I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying

There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]

Can anyone see what I am doing wrong.
Character 14 (the token referred to in the error message) is the character at which your table name should be. Are you sure that your string contains a value (ie. not an empty string "")? Try putting a try catch block around your code, and output the value of your SQL in a messagebox when the error occurs, and review the SQL in question.|||I have looked at the string when it messes up, and it shows or seems to show that the string is correct. I actually typed the string in hardcoded it, and it still messes up.
|||my ques are string numbers. like 1 as a string. if I hardcode in ONE then it works.

can I not use 1,2,3 as table names.
|||

Have a look at the help for the CREATE TABLE statement, and review what constitutes a valid table name.

As per http://msdn2.microsoft.com/en-US/library/ms174979(SQL.90).aspx:

table_name

Is the name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

Try putting square brackets around your table name - ie. start the query with something like:

CREATE TABLE [1] ...

rather than

CREATE TABLE 1 ...

This lets SQL server know that you're specifying a name, rather than using a reserved identifier

Creating Dynamic dimension Security with Custom Procedure

Hi All,

We are trying to implement dynamic dimension security using a Custom procedure written in C#.

If we create a single entry in the return set everything works fine (See Below)

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[4]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

SetBuilder.Add(TupleBuilder.ToTuple());

However when we try and add a second item to the Tuple builder the object returns the error : The 'Sub Branch Id' Hierarchy appears more than once in the tuple.

Code snippet :

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[2]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

{

Microsoft.AnalysisServices.AdomdServer.Expression Expression = new Microsoft.AnalysisServices.AdomdServer.Expression();

Expression.ExpressionText = "[Sub Branch Id].[All Branch].[3]";

Microsoft.AnalysisServices.AdomdServer.Member Member = Expression.CalculateMdxObject(null).ToMember();

TupleBuilder.Add(Member);

Expression=null;

Member=null;

}

SetBuilder.Add(TupleBuilder.ToTuple());

Please can some one help resolve this issue as its becoming critical for the project that we are currently working on, otherwise we will have to revisit the security model.

TIA

Hello,

most likely you don't create a new tuple for each new member. Create a new instance of TupleBuilder to resolve it.

Radim

|||

Thanks for the Help Radmin, It was that.

The source example we're using was from Teo Lacevs book, applied MS Analysis Services 2005. On Teo's Forum we have found the correct syntax (though not in C#), and it does work.

This is the link to the solution, http://prologika.com/CS/forums/thread/1861.aspx