[tonyloop]

hELLO everyone, I have been working on an application in C# that selects all names of columns and their properties from all tables in a database using this sample code

 SqlCommand dataCommand = new SqlCommand();
 dataCommand.Connection = dataConnection;
 
dataCommand.CommandText =
  "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.name AS COLUMN_NAME,"+
"systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,"+ 
"syscolumns.isnullable as IS_NULLABLE FROM syscolumns"+
"INNER JOIN systypes"+
	"ON syscolumns.xtype = systypes.xtype "+
	"LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id "+
  
 " LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id "+
   "WHERE syscolumns.id IN  "+
	"(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')";
			   
 SqlDataReader dataReader = dataCommand.ExecuteReader();

However, when i run the code in mssql server 2005 it runs and displays results properly. but when i run it in my c# program, it gives me the error:

Incorrect syntax near '.'
Incorrect syntax near 'AND'

JUST NEED SOME HELP IN DEBGGING THIS

[JellyBean]

You need to be very careful when creating SQL strings like this. The problem here is you are missing a space before the INNER JOIN systypes clause and at various other places in your SQL statement.

Here is a corrected version. (I cannot promise you have got the joins correct but it should parse better in SQL.)

dataCommand.CommandText =
  "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.name AS COLUMN_NAME,"+
"systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,"+ 
"syscolumns.isnullable as IS_NULLABLE FROM syscolumns"+
" INNER JOIN systypes"+
	" ON syscolumns.xtype = systypes.xtype "+
	"LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id "+
  
 " LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id "+
   "WHERE syscolumns.id IN  "+
	"(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')";

Tip: If using MS SQL Server, start the SQL Profiler tool and monitor the form of SQL statements ariving at the server. Alternately, insert a breakpoint in your code before SqlDataReader dataReader = dataCommand.ExecuteReader();. This way you can examine the command that is being passed to SQL and it will be easier to see where your mistakes are.

This post has been edited by JellyBean: 16 May 2007 - 04:22 AM

[tonyloop]

JellyBean, on 16 May, 2007 - 04:20 AM, said:

You need to be very careful when creating SQL strings like this. The problem here is you are missing a space before the INNER JOIN systypes clause and at various other places in your SQL statement.

Here is a corrected version. (I cannot promise you have got the joins correct but it should parse better in SQL.)

dataCommand.CommandText =
  "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.name AS COLUMN_NAME,"+
"systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,"+ 
"syscolumns.isnullable as IS_NULLABLE FROM syscolumns"+
" INNER JOIN systypes"+
	" ON syscolumns.xtype = systypes.xtype "+
	"LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id "+
  
 " LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id "+
   "WHERE syscolumns.id IN  "+
	"(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')";

Tip: If using MS SQL Server, start the SQL Profiler tool and monitor the form of SQL statements ariving at the server. Alternately, insert a breakpoint in your code before SqlDataReader dataReader = dataCommand.ExecuteReader();. This way you can examine the command that is being passed to SQL and it will be easier to see where your mistakes are.

Thanks jellybean, really thanks a lot. the command worked out. One more issue am still resolving is when am getting the information, i use the code

string name=datareader.GetString(0); // and so on

but when i want to retrieve a numeric value say

int length=datareader.GetInt32(2); 

it fails and gets an error at this point. what else can i use to do the same, or what do you think is the problem with that?

[tonyloop]

tonyloop, on 16 May, 2007 - 11:36 PM, said:

JellyBean, on 16 May, 2007 - 04:20 AM, said:

You need to be very careful when creating SQL strings like this. The problem here is you are missing a space before the INNER JOIN systypes clause and at various other places in your SQL statement.

Here is a corrected version. (I cannot promise you have got the joins correct but it should parse better in SQL.)

dataCommand.CommandText =
  "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.name AS COLUMN_NAME,"+
"systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,"+ 
"syscolumns.isnullable as IS_NULLABLE FROM syscolumns"+
" INNER JOIN systypes"+
	" ON syscolumns.xtype = systypes.xtype "+
	"LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id "+
  
 " LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id "+
   "WHERE syscolumns.id IN  "+
	"(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')";

Tip: If using MS SQL Server, start the SQL Profiler tool and monitor the form of SQL statements ariving at the server. Alternately, insert a breakpoint in your code before SqlDataReader dataReader = dataCommand.ExecuteReader();. This way you can examine the command that is being passed to SQL and it will be easier to see where your mistakes are.

Thanks jellybean, really thanks a lot. the command worked out. One more issue am still resolving is when am getting the information, i use the code

string name=datareader.GetString(0); // and so on

but when i want to retrieve a numeric value say

int length=datareader.GetInt32(2); 

it fails and gets an error at this point. what else can i use to do the same, or what do you think is the problem with that?

Its ok now ive managed to fix it.

[JellyBean]

Glad you solved it

If you get problems in the future it's well worth posting details of the exception. You can do this by wrapping your code in a try-catch block and doing a Console.Write(e.ToString()); (or the VB equivalent). Then copy the exception from the console panel in Visual Studio, and paste it into your post. Easy!

[matoksoz]

you should use backslash before ' and " .
like this;

\' and \"

Also do not use <>, because it is visual basic code which means "is not equal" . the c# equivalent is "!=".