-
May 1st, 2006, 11:51 AM
#1
Thread Starter
Fanatic Member
[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:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not TextBox1.Text = "" Then
AccessDataSource2.SelectParameters.Add("City", TextBox1.Text)
End If
AccessDataSource2.DataBind()
End Sub
City is the column name in the database.
Thanks!
-
May 1st, 2006, 02:01 PM
#2
Thread Starter
Fanatic Member
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:
Dim cn As OleDbConnection
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")
Dim da As New OleDbDataAdapter("SELECT * FROM tblListings WHERE City = ?", cn)
Dim das As New DataSet
If Not TextBox1.Text = "" Then
da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
End If
da.FillSchema(das, SchemaType.Source, "tblListings")
da.Fill(das, "tblListings")
DataList1.DataSource = das
DataList1.DataBind()
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
-
May 1st, 2006, 03:53 PM
#3
Frenzied Member
Re: [2005] Adding Parameters to DS at runtime
If statements? Kind of ugly but it would work...
VB Code:
Dim boolWhere as boolean = false
if TextBox1.Text <> "" Then
strSQL = strSQL & " WHERE City = ?"
boolWhere = true
da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
end if
if TextBox2.Text <> "" Then
if boolWhere = False then
strSQL = strSQL & " WHERE State = ?"
boolWhere = true
else
strSQL = strSQL & " AND State = ?"
end if
da.SelectCommand.Parameters.Add("@State", OleDbType.VarChar, 80).Value = TextBox2.Text
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.
-
May 1st, 2006, 04:05 PM
#4
Thread Starter
Fanatic Member
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?
Originally Posted by SeanGrebey
If statements? Kind of ugly but it would work...
VB Code:
Dim boolWhere as boolean = false
if TextBox1.Text <> "" Then
strSQL = strSQL & " WHERE City = ?"
boolWhere = true
da.SelectCommand.Parameters.Add("@City", OleDbType.VarChar, 80).Value = TextBox1.Text
end if
if TextBox2.Text <> "" Then
if boolWhere = False then
strSQL = strSQL & " WHERE State = ?"
boolWhere = true
else
strSQL = strSQL & " AND State = ?"
end if
da.SelectCommand.Parameters.Add("@State", OleDbType.VarChar, 80).Value = TextBox2.Text
end if
-
May 3rd, 2006, 01:24 PM
#5
Frenzied Member
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.
-
May 3rd, 2006, 01:35 PM
#6
Frenzied Member
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.
-
May 3rd, 2006, 02:02 PM
#7
Thread Starter
Fanatic Member
Re: [2005] Adding Parameters to DS at runtime
All are good ideas...
Thanks!!
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|