Sorx Sorx - 4 months ago 43
Vb.net Question

vb.net OleDbDataAdapter not working with Select From Where

If I Debug this I just get a Invalid Columnname Error("Name of the Object"). I am using a SQL database.

Protected Sub ddlKunden_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlKunden.SelectedIndexChanged
Dim strSql As String
Dim kontakt As String = ddlKunden.SelectedItem.Value
Dim dtbP As DataTable
Using connection As OleDbConnection = New OleDbConnection(strConnection)
connection.ConnectionString = strConnection
connection.Open()

'Kontaktpersonen laden
strSql = "SELECT * FROM Kontaktpersonen WHERE Nr =" & Chr(34) & kontakt & Chr(34)

dtbP = New DataTable()
Using dad As New OleDbDataAdapter(strSql, connection)
dad.Fill(dtbP)
End Using
ddlKontaktperson.Items.Clear()
ddlKontaktperson.DataSource = dtbP
ddlKontaktperson.DataTextField = "AP_Nam"
ddlKontaktperson.DataValueField = "ID"
ddlKontaktperson.DataBind()
End Using

ddlKontaktperson.Visible = True

End Sub


The Error pops at

dad.fill(dtbP)


It should select all rows Where Nr="SELECTED VALUE" and you select it in a dropdownlist. And all these rows should be saved in a Datatable then and are used in another dropdownlist.

It works when I try the exact same thing without the where.

Example:

'Kunden laden
strSql = "SELECT * FROM Kontakte"
dtbK = New DataTable()
Using dad As New OleDbDataAdapter(strSql, connection)
dad.Fill(dtbK)
End Using
ddlKunden.Items.Clear()
ddlKunden.DataSource = dtbK
ddlKunden.DataTextField = "Nr"
ddlKunden.DataValueField = "Nr"
ddlKunden.DataBind()

Answer

Please try following code You do not need to use "'" around parameters Just add these values as a parameter to the oledbcommand with its value and type Otherwise, your sql command will be vulnerable to sql injection

Dim strConnection As String = "Provider=sqloledb;Data Source=(local);" &
                              "Initial Catalog=kodyaz;" &
                              "User Id=sa;Password=sa"

Dim kontakt As Int32 = 1

Dim dtbP As DataTable

Using connection As OleDbConnection = New OleDbConnection(strConnection)
    connection.Open()

    Dim cmd As New OleDbCommand()
    cmd.Connection = connection
    cmd.CommandText = "SELECT * FROM Kontaktpersonen WHERE Nr = ?"
    cmd.Parameters.AddWithValue("Nr", kontakt)

    dtbP = New DataTable()
    Using dad As New OleDbDataAdapter(cmd)
        dad.Fill(dtbP)
    End Using

End Using