title: How to pass Datatable to SQL Server using asp.net
body: <p>I am working on an asp.net web application where I am passing a <code>DataTable</code> from an asp.net application to SQL Server stored procedure. </p>
  
  <p>My table in SQL Server is </p>
  
  <pre><code>Student(ID bigint, Name nvarchar(max), Reg bigint).
  </code></pre>
  
  <p>In this table, the <code>ID</code> is the primary key and auto incremented. My c# code to pass a <code>DataTable</code> to stored procedure on button click is:</p>
  
  <pre><code>protected void btnSubmit_Click(object sender, EventArgs e)
  {
      try
      {
          DataTable dt = new DataTable("Student");
  
          dt.Columns.Add("Reg", typeof(long));
          dt.Columns.Add("Name", typeof(string));
  
          // Create a new row
          for (int i = 0; i &lt; 3; i++)
          {
              DataRow NewRow = dt.NewRow();
  
              if (i == 0)
              {
                  NewRow["Name"] = "Raunak";
                  NewRow["Reg"] = Convert.ToInt64(1); ;
              }
              if (i == 1)
              {
                  NewRow["Name"] = "Vikash";
                  NewRow["Reg"] = Convert.ToInt64(1);
              }
              if (i == 2)
              {
                  NewRow["Name"] = "Deepak";
                  NewRow["Reg"] = Convert.ToInt64(1);
              }
  
              dt.Rows.Add(NewRow);
          }
  
          dt.AcceptChanges();
  
          string constring = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
          SqlConnection cnn = new SqlConnection(@"Data Source=.\sqlexpress; Initial Catalog=builderERP; Integrated Security=True;");           
  
          SqlCommand selectCommand = new SqlCommand("Student_Insert_Update_Delete", cnn);
          selectCommand.CommandType = CommandType.StoredProcedure;
          SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@Student", dt);
          tvpParam.SqlDbType = SqlDbType.Structured;
  
          cnn.Open();
  
          int xx = selectCommand.ExecuteNonQuery();
  
          if (xx &gt; 0)
          {
              lblMsg.Text = "Data Successfully Inserted.";
          }
          else
          {
              lblMsg.Text = "Data Insertion Failed.";
          }
          cnn.Close();
  
      }
      catch (Exception ex)
      {
          throw (ex);
      }
  }
  </code></pre>
  
  <p>My stored procedure in T-SQL is :</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Student_Insert_Update_Delete]
  (
      @Student AS [dbo].[StudentTableVal] Readonly
  )
  AS
  BEGIN   
  INSERT INTO Student(Name,Reg) 
          SELECT Name,Reg FROM @Student
          --commit transaction
  
   END
  </code></pre>
  
  <p>I have created a table type as:</p>
  
  <pre><code>CREATE TYPE StudentTableVal AS TABLE
  (
      Name NVARCHAR(max),
      Reg bigint
  ) 
  </code></pre>
  
  <p>But when I click the button to insert <code>DataTable</code> into <code>Student</code> table I get this error:</p>
  
  <blockquote>
    <p>Error converting data type nvarchar to bigint.<br>
    The data for table-valued parameter "@Student" doesn't conform to the table type of the parameter.</p>
  </blockquote>
  
  <p>Please help me someone.</p>