Click here to Skip to main content
15,891,184 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am making a vb .net winform project that uses a sql server database. A user inputs the details of a person (firstName, lastName, company, contactNumber, etc.) visiting a factory into textboxes, hits the save details button, and this saves the data in the datatable. This works fine, the problem is the next part. The user is then redirected to another form where the input details are shown from the database. This works for the first record but not for any record input after that, I get an error that says "There is no row at position 'n'" and the following line of code is highlighted in the form_Load:

VB
txtFirstName.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(1))

It is telling me that any rows after row 0 are not there but I know they are because I have input them and they are showing up in the datatable in the sql server database manager.
I cannot sort this problem, any help with this would be greatly appreciated. I am attaching the rest of the code that's involved with this problem.
Thanks in advanced.

VB
Private Sub previousVisitor_Load(sender As Object, e As EventArgs) Handles MyBase.Load

connectionString = "Data Source=.\SQLExpress;InitialCatalog=Visitors;" & _
"IntegratedSecurity=True;MultipleActiveResultSets=True"

sqlVisitorDetails = "SELECT * FROM visitorDetails WHERE idNumber=@idNumber"

    sqlCon.Open()
    sqlCmd = New SqlCommand(sqlVisitorDetails, sqlCon)
    sqlCmd.Parameters.AddWithValue("@idNumber", txtIdNumber.Text)

    dtVisitorDetails = loadDtVisitorDetails()

    txtFirstName.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(1))
    txtLastName.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(2))
    txtCompany.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(3))
    txtContactNumber.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(4))
    txtCountryCode.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(5))
    txtEmail.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(7))

    sqlCmd.Dispose()
    sqlCon.Close()

End Sub

Private Function loadDtVisitorDetails() As DataTable
    Dim dtVisitorDetails As DataTable = Nothing

    sqlVisitorDetails = "SELECT * FROM visitorDetails WHERE idNumber=" & txtIdNumber.Text

    dtVisitorDetails = fillDtVisitorDetails(sqlVisitorDetails)

    Return dtVisitorDetails
End Function

Public Function fillDtVisitorDetails(ByVal sqlVisitorDetails As String) As DataTable

    Dim dtVisitorDetails As New DataTable
    Dim da As New SqlDataAdapter
    Dim conCmd As New SqlCommand

    conCmd.CommandText = sqlVisitorDetails

    da.SelectCommand = conCmd
    da.SelectCommand.Connection = sqlCon

    dtVisitorDetails.Columns.GetEnumerator()
    da.Fill(dtVisitorDetails)

    Return dtVisitorDetails
End Function
Posted
Comments
Wombaticus 18-Jul-14 6:07am    
You're only returning one row - that with the given ID number. You're confusing this with the row number, hence your error.
Member 10804519 18-Jul-14 6:20am    
Thanks I just realised that, thank you.

1 solution

I changed
VB
(txtIdNumber.Text) - 1)
to '0' in the line
VB
txtFirstName.Text = CStr(dtVisitorDetails.Rows(CInt(CDbl(txtIdNumber.Text) - 1)).Item(1))
and the lines for lastName, company, etc.
So the line now reads
VB
txtFirstName.Text = CStr(dtVisitorDetails.Rows(0).Item(1))
which has solved my problem. I thought that if I had '0' there that it would return row '0'(i.e. ID number 1) each time, but this is not the case as I am querying the ID number in my sql statement so it shows the ID number queried.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900