Problem filtering datagrid

[Borracho1]

I just started with asp.net and i am having some problems defining the SQL statement. In the code i search the login account and i want this login name to filter the datagrid but there i get some errors


Sub page_load(Sender As Object, E As EventArgs)
Dim oConn as oledbconnection
Dim oCmd As OleDbCommand
Dim oParam as OleDbParameter
Dim sSQL As String
Dim objDataReader as OleDbDataReader

Dim arrSomething, strXPUser
arrSomething = split(Request.ServerVariables("LOGON_USER"),"\")
strXPUser = arrSomething(1)
response.Write(strXPUser)

oConn = new oledbconnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\Inetpub\

http://wwwroot\Obis2\DB\Inbev.mdb")

oConn.open()

sSQL = “SELECT * FROM tblpersoneel” 'WHERE [Personeelslidinlognaam] = strXPUser”
oCmd = New OleDbCommand(sSQL, oConn)

it must be a wrong use of the where clause but i can't find the answer. Hope someone can help me out here

Thanks borracho

 

[ca8msm]

You haven't built up the SQL String correctly. Try changing:

sSQL = “SELECT * FROM tblpersoneel” 'WHERE [Personeelslidinlognaam] = strXPUser”

to

sSQL = "SELECT * FROM tblpersoneel WHERE [Personeelslidinlognaam] = '" & strXPUser & "'"

I would also suggest using parameters instead of building your SQL String like that.



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Borracho1]

ok thanks a lot i will take a look at the use of parameters

borracho

 

[Borracho1]

Tried it but it won't work, the ' just puts the condition in comment.

But could you explain a little more what i have to change in order to make it a parameter query. I searched some code and tried to convert it to one but it just makes me more confused.


Sub page_load(Sender As Object, E As EventArgs)
Dim arrSomething, strXPUser
arrSomething = split(Request.ServerVariables("LOGON_USER"),"\")
strXPUser = arrSomething(1)
response.Write(strXPUser)

Dim oConn as oledbconnection
Dim oCmd As OleDbCommand
Dim sSQL As String = “SELECT * FROM tblpersoneel” WHERE (Personeelslidinlognaam) = ? )
Dim objDataReader as OleDbDataReader

oConn = new oledbconnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\Inetpub\

http://wwwroot\Obis2\DB\Inbev.mdb")

oConn.open()

oCmd.Parameters.Add("Personeelslidinlognaam", strXPUser)
oCmd = New OleDbCommand(sSQL, oConn)

'Get a datareader
objDataReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

'Do the DataBinding
dgResults.DataSource = objDataReader
dgResults.DataBind()

'Close the datareader/db connection
objDataReader.Close()
end sub

any help would be very appreciated

Borracho

 

[ca8msm]

Tried it but it won't work, the ' just puts the condition in comment.

That's because you keep closing the string variable with a double quote e.g.

Dim sSQL As String = "SELECT * FROM tblpersoneel[b]"[/b] WHERE (Personeelslidinlognaam) = ? )

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Borracho1]

ok thanks a lot for your help ca8msm, got it working.

Borracho

 

[Borracho1]

Sorry to bother again but i have another small question.

I used the above to populate a first datagrid and then i've put the value of the second column into a variable.

dim stri as string
stri = oDS.Tables(0).Rows(0)("FunctieID").ToString()

dim intab as integer
intab = cint(val(stri))

then i want to use this value to filter a second datagrid.
But despite the use of the same code as above it won't work or do i have to use other ' to compare to a number

dsSQL = "SELECT PersfunctieID, Checklijst FROM tblchecklijst WHERE [PersfunctieID] = '" & intab & "' "

Thanks
Borracho1

 

[ca8msm]

If it's just a number field, then you may not have to use quotation marks e.g.

dsSQL = "SELECT PersfunctieID, Checklijst FROM tblchecklijst WHERE [PersfunctieID] = " & intab

although some databases will accept it if you do. The above syntax you have written is correct, so I'd suggest running the query actually in the database itself to see if it returns any results (and therefore it should show you whether you have a problem with your code elsewhere, or whether it is a problem with the actual SQL).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Borracho1]

thanks a lot again, it worked immediately without the quotation marks.

Borracho1