Trying to update record with GUID key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope I'm posting to the proper group, but would greatly apprectiate any
feedback.

When I try the following update query (in ASP.Net using C# linked to
SQL2000) I consistenly get the following error message:

Index (zero based) must be greater than or equal to zero and less than the
size of the argument list.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

After spending hours looking over code I don't see anything wrong. Does
anyone have any ideas???

Thanks for any feedback!

-- Abe
private void UpdateSurvey()
{
SqlConnection con;
SqlCommand cmd;
string sql;

StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();

sb.Append("UPDATE [tblEvaluation] SET ");
// DateReceived is date/time; Q1-9 in Int
sb.Append("DateReceived='{0}',
Q1='{1}',Q2='{2}',Q3='{3}',Q4='{4}',Q5='{5}',Q6='{6}',Q7='{7}',Q8='{8}'");

values.Add(System.DateTime.Now);
//RdoQ1-9 are radiobuttionList controls
values.Add(this.rdoQ1.SelectedValue.ToString());
values.Add(this.rdoQ2.SelectedValue.ToString());
values.Add(this.rdoQ3.SelectedValue.ToString());
values.Add(this.rdoQ4.SelectedValue.ToString());
values.Add(this.rdoQ5.SelectedValue.ToString());
values.Add(this.rdoQ6.SelectedValue.ToString());
values.Add(this.rdoQ7.SelectedValue.ToString());
values.Add(this.rdoQ8.SelectedValue.ToString());
//txtComment1-9 and Comments are txtbox controls
if (txtComment1.Text != string.Empty)
sb.Append(", Q1Comment ='" + this.txtComment1.Text + "'");

if (txtComment2.Text != string.Empty)
sb.Append(", Q2Comment ='" + this.txtComment2.Text + "'");

if (txtComment3.Text != string.Empty)
sb.Append(", Q3Comment ='" + this.txtComment3.Text + "'");

if (txtComment4.Text != string.Empty)
sb.Append(", Q4Comment ='" + this.txtComment4.Text + "'");

if (txtComment5.Text != string.Empty)
sb.Append(", Q5Comment ='" + this.txtComment5.Text + "'");

if (txtComment6.Text != string.Empty)
sb.Append(", Q6Comment ='" + this.txtComment6.Text + "'");

if (txtComment7.Text != string.Empty)
sb.Append(", Q7Comment ='" + this.txtComment7.Text + "'");

if (txtComment8.Text != string.Empty)
sb.Append(", Q8Comment ='" + this.txtComment8.Text + "'");

if (txtComments.Text != string.Empty)
sb.Append(", Comments ='" + this.txtComments.Text + "'");
// EvalGUID is the primary key (It exists)
// response is a variable in '{xx-xx-xx}'
format passed in from URL and captured QueryString(); method.
sb.Append(" WHERE EvalGUID = " + response);
// Here is where I consistently blow up with error above
sql = String.Format(sb.ToString(), values.ToArray());

con = new SqlConnection("data source=localhost; initial
catalog=TCHDataSQL; user id =sa");
cmd = new SqlCommand(sql, con);

con.Open();



cmd.ExecuteNonQuery();
con.Close();

/* on purpose not trapping errors for now!
bool redirect=true;
try
{

}
catch
{
}
finally
{

}
*/
}

}
}
 
1) I would use a Parameters collection to build the INSERT and its
parameters.
2) Never develop with the SA account unless you're building a system
utility.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

AbeR said:
I hope I'm posting to the proper group, but would greatly apprectiate any
feedback.

When I try the following update query (in ASP.Net using C# linked to
SQL2000) I consistenly get the following error message:

Index (zero based) must be greater than or equal to zero and less than the
size of the argument list.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

After spending hours looking over code I don't see anything wrong. Does
anyone have any ideas???

Thanks for any feedback!

-- Abe
private void UpdateSurvey()
{
SqlConnection con;
SqlCommand cmd;
string sql;

StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();

sb.Append("UPDATE [tblEvaluation] SET ");
// DateReceived is date/time; Q1-9 in Int
sb.Append("DateReceived='{0}',
Q1='{1}',Q2='{2}',Q3='{3}',Q4='{4}',Q5='{5}',Q6='{6}',Q7='{7}',Q8='{8}'");

values.Add(System.DateTime.Now);
//RdoQ1-9 are radiobuttionList controls
values.Add(this.rdoQ1.SelectedValue.ToString());
values.Add(this.rdoQ2.SelectedValue.ToString());
values.Add(this.rdoQ3.SelectedValue.ToString());
values.Add(this.rdoQ4.SelectedValue.ToString());
values.Add(this.rdoQ5.SelectedValue.ToString());
values.Add(this.rdoQ6.SelectedValue.ToString());
values.Add(this.rdoQ7.SelectedValue.ToString());
values.Add(this.rdoQ8.SelectedValue.ToString());
//txtComment1-9 and Comments are txtbox controls
if (txtComment1.Text != string.Empty)
sb.Append(", Q1Comment ='" + this.txtComment1.Text + "'");

if (txtComment2.Text != string.Empty)
sb.Append(", Q2Comment ='" + this.txtComment2.Text + "'");

if (txtComment3.Text != string.Empty)
sb.Append(", Q3Comment ='" + this.txtComment3.Text + "'");

if (txtComment4.Text != string.Empty)
sb.Append(", Q4Comment ='" + this.txtComment4.Text + "'");

if (txtComment5.Text != string.Empty)
sb.Append(", Q5Comment ='" + this.txtComment5.Text + "'");

if (txtComment6.Text != string.Empty)
sb.Append(", Q6Comment ='" + this.txtComment6.Text + "'");

if (txtComment7.Text != string.Empty)
sb.Append(", Q7Comment ='" + this.txtComment7.Text + "'");

if (txtComment8.Text != string.Empty)
sb.Append(", Q8Comment ='" + this.txtComment8.Text + "'");

if (txtComments.Text != string.Empty)
sb.Append(", Comments ='" + this.txtComments.Text + "'");
// EvalGUID is the primary key (It exists)
// response is a variable in '{xx-xx-xx}'
format passed in from URL and captured QueryString(); method.
sb.Append(" WHERE EvalGUID = " + response);
// Here is where I consistently blow up with error above
sql = String.Format(sb.ToString(), values.ToArray());

con = new SqlConnection("data source=localhost; initial
catalog=TCHDataSQL; user id =sa");
cmd = new SqlCommand(sql, con);

con.Open();



cmd.ExecuteNonQuery();
con.Close();

/* on purpose not trapping errors for now!
bool redirect=true;
try
{

}
catch
{
}
finally
{

}
*/
}

}
}
Click to expand...
 
I'm new to ASP.NET (I'm a vb/vba/asp developer that is just migrating now to
..NET and C#). I was trying to follow a WROX example, but I think my problem
is that in the Insert statement I am doing a where against a GUID. I don't
know what type of casting to use. Is it a largeInt? I've looked everywhere
and can't find an answer.

I'm trying to email surveys (with the EvalGUID parameter as part of the URL)
and tracking responses for follow-up. I figured that would make it difficult
for folks to "walk" the db.

(2) You're probably right about developing using 'SA' , I was just being
lazy :-(

I'll research the parameters collection.

Thanks for the feedback.

-- Abe

William (Bill) Vaughn said:
1) I would use a Parameters collection to build the INSERT and its
parameters.
2) Never develop with the SA account unless you're building a system
utility.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

AbeR said:
I hope I'm posting to the proper group, but would greatly apprectiate any
feedback.

When I try the following update query (in ASP.Net using C# linked to
SQL2000) I consistenly get the following error message:

Index (zero based) must be greater than or equal to zero and less than the
size of the argument list.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

After spending hours looking over code I don't see anything wrong. Does
anyone have any ideas???

Thanks for any feedback!

-- Abe
private void UpdateSurvey()
{
SqlConnection con;
SqlCommand cmd;
string sql;

StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();

sb.Append("UPDATE [tblEvaluation] SET ");
// DateReceived is date/time; Q1-9 in Int
sb.Append("DateReceived='{0}',
Q1='{1}',Q2='{2}',Q3='{3}',Q4='{4}',Q5='{5}',Q6='{6}',Q7='{7}',Q8='{8}'");

values.Add(System.DateTime.Now);
//RdoQ1-9 are radiobuttionList controls
values.Add(this.rdoQ1.SelectedValue.ToString());
values.Add(this.rdoQ2.SelectedValue.ToString());
values.Add(this.rdoQ3.SelectedValue.ToString());
values.Add(this.rdoQ4.SelectedValue.ToString());
values.Add(this.rdoQ5.SelectedValue.ToString());
values.Add(this.rdoQ6.SelectedValue.ToString());
values.Add(this.rdoQ7.SelectedValue.ToString());
values.Add(this.rdoQ8.SelectedValue.ToString());
//txtComment1-9 and Comments are txtbox controls
if (txtComment1.Text != string.Empty)
sb.Append(", Q1Comment ='" + this.txtComment1.Text + "'");

if (txtComment2.Text != string.Empty)
sb.Append(", Q2Comment ='" + this.txtComment2.Text + "'");

if (txtComment3.Text != string.Empty)
sb.Append(", Q3Comment ='" + this.txtComment3.Text + "'");

if (txtComment4.Text != string.Empty)
sb.Append(", Q4Comment ='" + this.txtComment4.Text + "'");

if (txtComment5.Text != string.Empty)
sb.Append(", Q5Comment ='" + this.txtComment5.Text + "'");

if (txtComment6.Text != string.Empty)
sb.Append(", Q6Comment ='" + this.txtComment6.Text + "'");

if (txtComment7.Text != string.Empty)
sb.Append(", Q7Comment ='" + this.txtComment7.Text + "'");

if (txtComment8.Text != string.Empty)
sb.Append(", Q8Comment ='" + this.txtComment8.Text + "'");

if (txtComments.Text != string.Empty)
sb.Append(", Comments ='" + this.txtComments.Text + "'");
// EvalGUID is the primary key (It exists)
// response is a variable in '{xx-xx-xx}'
format passed in from URL and captured QueryString(); method.
sb.Append(" WHERE EvalGUID = " + response);
// Here is where I consistently blow up with error above
sql = String.Format(sb.ToString(), values.ToArray());

con = new SqlConnection("data source=localhost; initial
catalog=TCHDataSQL; user id =sa");
cmd = new SqlCommand(sql, con);

con.Open();



cmd.ExecuteNonQuery();
con.Close();

/* on purpose not trapping errors for now!
bool redirect=true;
try
{

}
catch
{
}
finally
{

}
*/
}

}
}
Click to expand...
Click to expand...
 
Back
Top