Insert into database

5 Apr 2006 8:57 AM

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é

5 Apr 2006 9:15 AM

Cor Ligthert [MVP]

Frederik,

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

Have a look to the overloaded tostring(iformatprovider)
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

Show quoteHide quote

"Frederik Vanderhaeghe" <frederikvanderhae***@gmail.com> schreef in bericht
news:%23M5v67IWGHA.4484@TK2MSFTNGP02.phx.gbl...
> 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é
>
>

5 Apr 2006 9:28 AM

Frederik Vanderhaeghe

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é

Show quoteHide quote

"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
news:OtidOFJWGHA.4132@TK2MSFTNGP04.phx.gbl...
> Frederik,
>
> The most change you have is that the date goes wrong.
>
> Have a look to the overloaded tostring(iformatprovider)
> 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" <frederikvanderhae***@gmail.com> schreef in
> bericht news:%23M5v67IWGHA.4484@TK2MSFTNGP02.phx.gbl...
>> 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é
>>
>>
>
>

5 Apr 2006 9:58 AM

Andrew Morton

Frederik Vanderhaeghe wrote:

> 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.

<snip>

> "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???

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

5 Apr 2006 10:05 AM

Frederik Vanderhaeghe

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é

Show quoteHide quote

"Andrew Morton" <a**@in-press.co.uk.invalid> wrote in message
news:e4myGeJWGHA.3328@TK2MSFTNGP02.phx.gbl...
> Frederik Vanderhaeghe wrote:
>> 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.
> <snip>
>> "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???
>
> 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
>

5 Apr 2006 10:12 AM

Cor Ligthert [MVP]

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

5 Apr 2006 5:07 PM

Jim Hughes

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

Show quoteHide quote

"Frederik Vanderhaeghe" <frederikvanderhae***@gmail.com> wrote in message
news:%23M5v67IWGHA.4484@TK2MSFTNGP02.phx.gbl...
> 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é
>
>