Review Insert snippet lines 1

[lance59]

Working on a project and wanted to make sure I am on the right track. This is my SQL Insert snippet. Is this the right way to do an Insert? It works just want to be sure I'm not missing anything.

TIA

This is my connection string. I have it in a module so I can use it other places.

Public conn_string As String = "server=SQL_2005;database=Test;UID=sa;Pwd=test"


Dim cn As New SqlConnection(conn_string)
Dim objCommand As New SqlCommand("", cn)
cn.Open()
objCommand.CommandText = "INSERT INTO patient_images(ENTERPRISE_ID,PRACTICE_ID,PERSON_ID,ENC_ID,ORIG_IMAGE_FILE,IMAGE_DESC,IMAGE_FILE,IMAGE_ID,CREATED_BY, MODIFIED_BY) VALUES('" & enterprise_id & "','" & practice_id & "', '" & person_id & "', '" & enc_id & "', '" & orig_image_file & "', '" & image_desc & "', '" & image_file & "', '" & image_id & "', '" & created_by & "', '" & modified_by & "')"
objCommand.ExecuteNonQuery()
cn.Close()
cn = Nothing

 

[chrissie1]

Yep you are missing a lot. Like something called sql-injection.

change to this (untested)

 Dim cn As New SqlConnection(conn_string)
            Dim objCommand As New SqlCommand("", cn)
            cn.Open()
            objcommand.parameters.add(new sqlparameter("enterprise_id",sqltype.int)
            objcommand.parameters(0).value = enterprise_id
............ 'do all the parameters
            objCommand.CommandText = "INSERT INTO 
patient_images(ENTERPRISE_ID,PRACTICE_ID,PERSON_ID,ENC_ID,ORIG_IMAGE_FILE,IMAGE_DESC,IMAGE_FILE,IMAGE_ID,CREATED_BY, MODIFIED_BY) VALUES(@enterprise_id,@practice_id,@person_id,@ enc_id,@orig_image_file,@image_desc,@image_file,@image_id,@created_by ,@modified_by)"
            objCommand.ExecuteNonQuery()
            cn.Close()
            cn = Nothing

Since I can only guess the datatypes of the parameters I won't go any further but they should be the same as your databasetypes.

BTW created_by and modified_by can be done by using default values at the sql-server side.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett