nudAge.Value is numeric up down. In my sql database, it is Age(int, null)
.
I did,
insert.Parameters.AddWithValue("@age", nudAge.Value.ToString())
I am not sure if I should use .ToString()
date_of_confinement is datetime in my db. Should the format be like this?
insert.Parameters.AddWithValue("@date_of_confinement", dtpDate.Value.ToShortDateString)
Also, for number_of_bottles which is text in vb.net and Int in db, I've used Int32.Parse but not sure,
insert.Parameters.AddWithValue("@number_of_bottles", Int32.Parse(txtBottle.Text))
but I still got the error 'System.InvalidCastException'.
This is my code:
PatientInfoConnection.Open() 'open database connection
Dim sql As String = ""
sql = "insert into PatientInfo (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", Int32.Parse(txtAge.Text))
insert.Parameters.AddWithValue("@date_of_confinement", Date.Parse(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", Int32.Parse(txtDrop.Text))
insert.ExecuteNonQuery()
This is my new code: ( I modified some. I didnt get any error but when i want to save by clicking save button, it didn't do anything. Do I have problem with my connection to database? Thanks!)
Private Sub PatientInfoBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PatientInfoBindingNavigatorSaveItem.Click
Me.Validate()
Me.PatientInfoBindingSource.EndEdit()
Me.PatientInfoTableAdapter.Update(Me.PatientInfoDBDataSet.PatientInfo)
End Sub
Private Sub EditInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'PatientInfoDBDataSet.PatientInfo' table. You can move, or remove it, as needed.
Me.PatientInfoTableAdapter.Fill(Me.PatientInfoDBDataSet.PatientInfo)
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
Try
'To check if all values have been filled up
If txtName.Text = "" Or txtAge.Text = "" Or dtpDate.Value = "" _
Or txtSickness.Text = "" Or txtFluid.Text = "" Or txtBottle.Text = "" Or txtDrop.Text = "" _
Then
MsgBox("Please Complete All the Required Fields")
Else
Try
Dim PatientInfoConnection As SqlConnection = New _
SqlConnection("Server=CATH-PC; database=PatientInfoDB;user id=sa;password=********") 'connection to SQL database
PatientInfoConnection.Open() 'open database connection
Dim sql As String = ""
sql = "insert into PatientInfo (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", Convert.ToInt32(txtAge.Text))
insert.Parameters.AddWithValue("@date_of_confinement", Date.Parse(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", Convert.ToInt32(txtBottle.Text))
insert.Parameters.AddWithValue("@drop_rate", Convert.ToInt32(txtDrop.Text))
insert.ExecuteNonQuery()
PatientInfoConnection.Close() 'close database connection
MsgBox("Successfully Saved")
Me.Visible = False
Mainform.Show()
Catch myerror As MySqlException
MessageBox.Show("Error Connecting to Database: " & myerror.Message & ". Please contact the operator")
End Try
End If
Catch ex As Exception
End Try
End Sub
Add break point on your method on step through each line to locate where is the exact line where your exception occurs.
try this:
Dim sql As String
sql = "insert into PatientInfo (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", Convert.ToInt32(txtAge.Text))
insert.Parameters.AddWithValue("@date_of_confinement", Date.Parse(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", Convert.ToInt32(txtBottle.Text))
insert.Parameters.AddWithValue("@drop_rate", Convert.ToInt32(txtDrop.Text))
insert.ExecuteNonQuery()
Regards