Results 1 to 7 of 7

Thread: [RESOLVED] [2005] Adding Parameters to DS at runtime

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    910

    Resolved [RESOLVED] [2005] Adding Parameters to DS at runtime

    How can I go about adding a parameter to a datasource at runtime? I am using VB as code. Here is what I have but it doesnt work.

    VB Code:
    1. Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    2.         If Not TextBox1.Text = "" Then
    3.             AccessDataSource2.SelectParameters.Add("City", TextBox1.Text)
    4.         End If
    5.         AccessDataSource2.DataBind()
    6.     End Sub

    City is the column name in the database.
    Thanks!

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    910

    Re: [2005] Adding Parameters to DS at runtime

    Ok... Here's what I have...

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    VB Code:
    1. Dim cn As OleDbConnection
    2.         cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\Work\My Documents\Visual Studio 2005\WebSites\DCR\App_Data\DCR.mdb")
    3.  
    4.  
    5.         Dim da As New OleDbDataAdapter("SELECT * FROM tblListings WHERE City = ?", cn)
    6.         Dim das As New DataSet
    7.  
    8.         If Not TextBox1.Text = "" Then
    9.             da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
    10.         End If
    11.  
    12.         da.FillSchema(das, SchemaType.Source, "tblListings")
    13.         da.Fill(das, "tblListings")
    14.  
    15.         DataList1.DataSource = das
    16.         DataList1.DataBind()
    17.     End Sub

    Now.. What I am trying to do is if something is entered into a textbox, then that needs to be added as a parameter. But I may have 20 text boxes that can have inputs. Now the problem I have is how do I go about adding WHERE clauses depending on what has information entered in it? For instance... Lets say someone typed something in on textbox4, now I want to add "State = ?" to the where statement being that textbox4 now has an input. How can I do this programatically?
    Thanks

  3. #3
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: [2005] Adding Parameters to DS at runtime

    If statements? Kind of ugly but it would work...

    VB Code:
    1. Dim boolWhere as boolean = false
    2.  
    3. if TextBox1.Text <> "" Then
    4.   strSQL = strSQL & " WHERE City = ?"
    5.   boolWhere = true
    6.   da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
    7. end if
    8.  
    9. if TextBox2.Text <> "" Then
    10.   if boolWhere = False then
    11.     strSQL = strSQL & " WHERE State = ?"
    12.     boolWhere = true
    13.   else
    14.     strSQL = strSQL & " AND State = ?"
    15.   end if
    16.  
    17.   da.SelectCommand.Parameters.Add("@State", OleDbType.VarChar, 80).Value = TextBox2.Text
    18. end if
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    910

    Re: [2005] Adding Parameters to DS at runtime

    That would work.... people would make fun of me though Is there any better solutions out there?

    Quote Originally Posted by SeanGrebey
    If statements? Kind of ugly but it would work...

    VB Code:
    1. Dim boolWhere as boolean = false
    2.  
    3. if TextBox1.Text <> "" Then
    4.   strSQL = strSQL & " WHERE City = ?"
    5.   boolWhere = true
    6.   da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
    7. end if
    8.  
    9. if TextBox2.Text <> "" Then
    10.   if boolWhere = False then
    11.     strSQL = strSQL & " WHERE State = ?"
    12.     boolWhere = true
    13.   else
    14.     strSQL = strSQL & " AND State = ?"
    15.   end if
    16.  
    17.   da.SelectCommand.Parameters.Add("@State", OleDbType.VarChar, 80).Value = TextBox2.Text
    18. end if

  5. #5
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: [2005] Adding Parameters to DS at runtime

    Heh, you could simplify it and have all your text boxs be sequentially numbered, then you could just do a for loop with a .FindControl("TextBox" & counter) to get the correct TextBox to check and then have a Select Case to see which parameter to add based on which box you were on if that would make you feel less laughed at.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  6. #6
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: [2005] Adding Parameters to DS at runtime

    Or instead of the Select Case, you could create a View in your Database that has sequential numbered field names (e.g. Field1, Field2 mapped to City, State) as well that map to your actual table and just do an insert against the view. Then it is simple code.

    for counter as int32 = 0 to 20

    MyTextBox = me.FindControl("TextBox" & counter)

    if MyTextBox.Text <> "" then

    da.SelectCommand.Parameters.Add("@Field" & counter, OleDbType.VarChar, 80).Value = MyTextBox.Text

    Or something like that...
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    910

    Re: [2005] Adding Parameters to DS at runtime

    All are good ideas...
    Thanks!!

    Quote Originally Posted by SeanGrebey
    Or instead of the Select Case, you could create a View in your Database that has sequential numbered field names (e.g. Field1, Field2 mapped to City, State) as well that map to your actual table and just do an insert against the view. Then it is simple code.

    for counter as int32 = 0 to 20

    MyTextBox = me.FindControl("TextBox" & counter)

    if MyTextBox.Text <> "" then

    da.SelectCommand.Parameters.Add("@Field" & counter, OleDbType.VarChar, 80).Value = MyTextBox.Text

    Or something like that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width