Bad insert command?

Question

User-911157248 posted

 My insert statment is crying foul when I use a variable, is this allowed?

zfinal has a value, I have even tested it with a response.write.

SqlDataSource2.InsertCommand = "insert into bills1 (job,bpac,rek) values ('New   ','    ',zfinal)"

Variable 'ZFINAL' is not found.

Answer 1

User1014708013 posted

And where is zfinal defined? In your ASP.NET code or in SQL Server somewhere?

Imar

Answer 2

User-911157248 posted

For purposes of testing, I defined it immediately before defing the insertcommand

zfinal ="gorp"

Answer 3

User1014708013 posted

So it's  a VB variable, right? If so, it doesn't autmatically transition to SQL Server. With your current code, the variable is looked for within the context of the SQL statement in SQL Server.

One way is to concatenate the value to the SQL statement in your VB code

InsertCommand = "..... " & zfinal

However, this is a *very* bad practice as it opens your code for SQL injection if the value of zfinal comes from an untrusted source (e.g. the user). Instead, you should use parameterized queries:

http://www.google.com/search?hl=en&source=hp&q=.NEt+parameterized+queries+parameters&btnG=Google+Search&aq=f&aqi=&aql=&oq=&gs_rfai=

Hope this helps,

Imar

Answer 4

User-911157248 posted

Well it's actually a Foxpro table using vfpoledb . For this project I decided to insert a blank record, let the user edit it, then in the update I use paramters. For some reason, it won't take the variable which IS defined!

 

 

Answer 5

User1014708013 posted

In that case, can you show more code where you declare and assign the variable, and assign the variable's value to the SQL command?

Imar

Answer 6

User-1632285901 posted

hi

its the right syntax if zfinal is a variable

"INSERT INTO  bills1 (job,bpac,rek) VALUES('New','','" & zfinal & "')"

paste it in your code and try 

it always worked for me

Answer 7

User-1199946673 posted

"INSERT INTO  bills1 (job,bpac,rek) VALUES('New','','" & zfinal & "')"

paste it in your code and try 

it always worked for me

Really? try this

zfinal = "let's try this"

And then

"INSERT INTO  bills1 (job,bpac,rek) VALUES('New','','" & zfinal & "')"

That's one of the reasons you should parameterized queries

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access 

Answer 8

User-911157248 posted

not quite, still getting syntax error. 

Answer 9

User1014708013 posted

Again, please post your relevant code so we can take a look.....

Answer 10

User-1632285901 posted

give us how you get your variable zfinal to help u

Answer 11

User-911157248 posted

Here's relevant code, :

Dim zfinal As String

zfinal = "gorp"

SqlDataSource2.InsertCommand = "insert into bills1 (jon,bpac,rek) values ('New ','bpac', zfinal)"

Variable 'ZFINAL' is not found.

Answer 12

User1014708013 posted

Looks like you haven't seen or understood all the previous posts....

With the code you just posted, you pass the literal text zfinal to the database as it just a part of th SQL string. In your database, zfinal has no meaning and ths you get an error.

Instead, as explained earlier, you need to concatenate the .NET variable's value to your SQL statement (dangerous) or use parameters.

Regards,

Answer 13

User154448021 posted

for vb.net:

"insert into bills1 (job,bpac,rek) values ('New   ','    '," & zfinal & ")"

Answer 14

User-911157248 posted

right, I understand that, but I was getting syntax errors in the suggestions given. 

This is an exercise project so not using parameters for the insert(update yes) but still hope to make it work.

 

Answer 15

User1014708013 posted

Since the zfinal is a string, you need to wrap its value in single quotes like this:

"insert into bills1 (job,bpac,rek) values ('New   ','    ','" & zfinal & "')"

Hope this helps,

Imar

Answer 16

User-911157248 posted

Actually, I figured it at last, quotes were needed.

 

values ('New ',' '," & "'" & zfinal & "'" & ")"

ugh!

Answer 17

User-1199946673 posted

This is an exercise project so not using parameters for the insert

 

I really don't understand why for an exercise project you're not using parameters. What's the point in exercising bad practices, which hopefully you'll not use in real projects?

Answer 18

User-911157248 posted

your point is valid, I use all parameters in the real world, but just not this exercise.

 

 

Answer 19

User1014708013 posted

>> Actually, I figured it at last, quotes were needed.

Wasn't that exactly what I showed in my post (albeit with slightly different syntax)?

Imar