how to use params in vb data report command

[Alif]

Hi,

i need your urgent help!
i try to run my visual basic data report via code because i have 3 parameters that are used in the SQL query, and i don't know how to send them to the data report command query!.

do you know how do define the sql statement in Access with parameters ? do you know how do params are transferred to this sql statement from the code ? this sql statement should be set in visual basic data report command.

See my sql query:

"SELECT People.Id, People.Name, C_People.Kod_Course, Course.Name, C_People.Course_Date From C_People, People, Course WHERE C_People.OvNum = People.OvNum AND C_People.Kod_Course = " & Kod_Crs & "AND C_People.Course_Date = " & FromD_Crs & "AND C_People.LastDt = " & UpTD_Crs

thanks

[klintan]

In Access question mark is used for parameters, e.g.

SELECT
   People.Id, People.Name, C_People.Kod_Course, Course.Name, 
   C_People.Course_Date From C_People, People, Course 
WHERE C_People.OvNum = People.OvNum AND 
   C_People.Kod_Course = ? AND C_People.Course_Date = ? AND 
   C_People.LastDt = ?

You can then add the parameters to the command object in VB and specify the values for each parameter as required.

The parameters in the command object can have any name, e.g. @p1, @p2,@p3 or @Course, @Date, @LastDt or whatever you like.

[Alif]

Hi,

the code is in VB, here is a sample of it - it retrieve some data into params (Kod_Crs, FromD_Crs,UpTD_Crs), but then how can i relate these params to the access params in the data report command ?

Private Sub CmdSearch_Click()

' Save params for the report query
Dim Name_Crs As String
Dim FromD_Crs As Date
Dim UpTD_Crs As Date
Dim sql1 As String
Dim sql2 As String
Dim rsCrs As New Recordset
Dim rsCPeople As New ADODB.Recordset

Name_Crs = DataCombo1.Text

sql1 = "select Kod from Course where Name='" & Name_Crs & "';"
rsCrs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly
Kod_Crs = rsCrs.Fields("Kod").Value
FromD_Crs = FromDate.Value
UpTD_Crs = UpToDate.Value

...

end sub

thanks!

[klintan]

Look at this link for instance:
http://msdn.microsoft.com/library/de...mandparams.asp

[Alif]

hi,

it seems a vey good solution for me, thanks! I will try it later on.
shoudl I use in the code params the '@' before the parameters ?

[klintan]

No I don't think @ is necessary for parameters in Access, I always use it though so that I can have the same parameter names for SQL Server (my app can run with either a SQL Server or an Access DB).