N
NancyASAP
I'm reprising this message in hopes of getting an answer.
Connection to Access is via Jet 4.0 OLEDB provider. This sounds like a
hard bug to me!!
Nancy
Here's a very wierd one...
1) I import data from a text file into a dataset using the Microsoft
Jet 4.0 driver and a schema.ini file. Data is tab delimited.
2) Looping through the datatable, I can see that all of the data is
imported correctly.
3) I attach another dataadapter connected to my MSAccess 97 database,
and perform dataadapter.update. Records are inserted into my Access
table.
Here's the wierd part:
When I set the insert command of the dataadapter to oledb commandtype
= stored procedure, and use a qry stored in my MSAccess 97 database to
perform the insert, the data in each field in the table is truncated
to the length of whatever the data length was in the first record.
Ex: if first record is
1 TEST ABC
2 JOHNATHAN DEF
.... the second field on all records will be 4 chars long, no matter
what data was in the data table (record 2 will show "JOHN" in 2nd
field). Numeric fields are all set to a value of 0, even if data table
contains valid doubles (1234.56).
If I do not use the MSAccess qry for the insert, set commandtype to
text, and copy THE EXACT SAME SQL STATEMENT out of my qry and use it
as a hardcoded sql statement, the insert works correctly.
Note that the command parameters on both methods are EXACTLY the same
(cut and pasted).
Is this some kind of driver problem with Access 97? Or ???.
In code snippet below, if I uncomment the text type insert command and
use it, insert works. If I use my routine ConfigureAcctData, insert
fails.
Any help appreciated,
NancyASAP
-----------Code snippet:------------------
.....
'write rows to table. open connection to our database and pass in
'dataset.
If Not _conn.State = ConnectionState.Open Then _conn.Open()
'using this subroutine, insert works incorrectly.
ConfigureAcctDataDataAdapter()
'uncommenting the following and using it instead of Configure
AcctDataAdapter(), insert works correctly.
'configure data adapter insert command
'Dim cmdInsert As OleDbCommand
'Dim s As String
's = "INSERT INTO VeoData ( Account, RegRep, Description, " _
' & "Equity, MoneyMarket, BuyingPower, NetBalance, Period )" _
' & "SELECT ?, ?, ?, ?, ?, ?, ?, ?"
'cmdInsert = New OleDbCommand(s, _conn)
'cmdInsert.CommandType = CommandType.Text
'With cmdInsert.Parameters
' .Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
' .Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
' .Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
' .Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
' .Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
' .Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
' .Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
' .Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
'End With
'_da.InsertCommand = cmdInsert
ds.EnforceConstraints = False
_da.Update(ds, "VeoData")
...... etc.
Public Sub ConfigureAcctDataAdapter()
'***************************************************************
'Name: ConfigureAcctDataAdapter()
'Description: Configures dataadapter for read/write on VeoData
table.
'Inputs: none
'Outputs: none
'***************************************************************
Dim sSQL As String
Dim cmdSelect As OleDbCommand
Dim cmdInsert As OleDbCommand
Dim cmdUpdate As OleDbCommand
Dim cmdDelete As OleDbCommand
Dim pc As OleDbParameterCollection
Dim param As OleDbParameter
'***************************************************************
'Set up the data adapter Select statement.
'***************************************************************
cmdSelect = New OleDbCommand("qryAccountDataSelect", _conn)
cmdSelect.CommandType = CommandType.StoredProcedure
'***************************************************************
'Set up the data adapter Insert statement
'***************************************************************
cmdInsert = New OleDbCommand("qryAccountDataInsert", _conn)
cmdInsert.CommandType = CommandType.StoredProcedure
With cmdInsert.Parameters
'Account, BuyingPower, Description, Equity, ID, MoneyMarket,
NetBalance, Period, RegRep
.Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
.Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
.Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
.Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
.Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
.Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
.Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
.Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
End With
'***************************************************************
'Set up the dataadapter Update statement
'***************************************************************
cmdUpdate = New OleDbCommand("qryAccountDataUpdate", _conn)
cmdUpdate.CommandType = CommandType.StoredProcedure
'UPDATE VeoData SET Account = ?, BuyingPower = ?, Description = ?,
Equity = ?, MoneyMarket = ?, NetBalance = ?, Period = ?, RegRep = ?
'WHERE ID = ?;
With cmdUpdate.Parameters
.Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
.Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
.Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
.Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
.Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
.Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
.Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
.Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
End With
pc = cmdUpdate.Parameters
param = pc.Add("ID_Orig", OleDbType.Integer, 0, "ID")
param.SourceVersion = DataRowVersion.Original
'***************************************************************
'Set up the dataadapter Delete statement
'***************************************************************
cmdDelete = New OleDbCommand("qryAccountDataDelete", _conn)
cmdDelete.CommandType = CommandType.StoredProcedure
pc = cmdDelete.Parameters
param = pc.Add("ID_Orig", OleDbType.Integer, 0, "ID")
param.SourceVersion = DataRowVersion.Original
'***************************************************************
'Set up the dataadapter with commands
'***************************************************************
_da = New OleDbDataAdapter
With _da
.SelectCommand = cmdSelect
.InsertCommand = cmdInsert
.DeleteCommand = cmdDelete
.UpdateCommand = cmdUpdate
End With
End Sub
------ For completeness, here is Schema.ini used in import
-------------
[TempAccountData.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=Account Text Width 255
Col2=RegRep Text Width 255
Col3=Description Text Width 255
Col4=Equity Double
Col5=MoneyMarket Double
Col6=BuyingPower Double
Col7=NetBalance Double
Col8=Period Text Width 4
[VeoData]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=Account Text Width 255
Col2=RegRep Text Width 255
Col3=Description Text Width 255
Col4=Equity Double
Col5=MoneyMarket Double
Col6=BuyingPower Double
Col7=NetBalance Double
Col8=Period Text Width 4
Connection to Access is via Jet 4.0 OLEDB provider. This sounds like a
hard bug to me!!
Nancy
Here's a very wierd one...
1) I import data from a text file into a dataset using the Microsoft
Jet 4.0 driver and a schema.ini file. Data is tab delimited.
2) Looping through the datatable, I can see that all of the data is
imported correctly.
3) I attach another dataadapter connected to my MSAccess 97 database,
and perform dataadapter.update. Records are inserted into my Access
table.
Here's the wierd part:
When I set the insert command of the dataadapter to oledb commandtype
= stored procedure, and use a qry stored in my MSAccess 97 database to
perform the insert, the data in each field in the table is truncated
to the length of whatever the data length was in the first record.
Ex: if first record is
1 TEST ABC
2 JOHNATHAN DEF
.... the second field on all records will be 4 chars long, no matter
what data was in the data table (record 2 will show "JOHN" in 2nd
field). Numeric fields are all set to a value of 0, even if data table
contains valid doubles (1234.56).
If I do not use the MSAccess qry for the insert, set commandtype to
text, and copy THE EXACT SAME SQL STATEMENT out of my qry and use it
as a hardcoded sql statement, the insert works correctly.
Note that the command parameters on both methods are EXACTLY the same
(cut and pasted).
Is this some kind of driver problem with Access 97? Or ???.
In code snippet below, if I uncomment the text type insert command and
use it, insert works. If I use my routine ConfigureAcctData, insert
fails.
Any help appreciated,
NancyASAP
-----------Code snippet:------------------
.....
'write rows to table. open connection to our database and pass in
'dataset.
If Not _conn.State = ConnectionState.Open Then _conn.Open()
'using this subroutine, insert works incorrectly.
ConfigureAcctDataDataAdapter()
'uncommenting the following and using it instead of Configure
AcctDataAdapter(), insert works correctly.
'configure data adapter insert command
'Dim cmdInsert As OleDbCommand
'Dim s As String
's = "INSERT INTO VeoData ( Account, RegRep, Description, " _
' & "Equity, MoneyMarket, BuyingPower, NetBalance, Period )" _
' & "SELECT ?, ?, ?, ?, ?, ?, ?, ?"
'cmdInsert = New OleDbCommand(s, _conn)
'cmdInsert.CommandType = CommandType.Text
'With cmdInsert.Parameters
' .Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
' .Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
' .Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
' .Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
' .Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
' .Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
' .Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
' .Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
'End With
'_da.InsertCommand = cmdInsert
ds.EnforceConstraints = False
_da.Update(ds, "VeoData")
...... etc.
Public Sub ConfigureAcctDataAdapter()
'***************************************************************
'Name: ConfigureAcctDataAdapter()
'Description: Configures dataadapter for read/write on VeoData
table.
'Inputs: none
'Outputs: none
'***************************************************************
Dim sSQL As String
Dim cmdSelect As OleDbCommand
Dim cmdInsert As OleDbCommand
Dim cmdUpdate As OleDbCommand
Dim cmdDelete As OleDbCommand
Dim pc As OleDbParameterCollection
Dim param As OleDbParameter
'***************************************************************
'Set up the data adapter Select statement.
'***************************************************************
cmdSelect = New OleDbCommand("qryAccountDataSelect", _conn)
cmdSelect.CommandType = CommandType.StoredProcedure
'***************************************************************
'Set up the data adapter Insert statement
'***************************************************************
cmdInsert = New OleDbCommand("qryAccountDataInsert", _conn)
cmdInsert.CommandType = CommandType.StoredProcedure
With cmdInsert.Parameters
'Account, BuyingPower, Description, Equity, ID, MoneyMarket,
NetBalance, Period, RegRep
.Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
.Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
.Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
.Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
.Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
.Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
.Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
.Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
End With
'***************************************************************
'Set up the dataadapter Update statement
'***************************************************************
cmdUpdate = New OleDbCommand("qryAccountDataUpdate", _conn)
cmdUpdate.CommandType = CommandType.StoredProcedure
'UPDATE VeoData SET Account = ?, BuyingPower = ?, Description = ?,
Equity = ?, MoneyMarket = ?, NetBalance = ?, Period = ?, RegRep = ?
'WHERE ID = ?;
With cmdUpdate.Parameters
.Add(New OleDbParameter("Account", OleDbType.VarWChar, 255,
"Account"))
.Add(New OleDbParameter("RegRep", OleDbType.VarWChar, 255,
"RegRep"))
.Add(New OleDbParameter("Description", OleDbType.VarWChar, 255,
"Description"))
.Add(New OleDbParameter("Equity", OleDbType.Double, 0, "Equity"))
.Add(New OleDbParameter("MoneyMarket", OleDbType.Double, 0,
"MoneyMarket"))
.Add(New OleDbParameter("BuyingPower", OleDbType.Double, 0,
"BuyingPower"))
.Add(New OleDbParameter("NetBalance", OleDbType.Double, 0,
"NetBalance"))
.Add(New OleDbParameter("Period", OleDbType.VarWChar, 4, "Period"))
End With
pc = cmdUpdate.Parameters
param = pc.Add("ID_Orig", OleDbType.Integer, 0, "ID")
param.SourceVersion = DataRowVersion.Original
'***************************************************************
'Set up the dataadapter Delete statement
'***************************************************************
cmdDelete = New OleDbCommand("qryAccountDataDelete", _conn)
cmdDelete.CommandType = CommandType.StoredProcedure
pc = cmdDelete.Parameters
param = pc.Add("ID_Orig", OleDbType.Integer, 0, "ID")
param.SourceVersion = DataRowVersion.Original
'***************************************************************
'Set up the dataadapter with commands
'***************************************************************
_da = New OleDbDataAdapter
With _da
.SelectCommand = cmdSelect
.InsertCommand = cmdInsert
.DeleteCommand = cmdDelete
.UpdateCommand = cmdUpdate
End With
End Sub
------ For completeness, here is Schema.ini used in import
-------------
[TempAccountData.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=Account Text Width 255
Col2=RegRep Text Width 255
Col3=Description Text Width 255
Col4=Equity Double
Col5=MoneyMarket Double
Col6=BuyingPower Double
Col7=NetBalance Double
Col8=Period Text Width 4
[VeoData]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=Account Text Width 255
Col2=RegRep Text Width 255
Col3=Description Text Width 255
Col4=Equity Double
Col5=MoneyMarket Double
Col6=BuyingPower Double
Col7=NetBalance Double
Col8=Period Text Width 4