Solved

Syntax error converting datetime from character string

Posted on 2004-10-17
6
254 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

17 Experts available now in Live!

Get 1:1 Help Now