Olukayode Oluwole
asked on
Why is my tuple only writing 1 record and failing on a second iteration
I am trying to write 2 records from a tuple in my c# application.
The method only writes 1 records and then complains about the parameter being duplicated
See code below:
The error complaining about the parameter being duplicated happens on the EXecuteScalar line
Please the tuple defined below
See the Error display below
How can i ensure that the parameters are reused on the second and subsequent iterations
The method only writes 1 records and then complains about the parameter being duplicated
See code below:
public void CreateEducationForm(List<Tuple<string, string, string, string, string>> newTuples)
{
//Place to process tuples into the database
// Save Button Click
using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
{
// public.speducation_insert
using (var command = new NpgsqlCommand("public.speducation_writemasterdetail", conn))
//using (var command = new NpgsqlCommand("public.speducation_insert", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(value: new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn01 });
command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn07 });
command.Parameters.Add(new NpgsqlParameter("empidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = ((int)GridVariables.GridColumn08) });
command.Parameters.Add(new NpgsqlParameter("empeduidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.InputOutput, Value = ((int)GridVariables.GridColumn09) });
foreach (Tuple<string, string, string, string, string> tuple in newTuples)
{
// Avoid picking empty row in grid by using if clause
command.Parameters.Add(new NpgsqlParameter("schoolnamex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn02 });
command.Parameters.Add(new NpgsqlParameter("startdatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input, Value = Convert.ToDateTime(GridVariables.GridColumn03) });
command.Parameters.Add(new NpgsqlParameter("enddatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input, Value = Convert.ToDateTime(GridVariables.GridColumn04) });
command.Parameters.Add(new NpgsqlParameter("yearsexpx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = Convert.ToInt32(GridVariables.GridColumn05) });
command.Parameters.Add(new NpgsqlParameter("degreecodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn06 });
// Explicit Date Cast eg ((DateTime)g[0]["MyUntypedDateField"]).ToShortDateString()
int newvar = 0;
//Get newvar just to confirm new id. will use empjobidx to use updates if possible
//int newid = Convert.ToInt32(command.ExecuteNonQuery());
int newid = Convert.ToInt32(command.ExecuteScalar());
newvar = newid;
}
}
}
}
The error complaining about the parameter being duplicated happens on the EXecuteScalar line
Please the tuple defined below
See the Error display below
How can i ensure that the parameters are reused on the second and subsequent iterations
Instead of adding parameters each time through the loop with a value, add the parameters without values outside of the loop, and then inside the loop, update the value of the parameters.
ASKER
Can you please give me a one line example outside and inside the loop.
I seam to be having syntax errors
Thanks
Olukay
I seam to be having syntax errors
Thanks
Olukay
The code should be similar to:
Outside of your foreach loop:
NpgsqlParameter schoolname = new NpgsqlParameter("schoolnam ex", NpgsqlTypes.NpgsqlDbType.V archar) { Direction = ParameterDirection.Input};
command.Parameters.Add(sch oolname);
Inside your foreach loop:
schoolname = GridVariables.GridColumn02 ;
Outside of your foreach loop:
NpgsqlParameter schoolname = new NpgsqlParameter("schoolnam
command.Parameters.Add(sch
Inside your foreach loop:
schoolname = GridVariables.GridColumn02
ASKER
Tried but got this error
Cannot implicitly covert type 'string' to 'NpgsqlParameter'
I have tried to use ToString, ToChar without luck
The variable is defined as a string so i dont understand the error
See attached
Olukay
Conversion-issues.png
Cannot implicitly covert type 'string' to 'NpgsqlParameter'
I have tried to use ToString, ToChar without luck
The variable is defined as a string so i dont understand the error
See attached
[embed=file 1420174]
Olukay
Conversion-issues.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got the syntax right but only 1 of 2 records is still written (see code below)
When i used foreach tuple in Tuples I only got the last record written (This is most likely wrong)
When I tried foreach row in dgvDetailsTable.Rows i got a syntax error because the code is not in the same scope with the grid
Is there a way for me to reference the grid in a method not directly accessible to it
See the attachments
Regards
Olukay
public void CreateEducationForm(List<Tuple<string, string, string, string, string>> newTuples)
{
//Place to process tuples into the database
// Save Button Click
using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
{
// public.speducation_insert
using (var command = new NpgsqlCommand("public.speducation_writemasterdetail", conn))
//using (var command = new NpgsqlCommand("public.speducation_insert", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(value: new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn01 });
command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn07 });
command.Parameters.Add(new NpgsqlParameter("empidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = ((int)GridVariables.GridColumn08) });
command.Parameters.Add(new NpgsqlParameter("empeduidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.InputOutput, Value = ((int)GridVariables.GridColumn09) });
NpgsqlParameter schoolname = new NpgsqlParameter("schoolnamex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
command.Parameters.Add(schoolname);
NpgsqlParameter startdate = new NpgsqlParameter("startdatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input };
command.Parameters.Add(startdate);
NpgsqlParameter enddate = new NpgsqlParameter("enddatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input };
command.Parameters.Add(enddate);
NpgsqlParameter yearsexp = new NpgsqlParameter("yearsexpx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input };
command.Parameters.Add(yearsexp);
NpgsqlParameter degreecode = new NpgsqlParameter("degreecodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
command.Parameters.Add(degreecode);
// foreach (Tuple<string, string, string, string, string> tuple in newTuples)
foreach (DataGridViewRow row in dgvDetailsTable.Rows)
{
schoolname.Value = GridVariables.GridColumn02;
startdate.Value = Convert.ToDateTime(GridVariables.GridColumn03);
enddate.Value = Convert.ToDateTime(GridVariables.GridColumn04);
yearsexp.Value = Convert.ToInt32(GridVariables.GridColumn05);
degreecode.Value = GridVariables.GridColumn06;
int newvar = 0;
// int newid = Convert.ToInt32(command.ExecuteNonQuery());
int newid = Convert.ToInt32(command.ExecuteScalar());
newvar = newid;
}
}
}
}
When i used foreach tuple in Tuples I only got the last record written (This is most likely wrong)
When I tried foreach row in dgvDetailsTable.Rows i got a syntax error because the code is not in the same scope with the grid
Is there a way for me to reference the grid in a method not directly accessible to it
See the attachments
Regards
Olukay
ASKER
At this stage I think the best thing will be to close this thread and
restart another from where we stopped
Thanks for the effort
Olukay
restart another from where we stopped
Thanks for the effort
Olukay
ASKER
Thanks.
Will close thread now and reopen a new thread to
continue with the current issue
Efforts very much appreciated
Will close thread now and reopen a new thread to
continue with the current issue
Efforts very much appreciated