|
 |
ado_dotnet thread: Dataset Insertion problem
Message #1 by "David Horne" <david.horne@b...> on Mon, 4 Mar 2002 12:38:53 -0000
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0003_01C1C379.8BD0F170
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Can anyone help with problems inserting records into a dataset?
What seems like a well-formed INSERT command causes a run-time error:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll.
(The command has all the required fields in the appropriate order with
string values contained in single quotes).
I am successfully filling datagrids with the complete dataset or a
selection using data adapters. I can also delete or update records using
direct commands.
The code is as follows.
Private Sub mnuInsItem_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles mnuInsItem.Click
Dim RecordCount As Int32
Dim cmdInsert As New OleDbCommand()
'define database connection
cmdInsert.Connection = OleDbConnection1
If insDialog.ShowDialog = DialogResult.OK Then
If bInsert = True Then
'all required fields completed, get next record number
RecordCount = LastRecord("Inventory")
RecordCount += 1
MessageBox.Show(RecordCount, "Next")
'open connection
OleDbConnection1.Open()
'insert new record
cmdInsert.CommandText = "INSERT INTO Inventory VALUES("
& _
RecordCount & "," & _
"'" & insItem & "'" & "," & _
insCat & "," & _
"'" & insMake & "'" & "," & _
"'" & insModel & "'" & "," & _
"'" & insValue & "'" & "," & _
insRoom & ")"
cmdInsert.ExecuteNonQuery() <FALLS OVER HERE!!!!>
OleDbConnection1.Close()
End If
End If
End Sub
Private Function LastRecord(ByVal pTable As String) As Long
Dim x As Long
Dim dataReader As OleDb.OleDbDataReader
Dim strQuery As String
Select Case pTable
Case "Inventory"
strQuery = "Select InvId from Inventory"
Case "Rooms"
strQuery = "Select RoomId from Rooms"
Case "Categories"
strQuery = "Select CatId from Categories"
End Select
Dim cmdCount As New OleDb.OleDbCommand(strQuery,
OleDbConnection1)
OleDbConnection1.Open()
dataReader = cmdCount.ExecuteReader
LastRecord = 0
While dataReader.Read
x = dataReader.GetInt32(0)
If x > LastRecord Then 'because reader positions to BOF
after last read
LastRecord = x
End If
End While
dataReader.Close()
OleDbConnection1.Close()
End Function
I must be doing something wrong, here!! (Having to use a direct read to
get the next record number seems excessive. What was wrong with the
MoveLast Method in ADO?).
Oh, and I can't get code with references to ADO 2.7 to work either!
Any ideas will be appreciated,
Dave
Message #2 by "Terry Carr" <Terry.Carr@r...> on Mon, 4 Mar 2002 08:18:59 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_001A_01C1C355.3C7D5DD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
David,
What error message are you getting ? You should always supply the exact
error message when asking for help. Without that info, all I can give
you is a couple of general suggestions about your approach.
First, it's usually better to use the database for what it does best.
In this case, you are going through a lot of work to get a unique id.
Most databases have special data types or facilities to handle this.
For example: SQL Server (or the MSDE) has Identity fields, Oracle has
sequences, and MS Access has autonumber fields. They will automatically
generate the unique id during insert.
Second, even if you want to determine a unique id yourself, you
shouldn't loop through all the records in the table to get it. That
approach is fine if you have 10 inventory records, but if you have
10,000 or 1,000,000 it'll slow things down. Make sure the InvId is
indexed (it should be the primary key), then do your query as:
Select Max(InvId) from Inventory
This will return just one record with the largest value of InvId that is
accessed via an indexed search. This requires a much smaller number of
reads.
Lastly, you should always specifiy the field names in your insert
statement. For example,
cmdInsert.CommandText =3D "INSERT INTO Inventory
(InvId,Item,Cat,Make,Model,Value,Room)
VALUES(" & _
RecordCount & "," & _
"'" & insItem & "'" & "," & _
insCat & "," & _
"'" & insMake & "'" & "," & _
"'" & insModel & "'" & "," & _
"'" & insValue & "'" & "," & _
insRoom & ")"
This way there is no confusion about the fields being used in the
database and the order in which they are being supplied. It may be that
the database thinks they are in a different order than you do. This
also insulates your code against future database changes where fields
are added.
Terry
----- Original Message -----
From: David Horne
To: ADO.NET
Sent: Monday, March 04, 2002 7:38 AM
Subject: [ado_dotnet] Dataset Insertion problem
Can anyone help with problems inserting records into a dataset?
What seems like a well-formed INSERT command causes a run-time error:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll.
(The command has all the required fields in the appropriate order with
string values contained in single quotes).
I am successfully filling datagrids with the complete dataset or a
selection using data adapters. I can also delete or update records using
direct commands.
The code is as follows.
Private Sub mnuInsItem_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles mnuInsItem.Click
Dim RecordCount As Int32
Dim cmdInsert As New OleDbCommand()
'define database connection
cmdInsert.Connection =3D OleDbConnection1
If insDialog.ShowDialog =3D DialogResult.OK Then
If bInsert =3D True Then
'all required fields completed, get next record number
RecordCount =3D LastRecord("Inventory")
RecordCount +=3D 1
MessageBox.Show(RecordCount, "Next")
'open connection
OleDbConnection1.Open()
'insert new record
cmdInsert.CommandText =3D "INSERT INTO Inventory
VALUES(" & _
RecordCount & "," & _
"'" & insItem & "'" & "," & _
insCat & "," & _
"'" & insMake & "'" & "," & _
"'" & insModel & "'" & "," & _
"'" & insValue & "'" & "," & _
insRoom & ")"
cmdInsert.ExecuteNonQuery() <FALLS OVER HERE!!!!>
OleDbConnection1.Close()
End If
End If
End Sub
Private Function LastRecord(ByVal pTable As String) As Long
Dim x As Long
Dim dataReader As OleDb.OleDbDataReader
Dim strQuery As String
Select Case pTable
Case "Inventory"
strQuery =3D "Select InvId from Inventory"
Case "Rooms"
strQuery =3D "Select RoomId from Rooms"
Case "Categories"
strQuery =3D "Select CatId from Categories"
End Select
Dim cmdCount As New OleDb.OleDbCommand(strQuery,
OleDbConnection1)
OleDbConnection1.Open()
dataReader =3D cmdCount.ExecuteReader
LastRecord =3D 0
While dataReader.Read
x =3D dataReader.GetInt32(0)
If x > LastRecord Then 'because reader positions to BOF
after last read
LastRecord =3D x
End If
End While
dataReader.Close()
OleDbConnection1.Close()
End Function
I must be doing something wrong, here!! (Having to use a direct read
to get the next record number seems excessive. What was wrong with the
MoveLast Method in ADO?).
Oh, and I can't get code with references to ADO 2.7 to work either!
Any ideas will be appreciated,
Dave
$subst('Email.Unsub').
Message #3 by "Terry Carr" <Terry.Carr@r...> on Mon, 4 Mar 2002 08:44:07 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0053_01C1C358.BF2A66D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
David,
What error message are you getting ? You should always supply the exact
error message when asking for help. Without that info, all I can give
you is a couple of general suggestions about your approach.
First, it's usually better to use the database for what it does best.
In this case, you are going through a lot of work to get a unique id.
Most databases have special data types or facilities to handle this.
For example: SQL Server (or the MSDE) has Identity fields, Oracle has
sequences, and MS Access has autonumber fields. They will automatically
generate the unique id during insert.
Second, even if you want to determine a unique id yourself, you
shouldn't loop through all the records in the table to get it. That
approach is fine if you have 10 inventory records, but if you have
10,000 or 1,000,000 it'll slow things down. Make sure the InvId is
indexed (it should be the primary key), then do your query as:
Select Max(InvId) from Inventory
This will return just one record with the largest value of InvId that is
accessed via an indexed search. This requires a much smaller number of
reads.
Lastly, you should always specifiy the field names in your insert
statement. For example,
cmdInsert.CommandText =3D "INSERT INTO Inventory
(InvId,Item,Cat,Make,Model,Value,Room)
VALUES(" & _
RecordCount & "," & _
"'" & insItem & "'" & "," & _
insCat & "," & _
"'" & insMake & "'" & "," & _
"'" & insModel & "'" & "," & _
"'" & insValue & "'" & "," & _
insRoom & ")"
This way there is no confusion about the fields being used in the
database and the order in which they are being supplied. It may be that
the database thinks they are in a different order than you do. This
also insulates your code against future database changes where fields
are added.
Terry
Message #4 by "Terry Carr" <Terry.Carr@r...> on Mon, 4 Mar 2002 09:29:51 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0082_01C1C35F.2290A9E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
David,
What error message are you getting ? You should always supply the exact
error message when asking for help. Without that info, all I can give
you is a couple of general suggestions about your approach.
First, it's usually better to use the database for what it does best.
In this case, you are going through a lot of work to get a unique id.
Most databases have special data types or facilities to handle this.
For example: SQL Server (or the MSDE) has Identity fields, Oracle has
sequences, and MS Access has autonumber fields. They will automatically
generate the unique id during insert.
Second, even if you want to determine a unique id yourself, you
shouldn't loop through all the records in the table to get it. That
approach is fine if you have 10 inventory records, but if you have
10,000 or 1,000,000 it'll slow things down. Make sure the InvId is
indexed (it should be the primary key), then do your query as:
Select Max(InvId) from Inventory
This will return just one record with the largest value of InvId that is
accessed via an indexed search. This requires a much smaller number of
reads.
Lastly, you should always specifiy the field names in your insert
statement. For example,
cmdInsert.CommandText =3D "INSERT INTO Inventory
(InvId,Item,Cat,Make,Model,Value,Room)
VALUES(" & _
RecordCount & "," & _
"'" & insItem & "'" & "," & _
insCat & "," & _
"'" & insMake & "'" & "," & _
"'" & insModel & "'" & "," & _
"'" & insValue & "'" & "," & _
insRoom & ")"
This way there is no confusion about the fields being used in the
database and the order in which they are being supplied. It may be that
the database thinks they are in a different order than you do. This
also insulates your code against future database changes where fields
are added.
Terry
|
|
|
View Cart
