CommandBuilder flaws

[Bmxpert]

I'm using a odbccommandbuilder to generate update commandos after I've made adjustments in my dataset. The problem is that a tablename can contain spaces. That's not a problem when you put the tablename between brackets (i.e. [volledig afgewerkt nl]) , but the commandbuilder doesn't do this, so I get errors that my updatestatement isn't correct.

this is an extract from my code:

adap.SelectCommand.CommandText = "SELECT " & veldstring2 & " FROM [" & tabel & "]"
adap.Fill(ds, "*")
Dim cmdbd As New Odbc.OdbcCommandBuilder(adap)

'code to adjust the dataset
Try
adap.Update(ds, ds.Tables(0).TableName)
Catch errr As Exception
....
End Try

This is the error message:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

and this is the automatically generated updatestatement:

UPDATE verkeerd nummer nl SET CITY = ? WHERE ( ((? = 1 AND CITY IS NULL) OR (CITY = ?)) AND (indice = ?) )
1

So all would be fine if the command builder would just put the tablename between brackets like this [verkeerd nummer nl]

Is there a way I can force this?

[Bmxpert]

Is there really nobody who can help me with this. I'm getting sick and tired of all the vb.net bugs I've met during the past weeks. Since about 3 weeks I've started using vb.net to make an application for a company. In this application it has to be possible to make updates in different databases (Access and SQL-Server). Connecting to SQL-Server databases and updating them hasn caused much problems. But with Access databases I've have had already so much problems that I have worked the double of the time I would have worked without encountering these problems. Here are some examples:

To update, I sometimes have to use the replace function (ex. to remove some signs in phonenumbers like '/' or '-'. Problem: not one provider for Access databases supports this function.

So then I tried to put my data in a dataset and adjust every value seperatly. Problem: when I call the update method when using an oledbdatadapter. I get 'Expected query name after EXECUTE'

So now I try the same but with an odbcdataadapter. This works fine until I get tablenames with spaces. Then I get the problem described above.

I believe microsoft really has to something about their software. Because this is not the only software of microsoft that doesn't always do what it's supposed to.

[Cander]

So why not build your own Update statement with the commandbuilder? That is what it is there for

I'm getting sick and tired of all the vb.net bugs I've met during the past weeks

You need to learn the difference between a bug, and something not implemented. You cant expect the .NET frmework to do every little thing you want.

[Cander]

SQL Insert command smaple

b.InsertCommand = New SqlClient.SqlCommand("INSERT INTO Pay([Voucher Date], [Voucher Number], [Company Code], " + _
                "[Start Date],[End Date], PEC, [Country Code], Amount, [Component of Pay], Grade, O4E, CBPO, PAS, SSN, " + _
                "TCC, [Order Number], [Travel Days], Zip, [AD Days], [Inactive Duty Periods], [Duty Type], TID, [Pgm Source], " + _
                "PG1, PG2, [Tax Auth], Col106, ContID) VALUES(@VDate, @VNumber, @Compcode, @SDate, @EDate, @PEC, @CountryCode, " + _
                "@Amnt, @COP, @Grade, @O4E, @CBPO, @PAS, @SSN, @TCC, @ONumber, @TDays, @Zip, @ADDays, @DPeriods, @DType, @TID, @PSource, " + _
                "@PG1, @PG2, @TaxAuth, @Col106, @ContID)", _
                b.SelectCommand.Connection)
            With b.InsertCommand.Parameters
                .Add("@VDate", SqlDbType.NVarChar, 6, "Voucher Date")
                .Add("@VNumber", SqlDbType.NVarChar, 6, "Voucher Number")
                .Add("@Compcode", SqlDbType.NVarChar, 7, "Company Code")
                .Add("@SDate", SqlDbType.NVarChar, 6, "Start Date")
                .Add("@EDate", SqlDbType.NVarChar, 6, "End Date")
                .Add("@PEC", SqlDbType.NVarChar, 6, "PEC")
                .Add("@CountryCode", SqlDbType.NVarChar, 2, "Country Code")
                .Add("@Amnt", SqlDbType.NVarChar, 9, "Amount")
                .Add("@COP", SqlDbType.NVarChar, 2, "Component of Pay")
                .Add("@Grade", SqlDbType.NVarChar, 2, "Grade")
                .Add("@O4E", SqlDbType.NVarChar, 1, "O4E")
                .Add("@CBPO", SqlDbType.NVarChar, 2, "CBPO")
                .Add("@PAS", SqlDbType.NVarChar, 6, "PAS")
                .Add("@SSN", SqlDbType.NVarChar, 9, "SSN")
                .Add("@TCC", SqlDbType.NVarChar, 2, "TCC")
                .Add("@ONumber", SqlDbType.NVarChar, 12, "Order Number")
                .Add("@TDays", SqlDbType.NVarChar, 1, "Travel Days")
                .Add("@Zip", SqlDbType.NVarChar, 5, "Zip")
                .Add("@ADDays", SqlDbType.NVarChar, 4, "AD Days")
                .Add("@DPeriods", SqlDbType.NVarChar, 2, "Inactive Duty Periods")
                .Add("@DType", SqlDbType.NVarChar, 2, "Duty Type")
                .Add("@TID", SqlDbType.NVarChar, 3, "TID")
                .Add("@PSource", SqlDbType.NVarChar, 1, "Pgm Source")
                .Add("@PG1", SqlDbType.NVarChar, 1, "PG1")
                .Add("@PG2", SqlDbType.NVarChar, 1, "PG2")
                .Add("@TaxAuth", SqlDbType.NVarChar, 2, "Tax Auth")
                .Add("@Col106", SqlDbType.NVarChar, 1, "Col106")
                .Add("@ContID", SqlDbType.NVarChar, 4, "ContID")
            End With

different but basicalyl same technique.

[Bmxpert]

Tnx for the info, and sorry about the bug accusations. I was getting really pissed at the time, so I had to complain somewhere. Anyways, I found a way to solve my problem:

cmdbd.QuotePrefix = "["
cmdbd.QuoteSuffix = "]"

Isn't there someway I could make a windows component (for example a COM component) wich implements the replace function for a provider so that for instance I can extend Microsoft Jet ole db provider with my own COM-component to make it support the replace function?