ASP.NET RSS ASP.NET RSS

SQL Create table error

Please support our ASP.NET advertiser: Intel Parallel Studio Home
Closed Thread

•
•
Join Date: Jun 2006
Posts: 22
Reputation: infinity4ever is an unknown quantity at this point 
Solved Threads: 0
infinity4ever infinity4ever is offline Offline
Newbie Poster

SQL Create table error

 
0
  #1
Jun 18th, 2006
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?
Quick reply to this message  
•
•
Join Date: Feb 2003
Posts: 793
Reputation: Paladine has a spectacular aura about Paladine has a spectacular aura about Paladine has a spectacular aura about 
Solved Threads: 27
Team Colleague
Paladine's Avatar
Paladine Paladine is offline Offline
Master Poster

Re: SQL Create table error

 
0
  #2
Jun 19th, 2006
•
•
•
•
Originally Posted by 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?
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:

ASP.NET Syntax (Toggle Plain Text) 
  1. CREATE TABLE test 
  2. (
  3.        ID  Integer  NOT NULL,
  4.        ColumnName  VARCHAR(30)
  5. )
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:
ASP.NET Syntax (Toggle Plain Text) 
  1.         DA.SelectCommand = SQLCreate
  2.         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

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code
Quick reply to this message  
•
•
Join Date: Jun 2006
Posts: 22
Reputation: infinity4ever is an unknown quantity at this point 
Solved Threads: 0
infinity4ever infinity4ever is offline Offline
Newbie Poster

Re: SQL Create table error

 
0
  #3
Jun 19th, 2006
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
Quick reply to this message  
•
•
Join Date: Jul 2005
Posts: 483
Reputation: campkev is an unknown quantity at this point 
Solved Threads: 19
campkev campkev is offline Offline
Posting Pro in Training

Re: SQL Create table error

 
0
  #4
Jun 19th, 2006
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)
Quick reply to this message  
Closed Thread

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP.NET Forum
Thread Tools Search this Thread



.net 2.0 3.5 activexcontrol advice ajax alltypeofvideos asp asp.net bc30451 bottomasp.net browser businesslogiclayer button c# c#gridviewcolumn checkbox click commonfunctions compatible confirmationcodegeneration content contenttype countryselector courier css dataaccesslayer database datagrid datagridview datagridviewcheckbox datalist deadlock development dgv dropdownlist dropdownmenu edit expose flash flv formatdecimal forms formview gridview homeedition iframe iis javascript jquery list listbox login menu microsoft mono mouse mssql multistepregistration nameisnotdeclared news numerical objects order panelmasterpagebuttoncontrols problem radio ratings reportemail rotatepage save schoolproject search security serializesmo.table silverlight smartcard software sql-server sqlserver2005 suse textbox tracking unauthorized validation vb.net video videos virtualdirectory vista visual-studio visualstudio web webapplications webarchitecture webdevelopemnt webprogramming webservice xml youareanotmemberofthedebuggerusers
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC