where clause

Question

where clause

mkkb917

SSC Enthusiast

Points: 142
More actions
May 17, 2014 at 1:28 pm

#286775

sir
i am facing a strange issue
i am using visual studio with sql server express edition
i query designer i query the table as
SELECT TOP (1) StatusID
FROM STATUS
WHERE (UserName = 'temp') AND (Status = 'active')
ORDER BY StatusID DESC
it works fine and excellent
but when i query the same thing in my Vb code
it did not fill the datatable or did not return any value
the code is
Dim active As String
active = "active"
Dim DAStatusId As New SqlDataAdapter("SELECT StatusID FROM STATUS WHERE UserName = '" & txtBillUserName.Text & "' AND Status = '" & active & "' ORDER BY StatusID DESC ", con)
Dim DTStatusId As New DataTable
con.Open()
DAStatusId.Fill(DTStatusId)
con.Close()
If DTStatusId.Rows.Count <> 0 Then
' copy the Statusdate of a selected user to a class level variable
StatusNumber = (DTStatusId.Rows(0).Item(0)("StatusID"))
MsgBox("Date of activation is ", StatusNumber.ToString)
Else
MsgBox("did not find user avtivation date")
End If
i did not understand what is wrong with it
kindly take a look and help me
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure if the problem is in the missing TOP (1) from your VB Code.

Another problem that you might encounter is SQL Injection. What would happen if someone enters on txtBillUserName something like this?

' DROP TABLE Status --

It's possible and that's why you should use parametrized queries.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Could be a case of missing schema qualified reference. Don't rely on the default schema and add the schema name to the table name in the query.

Technically, there is nothing else wrong with the query but I suggest you follow Luis's advice on using parametrized querie.

😎

Dim DAStatusId As New SqlDataAdapter("SELECT StatusID FROM dbo.STATUS WHERE UserName = '" & txtBillUserName.Text & "' AND Status = '" & active & "' ORDER BY StatusID DESC ", con)

shubham.saxena67 Default port Points: 1490 More actions · Answer 3

Dear Friend,

Your Sql query seems good, Try to fill dataset like below:

Update Your VB code::

SqlDataAdapter da=new SqlDataAdapter("<Query here>",Connection Object);

DataSet ds;

da.fill(ds.Tables[0]);

may this will help you..