CODEWITHSUNDEEP
c#asp.netsql-serverdatatable
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to pass Datatable to SQL Server using asp.net

I am working on an asp.net web application where I am passing a DataTable from an asp.net application to SQL Server stored procedure.

My table in SQL Server is 

Student(ID bigint, Name nvarchar(max), Reg bigint).

In this table, the ID is the primary key and auto incremented. My c# code to pass a DataTable to stored procedure on button click is:

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 < 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 > 0)
        {
            lblMsg.Text = "Data Successfully Inserted.";
        }
        else
        {
            lblMsg.Text = "Data Insertion Failed.";
        }
        cnn.Close();

    }
    catch (Exception ex)
    {
        throw (ex);
    }
}

My stored procedure in T-SQL is :

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

I have created a table type as:

CREATE TYPE StudentTableVal AS TABLE
(
    Name NVARCHAR(max),
    Reg bigint
)

But when I click the button to insert DataTable into Student table I get this error:

Error converting data type nvarchar to bigint.
The data for table-valued parameter "@Student" doesn't conform to the table type of the parameter.

Please help me someone.

Upvotes: 0

Views: 2225

Answers (2)

Swapy
Swapy

Reputation: 55

Try Printing the @Student parameter value in the SQL using Print command once you get the complete SQL string that gets generated dynamically try executing the complete sql statement written in the Student_Insert_Update_Delete stored procedure by this you will come to know what is missing in your statement

Upvotes: 0

StefanoGermani
StefanoGermani

Reputation: 741

The only thing I can think of is that it's not mapping the column by name but by index so try to invert the column definition and see what happens:

    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Reg", typeof(long));

Hope it works.

Upvotes: 2

Related Questions