T
Todd Gilbey
Guest
Description
So I have a C# code which is designed to insert data into an Azure SQL database from a webform. Whenever I run debug programme I get an exception message saying there is a syntax error near the "@Address" field when the debug executes the line of code:
insert.ExecuteNonQuery();
There shouldn't be a syntax error because the code is correct.
The code below was developed to the exact instruction of the following training video:
https://www.youtube.com/watch?v=POWm4EfU9bA
Software Used + Versions
Microsoft Visual Studio Community 2017
Version 15.9.6
VisualStudio.15.Release/15.9.6+28307.344
Microsoft .NET Framework
Version 4.7.03056
Installed Version: Community
Application Insights Tools for Visual Studio Package 8.14.11009.1
Application Insights Tools for Visual Studio
ASP.NET and Web Tools 2017 15.9.04012.0
ASP.NET and Web Tools 2017
ASP.NET Core Razor Language Services 15.8.31590
Provides languages services for ASP.NET Core Razor.
ASP.NET Web Frameworks and Tools 2017 5.2.60913.0
For additional information, visit The Official Microsoft ASP.NET Site
Azure App Service Tools v3.0.0 15.9.03024.0
Azure App Service Tools v3.0.0
Azure Data Lake Node 1.0
This package contains the Data Lake integration nodes for Server Explorer.
Azure Data Lake Tools for Visual Studio 2.3.3000.2
Microsoft Azure Data Lake Tools for Visual Studio
Azure Functions and Web Jobs Tools 15.9.02046.0
Azure Functions and Web Jobs Tools
Azure Stream Analytics Tools for Visual Studio 2.3.3000.2
Microsoft Azure Stream Analytics Tools for Visual Studio
C# Tools 2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Common Azure Tools 1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.
Fabric.DiagnosticEvents 1.0
Fabric Diagnostic Events
JavaScript Language Service 2.0
JavaScript Language Service
Microsoft Azure HDInsight Azure Node 2.3.3000.2
HDInsight Node under Azure Node
Microsoft Azure Hive Query Language Service 2.3.3000.2
Language service for Hive query
Microsoft Azure Service Fabric Tools for Visual Studio 2.4
Microsoft Azure Service Fabric Tools for Visual Studio
Microsoft Azure Stream Analytics Language Service 2.3.3000.2
Language service for Azure Stream Analytics
Microsoft Azure Stream Analytics Node 1.0
Azure Stream Analytics Node under Azure Node
Microsoft Azure Tools 2.9
Microsoft Azure Tools for Microsoft Visual Studio 2017 - v2.9.10730.2
Microsoft Continuous Delivery Tools for Visual Studio 0.4
Simplifying the configuration of Azure DevOps pipelines from within the Visual Studio IDE.
Microsoft JVM Debugger 1.0
Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines
Microsoft Library Manager 1.0
Install client-side libraries easily to any web project
Microsoft MI-Based Debugger 1.0
Provides support for connecting Visual Studio to MI compatible debuggers
Microsoft Visual Studio Tools for Containers 1.1
Develop, run, validate your ASP.NET Core applications in the target environment. F5 your application directly into a container with debugging, or CTRL + F5 to edit & refresh your app without having to rebuild the container.
NuGet Package Manager 4.6.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit NuGet Documentation.
ProjectServicesPackage Extension 1.0
ProjectServicesPackage Visual Studio Extension Detailed Info
ResourcePackage Extension 1.0
ResourcePackage Visual Studio Extension Detailed Info
ResourcePackage Extension 1.0
ResourcePackage Visual Studio Extension Detailed Info
SQL Server Data Tools 15.1.61901.03220
Microsoft SQL Server Data Tools
ToolWindowHostedEditor 1.0
Hosting json editor into a tool window
TypeScript Tools 15.9.20918.2001
TypeScript Tools for Microsoft Visual Studio
Visual Basic Tools 2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Visual F# Tools 10.2 for F# 4.5 15.8.0.0. Commit Hash: 6e26c5bacc8c4201e962f5bdde0a177f82f88691.
Microsoft Visual F# Tools 10.2 for F# 4.5
Visual Studio Code Debug Adapter Host Package 1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio
Visual Studio Tools for Containers 1.0
Visual Studio Tools for Containers
--------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server Management Studio 14.0.17289.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 10.0.17134.1
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.17134.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17134
----------------------------------------------------------------------------------------------------------------------------
MS Azure SQL Web App + SQL
Current Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace StormWeb
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == true)
{
Label1.Text = ("Great job! Data was inserted");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection stormconn = new SqlConnection("Server=tcp:bluecitydb.database.windows.net,1433;Initial Catalog=xxxx;Persist Security Info=False;User ID=xxxx;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
{
SqlCommand insert = new SqlCommand("EXEC dbo.InsertDetails @Fullname @Address @Email", stormconn);
insert.Parameters.AddWithValue("@Fullname", TextBox1.Text);
insert.Parameters.AddWithValue("@Address", TextBox2.Text);
insert.Parameters.AddWithValue("@Email", TextBox3.Text);
stormconn.Open();
insert.ExecuteNonQuery();
stormconn.Close();
if (IsPostBack)
{
TextBox1.Text = "";
}
}
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="StormWeb.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label">Insert Full Name</asp:Label>
<br />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <br />
<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
/****** Object: StoredProcedure [dbo].[InsertDetails] Script Date: 06/02/2019 10:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
ALTER PROCEDURE [dbo].[InsertDetails]
(
-- Add the parameters for the stored procedure here
@Fullname nchar (50),
@Address nchar (50),
@Email nchar (50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
INSERT INTO dbo.tblFullname (Fullname) VALUES (@Fullname)
INSERT INTO dbo.tblFullname (Address) VALUES (@Address)
INSERT INTO dbo.tblFullname (Email) VALUES (@Email)
END
What I’ve tried
Completely recreating the database and stored procedure.
I've taken out the @Address & Email (subsequently been reinserted) fields both on the table, the stored procedure and the HTML document. The debug messages is still telling me I have a syntax error near the @Address field despite there no longer being an address field. It's telling me there is a syntax error near a non-existent field.
Opened up the Client IP address on the Azure SQL Database Portal.
Putting commas between the arguments.
Debug/Diagnostic Messages
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Incorrect syntax near '@Address'.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/2/ROOT-1-131939257896594543): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\PrivateAssemblies\Runtime\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Additional Information
This is making me really angry. If I tell a piece of software do to something it does it, it's not a disobedient child who "cant be bothered" to work today. It's a computer. Doesn't have a mind of it's own so why is it behaving like it is?
I don't want any suggestion to changes in my coding. I have solid evidence that the code works from the training video. I'd like to know why the code works in the training video but not for me.
Continue reading...
So I have a C# code which is designed to insert data into an Azure SQL database from a webform. Whenever I run debug programme I get an exception message saying there is a syntax error near the "@Address" field when the debug executes the line of code:
insert.ExecuteNonQuery();
There shouldn't be a syntax error because the code is correct.
The code below was developed to the exact instruction of the following training video:
https://www.youtube.com/watch?v=POWm4EfU9bA
Software Used + Versions
Microsoft Visual Studio Community 2017
Version 15.9.6
VisualStudio.15.Release/15.9.6+28307.344
Microsoft .NET Framework
Version 4.7.03056
Installed Version: Community
Application Insights Tools for Visual Studio Package 8.14.11009.1
Application Insights Tools for Visual Studio
ASP.NET and Web Tools 2017 15.9.04012.0
ASP.NET and Web Tools 2017
ASP.NET Core Razor Language Services 15.8.31590
Provides languages services for ASP.NET Core Razor.
ASP.NET Web Frameworks and Tools 2017 5.2.60913.0
For additional information, visit The Official Microsoft ASP.NET Site
Azure App Service Tools v3.0.0 15.9.03024.0
Azure App Service Tools v3.0.0
Azure Data Lake Node 1.0
This package contains the Data Lake integration nodes for Server Explorer.
Azure Data Lake Tools for Visual Studio 2.3.3000.2
Microsoft Azure Data Lake Tools for Visual Studio
Azure Functions and Web Jobs Tools 15.9.02046.0
Azure Functions and Web Jobs Tools
Azure Stream Analytics Tools for Visual Studio 2.3.3000.2
Microsoft Azure Stream Analytics Tools for Visual Studio
C# Tools 2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Common Azure Tools 1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.
Fabric.DiagnosticEvents 1.0
Fabric Diagnostic Events
JavaScript Language Service 2.0
JavaScript Language Service
Microsoft Azure HDInsight Azure Node 2.3.3000.2
HDInsight Node under Azure Node
Microsoft Azure Hive Query Language Service 2.3.3000.2
Language service for Hive query
Microsoft Azure Service Fabric Tools for Visual Studio 2.4
Microsoft Azure Service Fabric Tools for Visual Studio
Microsoft Azure Stream Analytics Language Service 2.3.3000.2
Language service for Azure Stream Analytics
Microsoft Azure Stream Analytics Node 1.0
Azure Stream Analytics Node under Azure Node
Microsoft Azure Tools 2.9
Microsoft Azure Tools for Microsoft Visual Studio 2017 - v2.9.10730.2
Microsoft Continuous Delivery Tools for Visual Studio 0.4
Simplifying the configuration of Azure DevOps pipelines from within the Visual Studio IDE.
Microsoft JVM Debugger 1.0
Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines
Microsoft Library Manager 1.0
Install client-side libraries easily to any web project
Microsoft MI-Based Debugger 1.0
Provides support for connecting Visual Studio to MI compatible debuggers
Microsoft Visual Studio Tools for Containers 1.1
Develop, run, validate your ASP.NET Core applications in the target environment. F5 your application directly into a container with debugging, or CTRL + F5 to edit & refresh your app without having to rebuild the container.
NuGet Package Manager 4.6.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit NuGet Documentation.
ProjectServicesPackage Extension 1.0
ProjectServicesPackage Visual Studio Extension Detailed Info
ResourcePackage Extension 1.0
ResourcePackage Visual Studio Extension Detailed Info
ResourcePackage Extension 1.0
ResourcePackage Visual Studio Extension Detailed Info
SQL Server Data Tools 15.1.61901.03220
Microsoft SQL Server Data Tools
ToolWindowHostedEditor 1.0
Hosting json editor into a tool window
TypeScript Tools 15.9.20918.2001
TypeScript Tools for Microsoft Visual Studio
Visual Basic Tools 2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Visual F# Tools 10.2 for F# 4.5 15.8.0.0. Commit Hash: 6e26c5bacc8c4201e962f5bdde0a177f82f88691.
Microsoft Visual F# Tools 10.2 for F# 4.5
Visual Studio Code Debug Adapter Host Package 1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio
Visual Studio Tools for Containers 1.0
Visual Studio Tools for Containers
--------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server Management Studio 14.0.17289.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 10.0.17134.1
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.17134.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17134
----------------------------------------------------------------------------------------------------------------------------
MS Azure SQL Web App + SQL
Current Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace StormWeb
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == true)
{
Label1.Text = ("Great job! Data was inserted");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection stormconn = new SqlConnection("Server=tcp:bluecitydb.database.windows.net,1433;Initial Catalog=xxxx;Persist Security Info=False;User ID=xxxx;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
{
SqlCommand insert = new SqlCommand("EXEC dbo.InsertDetails @Fullname @Address @Email", stormconn);
insert.Parameters.AddWithValue("@Fullname", TextBox1.Text);
insert.Parameters.AddWithValue("@Address", TextBox2.Text);
insert.Parameters.AddWithValue("@Email", TextBox3.Text);
stormconn.Open();
insert.ExecuteNonQuery();
stormconn.Close();
if (IsPostBack)
{
TextBox1.Text = "";
}
}
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="StormWeb.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label">Insert Full Name</asp:Label>
<br />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <br />
<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
/****** Object: StoredProcedure [dbo].[InsertDetails] Script Date: 06/02/2019 10:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
ALTER PROCEDURE [dbo].[InsertDetails]
(
-- Add the parameters for the stored procedure here
@Fullname nchar (50),
@Address nchar (50),
@Email nchar (50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
INSERT INTO dbo.tblFullname (Fullname) VALUES (@Fullname)
INSERT INTO dbo.tblFullname (Address) VALUES (@Address)
INSERT INTO dbo.tblFullname (Email) VALUES (@Email)
END
What I’ve tried
Completely recreating the database and stored procedure.
I've taken out the @Address & Email (subsequently been reinserted) fields both on the table, the stored procedure and the HTML document. The debug messages is still telling me I have a syntax error near the @Address field despite there no longer being an address field. It's telling me there is a syntax error near a non-existent field.
Opened up the Client IP address on the Azure SQL Database Portal.
Putting commas between the arguments.
Debug/Diagnostic Messages
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Incorrect syntax near '@Address'.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/2/ROOT-1-131939257896594543): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\PrivateAssemblies\Runtime\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Additional Information
This is making me really angry. If I tell a piece of software do to something it does it, it's not a disobedient child who "cant be bothered" to work today. It's a computer. Doesn't have a mind of it's own so why is it behaving like it is?
I don't want any suggestion to changes in my coding. I have solid evidence that the code works from the training video. I'd like to know why the code works in the training video but not for me.
Continue reading...