Hi there

I have an error when I'm trying to insert a record into a SQL Server 
Database. 

The problem is inserting an integer. I have dropdownlist which displays 
the CompanyName from one table and I want to insert their cmsId (an 
integer) into another table. I have set the DataTextField of the 
dropdownlist to be the CompanyName and the DataValueField to be their 
cmsID (an integer).

I keep getting the following error message "Input string was not in a 
correct format." What does this mean? It's being mistaken for a string 
instead of an integer? How do I fix it?

If I hardcode the integer into the SQL statement instead of using a 
parameter (see below), there is no problem at all. 


<SCRIPT language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
String strConnection = ConfigurationSettings.AppSettings["CMS"];
  
if (!IsPostBack)
{

//retreiving the values for the dropdownlist
String strSQLforListBox = "Select cmsID, CompanyName From tblCMS order by 
CompanyName";
							
SqlConnection objConnection = new SqlConnection(strConnection);
SqlCommand objCommand = new SqlCommand(strSQLforListBox, objConnection);

objConnection.Open();
drpCompanyName.DataSource = objCommand.ExecuteReader();

//setting the values of the dropdown list
drpCompanyName.DataTextField = "CompanyName";
drpCompanyName.DataValueField = "cmsID";
drpCompanyName.DataBind();
objConnection.Close();
}
}

private void btnInsert_Click(object sender, System.EventArgs e)
{
  if (Page.IsValid)
  {

// the following strSQL works with the integer hard coded in
//String strSQL = "INSERT INTO tblCMSLink(cmsId, Website, Comments) VALUES 
(9,@Website,@Comments)"; 
//strSQL which fails using the @cmsId parameter
String strSQL = "INSERT INTO tblCMSLink(cmsId, Website, Comments) VALUES 
(@cmsId,@Website,@Comments)"; 

String strConnection = ConfigurationSettings.AppSettings["CMS"];
SqlConnection objConnection = new SqlConnection(strConnection);

   SqlCommand dbComm = new SqlCommand(strSQL, objConnection);
   dbComm.Parameters.Add("@cmsId", SqlDbType.Int, 4);
   dbComm.Parameters.Add("@Website", SqlDbType.VarChar, 100);
   dbComm.Parameters.Add("@Comments", SqlDbType.VarChar, 800);

   //is this the right syntax??
   dbComm.Parameters["@cmsID"].Value = drpCompanyName.DataValueField;
   dbComm.Parameters["@Website"].Value = txtLink.Text;
   dbComm.Parameters["@Comments"].Value = txtComments.Text;

   objConnection.Open();
 //i have used ExecuteScalar() which works, but is there a better method 
to use?
   dbComm.ExecuteScalar();
   objConnection.Close();
  }
}

Cheers!

Even though you are creating a dropdown list value from a numeric data
field, as far as the postback is concerned, it's still just a string cause
it's coming from good-old HTML.  So you probably need to typecast the drop
down list value to an integer before assigning it to the SQL parameter.

   dbComm.Parameters["@cmsID"].Value = CType(drpCompanyName.DataValueField,
Integer);

Or something of that nature.

-----Original Message-----
From: m.murray@s... [mailto:m.murray@s...]
Sent: Tuesday, February 25, 2003 17:30
To: aspx_beginners
Subject: [aspx_beginners] Error: Input string was not in a correct
format


Hi there

I have an error when I'm trying to insert a record into a SQL Server
Database.

The problem is inserting an integer. I have dropdownlist which displays
the CompanyName from one table and I want to insert their cmsId (an
integer) into another table. I have set the DataTextField of the
dropdownlist to be the CompanyName and the DataValueField to be their
cmsID (an integer).

I keep getting the following error message "Input string was not in a
correct format." What does this mean? It's being mistaken for a string
instead of an integer? How do I fix it?

If I hardcode the integer into the SQL statement instead of using a
parameter (see below), there is no problem at all.


<SCRIPT language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
String strConnection = ConfigurationSettings.AppSettings["CMS"];

if (!IsPostBack)
{

//retreiving the values for the dropdownlist
String strSQLforListBox = "Select cmsID, CompanyName From tblCMS order by
CompanyName";

SqlConnection objConnection = new SqlConnection(strConnection);
SqlCommand objCommand = new SqlCommand(strSQLforListBox, objConnection);

objConnection.Open();
drpCompanyName.DataSource = objCommand.ExecuteReader();

//setting the values of the dropdown list
drpCompanyName.DataTextField = "CompanyName";
drpCompanyName.DataValueField = "cmsID";
drpCompanyName.DataBind();
objConnection.Close();
}
}

private void btnInsert_Click(object sender, System.EventArgs e)
{
  if (Page.IsValid)
  {

// the following strSQL works with the integer hard coded in
//String strSQL = "INSERT INTO tblCMSLink(cmsId, Website, Comments) VALUES
(9,@Website,@Comments)";
//strSQL which fails using the @cmsId parameter
String strSQL = "INSERT INTO tblCMSLink(cmsId, Website, Comments) VALUES
(@cmsId,@Website,@Comments)";

String strConnection = ConfigurationSettings.AppSettings["CMS"];
SqlConnection objConnection = new SqlConnection(strConnection);

   SqlCommand dbComm = new SqlCommand(strSQL, objConnection);
   dbComm.Parameters.Add("@cmsId", SqlDbType.Int, 4);
   dbComm.Parameters.Add("@Website", SqlDbType.VarChar, 100);
   dbComm.Parameters.Add("@Comments", SqlDbType.VarChar, 800);

   //is this the right syntax??
   dbComm.Parameters["@cmsID"].Value = drpCompanyName.DataValueField;
   dbComm.Parameters["@Website"].Value = txtLink.Text;
   dbComm.Parameters["@Comments"].Value = txtComments.Text;

   objConnection.Open();
 //i have used ExecuteScalar() which works, but is there a better method
to use?
   dbComm.ExecuteScalar();
   objConnection.Close();
  }
}

Cheers!

Hi Peter 

Thanks for you tip.

I've tried what you suggested but this is what happens...


Compilation Error 
Description: An error occurred during the compilation of a resource 
required to service this request. Please review the following specific 
error details and modify your source code appropriately. 

Compiler Error Message: CS0103: The name 'Integer' does not exist in the 
class or namespace 'ASP.cmslink_aspx'

Source Error:

Line 65:    dbComm.Parameters["@cmsID"].Value = CType
(drpCompanyName.DataValueField,Integer);
Line 66:    dbComm.Parameters["@Website"].Value = txtLink.Text;
Line 67:    dbComm.Parameters["@Comments"].Value = txtComments.Text;
 
Do I have to add anymore namespaces? Any ideas?

Cheers!

Sorry, I have lost the original post, but perhaps it's a language issue.  I
wasn't really paying attention to the language.  Looks like you are in C#,
so you need the integer data type and the correct flavor of type casting for
C#.

I *believe* what you want may look something like this...

	dbComm.Parameters["@cmsID"].Value = (int) drpCompanyName.DataValueField;

		or maybe

	dbComm.Parameters["@cmsID"].Value 
Convert.ToInt16(drpCompanyName.DataValueField);

I don't use C#, but I think that's the syntax, someone please correct me if
I'm wrong.

Peter

-----Original Message-----
From: m.murray@s... [mailto:m.murray@s...]
Sent: Wednesday, February 26, 2003 10:58
To: aspx_beginners
Subject: [aspx_beginners] RE: Error: Input string was not in a correct
format


Hi Peter

Thanks for you tip.

I've tried what you suggested but this is what happens...


Compilation Error
Description: An error occurred during the compilation of a resource
required to service this request. Please review the following specific
error details and modify your source code appropriately.

Compiler Error Message: CS0103: The name 'Integer' does not exist in the
class or namespace 'ASP.cmslink_aspx'

Source Error:

Line 65:    dbComm.Parameters["@cmsID"].Value = CType
(drpCompanyName.DataValueField,Integer);
Line 66:    dbComm.Parameters["@Website"].Value = txtLink.Text;
Line 67:    dbComm.Parameters["@Comments"].Value = txtComments.Text;

Do I have to add anymore namespaces? Any ideas?

Cheers!