CODEWITHSUNDEEP
sqlvb.netms-access
Silverdream
Silverdream

Reputation: 1

VB.Net to AccessDB by SQL - INSERT INTO with parameters error

The following code has one update and one insert statement, using the same parameters. The update function WORKS, the Insert into statement does not... (in update, all parameters are successfully set in the DB)

I won't post the rest of connection string, as this already is set up and working.

I receive the following error: Data type mismatch in criteria expression.

Dim UpdateCon As MasterConnection = New MasterConnection()
Dim myCommand As OleDb.OleDbCommand = UpdateCon.GetConnection().CreateCommand
myCommand.Parameters.Add(New OleDb.OleDbParameter("@Navn", BoxKundeNavn.Text)) 'text field'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@Hendelsesdato", DtpHendelsesdato.Value.ToString)) 'datetimepicker'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@Event", BoxTrojans.Text)) 'text field'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@Annet", BoxLogg.Text)) 'text field'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@EndretAv", Analyst.ToString)) 'string variable'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@EndretDato", Date.Now().ToString)) 'datetime picker'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@Kontaktet", contacted)) 'boolean'
myCommand.Parameters.Add(New OleDb.OleDbParameter("@BrukerID", BoxKundeID.Text)) 'text field'

If userExist = True Then
    Dim SqlText As String = "Update master Set Navn= @Navn, Hendelsesdato= @Hendelsesdato, Event= @Event," & _
                "Annet= @Annet, EndretAv= @EndretAv, EndretDato=@EndretDato, Kontaktet=@Kontaktet WHERE BrukerID= @BrukerID"
    myCommand.CommandText() = SqlText
Else
    intCheck = MsgBox("Du er nå i ferd med å legge til en ny unik kundeID i database, er dette riktig?", vbYesNo, "Ny kundeID")
    If intCheck = True Then
        Dim SqlText As String = "INSERT INTO master (Hendelsesdato, BrukerID, Navn, Event, Annet, Kontaktet, EndretDato, EndretAv) " & _
                    "VALUES (@Hendelsesdato, @BrukerID, @Navn, @Event, @Annet, @Kontaktet, @EndretDato, @EndretAv)"
        myCommand.CommandText() = SqlText

    Else
        Exit Sub
    End If
End If

Dim test As Integer
Try
    UpdateCon.GetConnection.Open()
    test = myCommand.ExecuteNonQuery()
    UpdateCon.GetConnection.Close()
Catch ex As OleDb.OleDbException
    MsgBox(ex.Message.ToString)
End Try

Upvotes: 0

Views: 774

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

Access.OLEDB ignores parameter names; the parameters must be defined in the exact order that they appear in the command text. Since your two commands use the same parameters in a different order you'll have to use separate blocks of code to define the parameters for each one.

Upvotes: 2

Related Questions