Unable to Save Array values in database
-
Iam Trying to Insert Array values in the database , but first value get inserted but when my loop increments and try to insert the second value iam getting the Error "exSql = {"The variable name '@BezierCusp' has already been declared. Variable names must be unique within a query batch or stored procedure."}Please help me out where iam wrong SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=localhost;Initial Catalog=InkDb;Integrated Security=True; Persist Security Info=True;Packet Size=4096"; string qry = "Insert into beziertable values(@strokeIndex,@cuspIndex,@BezierCusp)"; SqlCommand sqlInsertCmd = new SqlCommand(qry, con); sqlInsertCmd.CommandText = qry; sqlInsertCmd.CommandType = CommandType.Text; int count; int i; sqlInsertCmd.Parameters.AddWithValue("@strokeIndex", mInkoverlay.Ink.Strokes.IndexOf(stroke)); int cus; cus = Convert.ToInt16(stroke.BezierCusps.GetValue(0)); sqlInsertCmd.Parameters.AddWithValue("@cuspIndex", cus); //Using loop to get the all stroke.GetPoint , it will return Array(2dimension) , i want to add all values of array //In the Database In the parameter Bezier Cusp ,but it show me the error that"exSql = {"The variable name '@BezierCusp' has already been declared. Variable names must be unique within a query batch or stored procedure."} for (i = 0; i <= 100; i = i + 1) { val=stroke.GetPoint(i).X; sqlInsertCmd.Parameters.AddWithValue("@BezierCusp",val); sqlInsertCmd.Connection = con; SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter(); sqlDataAdapter3.InsertCommand = sqlInsertCmd; con.Open(); sqlInsertCmd.ExecuteNonQuery(); con.Close(); } Iam unable to find out the Reason my table structure should be like this after Inserting Stroke Index Cusp Index Bezeir Cusp 0 0 1st value of array(X componet) 1 1 2nd value of Array like this it should insert the Record in this manner tell me there any wrong logic iam using to insert the array values in database with regards Genius
-
Iam Trying to Insert Array values in the database , but first value get inserted but when my loop increments and try to insert the second value iam getting the Error "exSql = {"The variable name '@BezierCusp' has already been declared. Variable names must be unique within a query batch or stored procedure."}Please help me out where iam wrong SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=localhost;Initial Catalog=InkDb;Integrated Security=True; Persist Security Info=True;Packet Size=4096"; string qry = "Insert into beziertable values(@strokeIndex,@cuspIndex,@BezierCusp)"; SqlCommand sqlInsertCmd = new SqlCommand(qry, con); sqlInsertCmd.CommandText = qry; sqlInsertCmd.CommandType = CommandType.Text; int count; int i; sqlInsertCmd.Parameters.AddWithValue("@strokeIndex", mInkoverlay.Ink.Strokes.IndexOf(stroke)); int cus; cus = Convert.ToInt16(stroke.BezierCusps.GetValue(0)); sqlInsertCmd.Parameters.AddWithValue("@cuspIndex", cus); //Using loop to get the all stroke.GetPoint , it will return Array(2dimension) , i want to add all values of array //In the Database In the parameter Bezier Cusp ,but it show me the error that"exSql = {"The variable name '@BezierCusp' has already been declared. Variable names must be unique within a query batch or stored procedure."} for (i = 0; i <= 100; i = i + 1) { val=stroke.GetPoint(i).X; sqlInsertCmd.Parameters.AddWithValue("@BezierCusp",val); sqlInsertCmd.Connection = con; SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter(); sqlDataAdapter3.InsertCommand = sqlInsertCmd; con.Open(); sqlInsertCmd.ExecuteNonQuery(); con.Close(); } Iam unable to find out the Reason my table structure should be like this after Inserting Stroke Index Cusp Index Bezeir Cusp 0 0 1st value of array(X componet) 1 1 2nd value of Array like this it should insert the Record in this manner tell me there any wrong logic iam using to insert the array values in database with regards Genius
You should declare the parameters outside the for loop. e.g. change:
sqlInsertCmd.Parameters.AddWithValue("@BezierCusp",val);
to:
sqlInsertCmd.Parameters.Add("@BezierCusp", SqlDbType.whatever); // There are several overloads of this method, pick one that suits
and move it outside of the for loop. Then in its place put
// Note the syntax might not be exact here, I'm doing it from memory, but there are loads of examples on MSDN and elsewhere
sqlInsertCmd.Parameters["@BezierCusp"].Value = val;The error message you are getting is really quite accurate, it is simply telling you that you have already declared @BezierCusp and therefore can't do so again. Which is what your code tries to do on every iteration of the loop, after the first one.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
You should declare the parameters outside the for loop. e.g. change:
sqlInsertCmd.Parameters.AddWithValue("@BezierCusp",val);
to:
sqlInsertCmd.Parameters.Add("@BezierCusp", SqlDbType.whatever); // There are several overloads of this method, pick one that suits
and move it outside of the for loop. Then in its place put
// Note the syntax might not be exact here, I'm doing it from memory, but there are loads of examples on MSDN and elsewhere
sqlInsertCmd.Parameters["@BezierCusp"].Value = val;The error message you are getting is really quite accurate, it is simply telling you that you have already declared @BezierCusp and therefore can't do so again. Which is what your code tries to do on every iteration of the loop, after the first one.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
Thanks for your Prompt Reply sir I have made the changes in the code as you have suggested as shown: SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=localhost;Initial Catalog=InkDb;Integrated Security=True;Persist Security Info=True;Packet Size=4096"; string qry = "Insert into beziertable values(@strokeIndex,@cuspIndex,@BezierCusp)"; SqlCommand sqlInsertCmd = new SqlCommand(qry, con); sqlInsertCmd.CommandText = qry; sqlInsertCmd.CommandType = CommandType.Text; int count; int i; sqlInsertCmd.Parameters.AddWithValue("@strokeIndex", mInkoverlay.Ink.Strokes.IndexOf(stroke)); int cus; cus = Convert.ToInt16(stroke.BezierCusps.GetValue(0)); sqlInsertCmd.Parameters.AddWithValue("@cuspIndex", cus); // I have made the changes by making declaration outside the loop as sqlInsertCmd.Parameters.AddWithValue("@BezierCusp", val);//My loop works acuurate But as my loop execute i<=100 times and it come out of loop and the value which my variable "val" carry will get inserted into the database , but i want all the values of "val" for each iteration till i=0 to i>=100, But the last value of "val" get inserted , i dont want this , i want all the values of array of the Stroke.GetPoint(i).X. Please suggest me the solution to rectify the Problem/// for (i = 0; i <=100 i = i + 1) { val=stroke.GetPoint(i).X; } sqlInsertCmd.Parameters.AddWithValue("@BezierCusp", val); //Only last value which i get in "val" after loop completes get Inserted into Database , but i want all the values to be inserted into database , how i can do this please suggest how should i rectify the problem...// sqlInsertCmd.Connection = con; SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter(); sqlDataAdapter3.InsertCommand = sqlInsertCmd; con.Open(); sqlInsertCmd.ExecuteNonQuery(); con.Close(); Thanks alot sir, waiting for your response with regards Genius
-
Thanks for your Prompt Reply sir I have made the changes in the code as you have suggested as shown: SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=localhost;Initial Catalog=InkDb;Integrated Security=True;Persist Security Info=True;Packet Size=4096"; string qry = "Insert into beziertable values(@strokeIndex,@cuspIndex,@BezierCusp)"; SqlCommand sqlInsertCmd = new SqlCommand(qry, con); sqlInsertCmd.CommandText = qry; sqlInsertCmd.CommandType = CommandType.Text; int count; int i; sqlInsertCmd.Parameters.AddWithValue("@strokeIndex", mInkoverlay.Ink.Strokes.IndexOf(stroke)); int cus; cus = Convert.ToInt16(stroke.BezierCusps.GetValue(0)); sqlInsertCmd.Parameters.AddWithValue("@cuspIndex", cus); // I have made the changes by making declaration outside the loop as sqlInsertCmd.Parameters.AddWithValue("@BezierCusp", val);//My loop works acuurate But as my loop execute i<=100 times and it come out of loop and the value which my variable "val" carry will get inserted into the database , but i want all the values of "val" for each iteration till i=0 to i>=100, But the last value of "val" get inserted , i dont want this , i want all the values of array of the Stroke.GetPoint(i).X. Please suggest me the solution to rectify the Problem/// for (i = 0; i <=100 i = i + 1) { val=stroke.GetPoint(i).X; } sqlInsertCmd.Parameters.AddWithValue("@BezierCusp", val); //Only last value which i get in "val" after loop completes get Inserted into Database , but i want all the values to be inserted into database , how i can do this please suggest how should i rectify the problem...// sqlInsertCmd.Connection = con; SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter(); sqlDataAdapter3.InsertCommand = sqlInsertCmd; con.Open(); sqlInsertCmd.ExecuteNonQuery(); con.Close(); Thanks alot sir, waiting for your response with regards Genius
I think that you have missed the point I was trying to make. So I'll try again. :) First pseudo-code
Setup all parameter(s) *** Note NOT AddWithValue here, just Add. This MUST only be done once, so outside loop ***
start loop
{
set value of parameter to next value from array *** NOTE this has to be done for each value, so inside loop ***
execute command to add/insert record
}now with code from your original post
sqlInsertCmd.Connection = con; <=== Moved out of loop, only need to do it once, not for each iteration
// Create all the parameters for your sqlInsertCmd HERE. If there are more parameters, create them here with Add NOT AddWithValue
SqlParameter ciParam = sqlInsertCmd.Parameters.Add("@cuspIndex", SqlDbType.Int);
SqlParameter bcParam = sqlInsertCmd.Parameters.Add("@BezierCusp", SqlDbType.Int); // I assume it is an int, but change it to the type you need
//Using loop to get the all stroke.GetPoint , it will return Array(2dimension) , i want to add all values of array
//In the Database In the parameter Bezier Cusp ,but it show me the error that"exSql = {"The variable name '@BezierCusp' has already been
// declared. Variable names must be unique within a query batch or stored procedure."}
for (i = 0; i <= 100; i = i + 1)
{
// Set the values of all parameters here
ciParam.Value = Convert.ToInt16(stroke.BezierCusps.GetValue(i)); // <== I have assumed this should be a different value
// for each row. If not remove this line and restore
// your declaration (above)
bcParam.Value = stroke.GetPoint(i).X; <== Set the value of the parameter to the next value from the array
con.Open();
sqlInsertCmd.ExecuteNonQuery(); <== this stores value to database
con.Close();
}I have highlighted code I have changed, or moved. I have had to make some assumptions about data types and how your data works, but I hope you get the idea. Good Luck! :)
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
I think that you have missed the point I was trying to make. So I'll try again. :) First pseudo-code
Setup all parameter(s) *** Note NOT AddWithValue here, just Add. This MUST only be done once, so outside loop ***
start loop
{
set value of parameter to next value from array *** NOTE this has to be done for each value, so inside loop ***
execute command to add/insert record
}now with code from your original post
sqlInsertCmd.Connection = con; <=== Moved out of loop, only need to do it once, not for each iteration
// Create all the parameters for your sqlInsertCmd HERE. If there are more parameters, create them here with Add NOT AddWithValue
SqlParameter ciParam = sqlInsertCmd.Parameters.Add("@cuspIndex", SqlDbType.Int);
SqlParameter bcParam = sqlInsertCmd.Parameters.Add("@BezierCusp", SqlDbType.Int); // I assume it is an int, but change it to the type you need
//Using loop to get the all stroke.GetPoint , it will return Array(2dimension) , i want to add all values of array
//In the Database In the parameter Bezier Cusp ,but it show me the error that"exSql = {"The variable name '@BezierCusp' has already been
// declared. Variable names must be unique within a query batch or stored procedure."}
for (i = 0; i <= 100; i = i + 1)
{
// Set the values of all parameters here
ciParam.Value = Convert.ToInt16(stroke.BezierCusps.GetValue(i)); // <== I have assumed this should be a different value
// for each row. If not remove this line and restore
// your declaration (above)
bcParam.Value = stroke.GetPoint(i).X; <== Set the value of the parameter to the next value from the array
con.Open();
sqlInsertCmd.ExecuteNonQuery(); <== this stores value to database
con.Close();
}I have highlighted code I have changed, or moved. I have had to make some assumptions about data types and how your data works, but I hope you get the idea. Good Luck! :)
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
Thanks alot , it really helped me I really appreciate your Involvement in this forum keep in touch with regards Genius
-
Thanks alot , it really helped me I really appreciate your Involvement in this forum keep in touch with regards Genius
Thanks! My pleasure. :)
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”