Insert into database

[Frederik Vanderhaeghe]

Yo,

I have a problem inserting into my database. When i try the following:
"INSERT INTO TBL_Bestanden_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
VALUES
('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-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_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
VALUES(Id, DocumentNummmer , KlantNummer, KlantNaam, DocumentDatum,
DocumentBedrag, DocumentType)" it doesn't work.

What am I doing wrong???

Fré

[Cor Ligthert [MVP]]

Re: Insert into database

Frederik,

The most change you have is that the date goes wrong.

Have a look to the overloaded tostring(iforma tprovider)
http://msdn2.microsoft.com/en-us/library/29dxe1x2.aspx

Better is to use however parameters, have a look at this sample on our
website for that.

http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

The sample uses Dutch dates.

I hope this helps,

Cor


"Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> schreef in bericht
news:%23M5v67IW GHA.4484@TK2MSF TNGP02.phx.gbl. ..[color=blue]
> Yo,
>
> I have a problem inserting into my database. When i try the following:
> "INSERT INTO TBL_Bestanden_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
> VALUES
> ('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-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_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
> VALUES(Id, DocumentNummmer , KlantNummer, KlantNaam, DocumentDatum,
> DocumentBedrag, DocumentType)" it doesn't work.
>
> What am I doing wrong???
>
> Fré
>
>[/color]

[Frederik Vanderhaeghe]

Re: Insert into database

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é

"Cor Ligthert [MVP]" <notmyfirstname @planet.nl> wrote in message
news:OtidOFJWGH A.4132@TK2MSFTN GP04.phx.gbl...[color=blue]
> Frederik,
>
> The most change you have is that the date goes wrong.
>
> Have a look to the overloaded tostring(iforma tprovider)
> http://msdn2.microsoft.com/en-us/library/29dxe1x2.aspx
>
> Better is to use however parameters, have a look at this sample on our
> website for that.
>
> http://www.vb-tips.com/default.aspx?...3-eb8b44af0137
> The sample uses Dutch dates.
>
> I hope this helps,
>
> Cor
>
>
> "Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> schreef in
> bericht news:%23M5v67IW GHA.4484@TK2MSF TNGP02.phx.gbl. ..[color=green]
>> Yo,
>>
>> I have a problem inserting into my database. When i try the following:
>> "INSERT INTO TBL_Bestanden_Z oeken
>> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES
>> ('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-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_Z oeken
>> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES(Id, DocumentNummmer ,
>> KlantNummer, KlantNaam, DocumentDatum, DocumentBedrag, DocumentType)" it
>> doesn't work.
>>
>> What am I doing wrong???
>>
>> Fré
>>
>>[/color]
>
>[/color]

[Andrew Morton]

Re: Insert into database

Frederik Vanderhaeghe wrote:[color=blue]
> I have a problem inserting into my database. When i try the following:
> "INSERT INTO TBL_Bestanden_Z oeken
> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES
> ('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-01-2006','125','VF ')"
> it works.[/color]
<snip>[color=blue]
> "INSERT INTO TBL_Bestanden_Z oeken
> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES(Id, DocumentNummmer ,
> KlantNummer, KlantNaam, DocumentDatum, DocumentBedrag, DocumentType)"
> it doesn't work.
> What am I doing wrong???[/color]

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\\$file name";.

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

[Frederik Vanderhaeghe]

Re: Insert into database

I found the answer myself:

"INSERT INTO TBL_Bestanden_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
VALUES('" & Id & "','" & DocumentNummer & "','" & KlantNummer & "','" &
KlantNaam & "','" & DocumentDatum & "','" & DocumentBedrag & "','" &
DocumentType & "')"

Fré

"Andrew Morton" <akm@in-press.co.uk.inv alid> wrote in message
news:e4myGeJWGH A.3328@TK2MSFTN GP02.phx.gbl...[color=blue]
> Frederik Vanderhaeghe wrote:[color=green]
>> I have a problem inserting into my database. When i try the following:
>> "INSERT INTO TBL_Bestanden_Z oeken
>> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES
>> ('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-01-2006','125','VF ')"
>> it works.[/color]
> <snip>[color=green]
>> "INSERT INTO TBL_Bestanden_Z oeken
>> (Id,docnr,klnr, klnaam,datum,be drag,type) VALUES(Id, DocumentNummmer ,
>> KlantNummer, KlantNaam, DocumentDatum, DocumentBedrag, DocumentType)"
>> it doesn't work.
>> What am I doing wrong???[/color]
>
> 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\\$file name";.
>
> 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
>[/color]

[Cor Ligthert [MVP]]

Re: Insert into database

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

[Jim Hughes]

Re: Insert into database

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_Z oeken
(Id,docnr,klnr, klnaam,datum,be drag,type)
VALUES(@Id,@Doc umentNummmer, @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

"Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> wrote in message
news:%23M5v67IW GHA.4484@TK2MSF TNGP02.phx.gbl. ..[color=blue]
> Yo,
>
> I have a problem inserting into my database. When i try the following:
> "INSERT INTO TBL_Bestanden_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
> VALUES
> ('12345678-1234-1234-1234-123456789123',' VF-1234567','12345 ','Frederik','1 5-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_Z oeken (Id,docnr,klnr, klnaam,datum,be drag,type)
> VALUES(Id, DocumentNummmer , KlantNummer, KlantNaam, DocumentDatum,
> DocumentBedrag, DocumentType)" it doesn't work.
>
> What am I doing wrong???
>
> Fré
>
>[/color]