Cannot insert records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ricardo Furtado - 25 Mar 2008 11:57 GMT

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,NumeroProcesso,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. Can you
help?

my thanks in advanced

Reply to this Message

Ricardo Furtado - 25 Mar 2008 12:25 GMT

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"

> 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
[quoted text clipped - 26 lines]
>
> my thanks in advanced

Reply to this Message

Ricardo Furtado - 25 Mar 2008 17:14 GMT

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:\ProjectoCefalo\GestCeph\GestCeph\Cefalometria.mdf;Integrated
Security=True;MultipleActiveResultSets=True;Connect
Timeout=30;TrustServerCertificate=True;User Instance=False"

Reply to this Message

VB Forum / Database Access / March 2008

Cannot insert records