Visual Basic 2005 and SQL Server 2005

I've converted code from my Visual Basic 2005 application that used microsoft access 97 in order to use SQL Server 2005, but i'm having problems on saving records. V.Basic gives me no errors but the record is not updated.


Dim strSQLInsert As String = "INSERT INTO Paciente (CodTerceiro, Nome,DataNascimento,Sexo,Morada,Localidade,CodPostal,Telefone,Telemovel,PrimeiroNome,UltimoNome,Nume roProcesso,IdRaca) VALUES" & _
" ('" & strIdPaciente & "','" & strNomePaciente & "'," & Format(datDataNascimento, "MM-dd-yyyy") & "" & _
",'" & chrSexo.ToString & "',' ',' ',' ','" & strTelefone & "','" & strTelemovel & " '," & _
"'" & strPrimeiroNome & "','" & strUltimoNome & "','" & strProcesso & "'," & _
retornaIDRacaAPartirNomeRaca(strRaca) & ")"


oconnLigacao2 = New SqlConnection(m_Connection)
Dim dr2 As SqlCommand

dr2 = New SqlCommand(strSQLInsert, oconnLigacao2)
oconnLigacao2.Open()
dr2.CommandType = CommandType.Text
dr2.ExecuteNonQuery()
If oconnLigacao2.State <> ConnectionState.Closed Then
oconnLigacao2.Close()
End If



I've renamed OleDBCommand to SqlCommand and OleDBConnection to SqlConnection. When i use sqlDataReader, i can se the data in the rows but when i'm just trying to create records this doesn't seem to work.

Could it be because of the connection string?

Private m_Connection As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Cefalometria.mdf;Integrated Security=True;Connect Timeout=30;User Instance=False"

I've already tryed to change
User Instance=False
to
User Instance=True
But it doesn't Work and at debug time, the following error is shown:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed"

Can you help?


my thanks in advanced

Comments

  • Ok, i solved the problem
    The problem was in the connectionstring
    this way, works fine:
    Private m_Connection As String = "Data Source=.SQLEXPRESS;AttachDbFilename=C:ProjectoCefaloGestCephGestCephCefalometria.mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30;TrustServerCertificate=True;User Instance=False"

    : I've converted code from my Visual Basic 2005 application that used
    : microsoft access 97 in order to use SQL Server 2005, but i'm having
    : problems on saving records. V.Basic gives me no errors but the
    : record is not updated.
    :
    :
    : Dim strSQLInsert As String = "INSERT INTO Paciente (CodTerceiro,
    : Nome,DataNascimento,Sexo,Morada,Localidade,CodPostal,Telefone,Telemov
    : el,PrimeiroNome,UltimoNome,Nume roProcesso,IdRaca) VALUES" & _
    : " ('" & strIdPaciente & "','" & strNomePaciente & "'," &
    : Format(datDataNascimento, "MM-dd-yyyy") & "" & _
    : ",'" & chrSexo.ToString & "',' ',' ',' ','" & strTelefone & "','" &
    : strTelemovel & " '," & _
    : "'" & strPrimeiroNome & "','" & strUltimoNome & "','" & strProcesso
    : & "'," & _
    : retornaIDRacaAPartirNomeRaca(strRaca) & ")"
    :
    :
    : oconnLigacao2 = New SqlConnection(m_Connection)
    : Dim dr2 As SqlCommand
    :
    : dr2 = New SqlCommand(strSQLInsert, oconnLigacao2)
    : oconnLigacao2.Open()
    : dr2.CommandType = CommandType.Text
    : dr2.ExecuteNonQuery()
    : If oconnLigacao2.State <> ConnectionState.Closed Then
    : oconnLigacao2.Close()
    : End If
    :
    :
    :
    : I've renamed OleDBCommand to SqlCommand and OleDBConnection to
    : SqlConnection. When i use sqlDataReader, i can se the data in the
    : rows but when i'm just trying to create records this doesn't seem to
    : work.
    :
    : Could it be because of the connection string?
    :
    : Private m_Connection As String = "Data
    : Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Cefalometria.mdf
    : ;Integrated Security=True;Connect Timeout=30;User Instance=False"
    :
    : I've already tryed to change
    : User Instance=False
    : to
    : User Instance=True
    : But it doesn't Work and at debug time, the following error is shown:
    : "Failed to generate a user instance of SQL Server due to a failure
    : in starting the process for the user instance. The connection will
    : be closed"
    :
    : Can you help?
    :
    :
    : my thanks in advanced
    :
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion