Solved

Syntax error converting datetime from character string

Posted on 2004-10-17
6
258 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 43
Get number of Files in Directory and Sub Directories 2 52
VB.Net - MemoryMappedFiles - Confirm receipt 2 35
VB.NET 2008 Winforms Signing 13 41
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

679 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