-
Dec 29th, 2017, 10:12 AM
#1
Thread Starter
New Member
"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!
-
Dec 29th, 2017, 10:27 AM
#2
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
-
Dec 29th, 2017, 12:37 PM
#3
Re: "No row at position 0"
Originally Posted by reloaded2
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
-
Dec 30th, 2017, 06:36 PM
#4
Thread Starter
New Member
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!
-
Dec 30th, 2017, 06:51 PM
#5
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?
-
Dec 31st, 2017, 02:14 PM
#6
Thread Starter
New Member
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
-
Dec 31st, 2017, 02:38 PM
#7
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.
-
Dec 31st, 2017, 02:48 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|