How to save data to the sql database using SqlCommand

Question

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

This is my code:

Try

'===============Add New Patient===================
        patient_name = txtName.Text
        patient_age = nudAge.Value
        date_of_confinement = dtpDate.Value
        type_of_sickness = txtSickness.Text
        type_of_IVfluid = txtFluid.Text
        number_of_bottles = txtBottle.Text
        drop_rate = nudDrop.Value

        'To check if all values have been filled up
        If txtName.Text = "" Or nudAge.Value = "" Or dtpDate.Value = "" _
        Or txtSickness.Text = "" Or txtFluid.Text = "" Or txtBottle.Text = "" Or nudDrop.Value = "" _
        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 model (name, age, date_of_confinement,type_of_sickness, type_of_IVfluid, number_of_bottles, drop_rate)" & _
                       " values (@txtName.Text, @nudAge.Value, @dtpDate.Value, @txtSickness.Text, @txtFluid.Text, @txtBottle.Text, @nudDrop.Value)"

                Dim insert As New SqlCommand(sql, PatientInfoConnection)

                insert.Parameters.AddWithValue(patient_name, @txtName.Text)'@ error  
                insert.Parameters.AddWithValue("val2", nudAge.Value) 'error
                insert.Parameters.AddWithValue(Name, patient_name) 'not sure

                insert.ExecuteNonQuery()

                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

I am not sure about the SqlCommand as I don't know how to save the user's input to my database. I want to add value entered in a textbox to my database

Dim insert As New SqlCommand(sql, PatientInfoConnection)

insert.Parameters.AddWithValue(patient_name, @txtName.Text) 'error @ char is not valid

insert.Parameters.AddWithValue("val2", nudAge.Value) 'error

insert.Parameters.AddWithValue(Name, patient_name) 'not error but not sure

name(text,null) is one of the columns of my database which I designed in SQL Server Management Studio

Please help me. Any help would be appreciated. Thanks!

I got changed insert.Parameters.AddWithValue("@name", txtName.Text) and the sql = "insert into model part as what suggested but I got an error

"A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll"

I am confused

score 2 · Accepted Answer · answered Jul 18 '12 at 02:14

2

You are close.

Try just a single variable name, and match them.

So SQL would be:

sql = "insert into model (name, age, date_of_confinement,type_of_sickness, type_of_IVfluid, number_of_bottles, drop_rate)" & _
                   " values (@name, @age, @dateOfConfinement, @sicknessType, @fluidType, @bottleAmount, @dropRate)"

Then set parameter like:

insert.Parameters.AddWithValue("@name", txtName.Text)

answered Jul 18 '12 at 02:14

RiddlerDev

7,370
5
46
62

I got an error of "A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll" I am confused. – Mineko Jul 18 '12 at 02:23
Likely you are trying to pass an invalid type, or a type that is not consistent with the database value. You can try to set your values with "toString()" and such, best practice would be to specify the DbType for the parameter and then to verify that the value type is the same as your database table (int to int, varchar/char to string) – RiddlerDev Jul 18 '12 at 02:27
Here are some working examples: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx – RiddlerDev Jul 18 '12 at 02:59

How to save data to the sql database using SqlCommand

1 Answers1