none
Accessing a DatRow in a Dataset in an ACCESS Database RRS feed

  • Question

  • Using vb.NET, with my project,  I have got as far as the LoginForm :-

    The following block of code works (To a point) :-

         GblIndexKey = UsernameTextBox.Text

         Dim Conn As New OleDbConnection(GblConnection)

         ACCESSCommand = "SELECT * FROM operators"

         Dim DataAdapter As New OleDbDataAdapter(ACESSCommand, Conn)

         Dim DSetOPERATORS As New DataSet("OPERATORS")

         DataAdapter.Fill(DSetOPERATORS, "operators")

         Dim DtblOPERATORS As New DataTable

         DtblOPERATORS = DSetOPERATORS.Tables("operators")

         Dim DRowOPERATORS As DataRow

         For Each DRowOPERATORS In DtblOPERATORS.Rows

              OPRNAME = DRowOPERATORS(0).ToString

              OPRPWORD = DRowOPERATORS(1).ToString

              OPRCAT = DRowOPERATORS(2).ToString

         Next

    etc, etc, etc.....

    The obvious problem with this is it will only work efficiently with a database with only one datarow. As my test database has 4 datarows, then the code reads through all of my dataset's datarows and I end up with the datacolumns from the last datarow in the variables OPRNAME, OPRPWORD & OPRCAT.

    Obviously, the SELECT command needs some refinement. And here is my problem :-

    I have altered the Select command to the following (and seemingly countless variations) :-

    "SELECT * FROM operators WHERE OPRNAME = GblIndexKey"

    No matter what I do, I always get the same error in the Error Message MessageBox :-

         OleDbException was unhandled.

         No value given for one or more required parameters.

    At this stage, I give up because I can't get past this problem. Can someone give me some help with this please?

     


    Captain Baz
    Sunday, January 9, 2011 3:06 AM

Answers

  • Thanks guy. That worked! There is no way I would have got any further without your help!

    As I have said before, it's not what the documentation tells you that is the problem, it's what it DOESN'T tell you!

    Regards Barry

    PS : Is there somewhere I can go on the internet to get more information on this issue?

     


    Captain Baz
    • Marked as answer by Captain Baz Tuesday, January 11, 2011 10:11 PM
    Tuesday, January 11, 2011 10:11 PM

All replies

  • I'm adding converted code which is in C# .

    GblIndexKey = UsernameTextBox.Text;
    
    OleDbConnection Conn = new OleDbConnection(GblConnection);
    
    ACCESSCommand = "SELECT * FROM operators";
    
    OleDbDataAdapter DataAdapter = new OleDbDataAdapter(ACESSCommand, Conn);
    
    DataSet DSetOPERATORS = new DataSet("OPERATORS");
    
    DataAdapter.Fill(DSetOPERATORS, "operators");
    
    DataTable DtblOPERATORS = new DataTable();
    
    DtblOPERATORS = DSetOPERATORS.Tables["operators"];
    
    DataRow DRowOPERATORS = null;
    
    
    foreach (DataRow DRowOPERATORS_loopVariable in DtblOPERATORS.Rows) {
    	DRowOPERATORS = DRowOPERATORS_loopVariable;
    	OPRNAME = DRowOPERATORS[0].ToString();
    
    	OPRPWORD = DRowOPERATORS[1].ToString();
    
    	OPRCAT = DRowOPERATORS[2].ToString();
    
    
    }
    

    I couldn't find any problem with your code except the result, i can see you are overwriting OPRNAME, OPRPWORD & OPRCAT in loop, so at the end of the loop you will have the last row's value. I believe this not what you want. Do you want to save this value somewhere ?, or what the purpose of this code? .

    About exception message, which line you are getting that? , Place your code in Try{ } Catch{} block and  see the error description.

    Check this sample code for Try..catch

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _
    System.EventArgs) Handles Button1.Click
      Try
       Process.Start("http://www.microsoft.com")
      Catch ex As Exception
       MsgBox("Can't load Web page" & vbCrLf & ex.Message)
      End Try
    End Sub
    
    
    Tuesday, January 11, 2011 5:23 AM
  • You need to change the syntax of your SQL statement as GblIndexKey is being evaluated as a parameter (you haven't declared as part of the Command):

    SELECT * FROM operators WHERE OPRNAME = '" & GblIndexKey & "'"
    
    


    You may also want to consider using a Command Parameter:

       Dim AccessCommand As New OleDbCommand("SELECT * FROM operators WHERE OPRNAME = ?", Conn)
    
       AccessCommand.CommandType = CommandType.Text
    
       AccessCommand.Parameters.AddWithValue("Param1", GblIndexKey)
      
       Dim DataAdapter As New OleDbDataAdapter
    
       DataAdapter.SelectCommand = AccessCommand
    
       Dim DSetOPERATORS As New DataSet("OPERATORS")
    
       DataAdapter.Fill(DSetOPERATORS, "operators")
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, January 11, 2011 2:31 PM
  • Thanks guy. That worked! There is no way I would have got any further without your help!

    As I have said before, it's not what the documentation tells you that is the problem, it's what it DOESN'T tell you!

    Regards Barry

    PS : Is there somewhere I can go on the internet to get more information on this issue?

     


    Captain Baz
    • Marked as answer by Captain Baz Tuesday, January 11, 2011 10:11 PM
    Tuesday, January 11, 2011 10:11 PM
  • Documentation is usually the best place to start plus sample Visual Basic applications, otherwise; ask questions in the forums.

    Also, you may want to mark the answers that helped so others viewing this thread know what worked for you.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 12, 2011 1:08 AM