I am trying to create a table with variables from an ASP.NET form but it shows errors every time.
incorrect Syntax near '('
C# Code:
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,Title,TableName,IdTab")] SsmsColumn ssmsColumn, string database, string nomtable)
{
try
{
if (ModelState.IsValid)
{
ssmsColumn.Id = Guid.NewGuid();
_context.Add(ssmsColumn);
string primaryKey;
string noNull;
string colName = ssmsColumn.Title;
string TableName = nomtable;
string datatype = ssmsColumn.DataType;
int? maxChar = ssmsColumn.CharacterMax;
string charM = maxChar.ToString();
bool Key = ssmsColumn.PrimaryKey.HasValue ? ssmsColumn.PrimaryKey.Value : false;
bool nullable = ssmsColumn.IsNullable.HasValue ? ssmsColumn.IsNullable.Value : true;
if (Key)
{ primaryKey = "PRIMARY KEY"; }
else { primaryKey = ""; }
if (nullable)
{ noNull = "null"; }
else { noNull = "Not Null"; }
string sql = " Use " + database + "; CREATE TABLE " + TableName + " (" + colName + " "+datatype+"(" + maxChar + ")" + primaryKey + " " + noNull +");";
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Index));
}
ViewData["IdTab"] = new SelectList(_context.SsmsTable, "Id", "Title", ssmsColumn.IdTab);
DataSet ds = dblayer.Get_Instance();
ViewBag.database = ds.Tables[0];
}
catch (DbUpdateException /* ex */)
{
//Log the error (uncomment ex variable name and write a log.
ModelState.AddModelError("", "Unable to save changes. " +
"Try again, and if the problem persists " +
"see your system administrator.");
}
//DataSet ds = dblayer.Get_Datatype();
//ViewBag.datatype = ds.Tables[0];
//ViewBag.nom = "hello";
ViewBag.Tabname = TempData["Tabname"];
List<SsmsTypeData> datatypelist = new List<SsmsTypeData>();
datatypelist = (from product in _context.SsmsTypeData
select product).ToList();
datatypelist.Insert(0, new SsmsTypeData { Id = 0, Title = "Select" });
ViewBag.ListofType = datatypelist;
return View();
}
My Form in View:
<form asp-action="Create" asp-route-database="@ViewBag.dbname" asp-route-nomtable="@ViewBag.tabName" asp-route-id="@ViewBag.tabid">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
@*<div class="form-group">
<label asp-for="IdTab" class="control-label">
Table
</label>
<select asp-for="IdTab" class="form-control " asp-items="@ViewBag.IdTab" style="width: 100%;"></select>
<span asp-validation-for="IdTab" text-danger"></span>
</div>*@
<div class="form-group">
@*<select asp-for="IdBds" class="form-control" asp-items="ViewBag.IdBds"></select>*@
<input asp-for="IdTab" class="form-control" value="@ViewBag.tabid" type="hidden" />
<span asp-validation-for="IdTab" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Title" class="control-label"></label>
<input asp-for="Title" id="columname" name="columname" class="form-control" />
<span asp-validation-for="Title" class="text-danger"></span>
</div>
<!--<div class="form-group">
<label asp-for="TableName" class="control-label"></label>
<input asp-for="TableName" class="form-control" />
<span asp-validation-for="TableName" class="text-danger"></span>
</div>-->
<div class="form-group">
<label asp-for="IsNullable" class="control-label"></label>
<input type="checkbox" name="isNull" class="form-control" />
<span asp-validation-for="IsNullable" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="DataType" class="control-label"></label>
<!--<input asp-for="DataType" class="form-control" />-->
<select asp-for="DataType" name="dataType"
class="form-control"
asp-items="@(new SelectList(@ViewBag.ListofType,"Id", "Title"))">
--Select--
</select>
<span asp-validation-for="DataType" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="PrimaryKey" class="control-label"></label>
<input type="checkbox" name="primary" class="form-control" />
<span asp-validation-for="PrimaryKey" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="CharacterMax" class="control-label"></label>
<input asp-for="CharacterMax" name="Mchar" class="form-control" />
<span asp-validation-for="CharacterMax" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</form>
and this is my debugger in visual studio it stops on the sql query and it does not create the table :
Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request starting HTTP/1.1 POST http://localhost:44364/SsmsColumns/Create/5779dad1-80ae-4669-8f77-9e08f491f303?database=TestColumn application/x-www-form-urlencoded 314 Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker:Information: Route matched with {action = "Create", controller = "SsmsColumns"}. Executing action pfeProject2020.Controllers.SsmsColumnsController.Create (pfeProject2020) Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker:Information: Executing action method pfeProject2020.Controllers.SsmsColumnsController.Create (pfeProject2020) with arguments (pfeProject2020.Models.SsmsColumn, TestColumn, ) - Validation state: Valid Microsoft.EntityFrameworkCore.Infrastructure:Information: Entity Framework Core 2.1.11-servicing-32099 initialized 'DBManagementContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None Exception levée : 'System.Data.SqlClient.SqlException' dans System.Data.SqlClient.dll Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker:Information: Executed action pfeProject2020.Controllers.SsmsColumnsController.Create (pfeProject2020) in 642.5385ms 'dotnet.exe' (CoreCLR: clrhost) : Chargé 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\2.1.7\System.Diagnostics.StackTrace.dll'. Chargement des symboles ignoré. Le module est optimisé et l'option du débogueur 'Uniquement mon code' est activée. 'dotnet.exe' (CoreCLR: clrhost) : Chargé 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\2.1.7\System.Reflection.Metadata.dll'. Chargement des symboles ignoré. Le module est optimisé et l'option du débogueur 'Uniquement mon code' est activée. 'dotnet.exe' (CoreCLR: clrhost) : Chargé 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\2.1.7\System.IO.MemoryMappedFiles.dll'. Chargement des symboles ignoré. Le module est optimisé et l'option du débogueur 'Uniquement mon code' est activée. Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware:Error: An unhandled exception has occurred while executing the request.
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '('.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at pfeProject2020.Controllers.SsmsColumnsController.Create(SsmsColumn ssmsColumn, String database, String nomt) in C:\Users\21692\Downloads\pfe2020-master\pfe2020-master\Controllers\SsmsColumnsController.cs:line 167
at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at System.Threading.Tasks.ValueTask`1.get_Result()
at ...
The problem is likely to be in this snippet:
" (" + colName + " "+datatype+"(" + maxChar + ")"
This will work for types taking one parameter, such as varchar
or char
, but will be a syntax error for types like int
and date
which don't take any parameters.
You will need to make the parentheses (both of them) conditional on whether or not the data type takes a maxChar
parameter at all.
User contributions licensed under CC BY-SA 3.0