Solved

vb.net dataset errors connecting to SQL server and displaying fields on form.

Posted on 2016-07-20
17
29 Views
Last Modified: 2016-07-20
New to SQL and VB.Net but have used Access and VBA for years. I have a couple of questions regarding the code below. I get an error saying there is no row at position 0. I don't even know what that means, let alone what to do about it. Also, I have 3 comboboxes on the form I am trying to populate. They fill automatically when the form loads, but I want them to select the item that represents the specific record in the database. For example: I have 10 stores in the dropdown, but the record I am trying to retrieve is associated with the ID 5. It's DisplayMember is the name of the store, but its ValueMember is an ID. I want the combobox to select the the ID associated with the record in SQL and then display the DisplayMember correctly. Can I do this? Here is the code:
Public Sub psFillDataset()
        Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            sSQL = "Select * from tvwLeads where numLeadID=" & numSelected & " order by numLeadID"
            MsgBox(numSelected)
            Dim dbadp As New SqlDataAdapter(sSQL, sqlComm)
            Dim dTable As New DataTable
            dbadp.Fill(dTable)
            dbadp.Dispose()
            Me.txtSource.DataSource = dTable
            Me.txtType.Text = ("strType").ToString()
            Me.txtClient.Text = ("numClient").ToString()
            Me.txtLast.Text = dTable.Rows(0)("strLast").ToString()
            Me.txtFirst.Text = dTable.Rows(0)("strFirst").ToString()
            Me.tmTO.Text = dTable.Rows(0)("tmTO").ToString()
            Me.dtTO.Text = dTable.Rows(0)("dtTO").ToString()
            Me.tmCT.Text = dTable.Rows(0)("tmCTC").ToString()
            Me.dtCT.Text = dTable.Rows(0)("dtCTC").ToString()
            Me.chkBad.Text = dTable.Rows(0)("ysnBad").ToString()
            Me.txtManager.Text = dTable.Rows(0)("strManager").ToString()
            Me.cboSales.Text = dTable.Rows(0)("strName").ToString()
            Me.txtNotes.Text = dTable.Rows(0)("strNotes").ToString()
            Me.txtEmail.Text = dTable.Rows(0)("strResponse").ToString()
            Me.txtID.Text = dTable.Rows(0)("numLeadID").ToString()
            Me.txtSource.DisplayMember = "strSource"
            Me.cboVehicle.DisplayMember = "strLeadType"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
0
Comment
Question by:smm6809
  • 9
  • 8
17 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721225
>> I get an error saying there is no row at position 0

that means that your select query returns no row and you are trying to update the first row (index = 0)
0
 

Author Comment

by:smm6809
ID: 41721239
Thanks, I was using the wrong table. More importantly to me is what I'm trying to do with the comboboxes. Possible?
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41721254
how are you filling the combo boxes with all the various available values? They need to be fed somewhere (often in the form load). Once fed, you can set the current value like this:
Me.cboVehicle.SelectedValue = dTable.Rows(0)("yourvehicleidvariable")

Open in new window


check https://msdn.microsoft.com/en-us/library/system.windows.forms.listcontrol.selectedvalue(v=vs.110).aspx
0
 

Author Comment

by:smm6809
ID: 41721288
Thank you so much! One last question. These two fields are not displaying the value in the textbox. They are displaying strType and numClient. However, the information is in the database and the field names are correctly spelled. Any thoughts?

Me.txtType.Text = ("strType").ToString()
 Me.txtClient.Text = ("numClient").ToString()
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721305
because you simply set those values!

your code surely needs to be:

Me.txtType.Text = dTable.Rows(0)("strType").ToString()
 Me.txtClient.Text = dTable.Rows(0)("numClient").ToString()

Open in new window

0
 

Author Comment

by:smm6809
ID: 41721314
Haha so I did! How do I choose the best solution from you when they all helped? And thank you!
0
 

Author Comment

by:smm6809
ID: 41721368
Some of my comboboxes still not populating, but they're just filled in with items, not connected to the database. Do I still handle them the same with selected value? If so, they aren't working. Also, I have 2 checkboxes that arent populating. The database shows them as 0s and 1s. Are they handled differently?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721533
how and where do you feed those combos?

as for checkboxes, you need special code:
Me.chkBad.Checked = (dTable.Rows(0)("ysnBad").ToString() = "1")

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:smm6809
ID: 41721567
Thanks. This is how I fill the comboboxes on the ones that aren't populating:
cboVehicle.Items.Clear()
            cboVehicle.DisplayMember = "Text"
            cboVehicle.Items.Add(New With {.Text = "NEW"})
            cboVehicle.Items.Add(New With {.Text = "USED"})

            cboContact.Items.Clear()
            cboContact.DisplayMember = "Text"
            cboContact.Items.Add(New With {.Text = "LE"})
            cboContact.Items.Add(New With {.Text = "L/M & LE"})
            cboContact.Items.Add(New With {.Text = "Not Determined"})
            cboContact.Items.Add(New With {.Text = "YES"})
            cboContact.Items.Add(New With {.Text = "NO"})

Then I use Me.cboContact.SelectedValue = dTable.Rows(0)("strContact").ToString()
Me.cboVehicle.SelectedValue = dTable.Rows(0)("strVehicle").ToString()
to put them back into the form.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721572
in this case, you can simplify the syntax to:
cboVehicle.Items.Clear()
cboVehicle.Items.Add("NEW")
cboVehicle.Items.Add("USED")

Open in new window


then you will be able to use:
Me.cboVehicle.Text = dTable.Rows(0)("strLeadType").tostring

Open in new window

0
 

Author Comment

by:smm6809
ID: 41721588
Perfect, but checkboxes still not working. If I'm understanding you correctly, I place that code in the same spot as all the other code we worked on and if it is a 1 in the database, it should show as checked on the form. If so, it isn't showing as such. Also, if there is a null value will I get the message that there is no row at position 0? I am getting it for certain records that I know have some data but not all of the fields. How do you handle that?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721626
NULLs are a special value and you need to process them specially! You won't get an exception saying that there is no row at position 0 because this is really when there are no rows in your datatable.

To check for NULLs, you should:
if dTable.Rows(0)("strType") isnot nothing then
     Me.txtType.Text = dTable.Rows(0)("strType").ToString()
end if

Open in new window


For checkboxes, you can try:
if dTable.Rows(0)("ysnBad") is nothing orelse dTable.Rows(0)("ysnBad").ToString() = "0") then
   Me.chkBad.Checked = false
else
   Me.chkBad.Checked = true
end if

Open in new window

0
 

Author Comment

by:smm6809
ID: 41721659
That code looks exactly right but now it checks the boxes are always checked. Ugh, I want to go back to VBA. Do you know a good book that gets into this level of coding?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721662
what do you get if you do :
messagebox.show(dTable.Rows(0)("ysnBad").ToString()) 

Open in new window

chances are that you won't see 0 and 1 like you told me before!
0
 

Author Comment

by:smm6809
ID: 41721704
Got error before running: argument not specified for parameter "Prompt" of "Publci Function MsgBox(Prompt as Object,[Buttons as MsgboxStyle=ApplicationModal],Title As Object=Nothing]) as MsgBoxResult
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41721717
can you copy here your code? because mine is not using msgbox!
0
 

Author Comment

by:smm6809
ID: 41721730
Re-pasted what you sent. I get true, so I fixed the code. I appreciate your time today.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now