none
Cannot add data to Azure SQL Database from website form RRS feed

  • Question

  • Is there a valid reason as to why I can't add data to my SQL Database from my web application despite doing everything correctly? I'm currently training in MySQL & C# & have been using the following training video:

    https://www.youtube.com/watch?v=POWm4EfU9bA

    I'm using MS Visual Studio and MS SQL Server Management Studio, I'm also using Azure Web + SQL service and I have opened up the firewall allowing my client IP address through. 

    Here is my coding which "apparently" adds data to my SQL database from the form located in my front end web application:

    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 xxxxxx
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
           
    
            protected void Page_Load(object sender, EventArgs e)
            {
                if (Page.IsPostBack == true)
                {
                    Label1.Text = ("Great job!");
                    
                }
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                SqlConnection stormconn = new SqlConnection("Server=tcp:xxxx.database.windows.net,1433;Initial Catalog=xxxx;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
                {
                    SqlCommand insert = new SqlCommand("EXEC dbo.InsertFullname @Fullname", stormconn);
                    insert.Parameters.AddWithValue("@Fullname", TextBox1.Text);
                 
    
                    stormconn.Open();
                    insert.ExecuteNonQuery();
                    stormconn.Close();
    
                    if (IsPostBack)
                    {
                        TextBox1.Text = ("");
                    }
                }
            }
        }
    }


    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="bluecitywebapplication.WebForm1" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">   
            <asp:Label ID="Label1" runat="server" Text="Label">Type your full name here</asp:Label>
            <br />
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <input type="submit" value="submit" />
        </form>    
    </body>
    </html>
    /****** Object:  StoredProcedure [dbo].[InsertFullname]    Script Date: 03/02/2019 09:20:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      <Author, , Name>
    -- Create Date: <Create Date, , >
    -- Description: <Description, , >
    -- =============================================
    ALTER PROCEDURE [dbo].[InsertFullname]
    (
        -- Add the parameters for the stored procedure here
        @Fullname nchar(30)
    )
    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)
    	
    END

    This is also the last line of the diagnostics section:

    The program '[17620] iisexpress.exe' has exited with code -1 (0xffffffff)
    Is there any reason as to why my coding isn't doing what I'm programming it to do?
    Thanks

    Todd





    Monday, February 4, 2019 9:00 AM

All replies

  • Hi Todd Gilbey,

    Thank you for posting here.

    Since your question is more related to Web form, you could post a new thread in ASP.NET forum for suitable support.

    https://forums.asp.net/

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 4, 2019 1:10 PM
  • MySQL has its own client drivers, which you seem to be using MS SQL Server client drivers.

    And as mentioned, ASP.NET can be discussed at the ASP.NET forums, like the Getting Started Forum.

    Monday, February 4, 2019 1:31 PM
  • You appear to be using SQL Server, not MySQL. I believe that because you're using the SQL Server client, the query string you posted is a SQL query string and you mentioned using Azure SQL which would most likely mean you're using the SQL Server version in Azure.

    One of the problems I see is that your command is incorrect. You're trying to execute a stored procedure but calling it like a regular command. I suspect your call is failing.

    using (SqlConnection stormconn = new SQlConnection(…))
    {
       using (SqlCommand insert = new SqlCommand("InsertFullName", stormconn))
       {
          insert.CommandType = CommandType.StoredProcedure;
          insert.Parameters.AddWithValue("@fullName", TextBox1.Text);
    
          stormconn.Open();
          insert.ExecuteNonQuery();
       }
    };
    Set a breakpoint on that ExecuteNonQuery, verify the TextBox1.Text value is set and execute the command. If it fails you'll get an error otherwise it should be successful. If you don't see the data then verify your connection string. Also ensure that your view of the data in Azure is up to date.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, February 4, 2019 2:57 PM
  • Thanks but the second

    `CommandType`

    isn't recognised. Also, how can my command be wrong when I've taken it straight from a training video?

    I am so sick to the back teeth of this, nothing I do works despite copying somebody else who does. I'm so tired of wasting my time on stupidly-programmed software and it makes me so angry! 

    • Edited by Todd Gilbey Monday, February 4, 2019 3:40 PM Added information
    Monday, February 4, 2019 3:36 PM
  • CommandType is defined in System.Data. If you have a red squiggly under the CommandType and it says that it cannot find the identifier then you're missing a using statement. If you're using VS 2017+ then click the name to bring up the quick action icon (or right click and select Quick Actions) then select the option to add a `using System.Data` and it'll add the namespace. Then try compiling again.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, February 4, 2019 3:52 PM
  • Right, I;m sorry but I;ve just inserted your code, put in my connection string and all I get is error after error - 38 errors to be precise. WHY is this working for you and not for me?! I'm getting really pissed off now!


    • Edited by Todd Gilbey Monday, February 4, 2019 3:55 PM Corrected number of errors.
    Monday, February 4, 2019 3:53 PM
  • Thats fair enough, but please see new comment with pic. 
    Monday, February 4, 2019 3:54 PM
  • You didn't put your connection string in quotes. String literals in C# must be enclosed in double quotes. A connection string is a string so you wrap it. Ideally the connection string would be in a config file instead but for now you can simply store it in your code. 

    //TODO: Put your connection string here
    var connString = "Server=tcp:xxxx.data;Database=MyDatabase;User Id=User;Password=Password";
    
    try
    {
       using (SqlConnection stormconn = new SqlConnection(connString))
       using (SqlCommand insert = new    SqlCommand("InsertFullName", stormconn))
       {
          insert.CommandType = CommandType.StoredProcedure;
          insert.Parameters.AddWithValue("@fullName", TextBox1.Text);
    
          stormconn.Open();
          insert.ExecuteNonQuery();
       }
    } catch (Exception e)
    {
       //TODO: This line will get called if an error occurs while trying to connect and run the command - set a breakpoint here to more easily see what the error is so you can post it back to the forums
       var error = e;
    
       //Will rethrow the exception so normal error processing occurs
       throw;
    };

    I added a try-catch around your code. Since you're learning C# this will make it a little easier to see what is going on. Put a breakpoint (F9 on line or click in gray margin on left) on the line after the TODO in the catch statement. If that breakpoint is hit when run then an error occurred. `e` will tell you what error, the message and the callstack which is what we need to diagnose the issue.

    Finally, remember that you started the post mentioning you were learning with MySQL. You need to confirm if you're using MySQL or SQL Server. They are different and you need to be using the right connection objects for them. SQL Server uses the System.Data.SqlClient namespace and the SqlConnection type.

    MySQL requires that you download from NuGet the MySQL provider. 

    1. Right click the project and select Manage NuGet Packages. 
    2. On the Browse tab enter MySQL.Data.
    3. When the package appears click the Install button to add it to the project.
    4. Every place you're using Sql- (e.g. SqlConnection, SqlCommand) replace it with MySQL.
    5. Remove the using statement (at the top of the file) for System.Data.SqlClient.
    6. Add the using statement for MySql.Data.
    7. Verify the connection string matches the documentation given here.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, February 4, 2019 4:11 PM
  • OK well I'm using the SQL server, NOT MySQL
    Monday, February 4, 2019 4:36 PM
  • So now the latest bullshit from this piece of shit is that it now apparently doesn't recognise 

    stormconn

    We're clearly working on two different versions of the same software. I'm getting all these suggestions and none of them work!! 

    I'm working on Visual Studio 2017 15.9.6 and the version of Server Management Studio is 17.9.1 plus the following;

    • 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

    I'm literately about to start screaming at this thing, I'm so sick of ripping my hear out just because whoever developed this software is a complete imbecilic moron.

    Monday, February 4, 2019 4:46 PM
  • stormconn is the variable that is created in that using statement in the code you originally posted. The code I posted was a modification of yours to use the same variable. Can you please post the updated version of your code along with the error message(s) you're getting from the compiler?

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, February 4, 2019 4:58 PM
  • protected void Button1_Click(object sender, EventArgs e)
            {
                // TODO: Put your connection string here
    var connString = "Server=tcp:bluecitydb.data;Database=MyDatabase;User Id=xxx;Password=xxx";
    
                try
                {
                    using (SqlConnection stormconn = new SqlConnection(connString))
                    using (SqlCommand insert = new SqlCommand("InsertFullName", stormconn))
                    {
                        insert.CommandType = System.Data.CommandType.StoredProcedure;
                        insert.Parameters.AddWithValue("@fullName", TextBox1.Text);
    
                        stormconn.Open();
                        insert.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    //TODO: This line will get called if an error occurs while trying to connect and run the command - set a breakpoint here to more easily see what the error is so you can post it back to the forums
                    var error = e;
    
                    //Will rethrow the exception so normal error processing occurs
                    throw;
                };
            }
    I'm getting the error CS0136 - it's not liking the "e" I've changed this to "err"

    I've added a breakaway to the 
    var error = err;

    line. The result from this from debugging is illustrated 
    When I stop the debugger, I get the following:

    The program '[23572] iisexpress.exe' has exited with code -1 (0xffffffff).





    • Edited by Todd Gilbey Tuesday, February 5, 2019 11:13 AM Added diagnosics message.
    Tuesday, February 5, 2019 10:11 AM
  • "I'm getting the error CS0136 - it's not liking the "e" I"

    That's because you have multiple variables in the same scope with the name 'e'. The catch declares the variable 'e' to hold the current error. But you're in an event handler method and the defacto signature uses e for the EventArgs parameter as well. Hence the compiler sees two 'e' variables and doesn't know which to choose. Changing either to a different name resolves the issue.

    Set a breakpoint at the top of your Button1_Click function. Then run your code. It appears this is a handler for Button1 so click Button1 and it should call your method. If it doesn't hit the breakpoint then you haven't hooked up the event handler properly.

    1. Open the form in the designer.
    2. Open the Properties window if it is not yet open (F4 is the default).
    3. Click Button1 to select it.
    4. In the Properties window switch to the Events tab (toolbar) if you haven't already.
    5. Verify your Button1_Click method is linked to the Click event. If not then select it from the list.

    Once the breakpoint is hit step through the code line by line (F10) to execute each line. Verify the code is behaving the way you expect. After the ExecuteNonQuery returns then the data has been sent to the database. If it is still not there then it isn't an issue with the method but something else.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 5, 2019 2:47 PM
  • OK, this may help you, I've executed each piece of code line by like and here's what I found:

    Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
    The thread 0x3c10 has exited with code 0 (0x0).
    Exception thrown: 'System.Data.SqlClient.SqlException' in bluecitywebapplication.dll
    An exception of type 'System.Data.SqlClient.SqlException' occurred in bluecitywebapplication.dll but was not handled in user code
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)

    Maybe in danger of actually getting somewhere here. 

    Tuesday, February 5, 2019 5:01 PM
  • OK, the exception is from SQL. The error itself is 

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. "

    That means the server/database you're trying to connect to doesn't exist. Can you connect to this DB from SSMS? I notice in your connection string you're using an TCP IP address. For a local server this may work but for something in Azure I would expect it wouldn't. You'll need to get the correct connection string for your SQL Server in Azure. You should be able to do this from the Azure Portal. Once you've got the correct server information and can connect to it from SSMS then you should be able to put the server name into your connection string and try again.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 5, 2019 5:18 PM
  • Thats fine, I'll try that in a minute, The Azure portal connection string is featured there and is a simple "copy and paste" - if this is incorrect then Azure are to blame and they're going to get an earful. Would this be the ADO.NET string?

    But why all of a sudden my Step Into command has decided to be an arse. I run the debugger, it stops at the breakpoint as it should, it shows the yellow arrow where it shows the next line about to be executed.

    Why when I press F11 does the yellow arrow disappear and doesn't (wow, look at that, MS Visual Studio behaving like a disobedient fucking child! Shocker!) execute the next line of code? 

    If it's not one issue it's another...


    • Edited by Todd Gilbey Tuesday, February 5, 2019 5:31 PM Additional question.
    Tuesday, February 5, 2019 5:27 PM
  • Use F10, not F11. F10 is step over which steps through your code line by line. F11 is step into. If you are on a function call then it'll try to step into that function. If it isn't your function then you'll end up somewhere you probably don't want to be.

    As for the Azure connection I recommend you try connecting to the database using SSMS. The instructions are here. If that doesn't work then you have a security issue that you'll have to resolve. For questions related to connecting to SQL Azure you'll want to post over in their forums.

    Since you're new to C# and VS I might recommend that you start by using the local SQL instance that comes with VS first. This will let you get your code and VS figured out without Azure. Of course you'll have to restore whatever database you're trying to use in Azure to the local database instance but that isn't too hard. Once you've gotten your code working locally then you can try getting it talking to Azure.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 5, 2019 5:45 PM
  • For fuck sake! I press F10 and its the same fucking result! It toggles straight to the File menu (I googled this & I'm not the only one to have this fucking issue - it's clearly a programme bug). 

    Tell me serious question, how can Microsoft, a multi-million dollar company release a product onto the market that never does a fucking thing you want it to? MSVS is a complete fucking joke and the stupidity of the developers is beyond me. Whoever signed this off is a total fucking moron.

    I followed a training video and it worked first time, WHY is this working for the guy in the video and not for me? WHY? I am so angry right now I could throw this computer against the fucking wall. 

    While I appreciate your suggestion, I'm sorry but its a fucking computer, If I tell to do something, it does it. It's a computer, not a disobedient child who "can't be bother to do as it's told" today. I should not have to waste my time ripping my hair out just because some retard at Microsoft has released a piece of shit which they called MS Visual Studio! 

    I'm sorry but if I copy an instructor I should have the exact same result, or am I just using my common sense here?


    • Edited by Todd Gilbey Tuesday, February 5, 2019 6:01 PM typo error
    Tuesday, February 5, 2019 5:59 PM
  • I'm sorry you're having issues with VS. Many people use VS every day without any issues. There can be any # of reasons why you may be having issues but we aren't really going to be able to help you in the forums for this. You'll need to use Report a Problem in VS if you believe you have found an issue with VS. MS can then have someone look at your specific issue.

    As for whether a training video should work on your machine or not depends upon how old it is and how well the instructor set up the prereqs. If it is cloud-based then that tech changes rapidly so a video even a year ago is already likely out of date. You'd need to contact the instructor for issues with the training video.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 5, 2019 6:30 PM
  • I'm sorry you're having issues with VS. Many people use VS every day without any issues. There can be any # of reasons why you may be having issues but we aren't really going to be able to help you in the forums for this. You'll need to use Report a Problem in VS if you believe you have found an issue with VS. MS can then have someone look at your specific issue.

    As for whether a training video should work on your machine or not depends upon how old it is and how well the instructor set up the prereqs. If it is cloud-based then that tech changes rapidly so a video even a year ago is already likely out of date. You'd need to contact the instructor for issues with the training video.


    Michael Taylor http://www.michaeltaylorp3.net

    Yes well there is obviously a bug in the programme if I'm not the only one with these issues. I'm doing everything correctly- so it's a system error and not me. 

    Also, yes I can connect it to SSMS -shockingly the one thing that actually work on this pile of shit!

    • Edited by Todd Gilbey Wednesday, February 6, 2019 12:06 PM Answered a previius question
    Wednesday, February 6, 2019 12:04 PM
  • I'm sorry you're having issues with VS. Many people use VS every day without any issues. There can be any # of reasons why you may be having issues but we aren't really going to be able to help you in the forums for this. You'll need to use Report a Problem in VS if you believe you have found an issue with VS. MS can then have someone look at your specific issue.

    As for whether a training video should work on your machine or not depends upon how old it is and how well the instructor set up the prereqs. If it is cloud-based then that tech changes rapidly so a video even a year ago is already likely out of date. You'd need to contact the instructor for issues with the training video.


    Michael Taylor http://www.michaeltaylorp3.net

    Yes well there is obviously a bug in the programme if I'm not the only one with these issues. I'm doing everything correctly- so it's a system error and not me. 

    Also, yes I can connect it to SSMS -shockingly the one thing that actually work on this pile of shit!

    No, you're not doing everything correctly here, becuase if you were, then the program would be working. It comes down to debugging the program and having the expertise to debug and correct the program. Many times,  code from a tutorial doesn't work out the gate, and one has to debug the program and find out what the problem is and correct the problem, if possible. Sometimes,  tutorial code cannot be fixed.

    However, just becuase you can connect to the database using SSMS doesn't mean that you have MS SQL Server setup correctly for connections from a program you developed,   or the configuration of the connectionstring used by the program is not right. Those are the issues that you must investigate as to why the program cannot connect to the database using MS SQL Server.

    Maybe, the below forum is where you need to post.

    https://social.msdn.microsoft.com/Forums/en-US/home?category=windowsazureplatform

    Wednesday, February 6, 2019 1:03 PM
  • DA924, excuse me, but I AM coding correctly. I know that because I've just solved the problem - with no thanks to anyone on here - with my code.

    Also, there have been reports of bugs in VS' debugging programme, the "Step Over" feature doesn't debug line by line but just reverts to selecting the "File" menu.

    I'm sorry but as far as I'm concerned, as long as the degugger comes up with no errors, and the connection string in the correct one - which it was - then it should work. A computer is an inanimate object, its not a disobedient awkward child that decided it's not going to do as it's told today. If I tell a piece of software to do something, it either does it or gets smashed against the wall repeatedly.

    There was no logic whatsoever as to why my software didn't do as it was told but I guess I'm just going to have to live with the fact that MS have programmed the software to behave like a spoilt little brat. By the way, I have severe anger management issues - I'm the user, I'm in charge of the software, what I say goes. No error messages, or arguments, no opinions. 

    End of story!

    Thursday, February 7, 2019 11:07 AM
  • There was no logic whatsoever as to why my software didn't do as it was told but I guess I'm just going to have to live with the fact that MS have programmed the software to behave like a spoilt little brat. By the way, I have severe anger management issues - I'm the user, I'm in charge of the software, what I say goes. No error messages, or arguments, no opinions. 

    I find this hard to believe. A lot of job boards are looking for Azure developers now of days. So, Azure development can't as bad or giving these kind of issues in developing enterprise level solutions using  the Azure Web hosting platform. 

    Also, I have been using VS starting with VS6 COM. I have had a couple of issues with the debugger loosing the debug symbols and missing breakpoints and thing that nature over the years, which were easily correctable by just doing a clean and rebuild. I also have nave ever had a situation where code didn't do what I had programmed it to do either.   

    All I can say is hang in there and keep trying. 

    Thursday, February 7, 2019 12:26 PM
  • There was no logic whatsoever as to why my software didn't do as it was told but I guess I'm just going to have to live with the fact that MS have programmed the software to behave like a spoilt little brat. By the way, I have severe anger management issues - I'm the user, I'm in charge of the software, what I say goes. No error messages, or arguments, no opinions. 

    I find this hard to believe. A lot of job boards are looking for Azure developers now of days. So, Azure development can't as bad or giving these kind of issues in developing enterprise level solutions using  the Azure Web hosting platform. 

    Also, I have been using VS starting with VS6 COM. I have had a couple of issues with the debugger loosing the debug symbols and missing breakpoints and thing that nature over the years, which were easily correctable by just doing a clean and rebuild. I also have nave ever had a situation where code didn't do what I had programmed it to do either.   

    All I can say is hang in there and keep trying. 

    That's great, so now you've acknowledged that there are issues with the debugger, is there not room for the possibility that Visual Studio was released with other bugs in it? I'm even getting error messages saying "Null entries not allowed" despite all the fields in my form having data in them - again, its an unwarranted error message & it absolutely infuriates me. Here's the thread and see if you or anyone else can shed some light on this piece of shit...

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/572ba28f-7e15-45ba-ba51-aad0d2b73d98/visual-studio-quotcannot-insert-the-value-null-into-columnquot-messages-despite-the-column?forum=vsdebug#572ba28f-7e15-45ba-ba51-aad0d2b73d98

    Regards.

    Thursday, February 7, 2019 3:06 PM
  • When was the last time you have done a VS update? When VS notifies that an updates exist, I rereview the updates and decide if I want an update. There has only been a couple I have turned down an update that had nothing in VS I was using.

    Just like the Windows O/S updates, one must stay current with VS updates that are applicable.

    Thursday, February 7, 2019 3:41 PM