INSERT,SELECT,INSERT into DB

[M-RaH]

Hi folks ...

Can someone help me with this one ???

[SIZE=2][COLOR=#008000]
'Insert in Tabel Clienten
[/COLOR][/SIZE][SIZE=2]myDatabase.setQuery("INSERT INTO Clienten (Naam,Voornaam,Tussenvoegsel,Voorletter,Geslacht,Geboortejaar," & _
"Straatnaam,Huisnummer,Toevoegsel,Postcode,Woonplaats)" & _
"VALUES('" & TxtBoxNaam1.Text & "','" & TxtBoxVoornaam1.Text & "','" & TxtBoxTussenvoegsel1.Text & "','" & TxtBoxVoorletters1.Text & "'" & _
"'" & CmbBoxGeslacht1.Text & "','" & CmbBoxGeboortejaar.Text & "','" & TxtBoxStraat.Text & "','" & TxtBoxHuisnummer1.Text & "'" & _
"'" & TxtBoxToevoegsel1.Text & "','" & TxtBoxPostcode1.Text & "','" & TxtBoxWoonplaats1.Text & "')")
[/SIZE][SIZE=2][COLOR=#008000]'Select ClientID vanuit Clienten Get clientID from DB
[/COLOR][/SIZE][SIZE=2][COLOR=red]myDatabase.setQuery("SELECT ClientID From Clienten WHERE Naam = '" & TxtBoxNaam1.Text & "'")[/COLOR]
[/SIZE][SIZE=2][COLOR=#008000]'Insert in Tabel Criteria [/COLOR][/SIZE][SIZE=2]myDatabase.setQuery("INSERT INTO Criteria (ClientID, Geslacht, [Leeftijd Min], [Leeftijd Max], Woonplaats)" & _
"Values ('[COLOR=red]ClientID[/COLOR]','" & CmbBoxGeslacht2.Text & "','" & CmbBoxMinLeeftijd.Text & "','" & CmbBoxMaxLeeftijd.Text & "','" & TxtBoxWoonplaats2.Text & "')")
[/SIZE][SIZE=2][COLOR=#008000]'WRITE/UPDATE/DELETE DATA and EXECUTE STDP
[/COLOR][/SIZE][SIZE=2]myDatabase.executeQuery()
[/SIZE]

i want to write some data 2 the DB .... in the first INSERT ...

for the 2nd INSERT i need the ClientID generated by the DB when I INSERTED the first QUERY

So i made a SELECT query to get the ClientID ( thats's probably not the wright way, but i was just trying ... )

Then the 2nd Query wants to insert the data ...

But I get a error ... with the ClientID

" 1Syntax error converting the varchar value 'ClientID' to a column of data type int."

Can onyone tell how to do this right ???

thanx

 

[albertkhor]

your ClientID field in database is Integer?

 

[Hikidar]

Yep. It looks like your ClientID field in table Criteria is set to integer, and you are passing it text. You need to change the ClientID field to varchar or char.

 

[TechGnome]

Actualy what you need to do is extract the value from the SELECT query first, then you can pass it in to the other query.

-tg

 

[M-RaH]

solution

yeah ...
i extracted the clientID created by the DB when inserting the first query with Select ... and then inserted it with the 2nd insert ...

thanx for the help all of u

 

[TechGnome]

Okay...

your problem is this:

"Values ('ClientID','" & CmbBoxGeslacht2.Text

You're inserting the literal string ClientID in your SQL.... you don't want that... you need to sub that with your variable value.... just like you did with all the text boxes there too....

-tg

 

[M-RaH]

the solution

This is the final version of my code en it works perfectly

[SIZE=2][COLOR=#008000]
'Insert in View Clienten
[/COLOR][/SIZE][SIZE=2]myDatabase.setQuery("INSERT INTO Clienten (Naam, Voornaam, Tussenvoegsel, Voorletter, Geslacht, Geboortejaar," & _
"Straatnaam, Huisnummer, Toevoegsel, Postcode, Woonplaats)" & _
"VALUES('" & TxtBoxNaam1.Text & "','" & TxtBoxVoornaam1.Text & "','" & TxtBoxTussenvoegsel1.Text & "'," & _
"'" & TxtBoxVoorletters1.Text & "','" & CmbBoxGeslacht1.Text & "','" & CmbBoxGeboortejaar.Text & "'," & _
"'" & TxtBoxStraat.Text & "','" & TxtBoxHuisnummer1.Text & "','" & TxtBoxToevoegsel1.Text & "'," & _
"'" & TxtBoxPostcode1.Text & "','" & TxtBoxWoonplaats1.Text & "')")
myDatabase.executeQuery()
[/SIZE][SIZE=2][COLOR=#008000]'Select ClientID vanuit Clienten ( this part SELECTs the ID which is created in the DB for the inserted Name [COLOR=#4169e1][COLOR=blue]TxtBoxNaam1[/COLOR] [/COLOR])
[/COLOR][/SIZE][SIZE=2][COLOR=red]myDatabase.setQuery("SELECT ClientID From Clienten WHERE Naam = '" & TxtBoxNaam1.Text & "'")[/COLOR]
[/SIZE][SIZE=2][COLOR=#008000]'houdt waarde vast ( opbergen ) give clientID the value
[/COLOR][/SIZE][SIZE=2]clientID = myDatabase.readValue()
[/SIZE][SIZE=2][COLOR=#008000]'Insert in Tabel Criteria Bij spatie als hierbeneden tussen 2 woorden dan haakjes
[/COLOR][/SIZE][SIZE=2]myDatabase.setQuery("INSERT INTO Criteria (ClientID, Geslacht, [Leeftijd Min], [Leeftijd Max], Woonplaats)" & _
"Values ([COLOR=red]'" & ClientID & "'[/COLOR],'" & CmbBoxGeslacht2.Text & "','" & CmbBoxMinLeeftijd.Text & "','" & CmbBoxMaxLeeftijd.Text & "','" & TxtBoxWoonplaats2.Text & "')")
[/SIZE][SIZE=2][COLOR=#008000]'WRITE/UPDATE/DELETE DATA and EXECUTE STDP
[/COLOR][/SIZE][SIZE=2]myDatabase.executeQuery()
[/SIZE]