I am using vs.net 2008 vb and mysql I am using datasets alot in this application, however I am having a few small snags I am running into.

I created a login system that works quite well, but I need using the db or settings or a variable keep the logged in staffid and username constant on all the forms through out the forms. I have no idea how to do this. as it is information that in the end also has to be printed on the printout.

I can do a search that works very well when comparing to a table and row say example I can search by clientid2 if it does not exist it opens up a new window for the information to start to be gathered which holds dataset information in it to be accumulated. That kind of search I am ok with.

My search problem is I need to do a filter I guess with a dataset search and if it does exist it will pull that record up in detail view for editing or updating. There is a small catch to this though I need it to not overwrite the old data but keep the clientid1 and write a whole new set of information to the table or move the old set to a historical table and write the new set with everything updated and changed and the old information that is propagated to the form to the original table. I tried to do the search with a filter similar to

SELECT * FROM  `hinfo` WHERE  `clientid1` LIKE '%'

But it never gives me the fillby text box just a fillby button

My Last Problem is I have tables with all known zipcodes, I need it to when filled out on a form the zip code to fill in the City and State and County my tables for this are zip_code City State County.

To many of you this may be easy stuff, but I have been trying to solve these problem for the last 3 days. So any help in this would be greatly appreciated.

Thank you for your time
Charles

created a login system that works quite well, but I need using the db or settings or a variable keep the logged in staffid and username constant on all the forms through out the forms. I have no idea how to do this. as it is information that in the end also has to be printed on the printout.

Add a module to your project, in it you can declare Public variables to store the staffid and username.

My search problem is I need to do a filter I guess with a dataset search and if it does exist it will pull that record up in detail view for editing or updating. There is a small catch to this though I need it to not overwrite the old data but keep the clientid1 and write a whole new set of information to the table or move the old set to a historical table and write the new set with everything updated and changed and the old information that is propagated to the form to the original table. I tried to do the search with a filter similar to

The Tables property in the DataSet has a Select method that you can use to filter out the contents of your dataset.
It returns an array of DataRow.

Dim row() As DataRow
row = yourdataset.Tables("Clients").Select("<here you enter an SQL where-clause")

My Last Problem is I have tables with all known zipcodes, I need it to when filled out on a form the zip code to fill in the City and State and County my tables for this are zip_code City State County.

Assuming that these tables are also stored in the same DataSet as your clients and staff, you can use the same technique as above to query the dataset.

Dim cityRow() As DataRow
cityRow = yourdataset.Tables("City").Select("zipcode = '" & zipcode & "'")
Dim stateRow() As DataRow
stateRow = yourdataset.Tables("State").Select("city = '" & cityRow(0) & "'")
Comments
Very very helpful.

Ok I will try these could yougive me any help in the module you are talking about? I have not done modules as of yet. as for the search function I can do a search of my database but it never gives me the fill by textbox it gives me a fillby button as I said I need it to display in a detail view not the normal gridview not sure if that part makes a difference or not. Am heading into my vs.net 2008 to try out the help you have given me thank you by the way for the help and pointing in the right direction.

I am sorry also I mis-stated about the zip code thing, I have one table called zipcodes inside it are zip_codes State City County as the structure inside it the key is the zipcode itself. Would that make much difference in the coding you showed me?

Edited 6 Years Ago by DelDemoire: n/a

Ok I will try these could yougive me any help in the module you are talking about? I have not done modules as of yet.

As you would add a class to your project, you can add a module.
A module is just an empty code file that is declared as Module.

Module nameOfModule
   'Here you can collect a bunch of independent and un-related methods.
   'Including constants and publicly declared variables.
End Module

as for the search function I can do a search of my database but it never gives me the fill by textbox it gives me a fillby button as I said I need it to display in a detail view not the normal gridview not sure if that part makes a difference or not.

I'm sorry, but i don't understand what you mean by "fill by textbox" and "fill by button".
I'm assuming that you wish to display the information in a ListView or a DataGridView.
The ListView has a property named View that can be set to Details, which gives it a similar look as a DataGridView.
And the DataGridView can bind to a DataSet to display the data in a spreadsheat similar fashion.

I am sorry also I mis-stated about the zip code thing, I have one table called zipcodes inside it are zip_codes State City County as the structure inside it the key is the zipcode itself. Would that make much difference in the coding you showed me?

Not at all. Just two fewer lines of code to write. :)

Dim zipcodeRows() As DataRow
zipcodeRows= yourdataset.Tables("zipcodes").Select("zipcode = '" & zipcode & "'")

As you would add a class to your project, you can add a module.
A module is just an empty code file that is declared as Module.

Module nameOfModule
   'Here you can collect a bunch of independent and un-related methods.
   'Including constants and publicly declared variables.
End Module

I understand how to add a module from the add item menu, my problem is I do not understand what I would be needing to use in it or how to use it to collect the username and staffid to carry through the application throughout the entire workday. As the StaffId and the Preferably their Last Name which is all related in the database need to be displayed and also printed on all documents they print from the application. I do appreciate all your help, and thank you for taking the time to continue to respond, as I know how frustrating it can be to try and explain something with no idea what the other person has done so far.

I'm sorry, but i don't understand what you mean by "fill by textbox" and "fill by button".
I'm assuming that you wish to display the information in a ListView or a DataGridView.
The ListView has a property named View that can be set to Details, which gives it a similar look as a DataGridView.
And the DataGridView can bind to a DataSet to display the data in a spreadsheat similar fashion.

what I am doing is I go to my datasets I select from them detail view from the drop down on each table I am using. What I need to do is use the filter option and specify it to filter by wild card I guess of something similar to my first post where is makes a mysql query I guess, similar to:

SELECT * FROM  `hinfo` WHERE  `clientid1` LIKE '%'

Truth is I may not even be doing this in the most optimal way possible. As what I need is a text box that they put in the clientid hit search if it comes up it will bring up on a seperate form the case file for that client they searched for, if not it routes to a different form to add them.

Not at all. Just two fewer lines of code to write. :)

Dim zipcodeRows() As DataRow
zipcodeRows= yourdataset.Tables("zipcodes").Select("zipcode = '" & zipcode & "'")

Awesome so exactly how would I implement this so that it would when the staff enters into a text box say zipcodetextBox that it would then go through and fill in the citytextBox and the statetextBox with the appropriate City and state? Yes I am reaching beyond my skillsets on these three things. I Actually have a added problem but will get to that later after I fix these three problems.

Thank you for all your time and consideration in this matter.

Edited 6 Years Ago by DelDemoire: n/a

I understand how to add a module from the add item menu, my problem is I do not understand what I would be needing to use in it or how to use it to collect the username and staffid to carry through the application throughout the entire workday. As the StaffId and the Preferably their Last Name which is all related in the database need to be displayed and also printed on all documents they print from the application.

I was responding as to your first query on how to carry StaffId and Last Name through all forms.
In your login system you simply assign values to these variables and you can then use them from anywhere else in your program.

Module nameOfModule
   Public intStaffId As Integer
   Public strLastName As String
End Module

Truth is I may not even be doing this in the most optimal way possible. As what I need is a text box that they put in the clientid hit search if it comes up it will bring up on a seperate form the case file for that client they searched for, if not it routes to a different form to add them.

Here's one idea. In your Search buttons click event.

Private sub bnSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
   If txtClientIdSearch.Text.Equals("") Then Exit Sub

   Dim con As MySqlConnection = New MySqlConnection("connectionstring")
   Dim da As MySqlDataAdapter = Nothing
   Dim SQL As String = "SELECT * FROM hinfo WHERE clientid1 = '" & txtClientIdSearch.Text & "'"

   Try
      con.Open
      da = New MySqlDataAdapter(SQL, con)
      da.Fill(detailsDS)
      con.Close()

      Dim frm As DetailsForm = Nothing
      If detailsDs.Tables.Count > 0 AndAlso detailsDS.Tables(0).Rows.Count > 0 Then
         frm = New DetailsForm(detailsDS)
      Else
         MsgBox.Show("No details found for this client")
         frm = New DetailsForm()
      End If
      frm.Show()
   Catch ex As Exception
      'Do nothing
   End Try
End Sub

Public Class DetailsForm
   Private detailsDS As DataSet = Nothing

   Public Sub New()
      InitiliaizeComponents()
   End Sub

   Public Sub New(details As DataSet)
      InitializeComponents()
      detailsDS = details
   End Sub

   Private Sub DetailsForm_Load(ByVal sender As Object, ByVal e As EventArgs)
      If detailsDS IsNot Nothing Then
         'Code for filling out the form with information from the dataset.
      End If
   End Sub
End Class

In your form for showing details you create two constructors, one of which takes a dataset as an argument.
In the form load event of that form you can then check to see if the dataset was assigned and display the content, or an empty form for adding information.

Awesome so exactly how would I implement this so that it would when the staff enters into a text box say zipcodetextBox that it would then go through and fill in the citytextBox and the statetextBox with the appropriate City and state?

Perhaps a keydown event would be useful for the zipcodetextbox.
The user types a zipcode into the textbox and finishes with pressing the Enter key.

Private sub zipcodetextbox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
   If e.KeyCode = Keys.Enter Then
      Dim zipcodeRows() As DataRow
      zipcodeRows= yourdataset.Tables("zipcodes").Select("zipcode = '" & zipcodetextbox.Text & "'")
      If rows.Length > 0 Then
         citytextbox.Text = rows(0).Item("city")
         statetextbox.Text = rows(0).Item("state")
      End If
   End If
End Sub
Private sub zipcodetextbox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
   If e.KeyCode = Keys.Enter Then
      Dim zipcodeRows() As DataRow
      zipcodeRows= yourdataset.Tables("zipcodes").Select("zipcode = '" & zipcodetextbox.Text & "'")
      If rows.Length > 0 Then
         citytextbox.Text = rows(0).Item("city")
         statetextbox.Text = rows(0).Item("state")
      End If
   End If
End Sub

it is telling me rows is not declared in this coding if I change it to datarow is says that Length is not a member of System.Data.DataRows

Module nameOfModule
   Public intStaffId As Integer
   Public strLastName As String
End Module

I set this up as a module my question is about this, on my login all they put in is a username and password how will I assign the Staffid and their Last name to it? Silly question I am sure, but I am a bit lost on it as I am suspecting if I was keeping their username in the Module I would assign it similar to

Dim username.Textbox = strUsername

Correct me if I am off on this constructor if you would please and thank you so much for all your help.

Edited 6 Years Ago by DelDemoire: n/a

it is telling me rows is not declared in this coding if I change it to datarow is says that Length is not a member of System.Data.DataRows

I'm sorry, that line should state If zipcodeRows.Length > 0 Then

I set this up as a module my question is about this, on my login all they put in is a username and password how will I assign the Staffid and their Last name to it? Silly question I am sure, but I am a bit lost on it as I am suspecting if I was keeping their username in the Module I would assign it similar to

Dim username.Textbox = strUsername

Correct me if I am off on this constructor if you would please and thank you so much for all your help.

I'm sure that you have code in place to verify the username and password against the database.
You might wanna modify that slightly so that if it's a successful verification, then the return would contain information so that you can assign the public variables intStaffId and strLastname.
For example:

Private Sub VerifyLogin(Username As String, Password As String)
   If Not Username.Equals("") AndAlso Not Password.Equals("") Then
      Dim verifyRows() As DataRow
      verifyRows = yourdataset.Tables("Staff").Select("Username = '" & Username & "' AND Password = '" & Password & "'")
      If verifyRows.Length > 0 Then
         intStaffId = verifyRows(0).Item("StaffId")
         strLastName = verifyRows(0).Item("LastName")
      End If
   End If
End Sub
This question has already been answered. Start a new discussion instead.