CODEWITHSUNDEEP
c#sqlsql-server3-tier
Andre Smith
Andre Smith

Reputation: 207

Error adding new item to sql table

I am trying to add a new item to my Equipment table located in my SQL database. I am using 3 tier architecture. This particular add method uses two comboboxes with their display members set and their valuemembers set to the ID of their respective fields.

I am getting the error : "Input string was not in the correct format" when i press the add new equipment button (after all items have been entered into the textboxes and the values from the comboboxes have been selected. and i am not sure where it is comming from, my idea is that it is coming from the comboboxes.

my Equipment table contains 6 rows.

1.EquipmentNo int autonumber 2.EquipmentDesc nvarchar 3.SerialNo nvarchar 4.Barcode nvarchar 5.CategoryID nvarchar (fk of my Category table) 5.VenueID int (fk of my Venue table)

my Venue table has a VenueID (autonumber) and a RoomNumber(string) my Category table has a CategoryID (Hard for Hardware and Soft for Software) and Desription(Hardware and Software)

My business layer contains the following code: 

    public int AddEquipment(Equipment eq)
    {
        if (dbConn.State == ConnectionState.Closed)
        {
            dbConn.Open();
        }
        string sqlInsert = "sp_AddNewEquipment '" + eq.EquipmentDescription + "' , '" + eq.SerialNo + "' , '" + eq.Barcode + "' , '" + eq.CategoryID + "' , '" + eq.VenueID + "'";
        dbCmd = new SqlCommand(sqlInsert, dbConn);
        int x = dbCmd.ExecuteNonQuery();
        return x;
    }

my stored procedure is as follows:

    ALTER PROCEDURE [dbo].[sp_AddnewEquipment] 
    -- Add the parameters for the stored procedure here
    @EquipmentNo bigint,
    @EquipmentDescription nvarchar(50),
    @SerialNo nvarchar(50),
    @Barcode bigint,
    @CategoryID nvarchar(50),
@VenueID int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Equipment(EquipmentNo, EquipmentDescription, Barcode, SerialNo,   CategoryID, VenueID)
    VALUES(@EquipmentNo, @EquipmentDescription, @Barcode, @SerialNo, @CategoryID,     @VenueID)
END

And here is my code for my form present in the btnAdd_Click method:

Equipment eq = new Equipment(txtDescription.Text, txtSerialNo.Text, txtBarcode.Text,     cmbCategory.ValueMember.ToString(), Convert.ToInt32(cmbVenues.ValueMember));
eq.AddNewEquipment();

my comboboxes are filled in the page load method:

        //loading of category combobox
        cmbCategory.DataSource = c.GetAllCategories();
        cmbCategory.DisplayMember = "Description";
        cmbCategory.ValueMember = "Category ID";

        //Loading of venue combobox
        cmbVenues.DataSource = v.GetAllVenues();
        cmbVenues.DisplayMember = "Room Number";
        cmbVenues.ValueMember = "VenueID";

i want to return the ID's of the two comboboxes not the displayed text.

Thank You for you help

Upvotes: 0

Views: 241

Answers (2)

Ehsan
Ehsan

Reputation: 32681

Your code is open to SQLinjection

you should do it like this

using (var command = new SqlCommand("sp_AddNewEquipment", dbconn) { 
                           CommandType = CommandType.StoredProcedure }) {
   dbconn.Open();
   command.Parameters.Add(new SqlParameter("@EquipmentNo", eq.SerialNo));       
   command.Parameters.Add(new SqlParameter("@EquipmentDescription", eq.EquipmentDescription));
   command.Parameters.Add(new SqlParameter("@SerialNo",eq.SerialNo));
   command.Parameters.Add(new SqlParameter("@Barcode",eq.Barcode));
   command.Parameters.Add(new SqlParameter("@CategoryID",eq.CategoryID));
   command.Parameters.Add(new SqlParameter("@VenueID",eq.VenueID));
   command.ExecuteNonQuery();
   dbconn.Close();
}

Upvotes: 2

Andre Smith
Andre Smith

Reputation: 207

Ok i found my error.

cmbCategory.SelectedValue not cmbCategory.ValueMember.

Upvotes: 0

Related Questions