Access Stored Query

[sean]

Hi There,

I am trying to use ms access stored querys, I can get the query working as
SQL statements, I would like some help with the syntax for a stored query if
possible. Could someone paste a little code for me.

Sean

Dim firstname as string

firstname = "sean"

Dim myConnection As New OleDbConnection
Dim myCommand As OleDbCommand

myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA
Source=" & server.mappath("db_news_clicks.mdb"))
myConnection.Open()
myCommand = New OleDbCommand( "Insert INTO clicks ( url) Values ( @pUrl )",
myConnection )
myCommand.Parameters.Add( New OleDbParameter( "@pUrl", OleDbType.Varchar,
30 ))
myCommand.Parameters( "@pUrl" ).Value = FirstName
myCommand.ExecuteNonQuery()

 

[Miha Markic]

Hi sean,

Replace
Insert INTO clicks ( url) Values ( @pUrl )"
with
Insert INTO clicks ( url) Values (?)"

 

[Graeme Richardson]

Hi Sean, this is summarised code I'm using to bind a datagrid to the
tlkpTown table (See InsertCommand for the closest to what you're after).

Hope this helps, Graeme

' In header of class
'Imports System
'Imports System.Data
'Imports System.Data.OleDb

Public Sub BindTable(ByVal vstrTableName As String, ByVal vstrPrimaryKey As
String)

madpDataAdapter = New OleDbDataAdapter
madpDataAdapter.SelectCommand = SelectCommand
madpDataAdapter.InsertCommand = InsertCommand
madpDataAdapter.UpdateCommand = UpdateCommand
madpDataAdapter.DeleteCommand = DeleteCommand

mstrPrimaryKey = vstrPrimaryKey
mdtDataTable = New DataTable(vstrTableName)
madpDataAdapter.Fill(mdtDataTable)
mgrdTown.DataSource = mdtDataTable
mbmbBindingManagerBase = mgrdTown.BindingContext(mdtDataTable)

End Sub

Public Function SelectCommand() As OleDbCommand
' Filter for all tlkpTown records
SelectCommand = New System.Data.OleDb.OleDbCommand
SelectCommand.CommandText = "qfltTown"
SelectCommand.CommandType = CommandType.StoredProcedure
SelectCommand.Connection = mcnnConnection
End Function

Public Function UpdateCommand() As OleDbCommand
' Update one tlkpTown record
UpdateCommand = New OleDbCommand
UpdateCommand.CommandText = "qupdTown"
UpdateCommand.CommandType = CommandType.StoredProcedure
UpdateCommand.Connection = mcnnConnection
UpdateCommand.Parameters.Add(New OleDbParameter("plngUidTown",
OleDbType.Integer, 0, "uidTown"))
UpdateCommand.Parameters.Add(New OleDbParameter("pstrTown",
OleDbType.VarChar, 20, "Town"))
End Function

Public Function InsertCommand() As OleDbCommand
' Insert one tlkpTown record
InsertCommand = New OleDbCommand
InsertCommand.CommandText = "qappTown"
InsertCommand.CommandType = CommandType.StoredProcedure
InsertCommand.Connection = mcnnConnection
InsertCommand.Parameters.Add(New OleDbParameter("pstrTown",
OleDbType.VarChar, 20, "Town"))
End Function

Public Function DeleteCommand() As OleDbCommand
' Delete one tlkpTown record
DeleteCommand = New OleDbCommand
DeleteCommand.CommandText = "qdelTown"
DeleteCommand.CommandType = CommandType.StoredProcedure
DeleteCommand.Connection = mcnnConnection
DeleteCommand.Parameters.Add(New OleDbParameter("plngUidTown",
OleDbType.Integer, 0, "uidTown"))
End Function

 

[sean]

Hi Graham,

Thank you very much, with your help I was able to run the stored query with
out any problems!!!

Merry Christmas & Happy New Year

Sean