Results 1 to 8 of 8

Thread: "No row at position 0"

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    7

    "No row at position 0"

    I am using an SQL function to insert, select and update but when I enter the "dt.Rows(0)(0)" to get the resultsID from the database I get the error "there is no row at position 0"

    I am new to the forums so I am not sure what code you want me to show you. Let me know and I can screenshot it for you!

    Thanks!

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: "No row at position 0"

    Well, if there isn't data then there isn't data. I've never seen THAT be wrong, so the real question is WHY is there no data.

    The first sentence of the question has me a bit confused. "Using a SQL function" sounds like a stored procedure, but insert, select, and update makes it sound more like using a dataadapter, and that would be consistent with getting a datatable. You then talk about getting the resultsID, which sounds like a scalar value, and a datatable wouldn't necessarily be the best way to get a scalar value.

    However, if you feel that dt.Rows should have some rows, how about showing how you are filling dt? That might be the place to start.
    My usual boring signature: Nothing

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: "No row at position 0"

    Quote Originally Posted by reloaded2 View Post
    I can screenshot it for you!
    Word of advise... don't... the forum does weird things to images posted, especially screenshots, that make them unreadable. Instead copy and paste the code... then with it highlighted, click the "#" in the tool bar, or the "VB" button... that will put [code][/code] or [highlight=vb][/highlight] tags around it preserving the look of it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    7

    Re: "No row at position 0"

    This is the db module that uses the data adapter as Shaggy Hiker said

    Code:
    Module db
        Public Function runSQL(ByVal query As String)
    
            Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=login.mdb")
            Dim dt As New DataTable
            Dim DataAdapter As OleDb.OleDbDataAdapter
    
            Connection.Open()
            DataAdapter = New OleDb.OleDbDataAdapter(query, Connection)
            dt.Clear()
            DataAdapter.Fill(dt)
            Connection.Close()
            Return dt
        End Function
    End Module

    And this are my SQL statements:
    Code:
     Public Sub storeResult()
            If count = 1 Then
                runSQL("INSERT into resultsTable (Username, TopicID, Question, Ans" & count & ") VALUES ('" & frmLogin.username_public & "', '" & topicID & "', '" & questionText & "' , '" & ans & "')")
    
            ElseIf 1 < count <= 7 Then
                dt = runSQL("SELECT IDResults FROM resultsTable WHERE Username = '" & frmLogin.username_public & "' AND TopicID = '" & topicID & "' ORDER BY IDResults DESC")
    
    
    
                resultID = dt.Rows(0)(0)
                runSQL("UPDATE resultsTable SET Ans" & count & " ='" & ans & "' WHERE IDResults = '" & resultID & "'")
            End If
    
            If count = 7 Then
                runSQL("UPDATE resultsTable SET TotalScore '" & ans & "' WHERE IDResults = '" & resultID & "'")
            End If
        End Sub
    End Module
    This is the code that gets highlighted when the error message comes up:
    Code:
     resultID = dt.Rows(0)(0)
    I want to select the primary key "IDResults" as it is called in my database so I can set it to the variable resultID. I am quite new to VB so this is what I can provide. I hope this helps!

  5. #5
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: "No row at position 0"

    The first potential issue I see is that your function has no defined return type. That may not be THE problem, but that is something that should be specified regardless, so change that first and test.

    Code:
    Public Function runSQL(ByVal query As String) As DataTable
    Are you running with both Option Explicit and Option Strict On? In the Form/Class/Module where the storeResult sub exists, how and where is dt declared?

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    7

    Re: "No row at position 0"

    Yea I tried that and still the issue persists.

    To delare dt I just did it at the start of the module with
    Code:
     Dim dt As DataTable

  7. #7
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: "No row at position 0"

    Ok, so next steps would be to verify that your SQL Select statement is being populated the way you think it should be. There are many ways of debugging code like this, but one of the easier ways is to simply add a messagebox statement at the beginning of the runSQL Function and have it display query to make sure the Query String looks correct. You might see something like the Query saying:
    Code:
    "...WHERE Username = '(Empty string inside of single quotes)' ..."
    or
    Code:
    "...AND TopicID = '(Empty string inside of single quotes)' ..."
    in which case you need to figure out why the variables you are using to populate the search parameters are empty, for example.

    And at the end of the runSQL function, right before the Return statement, add a messagebox statement to display dt.Rows.Count. That will tell you if the DataTable is getting populated inside the runSQL function.

    And so on. Basically, you need to take ownership of stepping through the process of what is happening in your code and find out where things are not functioning the way you expect them to.

  8. #8
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: "No row at position 0"

    Also, you never answered whether you have Option Explicit and Option Strict turned on. It's not clear what frmLogin.username_public is or how it was declared, what count is or how it was declared, what topicID is or how it was declared, or what resultID is or how it was declared. I'm unable to assist further than I have above, so any further questions you may have will need to be answered by others.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width