Insert into database

  • Thread starter Thread starter Frederik Vanderhaeghe
  • Start date Start date
F

Frederik Vanderhaeghe

Yo,

I have a problem inserting into my database. When i try the following:
"INSERT INTO TBL_Bestanden_Zoeken (Id,docnr,klnr,klnaam,datum,bedrag,type)
VALUES
('12345678-1234-1234-1234-123456789123','VF-1234567','12345','Frederik','15-01-2006','125','VF')"
it works.

But I get my values from a method:
Public Sub DB_Wegschrijven(ByVal Id As String, ByVal KlantNummer As String,
ByVal KlantNaam As String, ByVal DocumentNummer As String, ByVal
DocumentDatum As String, ByVal DocumentBedrag As String, ByVal DocumentType
As String)

So when i try the following:

"INSERT INTO TBL_Bestanden_Zoeken (Id,docnr,klnr,klnaam,datum,bedrag,type)
VALUES(Id, DocumentNummmer, KlantNummer, KlantNaam, DocumentDatum,
DocumentBedrag, DocumentType)" it doesn't work.

What am I doing wrong???

Fré
 
Hi,

I didn't help, the date is not a problem, in the database it is also a
String, so it doesn't matter what is given in.

Fré
 
Frederik said:
I have a problem inserting into my database. When i try the following:
"INSERT INTO TBL_Bestanden_Zoeken
(Id,docnr,klnr,klnaam,datum,bedrag,type) VALUES
('12345678-1234-1234-1234-123456789123','VF-1234567','12345','Frederik','15-01-2006','125','VF')"
it works.
Click to expand...
"INSERT INTO TBL_Bestanden_Zoeken
(Id,docnr,klnr,klnaam,datum,bedrag,type) VALUES(Id, DocumentNummmer,
KlantNummer, KlantNaam, DocumentDatum, DocumentBedrag, DocumentType)"
it doesn't work.
What am I doing wrong???
Click to expand...

As Cor wrote, the best way is to use parameters.

However, look at the string of values: notice how you have quotes around
each value in the first example and not in the second example. VB doesn't
"know" that you are referring to variables inside the string in the way that
perl understands $file = "$folder\\$filename";.

Also, if you want to insist on doing it the inferior way then you should
check that each value does not contain a single quote (other dangerous
characters may be available) because then it will break, which could extend
to doing a DROP TABLE or even deleting all files on the server.
http://en.wikipedia.org/wiki/Sql_injection

Andrew
 
I found the answer myself:

"INSERT INTO TBL_Bestanden_Zoeken (Id,docnr,klnr,klnaam,datum,bedrag,type)
VALUES('" & Id & "','" & DocumentNummer & "','" & KlantNummer & "','" &
KlantNaam & "','" & DocumentDatum & "','" & DocumentBedrag & "','" &
DocumentType & "')"

Fré
 
Andrew,

I see it now as well, I missed something we see always when no parameters
are used

("'" & Id "' '" & DocumentNummmer etc

It looks for me always unreadble.

Cor
 
The variables "Id, DocumentNummmer, KlantNummer, KlantNaam, DocumentDatum,
DocumentBedrag, DocumentType" are not expanded to their literal values
prior to sending them to the database.

Your insert string should use a parameterized query.

dim sql as string = "INSERT INTO TBL_Bestanden_Zoeken
(Id,docnr,klnr,klnaam,datum,bedrag,type)
VALUES(@Id,@DocumentNummmer, @KlantNummer, @KlantNaam, @DocumentDatum,
@DocumentBedrag, @DocumentType)"

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)
cmd.Parameters.AddWithValue("@Id", "12345678-1234-1234-1234-123456789123")
cmd.Parameters.AddWithValue("@DocumentNummmer","12345")
..... repeat as required for additional parameters
 
Back
Top