Adding search option into a generated data form.

[EStallworth]

First off let me say that I am relatively new to the .NET framework and am making the transition from VB6 while writing this program learning as I go.

I am having trouble manipulating my data form to search through records.

I generated a data form using the wizard and am trying to add an option to sort through records based on certain criteria.

I have not been working too much with OLEDB adapters and did not want to create a parameter collection, so I added an SQL Data Adapter in order to parameterize my SQL. Though to sort I had to clear the predefined data bindings and create new ones. The problem is that it takes the functionality away from the form. After I search I am unable to update or add another record due to clearing the original bindings. Can anyone suggest an alternate route? Any suggestions would be appreciated. Thx.

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
DataSet161.Clear()
 
editID.DataBindings.Clear()
editAcctCode.DataBindings.Clear()
editName.DataBindings.Clear()
editBusinessType.DataBindings.Clear()
editNextPeriod.DataBindings.Clear()
editFirstPeriod.DataBindings.Clear()
editLastPeriod.DataBindings.Clear()
editAcctType.DataBindings.Clear()
editNumUnits.DataBindings.Clear()
editFein.DataBindings.Clear()
editmCountry.DataBindings.Clear()
editmStreet1.DataBindings.Clear()
editmStreet2.DataBindings.Clear()
editmCity.DataBindings.Clear()
editmState.DataBindings.Clear()
editmZipcode.DataBindings.Clear()
editlCountry.DataBindings.Clear()
editlStreet1.DataBindings.Clear()
editlStreet2.DataBindings.Clear()
editlCity.DataBindings.Clear()
editlState.DataBindings.Clear()
editlZipcode.DataBindings.Clear()
editPCPhone.DataBindings.Clear()
 
editID.DataBindings.Add(New Binding("Text", DataSet161, "org.id"))
 
editAcctCode.DataBindings.Add(New Binding("Text", DataSet161, "org.acctcode"))
 
editName.DataBindings.Add(New Binding("Text", DataSet161, "org.name"))
 
editBusinessType.DataBindings.Add(New Binding("Text", DataSet161, "org.businesstype"))
 
editNextPeriod.DataBindings.Add(New Binding("Text", DataSet161, "org.nextperiod"))
 
editFirstPeriod.DataBindings.Add(New Binding("Text", DataSet161, "org.firstperiod"))
 
editLastPeriod.DataBindings.Add(New Binding("Text", DataSet161, "org.lastperiod"))
 
editAcctType.DataBindings.Add(New Binding("Text", DataSet161, "org.accttype"))
 
editNumUnits.DataBindings.Add(New Binding("Text", DataSet161, "org.numunits"))
 
editFein.DataBindings.Add(New Binding("Text", DataSet161, "org.fein"))
 
editmCountry.DataBindings.Add(New Binding("Text", DataSet161, "org.mcountry"))
 
editmStreet1.DataBindings.Add(New Binding("Text", DataSet161, "org.mstreet1"))
 
editmStreet2.DataBindings.Add(New Binding("Text", DataSet161, "org.mstreet2"))
 
editmCity.DataBindings.Add(New Binding("Text", DataSet161, "org.mcity"))
 
editmState.DataBindings.Add(New Binding("Text", DataSet161, "org.mstate"))
 
editmZipcode.DataBindings.Add(New Binding("Text", DataSet161, "org.mzipcode"))
 
editlCountry.DataBindings.Add(New Binding("Text", DataSet161, "org.lCountry"))
 
editlStreet1.DataBindings.Add(New Binding("Text", DataSet161, "org.lStreet1"))
 
editlStreet2.DataBindings.Add(New Binding("Text", DataSet161, "org.lStreet2"))
 
editlCity.DataBindings.Add(New Binding("Text", DataSet161, "org.lCity"))
 
editlState.DataBindings.Add(New Binding("Text", DataSet161, "org.lState"))
 
editlZipcode.DataBindings.Add(New Binding("Text", DataSet161, "org.lZipcode"))
 
editPCPhone.DataBindings.Add(New Binding("Text", DataSet161, "org.pcphone"))
 
SqlDataAdapter1.SelectCommand.Parameters("@param2").Value = ComboBox1.Text
 
SqlDataAdapter1.Fill(DataSet161)
 
EndSub

 

[vis781]

Ick!!! I don't mean to be rude, sorry. Forget the dataform wizard your never going to learn anything that way. It's a good reference, but it does a lot of stuff that could be deemed as unnecessary. Start Again, although the connection string is a handy thing to store for later use. You can find it in the windows designer generateds bit.
Goto the designer and drop a dataset onto the form if the wizard kicks in just cancel it. Goto the properties window and add a datatable to the collection. Then add the datacolumns to the datatable, naming them as you go to match the columns in your database table. When you get that far post back and we'll do the next step, it's just a bit too much all for one post.

 

[EStallworth]

I figured someone would tell me that sooner or later! LOL! Was just trying to use it as a short cut. Got my dataset set up. Surely you meant for me to add this new dataset to a new windows form, correct? May be a dumb question but I like to clear up any and all uncertainties, no matter how small.

Since the data form wizard is using stuff that isn't needed should I just create a dataadapter fill the dataset using parameterized SQL, with a click event or other, and bind my textboxes to that dataset? When creating adapters in code I usually use the command builder. Is that a good idea or should I specify my own? Usually it works good enough. Though I was having a problem the other day sending the updated dataset to the DB. I created a table with the same layout as the DB, but the data being sent back is entered by the user and I was thrown an exception saying "Update unable to find Table Mapping['Table'] or Datatable 'Table" which after some googling leads me to believe the dataset being sent back is empty. So I added table mappings and it still is throwing an exception. Still working on it. From my understanding the table mappings are for relating incoming data from a DB but I figured I would still give it a try. Here is the code minus the details of the table itself.(Too drawn out)

row1 = table1.NewRow
row1("Identification Number") = id
row1("Account Number") = acctnum
row1("Name") = names
row1("Period") = periods
row1("Status") = status1
row1("Due Date") = duedates
row1("Return Type") = returntypes
row1("Number of Units Available") = numunits
row1("Number of Units Rented") = numrented
row1("Posting Date") = postingdates
row1("Payment Date") = paymentdates
row1("Receipt Date") = receiptdates
row1("Check Number") = checknum
row1("Check Amount") = checkamt
row1("Gross Receipts") = gross
row1("Exempt Receipts") = exempt
row1("Taxable Receipts") = taxable
row1("Adjustments") = adjust
row1("Total Due") = total
row1("Collection Allowance") = Decimal.Round(collection, 2)
row1("Penalty") = Decimal.Round(penalties, 2)
row1("Interest") = Decimal.Round(interests, 2)
row1("Total Amount Due") = totalamounts
row1("Total Paid") = totalspaid
row1("Return Balance") = returnbalances
row1("Account Balance") = acctbalance
table1.Rows.Add(row1)
 
'declaring new dataset, inserting table and binding it to a datagrid
Dim dataset13 AsNew DataSet
dataset13 = New DataSet
dataset13.Tables.Add(table1)
DataGrid1.SetDataBinding(dataset13, "Posts")
Dim octtdtconnstring AsString = ""
Dim cn AsNew SqlClient.SqlConnection(octtdtconnstring)
Dim sql As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select ID, Acctcode, 
Name, Period, RetnType, NumUnits, UnitsRented, Status, 
RetGross, RetExempt, RetTaxable, RetPreAdjust, RetAdjust, RetTaxDue, RetAllowance, RetPenalty, RetInterest, 
RetTotalDue, DueDate, InsertDate, PaymentDate, PostDate, ReceiptDate, CheckNumber, CheckAmount, 
Balance, TotalPay FROM Results ORDER BY AcctCode", cn)
'sql.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
With sql.TableMappings
.Add("ID", "Identification Number")
.Add("AcctCode", "Account Number")
.Add("Name", "Name/Business")
.Add("Period", "Period")
.Add("RetnType", "Return Type")
.Add("NumUnits", "Number of Units Available")
.Add("UnitsRented", "Number of Units Rented")
.Add("Status", "Status")
.Add("RetGross", "Gross Receipts")
.Add("RetExempt", "Exempt Receipts")
.Add("RetTaxable", "Taxable Receipts")
.Add("RetPreAdjust", "Taxable Receipts")
.Add("RetAdjust", "Adjustments")
.Add("RetTaxDue", "Total Due")
.Add("RetAllowance", "Collection Allowance")
.Add("RetPenalty", "Penalty")
.Add("RetInterest", "Interest")
.Add("RetTotalDue", "Total Amount Due")
.Add("DueDate", "Due Date")
.Add("InsertDate", "Due Date")
.Add("PostDate", "Posting Date")
.Add("PaymentDate", "Payment Date")
.Add("ReceiptDate", "Receipt Date")
.Add("CheckNumber", "Check Number")
.Add("CheckAmount", "Check Amount")
.Add("Balance", "Return Balance")
.Add("TotalPay", "Total Paid")
EndWith
 
 
Dim cmdbuild AsNew SqlClient.SqlCommandBuilder(sql)
sql.InsertCommand = cmdbuild.GetInsertCommand
sql.DeleteCommand = cmdbuild.GetDeleteCommand
sql.UpdateCommand = cmdbuild.GetUpdateCommand
 
Try
cn.Open()
cn.ConnectionTimeout.Equals(10)
'sql.Fill(dataset13) 'without this statement there is an exception being thrown due to dataset being "empty"
sql.Update(dataset13)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
EndTry

 

[vis781]

Surely you meant for me to add this new dataset to a new windows form, correct?

Correct, we'll start a-fresh on this form. As we go i'll be able to talk you through why your code on your generated bit doesn't work. Tell you what we'll do this the really easy way.

I've created a demo, see the attached file, It's really basic but it fills a datatable with 2 columns and binds them to textboxes. If I've totally insulted you by its simplicity then post back and i'll add the rest to update etc.

P.s I've used oledb but to change it to sqlclient is a matter of changing the namespaces. Just place the db.mdb file in C:\

 

[EStallworth]

Well there is nothing insulting about simplicity. Though this is very simple! LOL! The only thing I don't understand is using Me keyword. Never really used it much in VB6 but isn't that for public variables? I am assuming that is something new in the .NET framework.

 

[vis781]

'Me' is used to access anything that you have declared as being part of that class.You don't actually have to use the me keyword, but i always do e.g...

Private Mystring as string

Somewhere else.....

Me.Mystring = some other string

So now that you have seen my incredibly simple demo. What would you like it to do, and i'll add it with some commenting on what i'm doing. I understand from your first post that you want to do some searching based on criteria? What kind of criteria.

 

[EStallworth]

I am familiar with the keyword just thought it was for public use and not private as you used. Yeah I am just needing to filter through the records using the primary key or other distinguishing info to pull all associated fields to be inserted into textboxes or a datagrid in order to be edited and updated within the DB. I was using the wizard because it would automatically genereate the add, delete and update methods.

I am so glad that Monday is over and it is time to go home for the day. If there is a delay in my reply it's because I have an hour drive to get to the house.

 

[vis781]

Ok, i removed a textbox and added a combobox when you hit the button now and change the value in the combobox the row that the combobox reflects will be displayed in the textbox. Also added an update using the command builder object. Is that something like what you wanted?

 

[EStallworth]

Yes this is along the same lines of what I am looking for. Pulling from a lot more columns but the idea is the same. Good insight!

 

[EStallworth]

Thx!

Here is that form you have been helpiing with.

 

[Paszt]

Forget the dataform wizard your never going to learn anything that way.

Funny, I learned most of what I know about ADO.NET from studying the code generated by the wizard.

It's a good reference, but it does a lot of stuff that could be deemed as unnecessary.

I guess unnecessay is relative here. If you consider error trapping, well formed code, and Rapid Application Development unnecessary then I guess you could be right right.

 

[EStallworth]

Thx for the info dude! Trying to concentrate on learning more than I am on problem solving right now. It was better for me to see the structure step-by-step than using the wizard for the current form I am writing. I still like the wizard but needed to understand what it was doing. Learning VB6 in the classroom was easy, but upgrading to .NET without a teacher is kinda tricky since I only consult the book or this forum when a problem presents itself.

 

[vis781]

Originally Posted By Paszt
I guess unnecessay is relative here. If you consider error trapping, well formed code, and Rapid Application Development unnecessary then I guess you could be right right.

Is that supposed to be some kind of flippant remark or do you just not understand what the wizard does? Do you always choose the wizards against designing your own object model? Or is it that you just don't care what happens under the hood as long as your app 'works'
RAD does not apply to using wizards, RAD is a concept of vb.net language it is designed for RAD. I find your post somewhat insulting. What you say is completely ridiculous. Are you saying that you can't produce well formed code without the wizard?

 

[JohnH]

Wizards is a RAD feature, it makes setting up application faster, similar to using the Designer, as opposed to writing every little thing in code, even though the VB code itself is also RAD compared to more low level programming languages like C.