Error: Input string was not in a correct format

Question

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. 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); //if i use dbComm.Parameters[@cmsID].Value = Convert.ToInt32(drpCompanyName.DataValueField); it doesn't work either! 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!

Administrator · Answer

yay! thanks for your help! :o)

Administrator · Answer

Sorry i did not pay attention to it in my previous reply. The problem is in the wrong object you are using to access the value. DataValueField set the field in the datasource that provide the value to DropDown but it does not set or retrieve the value of that field. What you need to get the value is: DropDownList.SelectedItem.Value Hope it helps. Mike

Administrator · Answer

I have changed the DataValueField to the company name now so instead of seeing Abacus EIA i now see Abacus EIA I have changed the cmsID column in sql server to a varchar field and the insert works okay but in the cmsID field i have the string CompanyName instead of Abacus or EIA - what have I done wrong? Here's the code where i set up the dropdown... objConnection.Open(); drpCompanyName.DataSource = objCommand.ExecuteReader(); drpCompanyName.DataTextField = CompanyName; drpCompanyName.DataValueField = cmsID; drpCompanyName.DataBind(); objConnection.Close(); Cheers!

Administrator · Answer

I can`t see any problem with the code you use. My only guess is: Are you sure that drpCompanyName.DataValueField actually returns the integer? I don`t know why do you use ExecuteScalar() method wich returns scalar value. In your case I would use ExecuteNonQuery(). Mike