By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,326 Members | 881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,326 IT Pros & Developers. It's quick & easy.

Insert into database

P: n/a
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é
Apr 5 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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?...3-eb8b44af0137
The sample uses Dutch dates.

I hope this helps,

Cor
"Frederik Vanderhaeghe" <fr******************@gmail.com> schreef in bericht
news:%2****************@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é

Apr 5 '06 #2

P: n/a
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]" <no************@planet.nl> wrote in message
news:Ot**************@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?...3-eb8b44af0137
The sample uses Dutch dates.

I hope this helps,

Cor
"Frederik Vanderhaeghe" <fr******************@gmail.com> schreef in
bericht news:%2****************@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é


Apr 5 '06 #3

P: n/a
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
Apr 5 '06 #4

P: n/a
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 Morton" <ak*@in-press.co.uk.invalid> wrote in message
news:e4**************@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

Apr 5 '06 #5

P: n/a
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
Apr 5 '06 #6

P: n/a
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","12 345")
..... repeat as required for additional parameters

"Frederik Vanderhaeghe" <fr******************@gmail.com> wrote in message
news:%2****************@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é

Apr 5 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.