database help

[lauram340]

hey guys
i have a program that is to do with stock. I have a databse that has two tables in it that are linked with BANDID. the first table has BANDID and BANDNAME and the second has BANDID, TYPE and NUMBER.
BANDID is an autonumber.
What i need is when the user chooses band from a combobox and writes the name of the item in a text box, i want it to add to the second table with the same ID. i hope this makes sense.

[Hack]

Moved to database section.

A couple of questions:

What database are you using?

What platform are you creating the front end in (VB6 VB.NET Something else)?

[lauram340]

access and vb 6.0

[lauram340]

here is my program

i hope you can see what im trying to do

[szlamany]

Not too many of us are willing to download an entire program and look through it to find the code area that you are having questions about.

You might be better off doing a copy/paste into a post here - with the code in question - so we can direct you properly.

[lauram340]

it was only to show what im tryin to explain.
i havent got any code. i really need help with this!

[Al42]

Start by reading Beacon's ADO tutorial.

[LinXG]

Hi lauram340

First I'm going to suggest that you Index the BANDNAME in the Band table. I get the impression from the way your post is worded that the combobox only expects to have band names appear once per band.

There are probably several different ways you could tackle this. I will provide one that is reasonably easy to understand. To do this you will have to write two queries.

First: Get the BANDID. Open a Recordset to do this

yourSql = "SELECT BANDID WHERE BANDNAME = " & yourcomboBox.text 
yourBandID = yourRecordset("BANDID")

Second: Now that you have the BandID you can add the new record. Use the Excute method to do this.

yourSQL = "INSERT INTO Items (BANDID, ITEM) VALUES (" & yourBandID & ",'"  & yourText.Text & "')"
 
yourDBconnection.Execute (yourSQL)

If you are not familiar with recordsets and execute commmands, post back and I will provide a complete code sample.

[szlamany]

@linxg - did you mean to open a recordset in that first code snippet?

[LinXG]

Hi szlamany

His comboBox doesn't have the BANDID so he has to retrieve it and a recordset is how I would do this.

[szlamany]

My point being that if you look back at your post you don't appear to be actually opening yourRecordset - or am I missing something?

[lauram340]

hey
im not very familuar with it.
sorry. im really embarssed about how little i know.
do you know what i am trying to do?
Laura
x

[LinXG]

Hi szlamany;

Yes you're quite right. I'm assuming that she knows how to do all that but isn't sure quite how to tackle the problem.


Oops, now I see from a follow up post that she doesn't so I will post a full code example.

[lauram340]

LinXG do you have msn?
If so add me [email protected]
it may be easier
xxx

[LinXG]

Here is the sample I promised showing a two step method to do what you want. I haven't tested this code but I think it's good.

Dim myRecordSet As ADODB.Recordset
Dim myDBconn As New ADODB.Connection
Dim strConn as string 
Dim mySql As String
 
Dim myItem as String
Dim myBANDID as Long
 
strConn = "whatever you connection string is"
 
 
'------------------------------------------------
' First get the band ID
'------------------------------------------------
 
mySql = "SELECT BANDID FROM BAND WHERE BANDNAME = '" & yourCombobox.Text & "'"
 
Set myRecordSet = New ADODB.Recordset         
myRecordSet.Open mySql, StrConn, , , adCmdUnknown       ' Open the recordset
 
myBANDID = myRecordSet("BANDID")                        ' This is where we get the BAND ID
 
myRecordSet.Close
 
 
'------------------------------------------------
' Now add the Item
'------------------------------------------------
 
myItem = Text1.text                                      ' Item you will add
 
 
mySql = "INSERT INTO Items (BANDID, ITEM) VALUES (" & myBANDID & ",'"  & myItem & "')"
myDBconn.ConnectionString = strConn
myDBconn.Open 
myDBconn.Execute (mySql)                                ' This will add the record
myDBconn.Close                              ' Close when done

You could also try it in one query. Again no garantees

Dim myDBconn As New ADODB.Connection
Dim strConn as string 
Dim mySql As String
 
Dim myItem as String
Dim myBandName as string
 
 
strConn = "whatever you connection string is"
 
 
myBandName = yourComboBox.text                           ' The name of your band
myItem = Text1.text                                      ' Item you will add
 
mySQL = "INSERT INTO Items (BANDID, ITEM) SELECT [Band].BANDID, '" & myItem & "' "
mySQL = mySQL + "FROM [Band] LEFT JOIN Items ON [Band].BANDID = Items.BANDID "
mySQL = mySQL + "WHERE [Band].BANDNAME= '" & myBandName & "'"
 
 
myDBconn.ConnectionString = strConn
myDBconn.Open 
myDBconn.Execute (mySql)                                ' This will add the record
myDBconn.Close                              ' Close when done

I hope this helps you out.

[lauram340]

ahhhhhhh
this is all so hard.
i dont know what ADODB is or anything. this is what i got so far

Private Sub Form_Load()
Dim QuizRS As Recordset
Dim QuizQD As QueryDef
Dim i As Integer
 
 
Set QuizQD = DBName.QueryDefs("Table")
Set QuizRS = QuizQD.OpenRecordset
 
QuizRS.MoveFirst
For i = 0 To 9
cmbbandnames.Text = QuizRS!BANDNAME
QuizRS.MoveNext
Next
 
 
 
    
End Sub

and none of it works!
here all im trying to do is set the list in the database to a combobox

[LinXG]

Here is how to fill your combo box

Private Sub Form_Load()
    Dim MyRecordSet As ADODB.Recordset
    Dim MySQL As String
 
    MySQL = "SELECT BANDNAME FROM BAND"
    Set MyRecordSet = New ADODB.Recordset
    MyRecordSet.Open MySQL, yourConnectionString, , , adCmdUnknown
   
 
    Do While MyRecordSet.EOF = False
        yourComboBox.AddItem MyRecordSet("BANDNAME")
        MyRecordSet.MoveNext
    Loop
    
    MyRecordSet.Close
 
   End sub

[LinXG]

Hi Laura;

A couple of things that sometimes slip past people;

Your connection string will look something like this

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\yourDatabase.mdb;Persist Security Info=False"

Also make sure in your project that you have a Project Reference to Microsoft ActiveX Data Objects 2.x Library