SQL Create table error

[infinity4ever]

hi all

i write this code

Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnCreate.Click
Dim SQLCreate As New SqlCommand
Dim Conn As New SqlConnection
Dim DA As New SqlDataAdapter
Dim DS As New Data.DataSet
'Create DB
SQLCreate.CommandText = "CREATE TABLE test "
SQLCreate.Connection = Conn
Conn.ConnectionString = "Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RBD.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True"
DA.SelectCommand = SQLCreate
DA.Fill(DS)

it always gave me error in DA.Fill(DS) i dont know why?

this is the error

Server Error in '/RBD' Application.

Incorrect syntax near 'test'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'test'.

Source Error:

Line 24: Conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RBD.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"Line 25: DA.SelectCommand = SQLCreateLine 26: DA.Fill(DS)Line 27: Line 28: 'Create DBV table and first column
Source File: D:\Documents and Settings\200235102\My Documents\Visual Studio 2005\WebSites\RBD\DBDesign.aspx.vb Line: 26

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'test'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +292 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1020 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +82 DBDesing.btnCreate_Click(Object sender, EventArgs e) in D:\Documents and Settings\200235102\My Documents\Visual Studio 2005\WebSites\RBD\DBDesign.aspx.vb:26 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

anybody knows how to solve it??? i tried alot but i didnt find where the problem is... can anyone help me in it?

[Paladine]

hi all

i write this code

Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnCreate.Click
Dim SQLCreate As New SqlCommand
Dim Conn As New SqlConnection
Dim DA As New SqlDataAdapter
Dim DS As New Data.DataSet
'Create DB
SQLCreate.CommandText = "CREATE TABLE test "
SQLCreate.Connection = Conn
Conn.ConnectionString = "Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RBD.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True"
DA.SelectCommand = SQLCreate
DA.Fill(DS)

it always gave me error in DA.Fill(DS) i dont know why?

this is the error

Server Error in '/RBD' Application.

Incorrect syntax near 'test'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'test'.

Source Error:

Line 24: Conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RBD.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"Line 25: DA.SelectCommand = SQLCreateLine 26: DA.Fill(DS)Line 27: Line 28: 'Create DBV table and first column
Source File: D:\Documents and Settings\200235102\My Documents\Visual Studio 2005\WebSites\RBD\DBDesign.aspx.vb Line: 26

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'test'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +292 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1020 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +82 DBDesing.btnCreate_Click(Object sender, EventArgs e) in D:\Documents and Settings\200235102\My Documents\Visual Studio 2005\WebSites\RBD\DBDesign.aspx.vb:26 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

anybody knows how to solve it??? i tried alot but i didnt find where the problem is... can anyone help me in it?

Hi there, I think I can help.

First, I would highly recommend that you go and learn the basics around the SQL language; i.e. SELECTs, CREATE table statemtns. A good source for beginners is: http://www.w3schools.com/sql/sql_intro.asp

To solve you issue is that error message is telling you what is wrong. Let me explain. What you did in your code was this; you asked the ASP.NET engine (.NET) to go, execute the command CREATE TABLE, which as not correctly written . See the tutorial for more detail, but bascially a correct table statement would be something like this:

CREATE TABLE test 
(
       ID  Integer  NOT NULL,
       ColumnName  VARCHAR(30)
)

This would create the table, with 2 columns, the first column ID, and second one ColumnName, and the datatype for each column is specified.

You didn't provide the command with the Column names or datatypes or constraints. So the first error message was telling you just that, you didn't provide anything it recongnized as a standard SQL statement.

Saying that, this wasn't the first thing that was wrong, the first thing was... you never have the front end (application layer) create the tables in your database. Not good coding practice. As well, you are not using the correct code to even do that task.

As well, lets follow the code a little further:

DA.SelectCommand = SQLCreate
        DA.Fill(DS)

What was done here doesn't even make logical coding sense. You are setting the Select Command of a DataAdapter object to a NON SELECT command.

In SQL there is DML and DDL commands. (See tutorial for full definitions). The SELECTCOMMAND property accepts a DML type command, specificly SELECT (a query command). What you have done is provide a DDL command. The application would error out there.

And even if it hadn't, you then put the command in; FILL the DataAdapter with a Dataset, which you haven't got? Why? Because you have provided a CREATE table statement. By defnition a DATASET (DS in your code) is a subset of your database source data. A Create statement wouldn't yield this.

Hope this makes sense.

I highly recommend you take the time to go through the above tutorial I suggested, and some of the other tutorials (via google) you can find at 4GuysFromRolla, etc sites on ASP.NET 1.0 / 1.1 to get a better understanding and logic to coding.

The ASP.NET website is a good source if you want to learn the latest flavor of ASP.NET ... 2.0 http://www.asp.net/getstarted/default.aspx?tabid=61

:cool::cool:

[infinity4ever]

hi paladine

thx for your replying i appreciate it so much... and thx for your advices i will take them in consideration and i'll go through the tutorials am sure that they will help me alot...

actually what i wanna do is to create a table named (ex. test) and then i have a textbox that i can write the column name and choose the type of the column through option buttons or drop down list.

what i want to know is that possible? can i create a table and add columns to it through the web application that i am trying to make? and if i can what is the correct code for creating the table... i know the code for columns but the problem is with the table it self

[campkev]

when you create the table you have to have at least one column.
so go ahead and find out what you want the first column to be then create it.

to add columns later all you have to do is use the following format

alter table tablename add columname columntype

example

create table Customers (CustomerID int)

alter table Customers add FirstName varchar(25)