Glen,

I'm sorry, I told you I'm not as familiar with VB syntax as C#, but that was a stupid mistake on my part anyway. Hey, it was late at night and I was tired and not thinking straight. Good excuse? <g> Nah, probably not. And I forgot, I do have a little method I use to populate the parameter collection, so OK, let me try again:

Public Overridable Sub SetBasicParameters(ByVal Command As SqlCommand,
                                          ByVal Table As DataTable, ByVal Row As DataRow)
    Command.Parameters.Clear
    For i = 0 To ds.Tables(tableName).Columns.Count - 1
        Command.Parameters.Add(("@" + Table.Columns(i).ColumnName), Row(i))
    Next
End Sub

However, I see finally what your point was (after a nice, hot, morning cup of coffee!!) ... you have to use the other syntax because you're going to use those parameters for every row in the DataTable, not just one row. Silly me. The above method is only good on a row by row basis.

So, to get back to your original code, let's modify it just a bit:

Dim param As OleDbParameter

For iCtr = 0 To ds.Tables(tableName).Columns.Count - 1
	sColumnName = ds.Tables(tableName).Columns(iCtr).ColumnName
	
	param = new OleDbParameter("@" + sColumnName)
	param.SourceColumn = sColumnName
	
        Select Case ds.Tables(tableName).Columns(iCtr).DataType.Name
	
         Case "Int32"
	   param.OleDbType = OleDbType.BigInt

         Case "String"
	   param.OleDbType = OleDbType.VarChar
	   param.Size = 50

	 ' etc.etc.etc.
	End Select

	cmdInsert.Parameters.Add(param)
Next

Oh, and I was thinking some more about it last night, after I posted, and I was wondering why you needed to mess with parameters at all if you were planning on using the CommandBuilder ... supposedly, that takes care of all that messy stuff so you don't have to worry about it. (Personally, I never use it though ... I prefer the messy stuff. <g>)


~~Bonnie



>I used the format that you sent to me:
>
>prm=cmdInsert.Parameters.Add("@Key",ds.Tables("a").Columns("Key"))
>
>But I encounter an error:
>
>---> Index (zero based) must be greter than or equal to zero and less than the size of the argument list.
>
>I think the this error was caused by the column("Key"). Because in this column thier are many rows record involved. Remember that i used data grid..