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

Question

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

What is the BrukerID if the user doesn't exist? Is this automatically generated in the database - you are explicitly setting it in your insert statement. — Martin
– Martin, Commented Aug 12, 2013 at 11:02
Yes I know, that's exactly my point - what value does it have when the user does not exist? — Martin
– Martin, Commented Aug 12, 2013 at 11:10
When the user does not exist, the field will be filled manually by the user, and will contain data. this is checked further up in the program to see that it is not left blank (all fields are checked). The AccessDB is also a text field, and the BrukerID will contain 11 numbers or some letters+numbers. — Silverdream
– Silverdream, Commented Aug 12, 2013 at 11:19
Thanks for the idea, but it did not fix the problem, (all it wanted was to add CTypes in front of some variables and calls). I will continue to search code ;) — Silverdream
– Silverdream, Commented Aug 12, 2013 at 12:04

Gord Thompson · Accepted Answer · 2013-08-12 14:41:04Z

2

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.

answered Aug 12, 2013 at 14:41

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related