0

I'm new in Visual Studio and as well as SQL Server. I got confused on how to insert data from visual studio application

I got an error of System.InvalidCastException. I am confused of what is the problem.

This is my code:

PatientInfoConnection.Open() 'open database connection

Dim sql As String = ""
sql = "insert into model (name, age, date_of_confinement, type_of_sickness, type_of_IVfluid, number_of_bottles, drop_rate)" & _
                   " values (@name, @age, @date_of_confinement, @type_of_sickness, @type_of_IV_fluid, @number_of_bottles, @drop_rate)"

Dim insert As New SqlCommand(sql, PatientInfoConnection)

insert.Parameters.AddWithValue("@name", txtName.Text)
insert.Parameters.AddWithValue("@age", nudAge.Value.ToString())
insert.Parameters.AddWithValue("@date_of_confinement", dtpDate.Value.ToString())
insert.Parameters.AddWithValue("@type_of_sickness", txtSickness.Text)
insert.Parameters.AddWithValue("@type_of_IV_fluid", txtFluid.Text)
insert.Parameters.AddWithValue("@number_of_bottles", txtBottle.Text)
insert.Parameters.AddWithValue("@drop_rate", nudDrop.Value.ToString())

insert.ExecuteNonQuery()

I am thinking of I got problem with the values. nudAge.Value is numeric up down, in my sql database, it is Age(int, null). I also have dtpDate.Value. A date time picker. I think I should not use ToString() on it but I dont know the syntax..

Can anyone help me? Any help would be appreciated. Thanks!

date_of_confinement is datetime in my db. Is the format should be like this? insert.Parameters.AddWithValue("@date_of_confinement", dtpDate.Value.ToShortDateString)

Also, I did insert.Parameters.AddWithValue("@number_of_bottles", Int32.Parse(txtBottle.Text))

but I still got the error. Im confused. Thanks for your help!

this what i did now... insert.Parameters.AddWithValue("@date_of_confinement", Date.Parse(dtpDate.Value))

I also made changes like: 

   insert.Parameters.AddWithValue("@age", Convert.ToInt32(txtAge.Text))        
   insert.Parameters.AddWithValue("@number_of_bottles", Convert.ToInt32(txtBottle.Text)) 
   insert.Parameters.AddWithValue("@drop_rate", Convert.ToInt32(txtDrop.Text))

I dont have error anymore but when I click save, it didnt do anything even close the form and open the other form. Didn't do anything.

Thanks for your help! n.n

Mineko
  • 641
  • 2
  • 14
  • 21

1 Answers1

0

If date_of_confinement is a datetime in db, you should pass it as Date to the SqlParameter. Simply remove the ToString. The same is true for the NumericUpDown values.

insert.Parameters.AddWithValue("@name", txtName.Text)
insert.Parameters.AddWithValue("@age", nudAge.Value)
insert.Parameters.AddWithValue("@date_of_confinement", dtpDate.Value)
insert.Parameters.AddWithValue("@type_of_sickness", txtSickness.Text)
insert.Parameters.AddWithValue("@type_of_IV_fluid", txtFluid.Text)
insert.Parameters.AddWithValue("@number_of_bottles", Int32.Parse(txtBottle.Text))
insert.Parameters.AddWithValue("@drop_rate", nudDrop.Value)

Note that i've also parsed txtBottle.Text to an integer since i've assumed that number_of_bottles is an int column in db.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • date_of_confinement is datetime in my db. Is the format should be like this? insert.Parameters.AddWithValue("@date_of_confinement", dtpDate.Value.ToShortDateString) I did what you said but I still got the error – Mineko Jul 19 '12 at 00:45
  • @Mineko: In what line do you get the `InvalidCastException`? Have you debugged the code(set a breakpoint at the first `AddWithValue`)? You should not pass the datetime as string when it's also a datetime in the database, you should pass a `DateTime` (`Date` in VB.NET) then. – Tim Schmelter Jul 19 '12 at 07:58
  • insert.Parameters.AddWithValue("@date_of_confinement", Date.Parse(dtpDate.Value)) << this what i did now... I also made changes like: insert.Parameters.AddWithValue("@age", Convert.ToInt32(txtAge.Text)) insert.Parameters.AddWithValue("@number_of_bottles", Convert.ToInt32(txtBottle.Text)) insert.Parameters.AddWithValue("@drop_rate", Convert.ToInt32(txtDrop.Text)) I dont have error anymore but when I click save, it didnt do anything like hanging – Mineko Jul 19 '12 at 16:55