INSERT INTO fail

Question

int dose = Convert.ToInt16(DoseTextBox.Text.ToString());
            try
            {
                SqlCommand AddMed = new SqlCommand("INSERT INTO Medications VALUES(@Medication,@Dose,@manufacture)", mcs);

                AddMed.Parameters.Add("@Medication", SqlDbType.NVarChar).Value = MedName.Text.ToString();
                AddMed.Parameters.Add("@Dose", SqlDbType.Int).Value = dose;
                AddMed.Parameters.Add("@manufacture", SqlDbType.NVarChar).Value = ManuDB.Text.ToString();

                mcs.Open();
                AddMed.ExecuteNonQuery();
                mcs.Close();

                MessageBox.Show("Med Added", "Done!");
            }
            catch (Exception)
            {
                MessageBox.Show("Oops!", "Something went wrong");
            }

Am I doing anything wrong? The result of this function is "Med Added", but the Medication table doesn't seem to have any new rows no matter how many times I execute the query nor refresh the results using Ctrl+R

Am I missing something? I tried removing the ToString method from the returned Value, but that is not the problem. I believe. The ManuDB is Manufacturer Drop Box.

What RDBMS are you using? As far as I recall, ORACLE (for example) needs you to explicitly `COMMIT` the transaction. — MatBailie, May 03 '12 at 09:18
@Uflex commit? you mean execute the query? i also tried to use the SqlDataAdapter, but that seemed to fail also, so i m really out of the possible solutions that i know. — Ameer Adel, May 03 '12 at 09:20
@Dems: Because he's using a SqlCommand i've edited the SQL-Server tag. — Tim Schmelter, May 03 '12 at 09:21
@Dems i m using SQLserver 2005Express, and all the other insert queries seem to be working just so fine. — Ameer Adel, May 03 '12 at 09:23
Unintentionally hilarious title is unintentionally hilarious. — BoltClock, May 03 '12 at 09:23
@AmeerAdel : do you have more then 3 columns in the table `Medications `? — Arion, May 03 '12 at 09:23

Arion · Answer 1 · 2012-05-03T09:52:04.547

1

If you have more columns then 3. Another column. Then I think the insert part should be:

INSERT INTO Medications(Medication,Dose,manufacture) 
VALUES(@Medication,@Dose,@manufacture)

edited May 03 '12 at 09:52

answered May 03 '12 at 09:25

Arion

31,011
10
70
88

i actually have the MedID int identity as the primary key and i as i think, i cant insert into that, so i m just inserting to the medication, dose and the manufacture. – Ameer Adel May 03 '12 at 09:30
That is why you have to specify these columns. Like the answer above – Arion May 03 '12 at 09:32
@Arion: If the primary key column is an IDENTITY column then SQL Server skips over it. It's still good practice to specify the column names though for both readability and to protect the query against extra columns being added in between the ones referenced in the query in future. – weenoid May 03 '12 at 09:45
i removed the table and reinitialized it and all went well, thanks guys for your support, much appreciate your comments. – Ameer Adel May 06 '12 at 16:06

Euclides Mulémbwè · Answer 2 · 2012-05-03T09:32:15.550

1

try replacing the @variable in SqlCommand AddMed = new SqlCommand("INSERT INTO Medications VALUES(@Medication,@Dose,@manufacture)", mcs); whit interrogation point (?), like this:

SqlCommand AddMed = new SqlCommand("INSERT INTO Medications VALUES(?,?,?)", mcs);

AddMed.Parameters.Add("@Medication", SqlDbType.NVarChar).Value = MedName.Text.ToString();
AddMed.Parameters.Add("@Dose", SqlDbType.Int).Value = dose;
AddMed.Parameters.Add("@manufacture", SqlDbType.NVarChar).Value = ManuDB.Text.ToString();

edited May 03 '12 at 09:32

answered May 03 '12 at 09:26

Euclides Mulémbwè

1,677
11
18

1

In SQL-Server a parameter needs `@`, question mark is for ms access. – Tim Schmelter May 03 '12 at 09:30
and how to assign the values to each " ? " sign? – Ameer Adel May 03 '12 at 09:31
1

just edited my answer, when you add the parameter they replace the ?, so, the first parameter replaces the first ? and so on – Euclides Mulémbwè May 03 '12 at 09:33
1

I meant to remove the @variables from the SqlCommand not from the Parameters.Add. – Euclides Mulémbwè May 03 '12 at 09:36

Tim Schmelter · Answer 3 · 2012-05-03T09:52:15.117

1

First: You should not close the connection in the try block but in the finally. That ensures that the connection get also closed in case of an error.

Second: There might be an error with your parameters. Try to use AddWithValue instead and play around with the types. Does your Medication table has a primary key with identity increment or do you have to assign the ID manually? Show the datatype of the fields.

Edit: Anyway, strange enough that you don't get an error. Have you debugged to see what ExecuteNonQuery returns(should be 1)? A "stupid" question: Are you using the correct database? Maybe SQL-Server Express creates a copy in the bin/debug folder. Click on your MDF file and check the properties and make sure that copy To output directory is set to Do Not Copy.

edited May 03 '12 at 09:52

answered May 03 '12 at 09:28

Tim Schmelter

450,073
74
686
939

the MedID is set to increment by 1 and started with 1.. the default settings, i did the try-catch-finally and it worked, showed me the same " Medication Added " but not in the table itself. – Ameer Adel May 03 '12 at 09:37
i guess i will delete and recreate the table itself rather than wasting mytime and yourtime in this... idk, un-comprehensible error? – Ameer Adel May 03 '12 at 09:41

INSERT INTO fail

3 Answers3