This is how i did (
Its an Example) :
Dim conString As String = "Server=hcl\sqlexpress;Initial Catalog=vbtry;Integrated Security=True"
Dim conn As SqlConnection = New SqlConnection(conString)
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
Dim selCmdText As String = "SELECT *FROM DEPARTMENTS"
Dim selCommand As New SqlCommand(selCmdText, conn)
Dim insCmdText As String = "INSERT INTO DEPARTMENTS(Department_id,Department_name,Manager_id,Location_id)" & _
"VALUES(@DepId,@DepName,@ManId,@LocId)"
Dim insCommand As New SqlCommand(insCmdText, conn)
Dim adp As New SqlDataAdapter(selCommand)
adp.InsertCommand = insCommand
Dim param As New SqlParameter
param.ParameterName = "@DepId"
param.SqlDbType = SqlDbType.SmallInt
param.Value = Integer.Parse(txtDepId.Text)
insCommand.Parameters.Add(param)
param = New SqlParameter
param.ParameterName = "@DepName"
param.SqlDbType = SqlDbType.VarChar
param.Size = 15
param.Value = txtDepName.Text
insCommand.Parameters.Add(param)
param = New SqlParameter
param.ParameterName = "@ManId"
param.SqlDbType = SqlDbType.SmallInt
param.Value = Integer.Parse(txtManId.Text)
insCommand.Parameters.Add(param)
param = New SqlParameter
param.ParameterName = "@LocId"
param.SqlDbType = SqlDbType.SmallInt
param.Value = Integer.Parse(txtLocId.Text)
insCommand.Parameters.Add(param)
Dim dt As New DataTable
adp.Fill(dt)
Dim newRo As DataRow = dt.NewRow
newRo.Item("Department_id") = Integer.Parse(txtDepId.Text)
newRo.Item("Department_name") = txtDepName.Text
newRo.Item("Manager_id") = Integer.Parse(txtManId.Text)
newRo.Item("Location_id") = Integer.Parse(txtLocId.Text)
dt.Rows.Add(newRo)
adp.Update(dt)
lstView.ItemsSource = dt.DefaultView
End Sub
As you can see, I have used
SqlDataAdapter
class. With this I don't have to care about the opening and closing of connection. It will fill the values in the
DataTable
which on updating can be updated back to the Database. Second thing is the use of
SqlParameters
for each values that I want to insert to prevent SQL Injections. Avoid directly writing TextBox1.text, if someone writes
DROP TABLE
and other malicious commands, you can imagine what will happen!
Thirdly, I would recommend you to use LINQ to SQL, it's very easy to use and you don't have to write the big coding stuff above. Here is the link :
http://www.codeproject.com/KB/linq/linqtutorial.aspx[
^]
Hope it helped! :)