Help with databases

Help with databases

• Matthew.S.Davis

  Ok, now I feel even dumber.
  FYI, ATO is the TestNum

  Since the user enters an ATO number and a Vehicle, obviously the filter would change now too, in order to filter both the ATO and the Vehicle. Do you just add it on something like:
  Me.TripSheetInfoBindingSource.Filter = "ATO=" & atoEntryTextBox.Text & "Vehicle=" & vehicleEntryTextBox.Text
  Changing This throws an exception: Syntax Error: Missing operand after 'Vehicle' operator

  Here's the rest of my code:
  Dim tempCount As Integer = 0
  Dim tempTotal As Single
  Dim avgTemp As Single
  Dim dt As DataTable
  For Each dr As DataRow In Dt.Rows
  tempCount = tempCount + 1 ------------------------>Is there a way to not increment if the Temp = 0
  tempTotal = tempTotal + CType(dr.Item("BegTemp"), Single)
  Next
  If tempCount > 0 Then
  avgTemp = tempTotal / tempCount
  Else
  avgTemp = 0
  End If
  Return avgTemp
  End Function

  Ok, now, I'm in even deeper. To start, is there a reason you changed from a Sub to a Function What parameters should be in the call for the CalculateAverage(). Is there a way to eliminate the function/sub all together, I tried and just dim dt as datatable, and it tells me that dt is used before it has been assigned a value and a null reference exception could result at runtime...I do have a reason to eliminate the function/sub, but it's just to difficult to even try to explain. I can probably work around it though if I have to.
  To answer my question above, is there a way to temporarily assign the temp that you take from to the database to a dummy variable just to see if the Temp = 0, if it does equal zero, then don't increment the count...Some partial pseudocode:

  For Each dr As DataRow In Dt.Rows
  dummyTemp = TempFromDatabase
  If dummyTemp <> 0 Then
  tempCount += 1
  Else
  tempCount = tempCount
  tempTotal = tempTotal + CType(dr.Item("BegTemp"), Single)
  Next
  
  I'm in the middle of an extremely complex project, for me anyway, I've got myself in way over my head, so any continued help is appreciated.

  
  

• Steve Lasker -MS

  mmmmm i don't think there is other way to retrieve data from a database except with SQL

  which wizard do you talk about

  
  
  

• Sriram Naganathan

  hi,

  for sure there is a way to do this programmaticly, but dataview can select all the rows that contains TestNum,  VehNum as the user entered them, but can't go further more , you have to have iteration as what spooty said

  dim dv as new dataview(mytable,"TestNum  = "& textbox1.text &" and VehNum =  "& textbox2.text , "", DataViewRowState.CurrentRows)

  you can also use datarow collection to do the same thing

  dim datarows() as datarow =

  Mytable.Select("TestNum  = "& textbox1.text &" and VehNum =  "& textbox2.text )

  then you iterate through this collection

  Dim totalTemp as integer = 0

  for each dr as datarow in datarows

  totalTemp += dr("Temp")

  next

  in the dataview case you would iterate using DataRowView instead of dataRow

  for each dr as datarowview in dv

  totalTemp += dr("Temp")

  next

  then you can divided this totaltemp by the row count

   

   but for sure  it will be better if you retrieve the value directly from your database using SQl

  there is a differences between SQL , and sqlServer

  SQL (Structured query language) this language will be used to query your database like what the table adapter does and it will be used with almost all database types

  SQLServer is a database, not a language

  you can query your database like this

  dim sqlstring as string = "SELECT TestNum, VehNum, Avg(Temp) AS AvgOfTemp FROM MyTable GROUP BY TestNum, VehNum HAVING ((TestNum=["& textbox1.text &"]) AND (VehNum=["& textbox2.text &"]))"

  but you have to establish a connection with the database first , since you using MsAccess you can search this forum for OleDbCommand, and executeReader

  you will find some examples for how to do this

  http://forums.microsoft.com/MSDN/Search/Search.aspx words=executeReader&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=159

  hope this helps

   

  
  
  

• myEGO

  I disagree with the basic approach of the post above..

  I think the approach is "black box" and "cook book". I also think the wizards involved are very inflexible and they do not product code that is modifiable or quality code.

  That being the case, I so not support advocating these methods to users.

  
  
  

• VistaYou

  " I am not using SQL, I'm using an access database with the dataset and all that. 

   

  YaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaYYYYYYYYYYYY

  I found a little information suggesting a dataview.  Can anyone help me "

   

  Yes.  I've written a lot in this forum on Access.

  in the search engine... type Reneec and Access

  See what you come up with and ask questions from there. What do you feel you need a view/

  
  
  

• Bob Rogers

  The reason that this exception occurs was that it wasnt specifically coded to match you field names etc.    It was used as a demonstration of averaging a number of fields on a single record.

  VehNum                 Temp           Test1        Test2       Test3      Test4     ....
  123                            73                65              54            54            54   

  From your details now you are showing that the dataset contains the data in a different manner.   That is multiple records for a vehicle, one for each test so it is vertically shaped and not horizontal.   And you wish to process these.  

  So the following will iterate around the rows in a datatable and average the records in the TempField.  It will then return an average temp for that field.

  
      Function CalculateAverage(ByVal Dt As DataTable, ByVal TempField As String) As Single
          Dim iItemCount As Integer = 0
          Dim SglSumTemp As Single
          Dim SglAverage As Single
          For Each dr As DataRow In Dt.Rows
              iItemCount = iItemCount + 1

              SglSumTemp = SglSumTemp + CType(dr.Item(TempField), Single)
          Next
          If iItemCount > 0 Then
              SglAverage = SglSumTemp / iItemCount
          Else
              SglAverage = 0
          End If
          Return SglAverage
      End Function
  

   

  So the following requires no round tripping or additional calls to the SQL Database in order to calculate an average but does iterate around an existing datatable record.  

  Obviously now you see that it is critical to provide as much info as possible to describe your problem.

   

   

  
  

• PhotoJoe

  At this point, the person who asked this question has not posted again for 12 or so hours. I have to wonder if having four of the site moderators arguing over what is the best advice has scared him/her off.

  
  
  

• PkRichard

  "Access uses SQL .    SQL is structured Query Language and is used by access to retrieve records from the database."

  I believe this to be incredibly unfortunate. However you can minimize the use of SQL statement to just one. Which is to read data from a database to a table. I consider this a feature!

  
  
  

• James Alexander

  hi, terriM

  really i'm lost here ,do you have a dataset in your project or you don't , or do you want retrieve data from your database without dataset

  best regards

  
  
  

• Ashi

  I agree with you that beginners should be concentrating on programming which is why I recommend Access programming and iterative techniques because that will teach them fundamentals. It will teach them about tables, rows and columns and how to manipulate them.

  New programmers rarely ever have databases large enough to introduce any scaling issues it's almost unheard of this this board in fact.

  I also agree that for large undertaking a DBMS is the desired approach. There's no question about that. A DBMS is not an efficient use of resources for small databases - something which seems to be lost in today's discussions. So issues of scaling are used against my position but forgotten in discussing the actual scale of the projects that beginners are using.

  I do not believe that it's ever good advice to recommend the installation and use of a DBMS for a small project. That's not efficient use of resources and as you say it's distracting to beginners. If they learn dbms techniques they don't learn the fundamentals or understand what the dbms is doing undeneath.

  New developers here are rarely ever offered discussions of scaling. New users aren't even aware that it's an issue. That also needs to be considered in well rounded discussions with new developers.

  
  
  

• maitreg

  Change the following Code Lines

  Me.TripSheetInfoBindingSource.Filter = "ATO=" & atoEntryTextBox.Text & " AND Vehicle=" & vehicleEntryTextBox.Text

  Dim tempCount As Integer = 0
  Dim tempTotal As Single
  Dim avgTemp As Single
  Dim dt As DataTable
  For Each dr As DataRow In Dt.Rows

  if CType(dr.Item("BegTemp"), Single) <> 0 then
  tempCount = tempCount + 1
  tempTotal = tempTotal + CType(dr.Item("BegTemp"), Single)
  End if
  Next
  If tempCount > 0 Then
  avgTemp = tempTotal / tempCount
  Else
  avgTemp = 0
  End If
  Return avgTemp
  End Function
  

  
  

• Jiange Sun

  Ok, after looking through my project, I realized that the posting of my first question was a little simpler than it should have been.  It's hard to explain in words, so I'll give an example like I did before.

  TestNum           VehNum          Temp
  1234                    123               65
  4567                    546               72
  9087                    123               81
  1234                    256               79
  1234                    123               70
  4567                    546               69
  9087                    123               56
  1234                    256               83

  For example, the user would enter TestNum 1234 and VehNum 123

  A TestNum can have more than one vehicle, and a vehicle can be used in more than one test.

  Also, I'll need to be able to count the number of temperatures that I use, because it won't always be the same. And if the temp is zero, I want to ignore it (don't increment count if temp = 0).

  I just tried it using the VehNum like suggested, just to see if I could get it to work, and I got an exception:
  This is the code:
  Me.TripSheetInfoBindingSource.Filter = "Vehicle=" & atoEntryTextBox.Text '//Select Only Match
  CalculateAverage() '//Calculates Average

  '
  '
  Sub CalculateAverage()
  Dim i As Integer
  Dim sumTemps As Single
  Dim ICount As Integer = 3 ------------------------>
  For i = 1 To ICount
  Dim StrField = "Test" & i.ToString
  sumTemps = sumTemps + CType(Me.TripSheetInfoBindingSource.Current(StrField), Single)
  Next
  AvgTempTextBox.Text = (sumTemps / ICount).ToString
  End Sub

  This is the exception: (I bolded the line in the code that threw the exception)
  System.NullReferenceException was unhandled
    Message="Object variable or With block variable not set."

  Sorry for the confusion, and Thanks for the help.
  And if it changes anything, I'm using a component.
   

  
  

• psystems

  Hi Terri. I'm sorry that you seem to have recieved some conflicting advice here, but it seems I'm going to weigh in with yet another opinion.

  As has been said, you ARE using SQL, as you're using Access. SQL is another language, but not a difficult one. If you don't want to use SQL, you're better off not using Access, and storing your data as XML. You can at least then use XPath to find the things you're after instead of writing large chunks of iterative code.

  Because you're using a database, you can actually do this in one line of code. The SQL to get what you want would be something like

  Select AVG(temp) from tblVehicleTemps where vehicleID = 1

  assuming that tblVEhicleTemps is your table, and vechicleID and temp are the column names. Getting your data from the database is ALWAYS faster than sorting through it in code, that's why people use SQL databases in the first place, they are optimised for set based operations ( that is, operations across whole tables of data ) quickly and efficiently. Letting your database do what it does best is simply making the best use of the tools available to you.

  One caveat - I know that AVG is available in SQL Server, I don't know for sure if it's in Access.

  http://www.techonthenet.com/access/functions/numeric/avg.php

  Looks like it is.

  If you can write your SQL as functions/procedures within Access instead of via code, you will find they will run faster and be more secure. You won't notice the difference in this project, it's simply a best practice and the more you can start off by doing things properly, the more you'll build good habits that will serve you well in the long run.

  By all means, ask again if any of this is unclear. I can see that you've been given at least three opinions here. All of the ways you're being shown will work, the only difference is in how effecient these different approachs are, which means more in terms of which are useful to you in the long run. If you just want to get this working, then choose whichever approach you feel most comfortable with, but please bear in mind what I've said about what scales well to larger problems, and what does not.

  
  
  

• Garron Mosley

  OK, Lets get a few things clear here, Access uses SQL . SQL is structured Query Language and is used by access to retrieve records from the database.

  SQL Server is a DBMS which also uses SQL to access its database - but a slightly different flavor - both are different products but both use SQL.

  Now with that out the way. From your description I would assume that you already have a dataset populated in you application and you simply trying to filter it to a specific record and bring those details up.

  This should be pretty straightforward and I'll provide the code snippets to do something similar. I have a Textbox1 which contains the detail I want to filter On, and Textbox2 which is going to contain the Average of 3 fields Called Test1,Test2 and Test3 in the dataset. Everything else will assume you have a form that is databound to the dataset

  When I put something in the textbox1 and click the button it will display only those records matching the criteria and will also calculate an average based upon the currently selected item.

  
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  Me.Table1BindingSource.Filter = "VehicleID=" & TextBox1.Text '//Select Only Match
  CalculateAverage() '//Calculates Average
  End Sub

  Sub CalculateAverage()
  Dim i As Integer
  Dim sumTemps As Single
  Dim ICount As Integer = 3

  '//This is simply a way of Iterating around the Fields Called
  '//Test1, test2, Test3 in the database using the BindingSource.Current Property
  For i = 1 To ICount
  Dim StrField = "Test" & i.ToString
  sumTemps = sumTemps + CType(Me.Table1BindingSource.Current(StrField), Single)
  Next

  
  '//Set Textbox with Average
  TextBox2.Text = (sumTemps / ICount).ToString
  End Sub

  or it is possible to use a dataview

  Dim dv As DataView
  Me.Database1DataSet.Table1.DefaultView.RowFilter = "VehicleID=" & textbox1.text
  dv = Me.Database1DataSet.Table1.DefaultView

  and then use dv as your datasource.

  
  
• Help with databases