Avatar of Mafalda
Mafalda

asked on 

ADO.NET Disconnected Mode

Im trying to use the disconnected mode of ADO.NET
I keep receiving an error message saying "Incorrect syntax near '?'."
code snip following (disregard public/private ...)

I create the class
doctorData = New DoctorsData(Conn)

select the current record
          doctorData.dr = doctorData.ds.Tables("Doctors").Select("LicenseID='" + CurrentDoctorID + "'")(0)

I get the ight record adn i do a change

    doctorData.dr("FirstName") = txtFirstName.Text

Then i try to save the record

doctorData.Save()

and get the error in the only line there ....
da.Update(ds, "Doctors")
Public Class DoctorsData
 
    Sub New(ByRef Conn As SqlClient.SqlConnection)
      Dim cmdselect As String = "SELECT * FROM [RiskEx].[dbo].[Doctors]"
      Dim cmddelete As String = "DELETE FROM [RiskEx].[dbo].[Doctors] WHERE LicenseID = ?"
      Dim cmdupdate As String = "UPDATE [RiskEx].[dbo].[Doctors] SET LastName = ?, FirstName = ?, City = ?, Street = ?, ZipCode = ?, Comments = ? WHERE LicenseID = ?"
      Dim cmdinsert As String = "INSERT INTO [RiskEx].[dbo].[Doctors] (LicenseID, LastName, FirstName, City, Street, ZipCode, Comments) VALUES (?, ?, ?, ?, ?, ? , ?) "
 
      ds = New DataSet
      selectcommand = New SqlClient.SqlCommand(cmdselect, Conn)
      deletecommand = New SqlClient.SqlCommand(cmddelete, Conn)
      With deletecommand.Parameters.Add("@p1", SqlDbType.NVarChar, 12)
        .SourceColumn = "LicenseID"
        .SourceVersion = DataRowVersion.Original
      End With
      updatecommand = New SqlClient.SqlCommand(cmdupdate, Conn)
      'add arguments for SET clause
      updatecommand.Parameters.Add("@p1", SqlDbType.NVarChar, 20, "LastName")
      updatecommand.Parameters.Add("@p2", SqlDbType.NVarChar, 20, "FirstName")
      updatecommand.Parameters.Add("@p3", SqlDbType.NVarChar, 30, "City")
      updatecommand.Parameters.Add("@p4", SqlDbType.NVarChar, 30, "Street")
      updatecommand.Parameters.Add("@p5", SqlDbType.NVarChar, 10, "ZipCode")
      updatecommand.Parameters.Add("@p6", SqlDbType.NVarChar, 250, "Comments")
      'add arguments for WHERE clause
      updatecommand.Parameters.Add("@p4", SqlDbType.NVarChar, 12, "LicenseID").SourceVersion = DataRowVersion.Original
 
 
      insertcommand = New SqlClient.SqlCommand(cmdinsert, Conn)
      'add arguments for VALUES clause
      insertcommand.Parameters.Add("@p1", SqlDbType.NVarChar, 12, "LicenseID")
      insertcommand.Parameters.Add("@p2", SqlDbType.NVarChar, 20, "LastName")
      insertcommand.Parameters.Add("@p3", SqlDbType.NVarChar, 20, "FirstName")
      insertcommand.Parameters.Add("@p4", SqlDbType.NVarChar, 30, "City")
      insertcommand.Parameters.Add("@p5", SqlDbType.NVarChar, 30, "Street")
      insertcommand.Parameters.Add("@p6", SqlDbType.NVarChar, 10, "ZipCode")
      insertcommand.Parameters.Add("@p7", SqlDbType.NVarChar, 250, "Comments")
 
      da = New SqlClient.SqlDataAdapter()
      cmdBuilder = New SqlClient.SqlCommandBuilder(da)
      da.SelectCommand = selectcommand
      da.DeleteCommand = deletecommand
      da.UpdateCommand = updatecommand
      da.InsertCommand = insertcommand
      da.FillSchema(ds, SchemaType.Source, "Doctors")
      da.Fill(ds, "Doctors")
 
    End Sub
 
    Public Sub Save()
      da.Update(ds, "Doctors")
    End Sub
 
    'dataset
    Public ds As DataSet
    'current record 
    Public dr As DataRow
    'data adapter
    Public da As SqlClient.SqlDataAdapter
    'sql commands
    Public selectcommand As SqlClient.SqlCommand
    Public deletecommand As SqlClient.SqlCommand
    Public updatecommand As SqlClient.SqlCommand
    Public insertcommand As SqlClient.SqlCommand
    'command builder
    Public cmdBuilder As SqlClient.SqlCommandBuilder
 
  End Class

Open in new window

Visual Basic ClassicVisual Basic.NET

Avatar of undefined
Last Comment
Jorge Paulino

8/22/2022 - Mon