Saving data to sql using vb.net

[tebogop]

I'm trying to save data from textboxes in visual studio using vb.net to sql server 2008. the coonectiong is working perfectly but is not displaying the data output in data grid. This is the part of the code.

Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

 Dim MyConnObj As New SqlClient.SqlConnection

MyConnObj.ConnectionString = "Data Source=VCPTSTDST01PC16\SQLEXPRESS;Initial Catalog = MzanziDBSQL;Integrated Security=SSPI"
        MyConnObj.Open()

Dim sqlStr, sqlStrCheck As String

       
sqlStr = "INSERT INTO Patient(PatientID,Title,FirstName,LastName,Gender,IDNumber,Doctor,Phone,City,Address,PostalCode,Province,Country,Mobile,MedicalAid) VALUES(@PatientID,@Title,@FirstName,@LastName,@Gender,@IDNumber,@Doctor,@Phone,@City,@Address,@PostalCode,@Province,@Country,@Mobile,@MedicalAid)"
sqlStrCheck = "Select PatientID from Patient Where PatientID = '" + txtPatientID.Text + "'"
Dim sqlComm2 As New SqlCommand(sqlStrCheck, MyConnObj)
Dim dr As SqlDataReader = sqlComm2.ExecuteReader()

If Not dr.HasRows Then
            Dim sqlComm As New SqlCommand(sqlStr, MyConnObj)
            dr.Close()
            Dim errorMessages As New StringBuilder()

            Try
                sqlComm.ExecuteNonQuery()
            Catch ex As SqlException
                Dim i As Integer
                For i = 0 To ex.Errors.Count - 1
                    errorMessages.Append("Index #" & i.ToString() & ControlChars.NewLine _
                     & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
                    & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
                    & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
                     & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
                Next i
                Console.WriteLine(errorMessages.ToString())
            End Try

            MyConnObj.Close()
            MessageBox.Show("New Patient has been added to the database")
            Me.Close()
        Else
            MessageBox.Show("Patient already exists in database")
        End If

[lolafuertes]

IMO you miss to pass the parameters values to the query before executing the insert.

See here for an example.

Hope this helps.

[tebogop]

I tried implementing that example with my code, but the saving messsage box appears but still it is not working

[Pgmer]

Have you checked the database for record after insert?

[tebogop]

Im still new with sql server. I tried checking that the values i inserted but i couldn't view anything.

[lolafuertes]

Please post your updated code

[Netcode]

Do you get any error message?

[tebogop]

@chibex64, im not getting any error. It just showing msg box that says new patient has been added to database.

[lolafuertes]

Can you be so kind to put here your updated code?

thanks in advance

[Unhnd_Exception]

Move this

MyConnObj.Close()
MessageBox.Show("New Patient has been added to the database") 
Me.Close()

to underneith

sqlComm.ExecuteNonQuery()

They way you have it right now it is always going to say new patient added. Your probably catching an error but don't know it. Remove console.write line in your catch block and change it to msg(ex.message)

It should look like

Try                
    sqlComm.ExecuteNonQuery()
    MessageBox.Show("New Patient has been added to the database") 
    Me.Close()          
Catch ex As SqlException
    msgbox(ex.message)
Finally
   MyConnObj.Close
End Try

[tebogop]

Guys thanx so much for all the help..i got solution with @Unhnd_Exception. but thank you to everyone who was trying to help.sorry for late reply