parameter problems

D

dennist

I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
 
V

Visual Barty

The number that follows the dbtype is the size (in the
db), for Integer it would 4 (4 bytes). The parameter
following the size is the Source column which is the
column that gets mapped from your dataset, first thing to
check is that you have a column in your dataset
named "Issuer".

Hope this helps.

Bart A. Robinson, MCP
-----Original Message-----
I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
.
Click to expand...
 
D

dennis turner

Visual,


Funny enough, the parameter list must be one based, because the column
name was Text. The error message said no default value. Is that a
problem with the intellisense sensing the name is _Text, or is it a
problem of having the size 0.

1. If the problem is the size, what should the size of a memo field be?
Where would I find such information? I've looked everywhere, as I've
said.

2. If the problem is _Text instead of Text, what can I do to correct
that.

thanks,

Dennist
 
D

dennist

Well, Bart, I checked and issuer is there. However, to
my surprise, when I checked the name of column 3 it
turned out to be Text. Not _Text, just Text. I guess
it's one based, not zero based. I tried to change the
name of the column to TopicText, but then for some
bizarre reason the da wouldn't fill. I tried to change
the name back to Text but the da still wouldn't fill. I
substitited my backup copy of the database and got the
same error message.

Perhaps Text is a reserved keyword and that is the
problem. Or, perhaps the number 0 is not acceptible.
Any ideas on where I can find the proper number?

dennist
-----Original Message-----

The number that follows the dbtype is the size (in the
db), for Integer it would 4 (4 bytes). The parameter
following the size is the Source column which is the
column that gets mapped from your dataset, first thing to
check is that you have a column in your dataset
named "Issuer".

Hope this helps.

Bart A. Robinson, MCP
-----Original Message-----
I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add ("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
.
Click to expand...
.
Click to expand...
 
D

dennist

I changed all the integers to 4 bytes. I checked and
indeed Issuer is a column in my dataset. But to my
surprise, it seems the parameter collection is 1 based,
not 0 based. The name of column 3 turned out to be
Text. Not _Text, just Text.

I figured a problem might be that Text is a reserved
word. So I changed the name of the column to TopicText.
Lo and behold, intellisense still didn't recognize the
entity TopicText, just_Text. I closed the app and
reopened it. Still no TopicText.

I changed the name back to Text, but then the da wouldn't
fill. I closed the app again and reopened it. The da
still wouldn't fill.

Finally I substituted the backup database for the current
version, and it worked, getting the same error message as
I mentioned at the beginning - no default value for
parameter?_3.

I'm stuck. Also, 0 is probably the wrong number for a
memo field. Does the insert statement handle access memo
fields?

dennist

-----Original Message-----

The number that follows the dbtype is the size (in the
db), for Integer it would 4 (4 bytes). The parameter
following the size is the Source column which is the
column that gets mapped from your dataset, first thing to
check is that you have a column in your dataset
named "Issuer".

Hope this helps.

Bart A. Robinson, MCP
-----Original Message-----
I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add ("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
.
Click to expand...
.
Click to expand...
 
V

Visual Barty

The valid value for a "text" field or "memo" field for
size should be 16 (16 bytes).

Since you are using untyped datasets, your intellisense
won't recognize the column names for you because it
doesn't know what they are. If you switch this app to use
typed datasets then you will have full intellisense.

If you haven't ever created typed datasets before, I would
be happy to walk you through the process.

Hope this helps.

Bart A. Robinson, MCP
-----Original Message-----
I changed all the integers to 4 bytes. I checked and
indeed Issuer is a column in my dataset. But to my
surprise, it seems the parameter collection is 1 based,
not 0 based. The name of column 3 turned out to be
Text. Not _Text, just Text.

I figured a problem might be that Text is a reserved
word. So I changed the name of the column to TopicText.
Lo and behold, intellisense still didn't recognize the
entity TopicText, just_Text. I closed the app and
reopened it. Still no TopicText.

I changed the name back to Text, but then the da wouldn't
fill. I closed the app again and reopened it. The da
still wouldn't fill.

Finally I substituted the backup database for the current
version, and it worked, getting the same error message as
I mentioned at the beginning - no default value for
parameter?_3.

I'm stuck. Also, 0 is probably the wrong number for a
memo field. Does the insert statement handle access memo
fields?

dennist

-----Original Message-----

The number that follows the dbtype is the size (in the
db), for Integer it would 4 (4 bytes). The parameter
following the size is the Source column which is the
column that gets mapped from your dataset, first thing to
check is that you have a column in your dataset
named "Issuer".

Hope this helps.

Bart A. Robinson, MCP
-----Original Message-----
I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add ("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
D

dennis turner

It is a typed dataset. I used the xsd command to create it. But if I
want to change the name of a field from Text to TopicText I realized I
need a new schema. However, the write schema statement isn't running.
When I try it I get an error message. Is that because the schema is
already there?

This is getting to be one hell of a lot of work for something I knocked
off in a couple hours in ADO. I could also knock it off in a couple
hours using ComponentOne's DataObjects(I have the trial version). VB is
no longer a RAD tool. Geeks got ahold of it and we users just using it
to solve problems have been left in the dust.

dennist
 
K

Kevin Yu [MSFT]

Hi Dennis,

I agree that size is a problem. According to MSDN, if we specify the data
type parameter, the size value is required. Sinsce Issuer is an Integer
type, its size is 4. The memo type is mapped to OleDbType.LongVarWChar in
OleDb, and in .net framework it is a string. The size of the memo type
depends on the maximum number of characters you want to get from the
database. Here's a KB article for your reference:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320435

The name for Text field is generated as _Text is because Text is a commonly
used property name in .net class library. To prevent confusion, it is
generated as _Text.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: dennis turner <[email protected]>
| References: <[email protected]>
| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: Re: parameter problems
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: 7bit
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Date: Thu, 13 Nov 2003 07:58:45 -0800
| NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:66225
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Visual,
|
|
| Funny enough, the parameter list must be one based, because the column
| name was Text. The error message said no default value. Is that a
| problem with the intellisense sensing the name is _Text, or is it a
| problem of having the size 0.
|
| 1. If the problem is the size, what should the size of a memo field be?
| Where would I find such information? I've looked everywhere, as I've
| said.
|
| 2. If the problem is _Text instead of Text, what can I do to correct
| that.
|
| thanks,
|
| Dennist
|
|
|
| Don't just participate in USENET...get rewarded for it!
|
 
D

dennis turner

Thank you Kevin,

I made the changes and put the size of Text to 16, which gives me,
according to the schema, more than enough characters.

I still get the same error message: parameter?_3 has no default value.
Parameter 3 is Text, and how do I solve this? What does it mean, no
default value? I've given it a value, the contents of a textbox. Is 16
too small a number?

dennis
 
K

Kevin Yu [MSFT]

Hi Dennis,

From the information in your former posts, it seems to be an exception.
I've never see that exception before. Would you please send me your new
code? I'll be happy to do the code review. Please also include the steps of
reproducing the error. Sorry for the inconvenience.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: dennis turner <[email protected]>
| References: <[email protected]>
| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: Re: parameter problems
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: 7bit
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Date: Thu, 13 Nov 2003 23:13:48 -0800
| NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
| Lines: 1
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:66302
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Thank you Kevin,
|
| I made the changes and put the size of Text to 16, which gives me,
| according to the schema, more than enough characters.
|
| I still get the same error message: parameter?_3 has no default value.
| Parameter 3 is Text, and how do I solve this? What does it mean, no
| default value? I've given it a value, the contents of a textbox. Is 16
| too small a number?
|
| dennis
|
|
|
|
| Don't just participate in USENET...get rewarded for it!
|
 
K

Kevin Yu [MSFT]

Hi Dennis,

I've checked your code and the Access (.mdb) file. I found that when you
add the first parameter to the insert command, your code seems like the
following.

da.InsertCommand.Parameters.Add("@Topics", OleDb.OleDbType.VarChar,
255, "Topics")

The last parameter ("Topics") of Add() method is the source column of the
parameter. Actually, this column does not exist in the dataset. I think the
column name has to be "Title".

Also the insert command, originally:

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO
Topics(Topics,CreateDate,ChangeDate,Active) values (?,?,?,?)", cn)

has to be changed to

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO
Topics(Title,CreateDate,ChangeDate,Active) values (?,?,?,?)", cn)

Please check the database table "Topics" for more information.

Another thing I have to mention is that VS .net didn't rebuild your program
when you press run. To turn on build function, right click the solution
name in the solution explore, choose "properties" on the popup menu. Choose
Configuration Properties on the left, and check Build column on the right
grid.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: dennis turner <[email protected]>
| References: <[email protected]>
| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: Re: parameter problems
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: 7bit
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Date: Fri, 14 Nov 2003 04:55:48 -0800
| NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:66325
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| will send.
|
|
| thank you very much.
|
| dennist
|
|
|
|
| Don't just participate in USENET...get rewarded for it!
|
 
D

dennist

I received notification by email today that there's been
a reply in this thread for me(the 18th in Israel). But
it's not here. This is not the first time posts to
threads appear on devex but not here.

dennist
 
D

dennist

Yippie. Thanks to all concerned, especially Kevin Yu,
and with a few tweaks of my own, in this thread and four
or five others, it all worked in the end mission
completed, it all runs smoothly. In the end I had to
changed the database Text column to TopicText column and
recreate the xsd file. Many people helped me and at long
last I'm starting to get a grasp on ado.net. It's still
way too cumbersome to be called RAD, however.

dennist
-----Original Message-----
I'm unable to insert a new row because of the following
error message.

type = system.data.oledb.oledbexception
message = parameter?_3 has no default value.

First, I've gotten this far thanks mostly to Kevin Yu.
So I have confidence in the rest of the code. I'll put
the relevant code at the end. I've checked help, several
books and msdn for specific information on translating
microsoft access types to ado.net insert statements,
without getting any help.

One book had an example with a boolean, integer and
string types, so I'm fairly confident about those. Here
is an example of one of my parameer statements:

da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")

Nowhere in the literature did I find what number to put
after the type. Especially after the memo type, or what
the number even stands for. In ado.net core reference
these numbers aren't even put in. However the example in
the other book worked, so I guess this syntax is also
valid.

The parameter 3 is topic issuer, which is an integer. You
can see in the code below my message box statement. This
verified that the number was okay and correct. I'm
assuming the list is 0 based, and issuer is actually the
fourth field. I didn't include the autoincrementing ID.

Another mystery is why intellisense insists the third
field is _Text. Nowhere in the access design mode is
this field begun with an underscore or a space. Might it
be related to its being a memo field?

Anyway, here is the relevant code:

Public Function NewTopic(frm as frmTopicFromStart) As
Boolean

NewTopic = False
'Dim cn As New OleDbConnection(gstrConn)

Try
cn.Open()
Catch er As Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
End Try



'Dim da As OleDbDataAdapter = New OleDbDataAdapter
'Dim ds As New DataSet
'ds.DataSetName = "ds1"
'cn.Open()
'da.FillSchema(ds, SchemaType.Source, "Topics")
''cn.Close()
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\CodeUpdate\ds.xsd")


Dim cmd As New OleDbCommand("SELECT * FROM
Topics", cn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
' Fill the DataSet
Dim ds As DataSet = New DataSet
ds.DataSetName = "ds1"
'da.FillSchema(ds, SchemaType.Source, "Topics")
'ds.WriteXmlSchema("H:\HasbaraNET\ado.net
tests\TFSNet2\ds.xsd")
'da.Fill(ds, "Topics")








Dim dsA As New ds1

da.Fill(dsA, "Topics")
Dim tblTopics As ds1.TopicsDataTable = dsA.Tables
(0)
Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow
'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1
'rowTopics = dsA.Tables(0).Rows(iRow)
'MsgBox(rowTopics.Issuer.ToString)
'isn't working because no rows


rowTopics = tblTopics.NewTopicsRow






rowTopics.ParentID = CInt(frm.txtParentID.Text)
rowTopics.Title = frm.txtTopicTitle.Text
rowTopics._Text = frm.txtTextGeneral.Text
rowTopics.Issuer =
frm.cboTopicIssuers.SelectedValue
MsgBox(rowTopics.Issuer.ToString)
rowTopics.BeginText = 0
rowTopics.Length = frm.txtTextGeneral.TextLength
rowTopics.PointOfView = frm.cboPOV.SelectedValue
rowTopics.PermissionID = CInt
(frm.txtPermission.Text)
rowTopics.Publisher =
frm.cboPublications.SelectedValue
rowTopics.CreateDate = Now
rowTopics.ChangeDate = Now
rowTopics.Active = True
'rowTopics.PermissionID = 6

MsgBox(rowTopics._Text.GetType.ToString())




'rowTopics.ID = 11
'rowTopics.ID = Integer.MaxValue
tblTopics.AddTopicsRow(rowTopics)
'da.InsertCommand = New OleDb.OleDbCommand
("INSERT INTO Topics
(ID,Topics,CreateDate,ChangeDate,Active) values
(?,?,?,?,?)", cn)
da.InsertCommand = New OleDb.OleDbCommand ("INSERT
INTO Topics
(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active)
values (?,?,?,?,?,?,?,?,?,?,?,?)", cn)
'INSERT INTO [Order Details] (OrderID, ProductID,
Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT
@@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf OnRowUpDated


'Try
' da.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
'Catch er As Exception
' MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'End Try

da.InsertCommand.Parameters.Add("@ParentID",
OleDb.OleDbType.Integer, 0, "ParentID")
da.InsertCommand.Parameters.Add("@Title",
OleDb.OleDbType.VarChar, 255, "Title")
da.InsertCommand.Parameters.Add("@_Text",
OleDb.OleDbType.LongVarChar, 0, "_Text")
da.InsertCommand.Parameters.Add("@Issuer",
OleDb.OleDbType.Integer, 0, "Issuer")
da.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 0, "BeginText")
da.InsertCommand.Parameters.Add("@Length",
OleDb.OleDbType.Integer, 0, "Length")
da.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 0, "PointOfView")
da.InsertCommand.Parameters.Add("@PermissionID",
OleDb.OleDbType.Integer, 0, "PermissionID")
da.InsertCommand.Parameters.Add("@Publisher",
OleDb.OleDbType.Integer, 0, "Publisher")
da.InsertCommand.Parameters.Add("@CreateDate",
OleDb.OleDbType.Date, 8, "CreateDate")
da.InsertCommand.Parameters.Add("@ChangeDate",
OleDb.OleDbType.Date, 8, "ChangeDate")
da.InsertCommand.Parameters.Add("@Active",
OleDb.OleDbType.Boolean, 2, "Active")

Try
da.Update(dsA, "Topics")
Catch er As System.Exception
MessageBox.Show("Type = " &
er.GetType.ToString & vbCr & "Message = " & er.Message)
'Finally
' cn.Close()
End Try



'You now have access to all fields in the last
row through the DataRow object (dr)

cn.Close()


End Function
Friend Sub OnRowUpDated(ByVal sender As Object, ByVal
args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

and,

Friend Sub OnRowUpDated(ByVal sender As Object,
ByVal args As OleDb.OleDbRowUpdatedEventArgs)

'include a variable and a command to retrieve the
'identity value from the access database
'Dim strConn, strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE
DB Services=-4;Data Source=H:\HasbaraNET\ado.net
tests\Topics.mdb;"
'Dim cn As New OleDbConnection(strConn)
'MsgBox("before cn open")
'cn.Open()


Dim int1 As Integer = 0
Dim cmd1 As OleDb.OleDbCommand = New
OleDb.OleDbCommand("SELECT @@IDENTITY", cn)

If args.StatementType = StatementType.Insert Then
'Retrieve the identity value and store it in
the ID column
int1 = CInt(cmd1.ExecuteScalar)
MsgBox(int1)
End If

End Sub

If Kevin or somebody else can point me in the right
direction, I'd be very thankful.

dennis
.
Click to expand...
 
K

Kevin Yu [MSFT]

Hi Dennis,

Sorry for kept you waiting for so long. I've check the latest codes you
sent me, and found that the problem lies in the UpdateCommand. The
UpdateCommand.CommandText has to be a SQL statement. It can only contain
SQL syntax. In your code, you have mixed the VB code in it, such as
rowTopics._Text. These syntaxes are not acceptable by the SQL statement. So
you have to use the original code:

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO
Topics(ParentID,Title,_Text,Issuer,BeginText,Length,PointOfView,
PermissionID,Publisher,CreateDate,ChangeDate,Active) values
(?,?,?,?,?,?,?,?,?,?,?,?)", cn)

In the code above, the "_Text" is not a valid column name in the Access
database table. It has to be "Text". As the command is a SQL statement, all
the words like "Text" are column names and should be exactly match the
column names in the database table.

After correcting the command text, you can add the parameters one by one as
usual.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
D

dennist

And that sows up that problem. In any case I changed the
column name to TopicText and added two more columns,
redid the xsd file, did the rest of the cleanup and it
worked fine. In large part thanks to you. Now I can
bother people with new questions in new threads.

thanks again.

dennist
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

can't retrieve updated autoincremented value 5
Error in INSERT INTO statement 6
can't transfer value from class to form 2
Can't save new row to table 2
Transaction won't work 3
mysteries of instantiation 2
can't add row to ms access table 19
can't add row in code to access mdb 1

Top