Visual Basic Express fill combo box from SQL qry

[mnorton]

Hi - I am a pretty remedial visual studio express user. I recently understood how to do cascading comboboxes using the binding sources of the related tables, eg tbl 1 Make, Table 2 model, so as eg only ford models displayed in cbo2 if Ford selected in cbo1 - thanks to Beth Massi!

However I'm having some trouble with the following scenario. I have a 'tblUser' table with fields: Name, Name_id, Firstname and Surname. I have a tblAsset' table with fields: 'AssetNo' and 'Description'. On form I have 4 controls: 2 x cbo and 2 x txt. Now, there is no direct link between Asset and User tables, because our administrators don't put the current user User IDs or names in the Asset container in AD!...at least not in their own field... Just some daft free text field called 'Description', which has the current user of an asset as part of the description string, eg "SOH-12345, Room 2b, Melanie Jones". So what I successfully did in access was use a LIKE statement to get (eg) all the 'Jones' entries in a combobox (using the Surname field on the form) then select the correct one, and use the related AssetNo. (The reason for this was so I could use the resulting AssetNo as the value to remote control a PC using Remote Access/MSRA in Windows). So basically the SQL says something like SELECT tblAsset.Description WHERE Description LIKE "" & txtSurname & "".

I'm a bit stumped, and I guess it's a lack of knowledge about creating a dataset from this query, i'm not sure. What I need to do is use cboUser to select a user (Eg Mel Jones), that user's Surname appears in txtSurname. Then cboUserAsset needs to display all the 'Jones's' entries in the 'Description' field of the Asset table, and I select the one that says "SOH-54321, Room 2b, Mel Jones". When I select that, txtAsset is populated with "SOH-54321", and I can use that to run a connect command in MSRA.

So it looks like cboUser would fire off the SQL string and populate cboUserAsset, but I'm not sure how to get that data in that control. Sorry this is long-winded. I'm looking all over, and will continue to, but can anyone shed some light on what I'm not 'getting'? Many Thanks.

(For Info, I use VB express 10, and connect to my tables using the programme datasources function.)

[Dili1234]

Hope you you knpw to create the connection string

Dim myCommand As SqlCommand
  myCommand = New SqlCommand("SELECT * FROM Major ", myConnection)
        Dim da As New SqlDataAdapter
        SQLdr = myCommand.ExecuteReader()
        While SQLdr.Read()
            cmbMajorI.Items.Add(SQLdr("MajorName"))
        End While

[mnorton]

Hi,

Thanks for looking at this for me. I got a few errors when playing with this, I wonder if it's to do with the version or language - I'm using visual basic 2010 express. For the SqlCommand bit I'm getting error: Type 'SqlCommand' is not defined,and the same for SqlDataAdapter, also for SQLdr it says SQLdr is not declared - what is SQLdr in the code above? Am I able to 'define' SqlCommand somehow, or should I be using a different syntax?

I'm sorry, this is probably something I should be able to fill the blanks on, but I'm pretty much starting out with VB after getting relatively competent at Access, which is obviously much easier and more forgiving then the more grown up languages!

I think you pointed out to me what it seems I was failing to understand, I have to create a connection string or dataset, whereas I was just thinking I could put a sql string into the indexchanged of cbo1, then the data would just 'create' and could be referenced in cbo2. It now seems that's not the case...I have lots to do!

[Dili1234]

Your Most welcome. You have to write this coding in module

Module Module1
    Public connectionString As String = "Data Source=HP-PC;Initial Catalog=Vb_table;user ID =sa; password= 1234"


End Module

In the form............

Imports System.Data
Imports System.Data.SqlClient



Public Class Form2
    Dim connectionstring As String = Module1.connectionString
    Dim sqlconn As New SqlConnection(connectionstring)
    Public sqlDataset As New DataSet
    Dim myCommand1 As SqlCommand
    Dim cmd As SqlCommand

    Public SQLdr As SqlDataReader
    Dim myConnection As SqlConnection
    Dim nxtst As String
    Dim sqlcmd As New SqlCommand
    'Dim dep As String
    Dim tbStudent As DataTable
    Public n As Integer
    'Dim sname As String
    Dim myCommand2 As SqlCommand
    Dim myCommand As SqlCommand
    Public st As String = "ST"
    Public no As Integer
    Dim cmstudent As New SqlClient.SqlCommand
    Dim adstudent As New SqlDataAdapter
    Dim ds1 As New DataSet
    Dim dr1 As DataRow

This pictue how to put this coding..............

[mnorton]

ThankYou! This is really good of you. The kids have commandeered me for the next couple of days, so I'll let you know how I get on as soon as I get to it.

[mnorton]

Thanks, I've sorted it now, you helped me think a little more about what I was doing. In a nutshell, I created the dataset from the sql string, then I created the table adapter then it was just a matter of connecting the controls to the newly created table. I'm using visual studio so you can use various menus to do this. I think I should really know how to do this in raw code, but for now VS is helping me contextualise what I'm doing. Many Thanks.