Having trouble with SQL

[DruidAllanon]

I'm having some trouble with an insert into SQL statement. For some reason, when I execute the code, it not only inserts a new record, but also changes a random record to match!!! It's really frustrating as none of my other codes have this problem.

Here's the background. I've got a table with the following fields:
TskID: AutoNumber
SubProjID: Number
ItemSqk: Number
SubSqk: Number
DocNum: Text
Description: Text

Here's my code:

Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    
    mySQL = "INSERT INTO tbl_TaskInfo ( SubProjID, ItemSqk, SubSqk, DocNum, Description)"
    mySQL = mySQL & " VALUES (@SubPrjID, @ItmSqk, @SbSqk, @DocNm, @Desc)"
    
    Dim objCommand As ADODB.Command
    Set objCommand = New ADODB.Command
    With objCommand
        .ActiveConnection = cnn1
        .CommandType = adCmdText
        .CommandText = mySQL
        .Prepared = True
        .Parameters.Append .CreateParameter("SubPrjID", adBigInt, adParamInput, , SubProjID)
        .Parameters.Append .CreateParameter("ItmSqk", adBigInt, adParamInput, , ItemSqk)
        .Parameters.Append .CreateParameter("SbSqk", adBigInt, adParamInput, , SubSqk)
        .Parameters.Append .CreateParameter("DocNm", adVarChar, adParamInput, 200, DocNum)
        .Parameters.Append .CreateParameter("Desc", adVarChar, adParamInput, 200, Description)
        .Execute , , adExecuteNoRecords
    End With

Can someone please explain to me what is going on!?!?

Thanks in advance!

Mike

 

[jdraw]

Why are you creating parameters?

 mySQL = "INSERT INTO tbl_TaskInfo ( SubProjID, ItemSqk, SubSqk, DocNum, Description)"
    mySQL = mySQL & " VALUES ([COLOR="Blue"]@SubPrjID, @ItmSqk, @SbSqk, @DocNm, @Desc[/COLOR])"

The values in blue should be the values to be added to the fields in the new record. No need for parameters that I can see.

 

[DruidAllanon]

I like using parameters in SQL... seems cleaner to me...

I think I figured out a work around that is much simpler than the way I was attempting... guess I should stick to KISS...