Hello,
First of all my previous post didnt got approvals i dont know why and i am not sure about this one too if it will get or not 2nd i have converted a VB.Net 2005 sample from access to SQL server.
Table design is
ID integer as primary key auto increment
template as image
Here is the source code
Imports System.Data.sqlclient
Imports System.Runtime.InteropServices
' Template data
Public Class TTemplate
' Template itself
Public tpt As System.Array = Array.CreateInstance(GetType(Byte), GrFingerXLib.GRConstants.GR_MAX_SIZE_TEMPLATE)
' Template size
Public Size As Long
End Class
' Template list
Public Structure TTemplates
' ID
Public ID As Integer
' Template itself
Public template As TTemplate
End Structure
Public Class DBClass
' the database we'll be connecting to
' the connection object
Dim connection As New SqlConnection
' Open connection
Public Function OpenDB() As Boolean
Try
connection = New SqlClient.SqlConnection("server=DEVILS-PC\SQLEXPRESS;integrated security=yes;database=BR_Test")
Return True
Catch
Return False
End Try
End Function
' Close conection
Public Sub closeDB()
connection.Close()
End Sub
' Clear database
Public Sub clearDB()
Dim sqlCMD As SqlCommand = New SqlCommand("DELETE FROM enroll", connection)
' run "clear" query
sqlCMD.Connection.Open()
sqlCMD.ExecuteNonQuery()
sqlCMD.Connection.Close()
End Sub
' Add template to database. Returns added template ID.
Public Function AddTemplate(ByRef template As TTemplate) As Long
Dim da As New SqlDataAdapter("select * from enroll", connection)
' Create SQL command containing ? parameter for BLOB.
da.InsertCommand = New SqlCommand("INSERT INTO enroll (template) Values(?)", connection)
da.InsertCommand.CommandType = CommandType.Text
da.InsertCommand.Parameters.Add("@template", SqlDbType.VarBinary, template.Size, "template")
' Open connection
connection.Open()
' Fill DataSet.
Dim enroll As DataSet = New DataSet
da.Fill(enroll, "enroll")
' Add a new row.
' Create parameter for ? contained in the SQL statement.
Dim newRow As DataRow = enroll.Tables("enroll").NewRow()
newRow("template") = template.tpt
enroll.Tables("enroll").Rows.Add(newRow)
' Include an event to fill in the Autonumber value.
AddHandler da.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
' Update DataSet.
da.Update(enroll, "enroll")
connection.Close()
' return ID
Return newRow("ID")
End Function
' Event procedure for OnRowUpdated
Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As SqlRowUpdatedEventArgs)
' Include a variable and a command to retrieve identity value
' from Access database.
Dim newID As Integer = 1
Dim idCMD As SqlCommand = New SqlCommand("SELECT @@IDENTITY", connection)
If args.StatementType = StatementType.Insert Then
' Retrieve identity value and store it in column
newID = CInt(idCMD.ExecuteScalar())
args.Row("ID") = newID
End If
End Sub
' Returns a DataTable with all enrolled templates from database.
Public Function getTemplates() As TTemplates()
Dim ds As New DataSet
Dim da As New SqlDataAdapter("select * from enroll", connection)
Dim ttpts As TTemplates()
Dim i As Integer
' Get query response
da.Fill(ds)
Dim tpts As DataRowCollection = ds.Tables(0).Rows
' Create response array
ReDim ttpts(tpts.Count)
' No results?
If tpts.Count = 0 Then Return ttpts
' get each template and put results in our array
For i = 1 To tpts.Count
ttpts(i).template = New TTemplate
ttpts(i).ID = tpts.Item(i - 1).Item("ID")
ttpts(i).template.tpt = tpts.Item(i - 1).Item("template")
ttpts(i).template.Size = ttpts(i).template.tpt.Length
Next
Return ttpts
End Function
' Returns template with supplied ID.
Public Function getTemplate(ByVal id As Long) As Byte()
Dim ds As New DataSet
Dim da As New SqlDataAdapter("select * from enroll where ID = " & id, connection)
Dim tpt As New TTemplate
' Get query response
da.Fill(ds)
Dim tpts As DataRowCollection = ds.Tables(0).Rows
' No results?
If tpts.Count <> 1 Then Return Nothing
' Deserialize template and return it
Return tpts.Item(0).Item("template")
End Function
End Class
and i am getting errors on following lines in the code errors are
newID = CInt(idCMD.ExecuteScalar())
Conversion from type 'DBNull' to type 'Integer' is not valid.
second error is
da.Update(enroll, "enroll")
Incorrect syntax near '?'.
If any one can help me please e mail me to farhan_sagipk@hotmail.com
for my previous post also as i am not getting approvals and i need it urger thanks