Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
vb.net dataset errors connecting to SQL server and displaying fields on form.
Posted on 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").
Me.txtFirst.Text = dTable.Rows(0)("strFirst")
Me.tmTO.Text = dTable.Rows(0)("tmTO").ToS
Me.dtTO.Text = dTable.Rows(0)("dtTO").ToS
Me.tmCT.Text = dTable.Rows(0)("tmCTC").To
Me.dtCT.Text = dTable.Rows(0)("dtCTC").To
Me.chkBad.Text = dTable.Rows(0)("ysnBad").T
Me.txtManager.Text = dTable.Rows(0)("strManager
Me.cboSales.Text = dTable.Rows(0)("strName").
Me.txtNotes.Text = dTable.Rows(0)("strNotes")
Me.txtEmail.Text = dTable.Rows(0)("strRespons
Me.txtID.Text = dTable.Rows(0)("numLeadID"
Me.txtSource.DisplayMember
Me.cboVehicle.DisplayMembe
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
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").
Me.txtFirst.Text = dTable.Rows(0)("strFirst")
Me.tmTO.Text = dTable.Rows(0)("tmTO").ToS
Me.dtTO.Text = dTable.Rows(0)("dtTO").ToS
Me.tmCT.Text = dTable.Rows(0)("tmCTC").To
Me.dtCT.Text = dTable.Rows(0)("dtCTC").To
Me.chkBad.Text = dTable.Rows(0)("ysnBad").T
Me.txtManager.Text = dTable.Rows(0)("strManager
Me.cboSales.Text = dTable.Rows(0)("strName").
Me.txtNotes.Text = dTable.Rows(0)("strNotes")
Me.txtEmail.Text = dTable.Rows(0)("strRespons
Me.txtID.Text = dTable.Rows(0)("numLeadID"
Me.txtSource.DisplayMember
Me.cboVehicle.DisplayMembe
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
8
17 Comments
Active 1 day ago
>> 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)
that means that your select query returns no row and you are trying to update the first row (index = 0)
Thanks, I was using the wrong table. More importantly to me is what I'm trying to do with the comboboxes. Possible?
Active 1 day ago
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:
check https://msdn.microsoft.com/en-us/library/system.windows.forms.listcontrol.selectedvalue(v=vs.110).aspx
Me.cboVehicle.SelectedValue = dTable.Rows(0)("yourvehicleidvariable")
check https://msdn.microsoft.com/en-us/library/system.windows.forms.listcontrol.selectedvalue(v=vs.110).aspx
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()
Me.txtType.Text = ("strType").ToString()
Me.txtClient.Text = ("numClient").ToString()
Active 1 day ago
because you simply set those values!
your code surely needs to be:
your code surely needs to be:
Me.txtType.Text = dTable.Rows(0)("strType").ToString()
Me.txtClient.Text = dTable.Rows(0)("numClient").ToString()
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?
Active 1 day ago
how and where do you feed those combos?
as for checkboxes, you need special code:
as for checkboxes, you need special code:
Me.chkBad.Checked = (dTable.Rows(0)("ysnBad").ToString() = "1")
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.SelectedValu
Me.cboVehicle.SelectedValu
to put them back into the form.
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.SelectedValu
Me.cboVehicle.SelectedValu
to put them back into the form.
Active 1 day ago
in this case, you can simplify the syntax to:
then you will be able to use:
cboVehicle.Items.Clear()
cboVehicle.Items.Add("NEW")
cboVehicle.Items.Add("USED")
then you will be able to use:
Me.cboVehicle.Text = dTable.Rows(0)("strLeadType").tostring
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?
Active 1 day ago
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:
For checkboxes, you can try:
To check for NULLs, you should:
if dTable.Rows(0)("strType") isnot nothing then
Me.txtType.Text = dTable.Rows(0)("strType").ToString()
end if
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
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?
Active 1 day ago
what do you get if you do :
messagebox.show(dTable.Rows(0)("ysnBad").ToString())
Got error before running: argument not specified for parameter "Prompt" of "Publci Function MsgBox(Prompt as Object,[Buttons as MsgboxStyle=ApplicationMod
Featured Post
The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 6 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.