Click here to Skip to main content
15,908,834 members
Please Sign up or sign in to vote.
1.89/5 (3 votes)
See more:
How do I add a class item to a database?

I am getting an error in "@col1" in "classlist[n].col1".
C#
public class Class1
{
    public Int32 id { get; set; }
    public string col1 { get; set; }
    public string col2 { get; set; }
    public string col3 { get; set; }
}


C#
List<Class1> classlist = new List<Class1>();
classlist.Add(new Class1() { id = 1, col1 = "11", col2 = "22", col3="33" });
classlist.Add(new Class1() { id = 2,  col1 = "44", col2 = "55", col3="66" });
classlist.Add(new Class1() { id = 3,  col1 = "77", col2 = "88", col3="99" });
string connStr = ConfigurationManager.ConnectionStrings["MDFdb"].ConnectionString;
string cmdStr = "INSERT INTO [Table1] (col1,col2,col3) VALUES (@col1,@col2,@col3);";
for (int n = 1; n <= classlist.Count; n++)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Add("@col1", classlist[n].col1);
                cmd.Parameters.Add("@col2", classlist[n].col2);
                cmd.Parameters.Add("@col3", classlist[n].col3);
                conn.Close();
                conn.Dispose();
            }
        }
    }
    catch (Exception ex)
    {
        Label1.Text=ex.ToString();
    }
}


Error Code:
System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@col1". at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ClassToMDF._Default.Button1_Click(Object sender, EventArgs e) in C:\Users\User\path\Default.aspx.cs:line 36 ClientConnectionId:e373b91f-0471-4487-9667-bed0597b6391 
Posted
Comments
[no name] 26-Sep-14 15:53pm    
That is because you are executing your query before you add your parameters.... still... again.
Teledextri 26-Sep-14 16:44pm    
If someone can show me a better way I will accept your solution!

1 solution

Try serializing the class into a xml or Json String and save that to your Database.

Here is a good article
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900