Creating SQL Server Table in C#

Question

I am trying to create in asp.net using c# but i am getting syntax error here is the my code

string connectionstring = "server=AMAN;database=student;Integrated Security=True";
        string table = "CREATE TABLE IF NOT EXISTS details( Admission_no  char(50),First_Name char(50),Father_name char(50),recepit_no char(50),selectdate  datetime,acedemic_year char(50),class1 char(50),section char(50), roll_no char(50);";
         string fee_table = "CREATE TABLE IF NOT EXISTS fees(Admission_no char(50),  prospectues_fee float, registration_fee float,admission_fee float,security_money float,misslaneous_fee float,development_fee float,transport_fair float,computer_fee float,activity float,hostel_fee float,dely_fine float,back_dues float,tution_fee float,other_fee float,total float;";             
        SqlConnection conn = new SqlConnection(connectionstring);
        SqlCommand cmd;
        SqlCommand command;
        try
        {
            conn.Open();

            using (command = new SqlCommand(table, conn)) ;
            using ( cmd = new SqlCommand(fee_table, conn)) ;
            command.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            MessageBox.Show("connection open");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

    }

i am getting exception syantax error near the if

here is error message

The error is in your SQL code, not your C# code. Look up the proper SQL syntax for creating tables. — Maritim, Jul 03 '14 at 06:13
Check here; http://dev.mysql.com/doc/refman/5.1/en/create-table.html — Sander, Jul 03 '14 at 06:19

brainless coder · Answer 1 · 2014-07-03T06:25:27.843

Both your SQL statements missing end parenthesis -

string table = "CREATE TABLE IF NOT EXISTS details(..., roll_no char(50);";
string fee_table = "CREATE TABLE IF NOT EXISTS fees(....,total float;";

Add parenthesis at the end and also forgot that IF NOT EXISTS, is not for MSSQL -

string table = "CREATE TABLE details(.., roll_no char(50));";
string fee_table = "CREATE TABLE fees(...,total float);";

EDIT:

Check your syntax here -

http://msdn.microsoft.com/en-us/library/ms174979.aspx

Still if you are facing error, then mention the error log.

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

CREATE TABLE IF NOT EXISTS

is valid mySQL syntax not T-SQL syntax

see this answer Create Table If Not Exists Equivalent in SQL Server

edited May 23 '17 at 10:26

Community

1
1

answered Jul 03 '14 at 06:23

Stuart

575
2
10

score 0 · Accepted Answer · answered Jul 03 '14 at 06:38

There is a syntax error in your code. User the following syntax for creating the table while checking whther it already exists or not

string table = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'details') AND type in (N'U'))
CREATE TABLE  details( Admission_no  char(50),First_Name char(50),Father_name char(50),recepit_no char(50),selectdate  datetime,acedemic_year char(50),class1 char(50),section char(50), roll_no char(50))
";

string fee_table = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'fees') AND type in (N'U'))
CREATE TABLE fees(Admission_no char(50),  prospectues_fee float, registration_fee float,admission_fee float,security_money float,misslaneous_fee float,development_fee float,transport_fair float,computer_fee float,activity float,hostel_fee float,dely_fine float,back_dues float,tution_fee float,other_fee float,total float) 
";

If your string value spans over multiple lines then you can prefix the value with @. then you dont need to do + for each line. You can try removing @, it will give compilation error — Kiran Hegde, Jul 03 '14 at 06:47

Creating SQL Server Table in C#

3 Answers3