Solved

Syntax error converting datetime from character string

Posted on 2004-10-17
6
255 Views
Last Modified: 2010-04-23
I have web app and can't get past this error. I have datagrid with select column and the event fires this code which should place data from datagrid to sql database.
This code is working for all columns except the date time column.

Dim dgmyid As Integer
Dim dgmydate As String
Dim sql As String

dgmyid = mydatagrid.SelectedItem.Cells(1).Text
dgmydate = mydatagrid.SelectedItem.Cells(2).Text

SqlConnection1.Open()

Dim myinsertCommand As SqlCommand = New SqlCommand

myinsertCommand.Connection = SqlConnection1

sql = "insert into mytable(myid, mydate) values (@myid, @mydate); select myid, mydate from mytable where (dataID = @@IDENTITY)"

myinsertCommand.CommandText = sql

myinsertCommand.Parameters.Add("@myID", System.Data.SqlDbType.VarChar).Value = dgmyid
myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = dgmydate

myinsertCommand.ExecuteNonQuery()

SqlConnection1.Close()

Thanks for your help,
Donnie

 

       
0
Comment
Question by:Donnie4572
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
Timbo87 earned 500 total points
ID: 12333696
Try changing this line:

myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = dgmydate

To

myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = DateTime.Parse(dgmydate)

One question though, why do you have a SELECT statement after the INSERT when you're executing a non-query?
0
 
LVL 12

Author Comment

by:Donnie4572
ID: 12334141
This works! Very Good Timbo87! I have worked all day on this. (new to sql and vb)

I assumed that the ExecuteNonQuery needed the select statement. Since your post I have tried the above code without the select and got incorrect syntax near from.
Could you post any easier way to write this?
sql = "insert into mytable(myid, mydate) values (@myid, @mydate); select myid, mydate from mytable where (dataID = @@IDENTITY)"

Thanks,
0
 
LVL 15

Expert Comment

by:Timbo87
ID: 12334162
Simply removing that select shouldn't cause a syntax error. Does your sql string look like this?

sql = "insert into mytable(myid, mydate) values (@myid, @mydate)"
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Author Comment

by:Donnie4572
ID: 12334213
Yes.
Only I have the from where

sql = "insert into mytable(myid, mydate) values (@myid, @mydate) FROM mytable WHERE (dataID = @@IDENTITY)"
0
 
LVL 15

Expert Comment

by:Timbo87
ID: 12334396
I don't believe that's a valid SQL query. I think this is what you want:

sql = "insert into mytable(myid, mydate) values (@myid, @mydate)"

0
 
LVL 12

Author Comment

by:Donnie4572
ID: 12334599
You are right again. I removed the "select from and where"  it works well. My way was working but thanks for showing me the right way.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now