Adding Records to an access table via parameters

[minn]

I am attempring to write an insert statement to add records to an access database table. I do not want to define the values that will be added to the database in the query and wish to use parameters. I am a little confused as to how to do this:

So far I have got:

Dim addsoft As String = " Insert INTO [GKLS Software] " & _
" ([GKLS Software].Application, [GKLS Software].VendorID, [GKLS Software].SoftwareTypeID, " & _
" [GKLS Software].Product_Key, [GKLS Software].Comments) " & _
" VALUES (?,?,?,?,?) "

Is this correct or is the following correct?

Dim addsoft As String = " Insert INTO [GKLS Software] " & _
" ([GKLS Software].Application, [GKLS Software].VendorID, [GKLS Software].SoftwareTypeID, " & _
" [GKLS Software].Product_Key, [GKLS Software].Comments) " & _
" VALUES (@app,@vendId, @softtype, @prodkey,@comms) "

What i understood was that the first way is for oledb and the second for sqlserver.

Also, after the insert statement is defined, how do i pass the parameters to the statement. I know that for the second method its something like:

objcommand1.Parameters.Add("@app, txtapplication.Text)
objcommand1.Parameters.Add("@vendId, ddlvendors.selecteditem)
blah blah

however, if i have to use the first method how to i do this since each parameter is defined by a question-mark.

Please could someone kindly help.

Thanks in advance!

 

[TechGnome]

Actualy for Access, either method will work. SQL server driver does a named parameter replacement, while Access (or OLEDB) does a position-based replacement. So for OLEDB, either one will work.

-tg

 

[minn]

ok, thanks.

Another question, do i need to have a parameter for every single field in the database table. For example, my table has fields:

ApplicationID (Primary key and autonumber generated)
Application
VendorID
SoftwareTypeID
ProductKey
Comments

The form i have created has controls for all fields but not the application ID as I thought this would be taken care of by the access database since it is autonumber.

This is the code I have:

[SIZE=2][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnSubmit_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnSubmit.Click
[/SIZE][SIZE=2][COLOR=#008000]'define a new oledbcommand object
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] com1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
[/SIZE][SIZE=2][COLOR=#008000]'specify a connection to be used by the command object
[/COLOR][/SIZE][SIZE=2]com1.Connection = con
[/SIZE][SIZE=2][COLOR=#008000]'create insert statement with parameters
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] addsoft [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = " INSERT INTO [GKLS Software] " & _
" ([GKLS Software].Application, [GKLS Software].VendorID, [GKLS Software].SoftwareTypeID, " & _
" [GKLS Software].Product_Key, [GKLS Software].Comments) " & _
" VALUES(@app,@vendid,@softid,@prodkey,@comms) "
[/SIZE][SIZE=2][COLOR=#008000]'define the commandtext property
[/COLOR][/SIZE][SIZE=2]com1.CommandText = addsoft
[/SIZE][SIZE=2][COLOR=#008000]'specify which control is to provide the data for which parameter
[/COLOR][/SIZE][SIZE=2]com1.Parameters.Add("@app", txtapplication.Text)
com1.Parameters.Add("@vendid", ddlVendors.SelectedItem.Value)
com1.Parameters.Add("@softid", ddlsoftwaretype.SelectedItem.Value)
com1.Parameters.Add("@prodkey", txtproductkey.Text)
com1.Parameters.Add("@comms", txtcomments.Text)
[/SIZE][SIZE=2][COLOR=#008000]'open the db connection
[/COLOR][/SIZE][SIZE=2]con.Open()
[/SIZE][SIZE=2][COLOR=#008000]'execute the insert sql statement and cause data to be inserted into the db
[/COLOR][/SIZE][SIZE=2]com1.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#008000]'close the db connection
[/COLOR][/SIZE][SIZE=2]con.Close()
 
[/SIZE][/SIZE]

However this fails when i click the commit button with error:

The INSERT INTO statement contains the following unknown field name: 'GKLS Software.Application'. Make sure you have typed the name correctly, and try the operation again.

Please can someone help?

 

[TechGnome]

answered in the other thread.

-tg