Problem with INSERT Statement.

I have an insert statement thats causing me some issues.
Dim strname As String
Dim myname As String
myname = My.User.Name
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(" & myname & ")"
How do I do the syntax correctly for it to be inserted into my SQL Server correctly?

Thanks,
Tom

I'm not going to answer your question directly because what you are doing is a great security risk. You risk your database coming under a SQL Injection attack so I am not going to give you the quick fix. I am going to tell you how to fix your code AND plug the gaping security hole that you have.
Your code injects the string myname directly into the SQL statement. This should be replaced with a parameter so that the attack surface of the application is reduced.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(@.myname)"
Dim cmd as SqlCommand
cmd = New SqlCommand(strname, connection)
cmd.Parameters.Add("@.myname", myname)
cmd.ExecuteNonQuery()
I have replaced your injection with a parameter name (@.myname). Then in the command object I add a parameter with the same name and give it the value it needs.
Finally, here is an article aboutSQL Injection attacks and how to prevent them.

|||Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram|||

potturi_rp wrote:

Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram

Thats the same thing I had.

But I follow on not using the pure injection method.

Thanks,

Tom

Problem with Date Function ?

I insert date into table using getdate() function and that works fine.

Now from my query when I try to compare with it and returns nothing.

Thats how the date is stored in the table : 6/4/2007 1:46:57 PM

Now If I do

SELECT * FROM Sometable

WHERE date = getdate() ///Nothng returned

SELECT * FROM sometable

WHERE date = '6-4-2007' // Nothing is returned

How to Fix this issue ?

This has to do with the way that datetime data is stored. Try this:

Code Snippet

select * from someTable

where date >= convert(datetime(convert(varchar(10), getdate(), 101)))

and date < convert(datetime(convert(varchar(10), getdate()+1, 101)))

or

Code Snippet

select * from someTable

where date >= '6-4-2007'

and date < convert(datetime, '6-4-2007')+1

The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two.

Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer.

|||

Hello Kent,

That will return me all dates greater than that specific date. I want the exact date, is there any way to change how the date is stored ? As i'm creating my SQL programatically, it is hard to have everything done at that level.

|||

Sorry my bad, I made a mistake in dates. That works !!!

Is there any way to fix this at Database Level....

|||

Harsimrat,

Datatime data type can include also time part, so if you need to select rows you will have to specify the time part you want.

select *

from sometable

where date >= '20070604' and date < '20070605'

-- or

select *

from sometable

where date between '2007-06-04T10:00:00' and '2007-06-04T18:59:59.997'

If you used function getdate() to insert the rows, then remember that this function returns current date and time and it will not be useful to match rows inserted in the past unless both operations are executed in the same time.

-- this could work

declare @.t table (c1 datetime)

insert into @.t values(getdate())

select * from @.t where c1 = getdate()

-- but not this

declare @.t table (c1 datetime)

insert into @.t values(getdate())

waitfor delay '00:00:00.003'

select * from @.t where c1 = getdate()

AMB

|||

Adding to Kent's explanition, when you store a datetime value as you have, then you will not get a match unless the criteria has the EXACT same datetime value -up to the milliseconds.

So to find all values for a particular date, you need to look for values since midnight, and up to the next midnight. Kent provided one set of criteria that accomplishes that goal, here is another.

--For today

Code Snippet

WHERE ( [Date] >= dateadd( day, datediff( day, 0, getdate() ), 0 )
AND [Date] < dateadd( day, datediff( day, 0, getdate() + 1 ), 0)

)

p.s., You really shouldn't name your columns and tables with 'RESERVED WORDS'. [Date] is a reserved word that has special meaning in SQL Server. Refer to Books Online, Topic: 'Reserved Words'

|||

When I run this query:

Code Snippet

declare @.someTable table
( date datetime
)
insert into @.someTable
select '6/3/7 15:00' union all
select '6/3/7 23:59:59.997' union all
select '6/4/7' union all
select '6/4/7 8:00' union all
select '6/4/7 23:59:59.997' union all
select '6/5/7'
--select * from @.someTable

select * from @.someTable
where date >= convert(datetime, (convert(varchar(10), getdate(), 101)))
and date < convert(datetime, (convert(varchar(10), getdate()+1, 101)))

I get this result:

Code Snippet

date
--
2007-06-04 00:00:00.000
2007-06-04 08:00:00.000
2007-06-04 23:59:59.997

Is that result incorrect?