ASP.NET: I am having troubles trying to run a sql script

Question

I have installed Microsoft SQL Server 2014 Express, and I am am learning how to use ASP.NET with Visual Studio Express 2015 for Web to retrieve data from a veterinarian database called vetDatabase_Wizard.

As a beginning, I am retrieving a protocolID from an entered ID and storing the information in a class called clinicalCase. I am 100% new to SQL.

Here is my SQLQuery1.sql script (attempt):

Select * from tblCases 

CREATE PROCEDURE spGetCaseByID
    @ID int
BEGIN
    SELECT caseID, protocolID
    FROM tblCases 
    WHERE caseID = @ID
END

Here is my getCaseByID function calling my SQL script:

[WebMethod]
public clinicalCase getCaseByID(int ID)
{
    // Retrieve connection string
    string cs = ConfigurationManager.ConnectionStrings["vetDatabase_Wizard"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spGetCaseByID", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter parameter = new SqlParameter("@ID", ID);
        cmd.Parameters.Add(parameter);

        clinicalCase cases = new clinicalCase();

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            cases.ID = Convert.ToInt32(reader["ID"]);
            cases.protocolID = Convert.ToInt32(reader["protocolID"]);
        }

        return cases;
    }
}

When I run the function and try it, I get the following error at

SqlDataReader reader = cmd.ExecuteReader();

Could not find the stored procedure "spGetCaseByID".

I suspect it's my SQL syntax or the fact that I added a connection string in my web.config file because I didn't have one:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="vetDatabase_Wizard"
         connectionString="Data Source=JOHNATHANBO431E\SQLEXPRESS2014;Initial Catalog=vetDatabase_Wizard;Integrated Security=True"
         providerName="System.Data.SqlClient" />

  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.6.1"/>
    <httpRuntime targetFramework="4.6.1"/>
    <httpModules>
      <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"/>
    </httpModules>
  </system.web>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701"/>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
    </compilers>
  </system.codedom>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false"/>
    <modules>
      <remove name="ApplicationInsightsWebTracking"/>
      <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web"
        preCondition="managedHandler"/>
  </modules>

  </system.webServer>
</configuration>

I got the name and connectionString from the properties panel of the database.

I would greatly appreciate the community's feedback because I must be missing something obvious! I would like to thank you very much for your time! :)

EDIT: I realized that I need to first execute my SQL script in SQL Server Management Studio before using it in Visual Studio.

However, I get the following error:

SQL80001: Incorrect syntax: 'CREATE PROCEDURE' must be the only statement in the batch. vetApp

Hence, my SQL script is wrong and would appreciate debugging help.

Answer 1

you are trying to run multiple commands in SQL . In order to create stored procedure it should be the first line of code or place go in between the multiple sql command

Select * from tblCases 

GO

CREATE PROCEDURE spGetCaseByID
    @ID int
**AS**
BEGIN
    SELECT caseID, protocolID
    FROM tblCases 
    WHERE caseID = @ID
END

This will help