dataset parameters?

[Samir Nagheenanajar]

ive got a dataset im trying to filter from a drop down list. Ive tried a couple different ways to filter the data set from the DDL but cant figure it out. I can successfully filter an Access Data Source from the DDL but not the Object Data Source(data set). I set up the data set in web developer and have no idea how to use a parameter. Could some one point me in the right direction?

[brin351]

You can not filter a dataset object. You can filter a dataview so create and fill a dataview from your dataset, filter and use it as the data source

dim ds as dataset 'presuming your data is in it
dim dv as dataview = ds.Tables(0).DefaultView
dv.rowFilter = "columnName = 'some value'"

'now bid it ti your control

This isn't great for performance but it depends on what you need to do

[Samir Nagheenanajar]

Sounds like thats what i need. My im not sure how to get my dataset into the code. I created my data set with visual web developer, and as far as i can tell i have an objectdatasource that is a table adapter. Im also not sure if i am binding it correctly. Heres my code.

solutionsDS is my objectdatasource

    Protected Sub projectsDrop_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim solutionsDS As Data.DataSet 'presuming your data is in it
        Dim dv As Data.DataView = solutionsDS.Tables(0).DefaultView
        dv.RowFilter = "Solution Key = " & projectsDrop.SelectedValue

        'now bid it ti your control
        FormView1.DataSource = dv
        FormView1.DataBind()
    End Sub

[mendhak]

I created my data set with visual web developer

You see, because that sentence didn't make any logical sense, I've understood that you used some sort of a drag-drop method to create your data set. Am I right?

If so, then I strongly suggest that you start hand-coding these things. Because you then gain a clearer understanding of the way ADO.NET works, and then you wouldn't be facing this little anomaly of a problem.

And yeah, your code looks fine. Does it work though?

[Samir Nagheenanajar]

yeah it was a drag and drop method. The code above is not working. But i agree hand coding is better, i think ill try starting over and ditch the drag and drop thing.

[Samir Nagheenanajar]

OK, Im working on doing this all manually, so I actually have a clue whats happening. I don't know how to properly set up the form view. The code posted runs with no errors but the formview doesnt show up. do i need to do something in asp to make this work, or is there more VB to work on?

Im pretty sure I need an ObjectDataSource, if so how do i programatically created one with the dataset Ive got.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        planningAreaText.Text = Session("planningAreaSess")
       
        'create connection
        Dim dpadCon As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\webPAD\acccessdb\AdminTables12.mdb")
        'create data set
        Dim dpadDs As Data.DataSet = New Data.DataSet("DPAD")
        'create commad object
        Dim dpadCmd As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("SELECT * FROM Solutions")
        'create data adapter and refer to connection
        Dim dpadDa As Data.OleDb.OleDbDataAdapter = New Data.OleDb.OleDbDataAdapter("SELECT * FROM Solutions", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\webPAD\acccessdb\AdminTables12.mdb")
        'Dim dpadDa As Data.OleDb.OleDbDataAdapter = New Data.OleDb.OleDbDataAdapter(dpadCmd)
        'fill dataset
        dpadDa.Fill(dpadDs, "solutionsDs")
        
        Dim dv As Data.DataView = dpadDs.Tables("solutionsds").DefaultView
        'dv.RowFilter = "[Solution Key] LIKE " & "'" & projectsDrop.SelectedValue & "'"


        'now bid it ti your control
        
        
        FormView1.DataSource = dv
        FormView1.DataBind()
    End Sub

and the asp.net

<asp:FormView ID="FormView1" runat="server">
    </asp:FormView>

[brin351]

You don't need an objectDatasource, that is just adding another layer of data access more suited to a nTier application design.

If you want to get down to basics forget the formView to place the controls (textbox, label etc..) you want on the page and set the text to display.

Here is some code - but the form view expects only 1 record and your query returns all records

'add these to top of code page
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration.ConfigurationManager




        'connect to DB
        Dim myConnection As OleDbConnection
        myConnection = New OleDbConnection(ConnectionStrings("dbConnection").ConnectionString)
        myConnection.Open()

        ' Build a sql statement string    
        Dim query As String = "SELECT * FROM Solutions"

        ' Initialize the sqlCommand with the new query string.   
        Dim Command1 As OleDbCommand = New OleDbCommand(query, myConnection)

        'Execute the command
        Dim Adapter1 As New OleDbDataAdapter(Command1)
        Dim ds As New DataSet
        Adapter1.Fill(ds)

        'always close your connection when you have the data
        myConnection.Close()

        Dim dv As DataView = ds.Tables(0).DefaultView

        FormView1.DataSource = dv
        FormView1.DataBind()

here is using the dataReader - a more efficient way to return recods. A WHERE clause (in query) and parameter are added to return specific record/s

        'connect to DB
        Dim myConnection As OleDbConnection
        myConnection = New OleDbConnection(ConnectionStrings("dbConnection").ConnectionString)
        myConnection.Open()

        ' Build a sql statement string    
        Dim query As String = "Select * FROM Solutions WHERE someField = @someField"

        ' Initialize the sqlCommand with the new sql string.   
        Dim Command1 As SqlCommand = New SqlCommand(query, connMain)

        'Create new parameters for the sqlCommand object and initialize them to the input values.	  
        Command1.Parameters.AddWithValue("@someField", "dropDown.selectedvalue or some value")

        'Execute the command
        Dim read1 As SqlDataReader = Command1.ExecuteReader()

        'bind to formView
        FormView1.DataSource = read1
        FormView1.DataBind()

        'or to display many records in a list

        GridView1.DataSource = read1
        GridView1.DataBind()

        'or if you want to bind a single record to controls
        If read1.HasRows Then
            read1.Read()
            label1.text = read1.Item("column1").ToString
            label2.text = read1.Item("column2").ToString
        End If
        read1.Close()

        'always close your connection when you have finished
        myConnection.Close()

Its best to place your database connection string in the web.config file and get it for all code form there. Reason being it's only in one place if it ever needs changing - like between your local machine and when the sites on the server.

thats what Imports System.Configuration.ConfigurationManager and
myConnection = New OleDbConnection(ConnectionStrings("dbConnection").ConnectionString) are doing

in your web.config add

	<connectionStrings>
    <add name="dbConnection" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\webPAD\acccessdb\AdminTables12.mdb"/>
  </connectionStrings>

[Samir Nagheenanajar]

Thank you so much. If i need to be able to run some sql insert/update commands am I better off using a dataset? or is there some better way if i read the data with the datareader and use some other function to write to the database? Thanks again.

[brin351]

Working with databases is using ADO.NET there are heaps of example in the .net sdk and on the web. Basically you make a connection, specify a query and exicute the command. It works the same for access and sql server except instead of having oledbCommand, oledbConnection etc you have sqlCommand, sqlConnection.

When updating / inserting you are not returning records so instead of exectueReader or fill a dataset from and adapeter you use command.executeNonQuery

        'UPDATE
        'create UPDATE oledb string
        Dim query As String = ("UPDATE tableName SET" & _
        " column1 = @column1, " & _
        " column2 = @column2 " & _
        " WHERE column3 = @column3 ")
        'Create the UPDATE Command
        Dim updateCMD As oledbCommand = New oledbCommand(query, dbConnection)

        'Add the parameter values
        updateCMD.Parameters.AddWithValue("@column1", textBox1.text)
        updateCMD.Parameters.AddWithValue("@column2", textBox2.text)
        updateCMD.Parameters.AddWithValue("@column3", "primaryKeyOfTable or ID field")

        'Execute the UPDATE Command
        Dim recordsAffected As Int32 = updateCMD.ExecuteNonQuery()


        '**********************************************************************************************************


        'INSERT
        ' Build a oledb INSERT statement string    
        Dim query As String = "INSERT INTO tableName " & _
        " (column1, " & _
        "  column2, " & _
        "  column3)" & _
        " VALUES (@column1, " & _
        " @column2, " & _
        " @column3 );"

        ' Initialize the oledbCommand with the new oledb string.
        Dim Command1 As oledbCommand = New oledbCommand(query, dbConnection)

        ' Create new parameters for the oledbCommand object and initialize them to the input values.	  
        Command1.Parameters.AddWithValue("@column1", textBox1.text)
        Command1.Parameters.AddWithValue("@column2", textBox2.text)
        Command1.Parameters.AddWithValue("@column3", textBox3.text)

        Command1.ExecuteNonQuery()