help please!

[siomai]

hi everyone! this is my first time to post here and I'm glad to be a member of vbforums.com! i'm a beginner in programming and in vb.net platform...I'm having problems with comboboxes and textboxes.My problem is, i have a dbase in ms access and i bound the table productlist to my combobox which displays the productIDs. What i want to happen is that when a user selects a productID from the combobox, the other details in the productlist table corresponding to the id selected be displayed in the textboxes. I know this is simple but i just can't figure it out...there are some threads here that is somehow similar to my problem but I tried it and it didn't work...please help me! thanks in advance!

[brown monkey]

i've been playing around with the northwind database and categories table. and this is what i got. hope this helps.

Dim cn As New SqlConnection()
   Dim da As New SqlDataAdapter()
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      cn.ConnectionString = "user id=sa;password=password;initial catalog=northwind"
      cn.Open()
 
      Dim cmdtext As String = "select * from categories"
      da.SelectCommand = New SqlCommand(cmdtext, cn)
      Dim dr As SqlDataReader = da.SelectCommand.ExecuteReader
      While dr.Read
         ComboBox1.Items.Add(dr.GetInt32(0).ToString)
      End While
      dr.Close()
 
      ComboBox1.SelectedIndex = 0
   End Sub
 
   Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
      Dim cmdtext As String = "select * from categories where categoryid=" & ComboBox1.SelectedItem.ToString
      da.SelectCommand = New SqlCommand(cmdtext, cn)
      Dim dr As SqlDataReader = da.SelectCommand.ExecuteReader
      dr.Read()
 
      TextBox1.Text = dr.GetString(1)
      TextBox2.Text = dr.GetString(2)
      dr.Close()
   End Sub

i doubt this is not the best way. but sure for beginners like us. lol

[siomai]

thank you so much for replying! just wondering if you have a version for this code in ms access.=) if you don't this will also be fine...i'll look up for references to be able to convert your codes...thanks so much! take care!

[squirrelly1]

That will work for an access database too, or it should...

You just have to change the connection string...

"Provider = Microsoft Jet OLEDB 4.0; Data Source = " & DBLocation & ";Persist Security Info = False"

if you have a password, just change the last security part to

Jet OLEDB:Database Password = myPassWord

Laterz,

Squirrelly1

[squirrelly1]

My bad... I didn't look at the code completely...

Here's some sample code to connect to an Access Database:

First, reference the ADODB ActiveX controls...

Dim Conn as ADODB.Connection
Dim RS as ADODB.Recordset
 
Conn.Open ("Connection String") 'See above
RS.Open (SqlString, Conn, adOpenStatic, adLockPessimistic)

you could also do something like

RS = Conn.ExecuteSQL (SqlString)

or at least i think you can

Hope this helps some...

I never use binding to do my data access/manipulation...

you can just add the data to the combo box like so..

For i = 0 to RS.Recordcount - 1
   cboCombo.Items.Add (RS.Field("index or name of field"))
   RS.MoveNext
Next

Squirrelly1

[brown monkey]

does sqlblah_blah work in access? i don't know that

Dim cn As New OleDbConnection()
   Dim da As New OleDbDataAdapter()
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & Application.StartupPath & "\northwind.mdb"
      cn.Open()
 
      Dim cmdtext As String = "select * from categories"
      da.SelectCommand = New OleDbCommand(cmdtext, cn)
      Dim dr As OleDbDataReader = da.SelectCommand.ExecuteReader
      While dr.Read
         ComboBox1.Items.Add(dr.GetInt32(0).ToString)
      End While
      dr.Close()
 
      ComboBox1.SelectedIndex = 0
   End Sub
 
   Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
      Dim cmdtext As String = "select * from categories where categoryid=" & ComboBox1.SelectedItem.ToString
      da.SelectCommand = New OleDbCommand(cmdtext, cn)
      Dim dr As OleDbDataReader = da.SelectCommand.ExecuteReader
      dr.Read()
 
      TextBox1.Text = dr.GetString(1)
      TextBox2.Text = dr.GetString(2)
      dr.Close()
   End Sub

provided you have northwind.mdb in your project_folder\bin directory.

[brown monkey]

here's your northwind.mdb mate. just unzip.

[squirrelly1]

If you were asking if Access supports SQL, yes it does...

Squirrelly1

[brown monkey]

sort of. i mean i want to know can i use sqlconnection() object in access and other sqlother_objects. i don't know much about this thing. is it possible? and if so, how? thanks...

[siomai]

thank you very much...I'll try everything that you've said...thanks again!

[mendhak]

IN Access, as in MS-Access forms, or WITH Access?

If you mean WITH access, then yes, you'd use it just the same with all databases. THe only major difference would be your connection string. Do you know ADO.NET?

[brown monkey]

MS Access. can i use sqlconnection() and other sqlobjects? my wild guess is that MS Access is for oledb [and other ole provided] mysql is odbc. help me mate. i don't know much about this. thanks.

[siomai]

i was successful in connecting my access database but i had this error as the form is being loaded:

An unhandled exception of type 'System.InvalidCastException' occurred in system.data.dll

Additional information: The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable.

by the way, the data that i'm trying to retrieve have currency datatypes as well as string datatypes. i hope you could still help me.thanks in advance.

[mendhak]

Originally posted by brown monkey
MS Access. can i use sqlconnection() and other sqlobjects? my wild guess is that MS Access is for oledb [and other ole provided] mysql is odbc. help me mate. i don't know much about this. thanks.

I don't believe that it is possible to use .NET code in MS-Access forms. I think it still uses VBA. So the answer would be no, not possible.

[mendhak]

Originally posted by siomai
i was successful in connecting my access database but i had this error as the form is being loaded:

An unhandled exception of type 'System.InvalidCastException' occurred in system.data.dll

What line of code is highlighted?

Also, put a Try...Catch block around it and show the message that comes up.

[brown monkey]

[mendhak] thanks mate.

[siomai] somedatareader.getvalue(index).tostring(). just try to do the object getter for data reader and cast it to string. hope this helps.

[siomai]

[mendhak] it's on cmbID.text.add(dr.GetInt32(0).Tostring)

[siomai]

[brown monkey] is this a declaration? sorry i'm really new in programming. please bear with me. What i did is i searched for the format you gave me and change the getInt32(0).tostring to getvalue(0).tostring. Is that right?

[mendhak]

Why not just us a

dr.GetString(0)

instead? You're converting to string anyways!

Also, make sure you check for a null value!