Insert statements and 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 AsString = " 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 AsString = " 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]

It's done the same way. Doesn't matter what the param name is since it does postition based parameters - the first param added replaces the first ?, the second does the second ? and so on. Even when you give names in the original query, it still does position-based replacement. SQL Server on the other hand does named parameters, so in that case the names HAVE to match. In OLEDB, it doesn't.

-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][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]

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]

remove " [GKLS Software]" from the column names... you don't need it.

-tg

 

[minn]

Thanks alot mate, seems to have done the trick!

Much Appreciated!