.NET Newbie: Why is this SQL Statement Failing in C#?

Guest · Jan 8, 2004

I'm having a devil of a time getting the following to run and was wondering if someone could see why. Seems to be related to data type I'm sure, but new to .NET and C# so am in head banging mode.

The connection is good. Am trying to run a Stored Procedure. Error that comes up is 'Additional information: Input string was not in a correct format'. Note that it appears to fail on setting the cbPosting which is a checkbox.

Appreciate another set of eyes and obviously more experience than I currently have! 8-)

Thanks...Bobbo

SqlCommand myInsertCommand = mySqlConn.CreateCommand();
myInsertCommand.CommandText =
"EXECUTE Documents_Ins @Name, @State, @SubServeInCountyDays, " +
"@SubServeOutCountyDays, @PersServeInCountyDays, @PersServeOutCountyDays, " +
"@FileProofDays, @cbPersService, @cbDueDiligence, @DueDiligenceAttempts, " +
"@cb_Posting, @PostingAttempts, @cb_NotaryRequired, @cb_NoSunday, @DaysFromFile, " +
"@SubServeAge, @FileFee, @ServiceInstructions, @AdditionalRequiredDoc";

myInsertCommand.Parameters.Add("@DocName",SqlDbType.VarChar, 80, "DocName").Value = this.txtDocName.Text;
myInsertCommand.Parameters.Add("@State",SqlDbType.VarChar, 2, "State").Value = this.txtState.Text;
myInsertCommand.Parameters.Add("@SubServeInCountyDays",SqlDbType.SmallInt, 0, "SubServeInCountyDays").Value = SqlInt16.Parse(this.txtSubServeIn.Text);
myInsertCommand.Parameters.Add("@SubServeOutCountyDays",SqlDbType.SmallInt, 0, "SubServeOutCountyDays").Value = SqlInt16.Parse(this.txtSubServeOut.Text);
myInsertCommand.Parameters.Add("@PersServeInCountyDays",SqlDbType.SmallInt, 0, "PersServeInCountyDays").Value = SqlInt16.Parse(this.txtPersonalIn.Text);
myInsertCommand.Parameters.Add("@PersServeOutCountyDays",SqlDbType.SmallInt, 0, "PersServeOutCountyDays").Value = SqlInt16.Parse(this.txtPersonalOut.Text);
myInsertCommand.Parameters.Add("@FileProofDays",SqlDbType.SmallInt, 0, "FileProofDays").Value = SqlInt16.Parse(this.txtFileProof.Text);
myInsertCommand.Parameters.Add("@cbPersService",SqlDbType.Bit, 0, "cbPersService").Value = SqlBoolean.Parse(this.cbPersonalServeReq.Checked);
myInsertCommand.Parameters.Add("@cbDueDiligence",SqlDbType.Bit, 0, "cbDueDiligence").Value = SqlBoolean.Parse(this.cbDueDiligence.Checked);
myInsertCommand.Parameters.Add("@DueDiligenceAttempts",SqlDbType.SmallInt, 0, "DueDiligenceAttempts").Value = SqlInt16.Parse(this.txtDueDilAttempts.Text);
myInsertCommand.Parameters.Add("@cbPosting",SqlDbType.Bit, 0, "cbPosting").Value = SqlBoolean.Parse(this.cbPosting.Checked);
myInsertCommand.Parameters.Add("@PostingAttempts",SqlDbType.SmallInt, 0, "PostingAttempts").Value = SqlInt16.Parse(this.txtPostingAttempts.Text);
myInsertCommand.Parameters.Add("@cbNotaryRequired",SqlDbType.Bit, 0, "cbNotaryRequired").Value = SqlBoolean.Parse(this.cbNotaryReq.Checked);
myInsertCommand.Parameters.Add("@cbNoSunday",SqlDbType.Bit, 0, "cbNoSunday").Value = SqlBoolean.Parse(this.cbNoSunday.Checked);
myInsertCommand.Parameters.Add("@DaysFromFile",SqlDbType.SmallInt, 0, "DaysFromFile").Value = SqlInt16.Parse(this.txtDayFromFile.Text);
myInsertCommand.Parameters.Add("@SubServeAge",SqlDbType.SmallInt, 0, "SubServeAge").Value = SqlInt16.Parse(this.txtSubServeAge.Text);
myInsertCommand.Parameters.Add("@FileFee",SqlDbType.Money, 0, "FileFee").Value = SqlMoney.Parse(this.txtFilingFee.Value);
myInsertCommand.Parameters.Add("@ServiceInstructions",SqlDbType.Text, 2147483647, "ServiceInstructions").Value = this.txtInstructions.Text;
myInsertCommand.Parameters.Add("@AdditionalRequiredDoc",SqlDbType.Text, 2147483647, "AdditionalRequiredDoc").Value = this.txtAddlDocs.Text;

try
{
mySqlConn.Open();
myInsertCommand.ExecuteNonQuery();
mySqlConn.Close();
}
catch (SqlException err)
{
Console.WriteLine("A SqlException was thrown");
Console.WriteLine("Number = " + err.Number);
Console.WriteLine("Message = " + err.Message);
Console.WriteLine("StackTrace:/n" + err.StackTrace);
}

Munsifali Rashid · Jan 9, 2004

You might need to set the command type to a stored procedure.. Not sure if
this could be causing it, but you can give it a try. I've got some pretty
weird errors when trying to run stored procedures without setting this
property :-)

myInsertCommand.CommandType = CommandType.StoredProcedure;

Hope this helps,

Mun

--
Munsifali Rashid
http://www.munsplace.com/

Bobbo said:
I'm having a devil of a time getting the following to run and was

wondering if someone could see why. Seems to be related to data type I'm
sure, but new to .NET and C# so am in head banging mode.

The connection is good. Am trying to run a Stored Procedure. Error that

comes up is 'Additional information: Input string was not in a correct
format'. Note that it appears to fail on setting the cbPosting which is a
checkbox.

Appreciate another set of eyes and obviously more experience than I currently have!

Thanks...Bobbo

SqlCommand myInsertCommand = mySqlConn.CreateCommand();
myInsertCommand.CommandText =
"EXECUTE Documents_Ins @Name, @State, @SubServeInCountyDays, " +
"@SubServeOutCountyDays, @PersServeInCountyDays, @PersServeOutCountyDays, " +
"@FileProofDays, @cbPersService, @cbDueDiligence, @DueDiligenceAttempts, " +
"@cb_Posting, @PostingAttempts, @cb_NotaryRequired, @cb_NoSunday, @DaysFromFile, " +
"@SubServeAge, @FileFee, @ServiceInstructions, @AdditionalRequiredDoc";

myInsertCommand.Parameters.Add("@DocName",SqlDbType.VarChar, 80,

"DocName").Value = this.txtDocName.Text;

myInsertCommand.Parameters.Add("@State",SqlDbType.VarChar, 2,

"State").Value = this.txtState.Text;

myInsertCommand.Parameters.Add("@SubServeInCountyDays",SqlDbType.SmallInt,

0, "SubServeInCountyDays").Value = SqlInt16.Parse(this.txtSubServeIn.Text);myInsertCommand.Parameters.Add("@SubServeOutCountyDays",SqlDbType.SmallInt,
0, "SubServeOutCountyDays").Value =
SqlInt16.Parse(this.txtSubServeOut.Text);myInsertCommand.Parameters.Add("@PersServeInCountyDays",SqlDbType.SmallInt,
0, "PersServeInCountyDays").Value = SqlInt16.Parse(this.txtPersonalIn.Text);myInsertCommand.Parameters.Add("@PersServeOutCountyDays",SqlDbType.SmallInt,
0, "PersServeOutCountyDays").Value =
SqlInt16.Parse(this.txtPersonalOut.Text);

myInsertCommand.Parameters.Add("@FileProofDays",SqlDbType.SmallInt, 0,

"FileProofDays").Value = SqlInt16.Parse(this.txtFileProof.Text);

myInsertCommand.Parameters.Add("@cbPersService",SqlDbType.Bit, 0,

"cbPersService").Value = SqlBoolean.Parse(this.cbPersonalServeReq.Checked);

myInsertCommand.Parameters.Add("@cbDueDiligence",SqlDbType.Bit, 0,

"cbDueDiligence").Value = SqlBoolean.Parse(this.cbDueDiligence.Checked);

myInsertCommand.Parameters.Add("@DueDiligenceAttempts",SqlDbType.SmallInt,

0, "DueDiligenceAttempts").Value =
SqlInt16.Parse(this.txtDueDilAttempts.Text);

myInsertCommand.Parameters.Add("@cbPosting",SqlDbType.Bit, 0,

"cbPosting").Value = SqlBoolean.Parse(this.cbPosting.Checked);

myInsertCommand.Parameters.Add("@PostingAttempts",SqlDbType.SmallInt, 0,

"PostingAttempts").Value = SqlInt16.Parse(this.txtPostingAttempts.Text);

myInsertCommand.Parameters.Add("@cbNotaryRequired",SqlDbType.Bit, 0,

"cbNotaryRequired").Value = SqlBoolean.Parse(this.cbNotaryReq.Checked);

myInsertCommand.Parameters.Add("@cbNoSunday",SqlDbType.Bit, 0,

"cbNoSunday").Value = SqlBoolean.Parse(this.cbNoSunday.Checked);

myInsertCommand.Parameters.Add("@DaysFromFile",SqlDbType.SmallInt, 0,

"DaysFromFile").Value = SqlInt16.Parse(this.txtDayFromFile.Text);

myInsertCommand.Parameters.Add("@SubServeAge",SqlDbType.SmallInt, 0,

"SubServeAge").Value = SqlInt16.Parse(this.txtSubServeAge.Text);

myInsertCommand.Parameters.Add("@FileFee",SqlDbType.Money, 0,

"FileFee").Value = SqlMoney.Parse(this.txtFilingFee.Value);

myInsertCommand.Parameters.Add("@ServiceInstructions",SqlDbType.Text,

2147483647, "ServiceInstructions").Value = this.txtInstructions.Text;

myInsertCommand.Parameters.Add("@AdditionalRequiredDoc",SqlDbType.Text,

2147483647, "AdditionalRequiredDoc").Value = this.txtAddlDocs.Text;

Trebek · Jan 9, 2004

Set your myInsertCommand.CommandType = CommandType.StoredProc...

Also, no need to specify all those params in the CommandText. You are
already adding them via the SqlParameters collection as well as their
values.

Try simply setting 'myInsertCommand.CommandText ="Documents_Ins";'

Alex

Bobbo said:
I'm having a devil of a time getting the following to run and was

wondering if someone could see why. Seems to be related to data type I'm
sure, but new to .NET and C# so am in head banging mode.

The connection is good. Am trying to run a Stored Procedure. Error that

comes up is 'Additional information: Input string was not in a correct
format'. Note that it appears to fail on setting the cbPosting which is a
checkbox.

Appreciate another set of eyes and obviously more experience than I currently have!

Thanks...Bobbo

SqlCommand myInsertCommand = mySqlConn.CreateCommand();
myInsertCommand.CommandText =
"EXECUTE Documents_Ins @Name, @State, @SubServeInCountyDays, " +
"@SubServeOutCountyDays, @PersServeInCountyDays, @PersServeOutCountyDays, " +
"@FileProofDays, @cbPersService, @cbDueDiligence, @DueDiligenceAttempts, " +
"@cb_Posting, @PostingAttempts, @cb_NotaryRequired, @cb_NoSunday, @DaysFromFile, " +
"@SubServeAge, @FileFee, @ServiceInstructions, @AdditionalRequiredDoc";

myInsertCommand.Parameters.Add("@DocName",SqlDbType.VarChar, 80,

"DocName").Value = this.txtDocName.Text;

myInsertCommand.Parameters.Add("@State",SqlDbType.VarChar, 2,

"State").Value = this.txtState.Text;

myInsertCommand.Parameters.Add("@SubServeInCountyDays",SqlDbType.SmallInt,

0, "SubServeInCountyDays").Value = SqlInt16.Parse(this.txtSubServeIn.Text);myInsertCommand.Parameters.Add("@SubServeOutCountyDays",SqlDbType.SmallInt,
0, "SubServeOutCountyDays").Value =
SqlInt16.Parse(this.txtSubServeOut.Text);myInsertCommand.Parameters.Add("@PersServeInCountyDays",SqlDbType.SmallInt,
0, "PersServeInCountyDays").Value = SqlInt16.Parse(this.txtPersonalIn.Text);myInsertCommand.Parameters.Add("@PersServeOutCountyDays",SqlDbType.SmallInt,
0, "PersServeOutCountyDays").Value =
SqlInt16.Parse(this.txtPersonalOut.Text);

myInsertCommand.Parameters.Add("@FileProofDays",SqlDbType.SmallInt, 0,

"FileProofDays").Value = SqlInt16.Parse(this.txtFileProof.Text);

myInsertCommand.Parameters.Add("@cbPersService",SqlDbType.Bit, 0,

"cbPersService").Value = SqlBoolean.Parse(this.cbPersonalServeReq.Checked);

myInsertCommand.Parameters.Add("@cbDueDiligence",SqlDbType.Bit, 0,

"cbDueDiligence").Value = SqlBoolean.Parse(this.cbDueDiligence.Checked);

myInsertCommand.Parameters.Add("@DueDiligenceAttempts",SqlDbType.SmallInt,

0, "DueDiligenceAttempts").Value =
SqlInt16.Parse(this.txtDueDilAttempts.Text);

myInsertCommand.Parameters.Add("@cbPosting",SqlDbType.Bit, 0,

"cbPosting").Value = SqlBoolean.Parse(this.cbPosting.Checked);

myInsertCommand.Parameters.Add("@PostingAttempts",SqlDbType.SmallInt, 0,

"PostingAttempts").Value = SqlInt16.Parse(this.txtPostingAttempts.Text);

myInsertCommand.Parameters.Add("@cbNotaryRequired",SqlDbType.Bit, 0,

"cbNotaryRequired").Value = SqlBoolean.Parse(this.cbNotaryReq.Checked);

myInsertCommand.Parameters.Add("@cbNoSunday",SqlDbType.Bit, 0,

"cbNoSunday").Value = SqlBoolean.Parse(this.cbNoSunday.Checked);

myInsertCommand.Parameters.Add("@DaysFromFile",SqlDbType.SmallInt, 0,

"DaysFromFile").Value = SqlInt16.Parse(this.txtDayFromFile.Text);

myInsertCommand.Parameters.Add("@SubServeAge",SqlDbType.SmallInt, 0,

"SubServeAge").Value = SqlInt16.Parse(this.txtSubServeAge.Text);

myInsertCommand.Parameters.Add("@FileFee",SqlDbType.Money, 0,

"FileFee").Value = SqlMoney.Parse(this.txtFilingFee.Value);

myInsertCommand.Parameters.Add("@ServiceInstructions",SqlDbType.Text,

2147483647, "ServiceInstructions").Value = this.txtInstructions.Text;

myInsertCommand.Parameters.Add("@AdditionalRequiredDoc",SqlDbType.Text,

2147483647, "AdditionalRequiredDoc").Value = this.txtAddlDocs.Text;

Guest · Jan 9, 2004

Thanks to both of you....That did the trick!!

----- Trebek wrote: ----

Set your myInsertCommand.CommandType = CommandType.StoredProc..

Also, no need to specify all those params in the CommandText. You ar
already adding them via the SqlParameters collection as well as thei
values

Try simply setting 'myInsertCommand.CommandText ="Documents_Ins";

Ale

Bobbo said:
I'm having a devil of a time getting the following to run and wa

wondering if someone could see why. Seems to be related to data type I'
sure, but new to .NET and C# so am in head banging modecomes up is 'Additional information: Input string was not in a correc
format'. Note that it appears to fail on setting the cbPosting which is
checkbox

myInsertCommand.CommandText
"EXECUTE Documents_Ins @Name, @State @SubServeInCountyDays, "
"@SubServeOutCountyDays, @PersServeInCountyDays, @PersServeOutCountyDays "
"@FileProofDays, @cbPersService, @cbDueDiligence, @DueDiligenceAttempts,

"@cb_Posting, @PostingAttempts, @cb_NotaryRequired, @cb_NoSunday @DaysFromFile, "
"@SubServeAge, @FileFee, @ServiceInstructions, @AdditionalRequiredDoc"

"DocName").Value = this.txtDocName.Text

myInsertCommand.Parameters.Add("@State",SqlDbType.VarChar, 2

"State").Value = this.txtState.Text

myInsertCommand.Parameters.Add("@SubServeInCountyDays",SqlDbType.SmallInt

0, "SubServeInCountyDays").Value = SqlInt16.Parse(this.txtSubServeIn.Text)

myInsertCommand.Parameters.Add("@SubServeOutCountyDays",SqlDbType.SmallInt
0, "SubServeOutCountyDays").Value
SqlInt16.Parse(this.txtSubServeOut.Text)

myInsertCommand.Parameters.Add("@PersServeInCountyDays",SqlDbType.SmallInt
0, "PersServeInCountyDays").Value = SqlInt16.Parse(this.txtPersonalIn.Text)

myInsertCommand.Parameters.Add("@PersServeOutCountyDays",SqlDbType.SmallInt
0, "PersServeOutCountyDays").Value
SqlInt16.Parse(this.txtPersonalOut.Text)

myInsertCommand.Parameters.Add("@FileProofDays",SqlDbType.SmallInt, 0

"FileProofDays").Value = SqlInt16.Parse(this.txtFileProof.Text)

myInsertCommand.Parameters.Add("@cbPersService",SqlDbType.Bit, 0

"cbPersService").Value = SqlBoolean.Parse(this.cbPersonalServeReq.Checked)

myInsertCommand.Parameters.Add("@cbDueDiligence",SqlDbType.Bit, 0

"cbDueDiligence").Value = SqlBoolean.Parse(this.cbDueDiligence.Checked)

myInsertCommand.Parameters.Add("@DueDiligenceAttempts",SqlDbType.SmallInt

0, "DueDiligenceAttempts").Value
SqlInt16.Parse(this.txtDueDilAttempts.Text)

myInsertCommand.Parameters.Add("@cbPosting",SqlDbType.Bit, 0

"cbPosting").Value = SqlBoolean.Parse(this.cbPosting.Checked)

myInsertCommand.Parameters.Add("@PostingAttempts",SqlDbType.SmallInt, 0

"PostingAttempts").Value = SqlInt16.Parse(this.txtPostingAttempts.Text)

myInsertCommand.Parameters.Add("@cbNotaryRequired",SqlDbType.Bit, 0

"cbNotaryRequired").Value = SqlBoolean.Parse(this.cbNotaryReq.Checked)

myInsertCommand.Parameters.Add("@cbNoSunday",SqlDbType.Bit, 0

"cbNoSunday").Value = SqlBoolean.Parse(this.cbNoSunday.Checked)

myInsertCommand.Parameters.Add("@DaysFromFile",SqlDbType.SmallInt, 0

"DaysFromFile").Value = SqlInt16.Parse(this.txtDayFromFile.Text)

myInsertCommand.Parameters.Add("@SubServeAge",SqlDbType.SmallInt, 0

"SubServeAge").Value = SqlInt16.Parse(this.txtSubServeAge.Text)

myInsertCommand.Parameters.Add("@FileFee",SqlDbType.Money, 0,

"FileFee").Value = SqlMoney.Parse(this.txtFilingFee.Value);

myInsertCommand.Parameters.Add("@ServiceInstructions",SqlDbType.Text,

2147483647, "ServiceInstructions").Value = this.txtInstructions.Text;

myInsertCommand.Parameters.Add("@AdditionalRequiredDoc",SqlDbType.Text,

2147483647, "AdditionalRequiredDoc").Value = this.txtAddlDocs.Text;

.NET Newbie: Why is this SQL Statement Failing in C#?

Guest

Munsifali Rashid

Trebek

Guest