Re: Insert date into a datetime column in Visual Basic 2008

Gum wrote on Mon, 11 May 2009 22:50:01 -0700:

I noted your canned 'wrong discussion group'. I am using ADO.net so I
guess
I should go to the ADO.net discussion group although I think the issues
are more or less much the same. There were no answers to this issue
there.

The cast... was my attempt to try anything after using the 'dte'
directly but nonetheless your response indicated that I needed to
understand the use of literal strings in sql commands. As an
axiomatic student, I would be grateful if you send me a link to edify.
I present a more complete code to permit a better understanding of the
issue:

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String
Dim dte As System.DateTime


dte = DateTime.Now.ToString cmdString = "Insert
insertTB([date], [close]) Values ('dte', 'Fnumber')"

All that does is to try to insert the string "dte" into your [date] column.
That will not work. 'dte' has nothing to do with your dte variable as it's
wholly contained within the SQL executed on the server. You need to look
into using a parameterised query to pass the value of dte into the SQL, and
I'd also suggest that you avoid converting it to a string as this will also
possibly cause problems depending on the format used - just pass the value
as a date and let the data access layer handle it in the correct way. Is
Fnumber also a variable from your code? If so, same issue - you're trying to
insert the literal string 'Fnumber' into the [close] column.

conn = New SqlConnection("Data Source=WINALL;Initial
Catalog=DataFill;Integrated Security=True")
cmd = New SqlCommand(cmdString, conn)
conn.Open()

cmd.ExecuteNonQuery()
conn.Close()

I found a 'solution' at (albeit an access database which could be
easily adjusted to SQL via the importation of the relevant library ie.
System.Data.SQLClient and changes to some of the statements eg
OleDbCommand)
:

MSaccessDateInsertInVB11082005000729AM/MSaccessDateInsertInVB.aspx>

Essentially, the same issues are involved, that is how to insert a date
into a datetime db. However, I was not successful in copying the
example, possibly due to the terse explanations given.

I'd suspect it's more to do with you not understanding how to pass a
variable into SQL. You need to do something like this pseudo-code (I use
ADO, not ADO.Net, so I can't provide a working example for you).

dte = Now
Fnumber = 1

cmdString = "Insert insertTB([date], [close]) Values (?, ?)"
parameterArray = (dte,Fnumber)

cmd.Execute cmdString, parameterArray


So you set the variable values (dte and Fnumber), then add placeholders in
cmdString for them (the question marks), and then execute the command
passing in the parameter array (this is the format used by ADO, I don't know
what you'd do in ADO.Net).

I'd suggest you Google for articles about using paramterised queries with
ADO.Net for working examples that you can then insert into your code.

Dan

"Bob Barrows" wrote:

Gum wrote:
I need to insert a date (i.e., "2009/05/12 02:46" or
system.datetime.tostring) into a datetime column using ADO. I got
an error saying that there is a problem converting the string into
datetime.
I am using a SQL string "INSERT insertTB([date],[close])
values('cast(dte as datetime)', 'Fnumber')"
How can I do this insert using the most efficient method?

Why would you think the string 'cast(dte as datetime)' could be
inserted into a datetime column? You do realize that's a literal
string, don't you? I think more of your code is necessary to enable
someone to solve your problem.

Are you using ADO or ADO.Net? If the latter:
***canned wrong-newsgroup reply************************
There was no way for you to know it (except maybe by browsing through
some of the previous questions in this newsgroup before posting yours
- always a recommended practice) , but this is a classic (COM-based)
ADO newsgroup.
ADO.Net bears very little resemblance to classic ADO so, while you
may be lucky enough to find a dotnet-knowledgeable person here who
can answer your question, you can eliminate the luck factor by
posting your question to a group where those dotnet-knowledgeable
people hang out. I suggest microsoft.public.dotnet.framework.adonet.
..******************************************************************


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"



.

Relevant Pages

  • Re: Compact SQL Database and ADO
    ... I've since made this change to the code for 'N' in front of the string. ... What happens when you change the working SQL statements to the ones I ... I haven't used SQL CE with ADO myself, I do still write a lot of VB6 apps ...
    (microsoft.public.data.ado)
  • Re: Source size and how to change
    ... It should not truncate sql string. ... It must be happening in the ADO call because the actual ...
    (microsoft.public.data.ado)
  • Getting Started
    ... I'm trying to get to grips with MS SQL and ADO at the same time. ... got a working method of adding a record to a table with unique ... function TMSSQLImporter.AddLocation(const Location: string): Integer; ...
    (borland.public.delphi.database.ado)
  • Executing stored procedures as CommandType=Text
    ... I don't think it is problem of sql query. ... You might need to check the connection ot connection ... >'p3'" that execute on a VB 6 application and ADO. ... >I receive the SQL requests in the string format with all ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)