Parameters in query to mdb using oledb

[Guest]

Hello,
I'm developing a website with asp .net and want to insert data into a mdb.
As I'm fetching the data from a web form I need to be able to have
parameters in the SQL.
Which sign indicates a parameter in this situation?
oledb objects should mean "%" or what?
Sample code:
cmdIns.CommandText =
"INSERT INTO qPersoner ( Företag, FNamn, ENamn" & _
", Adressrad1, Adressrad2, Pnr, Ort, Tfn1, Tfn2, Epost, " & _
"Medlem, Medlemsnr, Instruktör, Aktiv, Livinfo)" & _
" Values (%,%, %, %, %, %, %, %, %, %, %, %, %, %, %)"

 

[Guest]

I have just done the same thing, this was how I managed it.

Create an access query using SQL as per the example below:-

INSERT INTO tblMyTable ( Field1, Field2, Field3)
VALUES ([pField1], [pField2], [pField3]);

pField1 2 and 3 will now be treated as parameters, the only rules appear to
be that all parameters are mandatory and they must be added in the same order
they are present in the SQL statement.

Hope this helps,
Stuart

 

[Guest]

Two options.

1. Text queries in your .NET code: Use ? for each parameter you wish to
supply. Then add in order. This is the easiest.

2. Create a query in Access proper and use the parameter names. This will
work for newer versions of Access and acts much like a stored procedure.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Paul Clement]

¤ Hello,
¤ I'm developing a website with asp .net and want to insert data into a mdb.
¤ As I'm fetching the data from a web form I need to be able to have
¤ parameters in the SQL.
¤ Which sign indicates a parameter in this situation?
¤ oledb objects should mean "%" or what?
¤ Sample code:
¤ cmdIns.CommandText =
¤ "INSERT INTO qPersoner ( Företag, FNamn, ENamn" & _
¤ ", Adressrad1, Adressrad2, Pnr, Ort, Tfn1, Tfn2, Epost, " & _
¤ "Medlem, Medlemsnr, Instruktör, Aktiv, Livinfo)" & _
¤ " Values (%,%, %, %, %, %, %, %, %, %, %, %, %, %, %)"

The placeholder is a question mark, not a percent sign. Here is an example:

Dim AccessConn As System.Data.OleDb.OleDbConnection
Dim AccessCommand As System.Data.OleDb.OleDbCommand
Dim strValue As String = "This is a test"
AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

AccessCommand = New System.Data.OleDb.OleDbCommand
AccessCommand.Connection = AccessConn
AccessCommand.CommandText = "INSERT INTO MyTable(email) VALUES (?)"
AccessCommand.CommandType = CommandType.Text
AccessCommand.Parameters.Add("email", strValue)

AccessCommand.ExecuteNonQuery()

AccessConn.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Vladimir Sergeyev [MS]]

Regarding #1: this only applies to OleDb and Odbc ADO.Net providers. If
you're using Oracle ADO.Net provider, the placeholders are parameter names
with ":" added. For example:

OracleCommand myCommand = new OracleCommand("inset into MyTable
valuesp1)", myConn);
OracleParameter myParam = new OracleParameter();

myParam.Name = "p1";
myParam.OracleType = OracleType.VarChar;
myParam.Value = "some value";
myCommand.Add(myParam);
myCommand.ExecuteNonQuery();


Note that parameter name that is passed to OracleParameter does not have
":".

 

[Vladimir Sergeyev [MS]]

One correction: "myParam.Name" should be "myParam.ParameterName" ...

--
Vladimir Sergeyev
ADO.Net Test Team
Microsoft Corporation