Josh
7/26/2002 7:44:00 AM
This is (hopefully) a simple question.
With a dataset, using ODBC.net and an Access 2k database,
I can select rows just fine.
But if I try to Insert or Update, it gives an error that I
don't have enough parameters. In the "Variable View"
frame, or whatever it's called, I checked the dataadapter,
and it has enough parameters.
If I try to delete is says it can't delete from the
specified table. The .mdb files aren't read only.
Here is my code. All SQL statements have been tested as
queries in Access. In this example, I updated a record.
Nice and simple (so simple that it bothers me that it
won't work):
Dim cnString As String
Dim cn As OdbcConnection
cnString = "Driver={Microsoft Access Driver (*.mdb)};
DBQ="C:\Somepath\DataStore.mdb"
cn = New OdbcConnection(cnString)
cn.Open()
Dim ds As New DataSet()
Dim cmdSelect As New OdbcCommand("SELECT * FROM Users", cn)
Dim cmdInsert As New OdbcCommand("INSERT INTO Users(Email,
GroupID, Info, Name) VALUES (@Email, @GroupID, @Info,
@Name)", cn)
Dim cmdUpdate As New OdbcCommand("UPDATE Users SET Email =
@Email, GroupID = @GroupID, Info = @Info, Name = @Name
WHERE (Email = @OldEmail)", cn)
Dim cmdDelete As New OdbcCommand("DELETE FROM Users WHERE
(Email = 'JimMouch')", cn)
With cmdInsert.Parameters
.Add("@Email", OdbcType.VarChar, 50, "Email")
.Add("@GroupID", OdbcType.Int, 4, "GroupID")
.Add("@Info", OdbcType.VarChar, 50, "Info")
.Add("@Name", OdbcType.VarChar, 50, "Name")
End With
With cmdUpdate.Parameters
.Add("@Email", OdbcType.VarChar, 50, "Email")
.Add("@GroupID", OdbcType.Int, 4, "GroupID")
.Add("@Info", OdbcType.VarChar, 50, "Info")
.Add("@Name", OdbcType.VarChar, 50, "Name")
.Add("@OldEmail", OdbcType.VarChar,
50, "Email").SourceVersion = DataRowVersion.Original
End With
With cmdDelete.Parameters
.Add("@Email", OdbcType.VarChar,
50, "Email").SourceVersion = DataRowVersion.Original
End With
Dim da As OdbcDataAdapter = New OdbcDataAdapter()
With da
.SelectCommand = cmdSelect
.InsertCommand = cmdInsert
.UpdateCommand = cmdUpdate
.DeleteCommand = cmdDelete
End With
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
dr.Item("Name") = "Jeff"
Next
da.Update(ds)