Wandii,
http://www.vb-tips.com/default.aspx?ID=3405596d-4556-4aa8-be12-d7c12bbb3726
We have a problem at the moment with showing links on our website.
Therefore is here the text.
This sample is to show the following items for an SQLServer Database
Creating by hand the select, insert, update, delete commands including the
parameters
Filling that table
Showing that table with negative seeded identnumbers
Update that table (you can edit the table)
Not implemented is any error handling beside showing that there is an error
(not at the places where that in fact cannot happen in this sample).
You need for this a new project, drag in a DataGridView and a Button on the
form and paste this code in the class. (Although it is 2005 is it as well to
use with 2003 where you than have to change the DataGridView for a DataGrid
and some of the code.
If you want to use another DataBaseName change that name at DBName, be aware
that it in advance of the sample every time will be Droped (Deleted).
--------------------------------------------------------------------------------
Imports System.Data.sqlclient
Imports System.Data
Public Class Form1
Private DBName As String = "TestDataBaseAutoNumber"
Private ConnString As String
Friend da As New SqlDataAdapter
Friend Conn As New SqlConnection
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'To have a test is a very very small database created
CreateNewsqlDatabase(DBName)
'In this parts are the commands
CreateCommands()
'
'This part is to test the sample
da.FillSchema(dt, SchemaType.Mapped)
Dim col As DataColumn = dt.Columns(0)
col.AutoIncrement = True
col.AutoIncrementSeed = -1
col.AutoIncrementStep = -1
For i As Integer = 0 To 3
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(1) = ChrW(i + 65)
Next
dt.DefaultView.Sort = "WhatEver"
'
'the situation is only showed in the Datagrid
'the click on the button does the update
DataGridView1.DataSource = dt
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
BindingContext("dt").EndCurrentEdit()
da.Update(dt)
End Sub
Public Sub CreateCommands()
Dim nb As Byte = 0
Dim cmdSelect As New SqlCommand
Dim cmdInsert As New SqlCommand
Dim cmdUpdate As New SqlCommand
Dim cmdDelete As New SqlCommand
da.DeleteCommand = cmdDelete
da.InsertCommand = cmdInsert
da.SelectCommand = cmdSelect
da.UpdateCommand = cmdUpdate
da.TableMappings.AddRange(New Common.DataTableMapping() _
{New Common.DataTableMapping("Table", _
"Sample", New Common.DataColumnMapping() {New
Common.DataColumnMapping("AutoId", "AutoId"), _
New Common.DataColumnMapping("WhatEver", "WhatEver")})})
'
'cmdSelect
cmdSelect.CommandText = "SELECT AutoId, WhatEver FROM Sample"
cmdSelect.Connection = Conn
'
'cmdInsert
cmdInsert.CommandText = "INSERT INTO Sample(WhatEver) " & _
"VALUES (
@whatever); SELECT AutoId, " & _
"WhatEver FROM Sample WHERE (AutoId = Scope_Identity())"
cmdInsert.Connection = Conn
cmdInsert.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int,
4, "AutoId"))
cmdInsert.Parameters.Add(New SqlParameter("
@whatever",
SqlDbType.NVarChar, 50, "WhatEver"))
'
'cmdUpdate
cmdUpdate.CommandText = "UPDATE Sample SET WhatEver =
@whatever
WHERE (AutoId = @Original_AutoId) " & _
"AND (WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL
AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
cmdUpdate.Connection = Conn
cmdUpdate.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int,
4, "AutoId"))
cmdUpdate.Parameters.Add(New SqlParameter("
@whatever",
SqlDbType.NVarChar, 50, "WhatEver"))
cmdUpdate.Parameters.Add(New SqlParameter("@Original_AutoId",
SqlDbType.Int, 4, _
ParameterDirection.Input, False, nb, nb, _
"AutoId", DataRowVersion.Original, Nothing))
cmdUpdate.Parameters.Add(New SqlParameter("@Original_WhatEver", _
SqlDbType.NVarChar, 50, ParameterDirection.Input, False, nb, nb, _
"WhatEver", DataRowVersion.Original, Nothing))
'
'cmdDelete
cmdDelete.CommandText = "DELETE FROM Sample WHERE (AutoId =
@Original_AutoId) AND " & _
"(WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL AND
WhatEver IS NULL)"
cmdDelete.Connection = Conn
cmdDelete.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_AutoId", _
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
_
False, nb, nb, "AutoId", System.Data.DataRowVersion.Original,
Nothing))
cmdDelete.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_WhatEver", _
System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, _
nb, nb, "WhatEver", System.Data.DataRowVersion.Original, Nothing))
End Sub
Private Sub CreateNewsqlDatabase(ByVal DbName As String)
Dim ConnString As String = "Server = .\SQLExpress; Database = ;
Integrated Security = SSPI"
Conn = New SqlConnection(ConnString)
Dim strSQL As String = "if Exists (Select * From
master..sysdatabases Where Name = '" & DbName & "')"
strSQL += " DROP DATABASE " & DbName & ";" & vbCrLf
strSQL += " CREATE DATABASE " & DbName
Dim cmd As New SqlCommand(strSQL, Conn)
executecmd(cmd)
Conn.ConnectionString = "Server = .\SQLExpress; Database =" & DbName
& " ; Integrated Security = SSPI"
cmd.Connection = Conn
cmd.CommandText = "CREATE TABLE Sample ( " & _
"AutoId int identity NOT NULL," & _
"WhatEver NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) "
executecmd(cmd)
End Sub
Private Sub executecmd(ByVal cmd As SqlCommand)
Try
Conn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.Message, "sqlException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
Finally
Conn.Close()
End Try
End Sub
End Class
Cor
