INSERT,SELECT,INSERT into DB

M-RaH

Active member
Joined
Feb 9, 2006
Messages
25
Location
Netherlands
Programming Experience
Beginner
:cool: Hi folks ...

Can someone help me with this one ???

VB.NET:
[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
:cool:
 

Hikidar

New member
Joined
Mar 23, 2006
Messages
4
Programming Experience
1-3
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

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
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

Active member
Joined
Feb 9, 2006
Messages
25
Location
Netherlands
Programming Experience
Beginner
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

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Okay...

your problem is this:
VB.NET:
"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

Active member
Joined
Feb 9, 2006
Messages
25
Location
Netherlands
Programming Experience
Beginner
the solution

:D This is the final version of my code en it works perfectly ;)

VB.NET:
[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]

:eek:
 
Top Bottom