5 Replies - 10328 Views - Last Post: 17 June 2007 - 04:04 PM Rate Topic: -----

#1 tonyloop  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 07

dATABASE ACCESS WITH C#.NET

Posted 16 May 2007 - 02:56 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: dATABASE ACCESS WITH C#.NET

#2 JellyBean  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 60
  • Joined: 25-April 07

Re: dATABASE ACCESS WITH C#.NET

Posted 16 May 2007 - 04:20 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 tonyloop  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 07

Re: dATABASE ACCESS WITH C#.NET

Posted 16 May 2007 - 11:36 PM

View PostJellyBean, 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?
Was This Post Helpful? 0
  • +
  • -

#4 tonyloop  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 07

Re: dATABASE ACCESS WITH C#.NET

Posted 17 May 2007 - 02:21 AM

View Posttonyloop, on 16 May, 2007 - 11:36 PM, said:

View PostJellyBean, 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.
Was This Post Helpful? 0
  • +
  • -

#5 JellyBean  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 60
  • Joined: 25-April 07

Re: dATABASE ACCESS WITH C#.NET

Post icon  Posted 17 May 2007 - 02:33 AM

Glad you solved it :D

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!
Was This Post Helpful? 0
  • +
  • -

#6 matoksoz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 17-June 07

Re: dATABASE ACCESS WITH C#.NET

Posted 17 June 2007 - 04:04 PM

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 "!=".
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1