passing value from database to a variable

[khentz]

Hi,
Please help me how to pass value from database to a variable.
This is my query:

Dim it As String = "Mouse"
             Dim itNo As String
             SELECT * FROM tblITEquipmentName WHERE equipmentName LIKE '" & it & "%'"

I would like to get the ItEquipmentNo and pass it to variable itNo
Thanks.

[Pgmer]

Try
            Dim cn As SqlConnection
            Dim strCnn As String = "Your data base connection string"
            cn = New SqlConnection(strCnn)
            cn.Open()
            Dim comm As New SqlCommand("SELECT ItEquipmentNo  FROM tblITEquipmentName WHERE equipmentName LIKE '" & it & "%'", cn)
            '' If ur using Sp then Commandtype= CommandType.StoredProcedure if it is Text then comm.CommandType=CommandType.Text
            comm.CommandType = CommandType.Text
            Dim ds As SqlDataReader
            ds = comm.ExecuteScalar
            If ds.HasRows Then
                While ds.Read
                    ''
                    ' do what u want here using the data
                    Dim x As String = ds.Item("columnname")
                End While
            End If

            ''Close your connections and commands.
            cn.Close()

        Catch ex As Exception
            ''Handle error if any
        End Try

See if this can help u

[khentz]

I've tried your code.,but when the i click the button,.the messagebox pop without the value of equipmentNameID

Dim it As String = "Mouse"

            Dim strQueryITName As String = "SELECT equipmentNameID FROM tblITEquipmentName WHERE equipmentName LIKE '" & it & "%'"
            Dim cmdQueryITName As OleDbCommand = New OleDbCommand(strQueryITName, myConnection)
            Dim queryITNameReader As OleDbDataReader
            cmdQueryITName.CommandType = CommandType.Text

            queryITNameReader = cmdQueryITName.ExecuteReader

            If queryITNameReader.HasRows Then
                While queryITNameReader.Read

                    Dim it2 As String = queryITNameReader.Item("equipmentNameID")

                End While
            End If

[Pgmer]

When ur debugging the code are you getting anything into it2? And which database are u using? i dont think ur using SQL, i guess its Access database.. please check if the Query is correct and also u have data in database for that select statement

[khentz]

the it2 doesn't return anything. Yes, I'm using Access for my database. And my query is correct, because i've tried to put a message box in between the while statement. By the way, the equipmentNameID column is the primary key.

[Pgmer]

queryITNameReader.HasRows is true? and control is coming to Dim it2 As String = queryITNameReader.Item("equipmentNameID") point? if it has row it should fetch and assign to it2

[khentz]

Yep,. it has a row and it's true. That's what I'm expecting too. But when the message box pop up, it returns nothing. I've tried also to view the result in the label but the it gave the same result.

[khentz]

Thanks dude!.. I've already done it. :)

Dim strQ As String = "SELECT * FROM tblITEquipmentName WHERE equipmentName = 'Mouse'"
            Dim cmdQ As OleDbCommand = New OleDbCommand(strQ, myConnection)
            Dim QReader As OleDbDataReader
            Dim it As String

            QReader = cmdQ.ExecuteReader

            If QReader.HasRows Then
                While QReader.Read
                    it = QReader.Item("equipmentNameID")
                    MsgBox(it)
                End While
            End If