CODEWITHSUNDEEP
asp.netvb.net
adaam
adaam

Reputation: 3706

Adding Session Variable as OleDbParameter - running into error "

I am using a custom ASP.NET control that I found here: http://www.codeproject.com/Articles/5347/DataCalendar

I have used one of the templates in the source file download-able from the page above as a starting point for my custom calendar. My aim is to only display events that the current user has created. I am doing this by creating a session variable called "userName" and I am parameterizing it in a query like so:

Function GetEventData(startDate As DateTime, endDate As DateTime) As DataTable
    '--read data from an Access query
    Dim con As OleDbConnection = GetConnection()
    Dim cmd As OleDbCommand = New OleDbCommand()
    cmd.Connection = con
    cmd.Parameters.AddWithValue("@currentUser", Session("currentuser"))
    cmd.CommandText = String.Format("Select EventDate, CreatedBy, Count(*) From EventInfo Where (CreatedBy = @currentUser) and EventDate >= #{0}# And EventDate <= #{1}# Group By EventDate", _
                                    startDate, endDate)
    Dim ds As DataSet = New DataSet()
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
    da.Fill(ds)
    con.Close()
    Return ds.Tables(0)
End Function

Unfortunately I am receiving this error:

Parameter[0] '' has no default value.

I have ensured that I am logged in so it is not a problem of there being no value for User.Identity.Name (I don't think). I am creating the session variable in the Page Load sub:

Sub Page_Load(o As Object, e As EventArgs)
        Session("currentuser") = User.Identity.Name
    End Sub

So, what's going wrong?

Upvotes: 0

Views: 143

Answers (1)

jlvaquero
jlvaquero

Reputation: 8785

From MSDN :

The OLE DB.NET Provider does not support named parameters for passing parameters to an SQL Statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter.

Try:

cmd.CommandText = String.Format("Select EventDate, CreatedBy, Count(*) From EventInfo Where ([CreatedBy = ?]) and EventDate >= #{0}# And EventDate <= #{1}# Group By EventDate,CreatedBy", startDate, endDate)

Upvotes: 1

Related Questions