Can't insert into an access database

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Trying to INSERT into an Access database but I keep getting this message.

An OleDbParameter with ParameterName '@Details' is not contained by this OleDbParameterCollection.

Here is my code
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] sql [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]String 
[/color][/size][size=2]sql = "INSERT INTO events (Details) VALUES (@Details)" 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] SQLreturn [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Integer 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbCommand1.CommandText = sql 
[/size][size=2][color=#0000ff]Try
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Open() 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbCommand1.Parameters("@Details").Value = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxDetails.Text 
SQLreturn = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbCommand1.ExecuteNonQuery 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception 
MessageBox.Show(ex.Message) 
[/size][size=2][color=#0000ff]Finally 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Close() 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try 
[/color][/size][size=2][color=#0000ff]If[/color][/size][size=2] SQLreturn <> 1 [/size][size=2][color=#0000ff]Then 
[/color][/size][size=2]MessageBox.Show("Insert failed") 
[/size][size=2][color=#0000ff]Else 
[/color][/size][size=2]MessageBox.Show("INSERT completed") 
TextBoxDetails.Text = "" 
TextBoxDate.Text = "" 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If
[/color][/size]

Any ideas why it doesn't work, because from what I can tell it should do
Thanks for any help
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Instead of:

VB.NET:
Me.OleDbCommand1.Parameters("@Details").Value = Me.TextBoxDetails.Text

Try

VB.NET:
Me.OleDbCommand1.Parameters.Add("@Details", Me.TextBoxDetails.Text)

This statement adds the parameter to the collection and at the same time sets the value of the parameter to the Text property of TextBoxDetails.
 

bloukewer

Well-known member
Joined
Jul 2, 2004
Messages
88
Programming Experience
3-5
Ditto

I experienced a similar problem with parameters in my sql-code a while back. None of my tutors could tell me what I did wrong, so I figured out an alternative route. Create a new command object, by firstly building up a command-string (type String) of SQL and other run-time String values, passing the string to the constructor of the command object. Thereafter, if it's a query (Select) I created a new data-adapter, passing the command-object to the constructor of the adapter, otherwise, I used the command.executenonquery() function.

You can try it, but I reckon it won't always be a sufficient solution. Nonetheless, it proved to be a sufficient substitute 'till I am able to crack that sucker.
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Yeah I've come across a number of different ways of doing things but they don't all work, have to keep changing how I do things.

Thankfully once again thanks to Paszt this solution works. I use that solution in my ASP pages, just never though of using it in my VB app.

Cheers dude
 
Top Bottom